data-storage.py

import sqlite3, os, hashlib, secrets

#finds the current directory the program is running in
file_dir = os.path.dirname(os.path.realpath(__file__))

#defines the file paths for all the needed databases to make it easier to use later
file_names = {"database1"         : file_dir + "\\databases\\users.db"}

#defines the some sql commands to CREATE the tables later
cursor_create_table = {"create_database"       : ("""CREATE TABLE Users (Username TEXT,
                                                                            Password TEXT,
                                                                            Salt TEXT,
                                                                            Plain_Password TEXT,
                                                                            Email TEXT,
                                                                            Account_Type TEXT,
                                                                            Active INT);""")}

#defines the some SELECT sql commands in order to gather data from the table to make it easier to call them later
cursor_display_commands = {"select_users"      : ("SELECT * FROM Users ORDER BY Username"),
                           "get_user"      : ("SELECT * FROM Users WHERE Username = ?")}

#defines the some UPDATE sql commands in order to make it easier to call them later
cursor_update_commands = {"update_users"       : ("UPDATE Users SET Passowrd = ? WHERE Username = ?")}

#defines the some INSERT sql commands in order to make it easier to call them later
cursor_add_commands = {"new_row"     : ("INSERT INTO Users VALUES(?, ?, ?, ?, ?, ?, ?)")}

#defines the values that will be put into the sql databases later
table_details = {"table1" : [["bigBoss1", "", "", "passWord1!", "bigboss@danube.com", "admin", 1],
                            ["averageJoe1", "", "", "Password123?", "joe@gmail.com", "user", 1],
                            ["businessman101", "", "", "password", "businessman@company.com", "seller", 1],
                            ["headOfDanube", "", "", "5p]B9m,u3O", "ceo@danube.com", "admin", 1],
                            ["averageJoe2", "", "", "4Wa]60h2,E", "steve@gmail.com", "user", 1],
                            ["salesman1", "", "", "E7e788>*@}", "salesrep@company.com", "seller", 1],
                            ["danubEmployee1", "", "", "96W9xs`o%n", "employee@danube.com", "admin", 0],
                            ["averageJoe3", "", "", "p2P94|\yLR", "bob@gmail.com", "user", 0],
                            ["trader", "", "", "J86£rHpDE3", "trader@company.com", "seller", 0]]}


#executes a given sql query on the given the file and then returns the results to the user
def getDetails(file_path, cursor_command):
    connection = sqlite3.connect(file_path)             #creates a connection to the given database
    cursor = connection.cursor()                        #creates a cursor that can be used to execute SQL queries
    
    extracted_data = cursor.execute(cursor_command)     #executes the sql query on the given database
    data = extracted_data.fetchall()                    #stores all the data returned from the sql query in a 2d array
    
    connection.commit()                                 #commits changes to the database
    connection.close()                                  #closes the connection to the database
    
    return data                                         #returns the data out of the function

#executes a given sql query on the given the file and parameters then returns the results to the user
def getDetailsWithParam(file_path, cursor_command, parameter):
    connection = sqlite3.connect(file_path)             #creates a connection to the given database
    cursor = connection.cursor()                        #creates a cursor that can be used to execute SQL queries
    
    extracted_data = cursor.execute(cursor_command, parameter)      #executes the sql query on the given database
    data = extracted_data.fetchall()                                #stores all the data returned from the sql query in a 2d array
    
    connection.commit()                                 #commits changes to the database
    connection.close()                                  #closes the connection to the database
    
    return data                                         #returns the data out of the function

#executes a given sql query on the given the file
def addDetails(file_path, cursor_command, details):
    connection = sqlite3.connect(file_path)             #creates a connection to the given database
    cursor = connection.cursor()                        #creates a cursor that can be used to execute SQL queries
    
    cursor.execute(cursor_command, details)             #executes the sql query on the given database
    
    connection.commit()                                 #commits changes to the database
    connection.close()                                  #closes the connection to the database
    
    return True

#executes a given sql query on the given the file
def executeSQLCommand(file_path, cursor_command):
    connection = sqlite3.connect(file_path)             #creates a connection to the given database
    cursor = connection.cursor()                        #creates a cursor that can be used to execute SQL queries
    
    cursor.execute(cursor_command)                      #executes the sql query on the given database
    
    connection.commit()                                 #commits changes to the database
    connection.close()                                  #closes the connection to the database
    
    return True

#defines a function to create the necessary sql databases
def createTable():
    if not os.path.exists(file_names["database1"]):                                             #if the walks database does not exist
        executeSQLCommand(file_names["database1"], cursor_create_table["create_database"])      #runs the sql query in order to create it
        for details in table_details["table1"]:                                                 #loops through all the rows of data that need entering into the database
            details[2] = generateSalt()                                                         #gets a unique salt
            details[1] = hashPassword(details[3], details[2])                                   #gets the hashed password
            addDetails(file_names["database1"], cursor_add_commands["new_row"], details)        #executes the sql query

def generateSalt():
    return secrets.token_hex(16)    #generates 16 bytes of random data

def hashPassword(password, salt):
    return hashlib.sha256((salt + password).encode()).hexdigest()   #hashes the salted password

def validateLogin(test_user, test_pass):
    found_user = getDetailsWithParam(file_names["database1"], cursor_display_commands["get_user"], (test_user,))    #searches database for user
    
    if not found_user:                                          #checks if user exists
        return "no user found"
    
    found_user = found_user[0]                                  #reformats result
    test_pass_hashed = hashPassword(test_pass, found_user[2])   #hashes the inputted password with the found users salt

    if test_pass_hashed == found_user[1]:                       #checks to see if the hashed passwords match
        return True
    else:
        return False



createTable()       #creates the table

database = getDetails(file_names["database1"], cursor_display_commands["select_users"]) #displays all the data in the table to the user

for row in database:                            #displays the database for debug purposes
    print(row)
print("\n")

test_user = input("username: ")                 #takes input for username
test_pass = input("password: ")                 #takes input for password

result = validateLogin(test_user, test_pass)    #validates the login attempt
print("logged in:", result)                     #prints the result