def tableInfo(self):
try:
cur = self.db_conn.cursor()
cur.execute("SELECT * FROM pragma_table_info(\'" + self.table_name + "\')")
return cur.fetchall()
except sqlite3.Error as e:
return LoggingObject("ERROR", "table Info failed: " + str(e))
except Exception as e:
return LoggingObject("ERROR", str(e))
def setURead(self, id, status):
try:
cur = self.db_conn.cursor()
cur.execute("UPDATE " + self.table_name + " SET Uread=\"" + status + "\" WHERE policyID=" + id)
return "updated"
except sqlite3.Error as e:
return LoggingObject("ERROR", "set uread column failed: " + str(e))
except Exception as e:
return LoggingObject("ERROR", str(e))
def standardRequest(self, column, value):
try:
cur = self.db_conn.cursor()
cur.execute("SELECT * FROM " + self.table_name + " WHERE " + column + "=\"" + value + "\"")
return cur.fetchall()
except sqlite3.Error as e:
return LoggingObject("ERROR", "standard request failed: " + str(e))
except Exception as e:
return LoggingObject("ERROR", str(e))
def timeRequest(self, start, end):
try:
cur = self.db_conn.cursor()
cur.execute("SELECT * FROM " + self.table_name + " WHERE submissionT BETWEEN " + start + " AND " + end)
return cur.fetchall()
except sqlite3.Error as e:
return LoggingObject("ERROR", "time request failed: " + str(e))
except Exception as e:
return LoggingObject("ERROR", str(e))
def checkToken(self, user, token):
cur = self.user_auth.cursor()
cur.execute("SELECT accesstoken FROM auth_table WHERE username=\"" + user + "\"")
if(token == cur.fetchone()[0]):
LoggingObject("GRANTED", "user " + user + " accessed resource")
return True
else:
LoggingObject("DENIED", "user " + user + " used bad token")
return False
def initUserDatabase(self,passwords_file_location):
#this will be the database key names
keynames = "(username,passwordhash,accesstoken,submissionT,Uread)"
self.user_auth = sqlite3.connect(':memory:')
cur = self.user_auth.cursor()
#builds initial table, can re use sanitizeRows function and Uread will update on token request
cur.execute("CREATE TABLE auth_table (username,passwordhash,accesstoken,submissionT,Uread);")
#handle passwords.txt file opening
try:
f = open(passwords_file_location, "r")
#read line by line, add user to a database and hash the password, create a random hash for the user also
for line in f.readlines():
#NOTE: could all be in one line but would not be readable
username = line[9:line.find(",")]
pwdhashobj = hashlib.sha256(("CAKE" + (line[line.find("Password:"******"CAKE").encode('utf_8'))#TD: COMPLEX SALTS
passwordhash = pwdhashobj.hexdigest()
acchshobj = hashlib.sha256(("CAKE" + username + str(random.randint(100, 100000)) + "CAKE").encode('utf_8'))#<-TOKEN SCHEME
accesstoken = acchshobj.hexdigest()
row = self.sanitizeRow(username + "," + passwordhash + "," + accesstoken)
cur.execute("INSERT INTO auth_table " + keynames + " VALUES (" + row + ");")
except Exception as e:
LoggingObject("ERROR", str(e))
def handle(self, control_message):
if (control_message != ""):
print("place holder for something")
pass
try:
#initiallise conn when next connection is made
ssock, addr = self.sock.accept()
self.conn = self.context.wrap_socket(ssock, server_side=True)
recieved = str(self.conn.recv(4096))
LoggingObject("REQUEST", recieved)
#parse arguments from connection to get list of commands and thier parameters for database operations
return self.req.parameterise(self.req.sanitise(recieved))
except ssl.SSLError as e:
LoggingObject("ERROR", str(e))
except Exception as e:
LoggingObject("ERROR", "handling query: " + str(e))
def createDatabase(self, tablename, csv_data_arr):
try:
#create table in memory and connect to it, move cursor to start position and write CSV row data to table line by line
self.db_conn = sqlite3.connect(':memory:')
cur = self.db_conn.cursor()
cur.execute("CREATE TABLE " + tablename + "(" + csv_data_arr[0] + ",submissionT,Uread);")
#loops through each row of data and loads onto table after some sanitisation
for rows in range(1, len(csv_data_arr)-1):
row = self.sanitizeRow(csv_data_arr[rows])
cur.execute("INSERT INTO " + tablename + " (" + csv_data_arr[0] + ",submissionT,Uread) VALUES (" + row + ");")
self.db_conn.commit()
LoggingObject("INIT","database created with " + str(self.countRows(cur, tablename)) + " rows")
except sqlite3.Error as e:
LoggingObject("ERROR", "initialisation failed: " + str(e))
except Exception as e:
return LoggingObject("ERROR", str(e))
def __init__(self, addr, port, certpath):
self.req = HandleRequest
self.sock = socket.socket()
self.sock.bind((addr, port))
self.sock.listen(5)
self.context = ssl.SSLContext(ssl.PROTOCOL_TLS_SERVER)
self.context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
self.context.load_cert_chain(certpath + "localhost.pem",
certpath + "localhost.key")
self.context.options |= ssl.OP_NO_TLSv1 | ssl.OP_NO_TLSv1_1
self.context.set_ciphers(
'EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH')
self.conn = None
LoggingObject("INIT", "SSL server created")
def usertokenRequest(self, user, passwordhash):
try:
#find users password hash
cur = self.user_auth.cursor()
cur.execute("SELECT passwordhash FROM auth_table WHERE username=\"" + user + "\"")
#if password hash is correct then respond with token
hashvalue = self.time() + cur.fetchone()[0]
pwdhashobj = hashlib.sha256(hashvalue.encode('utf_8'))
if(pwdhashobj.hexdigest() == passwordhash):
cur.execute("SELECT accesstoken FROM auth_table WHERE username=\"" + user + "\"")
return cur.fetchone()[0]
else:
return LoggingObject("DENIED", "credentials not found")
except sqlite3.Error as e:
return LoggingObject("DENIED", "credentials not found")
except Exception as e:
return LoggingObject("DENIED", "credentials not found")#probably dont give a verbose error
def close(self):
LoggingObject("CLOSE", "closing service")
self.conn.close()