def match_user(username, password):
"""
Check if user credentials are correct, return if exists
:param username: The user attempting to authenticate
:param password: The corresponding password
:type username: str
:type password: str
:return: user
"""
db.connect()
cursor = db.cursor()
query = (
"SELECT userid, username FROM users WHERE username = %s AND password = %s"
)
user = None
try:
cursor.execute(query, (username, password))
users = cursor.fetchall()
if len(users):
user = users[0]
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return user
def get_user_name_by_id(userid):
"""
Get username from user id
:param userid: The id of the user
:return: The name of the user
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT username from users WHERE userid = %s"""
sql_value = (userid, )
#query = ("SELECT username from users WHERE userid =\"" + userid + "\"")
username = None
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("get_user_name_by_id: {}".format(sql_value))
users = cursor.fetchall()
if len(users):
username = users[0][0]
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_user_name_by_id: {}".format(err))
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return username
def get_projects_by_status_and_owner(userid, project_status):
"""
Retrieve all projects owned by a user with a specific status
:param userid: The id of the owner
:param project_status: The status to filter on
:type userid: str
:type project_status: str
:return: A list of projects
"""
db.connect()
cursor = db.cursor()
query = (
"SELECT * FROM projects WHERE project_status = %s AND userid = %s")
try:
cursor.execute(query, (project_status, userid))
projects = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
projects = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projects
def get_user_hashed_password(username, ip, fullpath):
"""
Check if user hashed password is match with database
Need to retreive salt value
:param username, ip, fullpath: The user attempting to authenticate
:type username: str
:return: salt (byte)
"""
db.connect()
cursor = db.cursor(prepared=True)
# query the username
sql_cmd = """SELECT password FROM users WHERE username = %s"""
sql_value = (username, )
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"A user attempt in IP get_user_hashed_password:{}-{}-{}".format(
ip, fullpath, sql_value))
password = cursor.fetchall()
if len(password):
password_return = password[0][0]
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_user_hashed_password: {}".format(err))
print(
"Failed executing query get_user_hashed_password: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return password_return
def get_project_by_id(projectid):
"""
Retrieve a project by its id
:param projectid: The project id
:type projectid: str
:return: The selected project
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT * FROM projects WHERE projectid = %s"""
sql_value = (projectid, )
#query = ("SELECT * FROM projects WHERE projectid = \"" + projectid + "\"")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("get_project_id input: {}".format(sql_value))
project = cursor.fetchall()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_project_by_id: {}".format(err))
print("Failed executing query: {}".format(err))
project = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return project[0]
def update_user_password(username, password, ip, fullpath):
"""
Update user's password
:param username: The user attempting to authenticate
:param password: The new corresponding password
:type username: str
:type password: str
:return: user
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """UPDATE users SET password=%s WHERE username = %s """
sql_value = (
password,
username,
)
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"A user success update_user_password:{}-{}-{}".format(
ip, fullpath, sql_value))
db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query update_user_password: {}".format(err))
print("Failed executing query update_user_password: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return
def get_categories():
"""
Get all categories
:return: List of categories
"""
db.connect()
cursor = db.cursor(prepared=True)
query = ("SELECT * FROM project_category")
try:
cursor.execute(query)
logger.log_input_msg("project.py>>get_categories:{}".format(query))
categories = cursor.fetchall()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query project.py>>get_categories: {}".format(
err))
print("Failed executing query: {}".format(err))
categories = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return categories
def set_task(projectid, task_title, task_description, budget):
"""
Create a task
:param projectid: The corresponding project id
:param task_title: The title of the task
:param task_description: The description of the task
:param budget: The task budget
:type projectid: str
:type task_title: str
:type task_description: str
:type budget: str
"""
db.connect()
cursor = db.cursor()
query = (
"INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (%s, %s, %s, %s, %s)"
)
try:
cursor.execute(query, (projectid, task_title, task_description, budget,
"waiting for delivery"))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def get_task_files(taskid):
"""
Retrieve all filenames registered in a task
:param taskid: The task id
:type taskid: str
:return: An array of filenames
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT filename FROM task_files WHERE taskid = %s"""
sql_value = (str(taskid), )
#query = ("SELECT filename FROM task_files WHERE taskid = \"" + str(taskid) + "\"")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("get_task_file: {}".format(sql_value))
filenames = cursor.fetchall()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_task_files: {}".format(err))
print("Failed executing query: {}".format(err))
filenames = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return filenames
def get_projects_by_participant_and_status(userid, project_status):
"""
Retrieve all projects where the user is a participant with specific status
:param userid: The id of the participant
:param project_status: The status to filter on
:type userid: str
:type project_status: str
:return: A list of projects
"""
db.connect()
cursor = db.cursor()
query = (
"SELECT * FROM projects, projects_users WHERE projects.project_status = %s AND projects_users.userid = %s AND projects_users.projectid = projects.projectid"
)
db.connect()
try:
cursor.execute(query, (project_status, userid))
projects = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
projects = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projects
def get_tasks_by_project_id(projectid):
"""
Get all tasks belonging to a project
:param project_id: The id of the project holding the tasks
:type project_id: str
:return: List of tasks
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT * FROM tasks WHERE projectid = %s"""
sql_value = (projectid, )
#query = ("SELECT * FROM tasks WHERE projectid = \"" + projectid + "\"")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("get_task_by_project_id: {}".format(sql_value))
tasks = cursor.fetchall()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_tasks_by_project_id: {}".format(err))
print("Failed executing query: {}".format(err))
tasks = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return tasks
def update_project_status(projectid, status):
"""
Change the status of a selected project
:param projectid: The project id
:param status: The status to change to, should be either open, in progress or finished
:type projectid: str
:type status: str
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """UPDATE projects SET project_status = %s WHERE projectid = %s"""
sql_value = (
status,
projectid,
)
#query = ("UPDATE projects SET project_status = \"" + status +
# "\" WHERE projectid = \"" + projectid + "\"")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"update_project_status_input: {}".format(sql_value))
db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query update_project_status: {}".format(err))
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def get_projects_by_owner(userid):
"""
Retrieve all projects created by a specific user
:param userid: The id of the user
:type userid: str
:return: An array of projects
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT * FROM projects WHERE userid = %s"""
sql_value = (userid, )
#query = ("SELECT * FROM projects WHERE userid = \"" + userid + "\"")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("get_project_by_owner: {}".format(sql_value))
projects = cursor.fetchall()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query get_projects_by_owner: {}".format(err))
print("Failed executing query: {}".format(err))
projects = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projects
def set_projects_user(projectid,
userid,
read_permission="TRUE",
write_permission="NULL",
modify_permission="NULL"):
"""
Add a user to a project with specific permissions
:param projectid: The project id
:param userid: The user id
:param read_permission: Describes whether a user can view information about a project
:param write_permission: Describes whether a user can add files to tasks
:param modify_permission: Describes wheter a user can deliver tasks
:type projectid: str
:type userid: str
:type read_permission: str
:type write_permission: str
"""
db.connect()
cursor = db.cursor()
# query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
try:
# cursor.execute(query, (projectid, userid, read_permission, write_permission, modify_permission))
cursor.execute(
query,
(projectid, userid, read_permission == "TRUE", write_permission
== "TRUE", modify_permission == "TRUE"))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def set_project(categoryid, userid, project_title, project_description,
project_status):
"""
Store a project in the database
:param categoryid: The id of the corresponding category
:param userid: The id of the project owner
:param project_title: The title of the project
:param project_description: The project description
:param project_status: The status of the project
:type categoryid: str
:type userid: str
:type project_title: str
:type project_description: str
:type project_status: str
:return: The id of the new project
"""
db.connect()
cursor = db.cursor()
query = ("INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)")
try:
cursor.execute(query, (categoryid, userid, project_title,
project_description, project_status))
db.commit()
users_projects = get_projects_by_owner(userid)
projectid = users_projects[-1][0]
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
projectid = None
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projectid
def get_user_permissions(userid, projectid):
"""
Get permissions for a selected users in a specific project
:param userid: The id of the user
:param projectid: The id of the project
:type userid: str
:type projectid: str
:return: Permissions as an array of numbers as boolean values
"""
db.connect()
cursor = db.cursor()
query = (
"SELECT read_permission, write_permission, modify_permission FROM projects_users WHERE projectid = %s AND userid = %s"
)
try:
cursor.execute(query, (projectid, userid))
permissions = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
if len(permissions):
return permissions[0]
return [0, 0, 0]
def set_task_file(taskid, filename):
"""
Register a new task - file relationship
:param taskid: The task id
:param filename: The name of the file
:type taskid: str
:type filename: str
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """INSERT INTO task_files (taskid, filename) VALUES (%s, %s)"""
sql_value = (
taskid,
filename,
)
#query = ("INSERT INTO task_files (taskid, filename) VALUES (\"" +
# taskid + "\", \"" + filename + "\")")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("set_task_file: {}".format(sql_value))
db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query set_task_file: {}".format(err))
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def update_task_status(taskid, status):
db.connect()
cursor = db.cursor()
query = ("UPDATE tasks SET task_status = %s WHERE taskid = %s")
try:
cursor.execute(query, (status, taskid))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def check_if_user_is_verified_by_verification_key(verification_key):
db.connect()
cursor = db.cursor()
query = "SELECT verified FROM users WHERE verification_key = %s"
result = ""
try:
cursor.execute(query, (verification_key, ))
result = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
finally:
cursor.close()
db.close()
return result[0][0] == 1
def set_project(categoryid, userid, project_title, project_description,
project_status):
"""
Store a project in the database
:param categoryid: The id of the corresponding category
:param userid: The id of the project owner
:param project_title: The title of the project
:param project_description: The project description
:param project_status: The status of the project
:type categoryid: str
:type userid: str
:type project_title: str
:type project_description: str
:type project_status: str
:return: The id of the new project
"""
#using mysql placeholder
# reference: https://pymysql.readthedocs.io/en/latest/user/examples.html
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = "INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)"
sql_value = (
categoryid,
userid,
project_title,
project_description,
project_status,
)
#query = ("INSERT INTO projects VALUES (NULL, \"" +
# categoryid + "\", \"" + userid + "\", \"" + project_title + "\", \"" +
# project_description + "\", \"" + project_status + "\")")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("set_porject_input: {}".format(sql_value))
db.commit()
users_projects = get_projects_by_owner(userid)
projectid = users_projects[-1][0]
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query set_porject_input: {}".format(err))
print("Failed executing query: {}".format(err))
projectid = None
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projectid
def match_verification_key(verification_key):
db.connect()
cursor = db.cursor()
query = "SELECT verification_key FROM users WHERE verification_key = %s AND verified = 0"
result = ""
try:
cursor.execute(query, (verification_key, ))
result = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
finally:
cursor.close()
db.close()
return result == verification_key
def verify_user_by_email(verification_key):
is_real_key = match_verification_key(verification_key)
db.connect()
cursor = db.cursor()
query = "UPDATE users SET verified = 1 WHERE verification_key = %s"
try:
cursor.execute(query, (verification_key, ))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
exit(1)
finally:
cursor.close()
db.close()
def set_projects_user(projectid,
userid,
read_permission="TRUE",
write_permission="NULL",
modify_permission="NULL"):
"""
Add a user to a project with specific permissions
:param projectid: The project id
:param userid: The user id
:param read_permission: Describes whether a user can view information about a project
:param write_permission: Describes whether a user can add files to tasks
:param modify_permission: Describes wheter a user can deliver tasks
:type projectid: str
:type userid: str
:type read_permission: str
:type write_permission: str
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)"""
sql_value = (
projectid,
userid,
read_permission,
write_permission,
modify_permission,
)
#query = ("INSERT INTO projects_users VALUES (\"" + projectid + "\", \"" +
# userid + "\", " + read_permission + ", " +
# write_permission + ", " + modify_permission + ")")
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg("set_project_users: {}".format(sql_value))
db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query set_project_users: {}, {}".format(
err, sql_value))
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def update_reset_token(username, token, ip, fullpath):
"""
Update user's forget token
:param username: The user attempting to authenticate
:param email: The user's corresponding email
:param ip: user's ip address
:param fullpath: user's accessed file path
:timestamp: user's accessed time stamp
:type username: str
:type email: str
:param ip: str
:param fullpath: str
:timestamp: str
:return: email
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """UPDATE users SET verify_token = %s WHERE username = %s"""
sql_value = (
token,
username,
)
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"A user success update_reset_token:{}-{}-{}".format(
ip, fullpath, sql_value))
db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query update_reset_token: {}".format(err))
print("Failed executing query update_reset_token: {}".format(err))
cursor.fetchall()
exit(1)
except Exception as e:
print("An exception occur during execute update_reset_token {}".format(
e))
finally:
cursor.close()
db.close()
return
def forget_password_match(username, email, ip, fullpath):
"""
Update user's password
:param username: The user attempting to authenticate
:param email: The user's corresponding email
:param ip: user's ip address
:param fullpath: user's accessed file path
:timestamp: user's accessed time stamp
:type username: str
:type email: str
:param ip: str
:param fullpath: str
:timestamp: str
:return: email
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """SELECT email FROM users WHERE username = %s AND email=%s"""
sql_value = (
username,
email,
)
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"A user success forget_password_match:{}-{}-{}".format(
ip, fullpath, sql_value))
query_result = cursor.fetchall()
if len(query_result):
email = query_result[0][0]
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query forget_password_match: {}".format(err))
print("Failed executing query forget_password_match: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return email
def record_user_login(username, ip, fullpath, access_time):
"""
Create a lock out function.
:param username: The user attempting to authenticate
:type username: str
:param ip: The user's ip
:type username: str
:param fullpath: The user access path
:type username: str
:return: times of query
reference: https://stackoverflow.com/questions/19966123/get-time-interval-in-mysql
"""
db.connect()
cursor = db.cursor(prepared=True)
#print("enter logger!")
# log the request
sql_cmd_log = """INSERT INTO user_access_time VALUES (NULL, %s, %s, %s)"""
#print (sql_cmd_log)
msg = "A user attempt:IP:{}-{}".format(ip, fullpath)
#print(len(msg))
sql_value_log = (
username,
msg,
access_time,
)
try:
cursor.execute(sql_cmd_log, sql_value_log)
log_input_msg("A user attempt in IP record_user_login:{}-{}-{}".format(
ip, fullpath, sql_value_log))
db.commit()
except mysql.connector.Error as err:
log_error_msg(
"Failed executing query record_user_login: {}".format(err))
print("Failed executing query record_user_login: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return
def get_user(userid):
"""
Retreive all information about user by userid
:return: all rows of user
"""
db.connect()
cursor = db.cursor()
query = ("SELECT * FROM users WHERE userid = %s")
try:
cursor.execute(query, (userid, ))
user = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
users = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return user
def get_users():
"""
Retreive all registrered users from the database
:return: users
"""
db.connect()
cursor = db.cursor()
query = ("SELECT userid, username from users")
try:
cursor.execute(query)
users = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
users = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return users
def get_projects():
"""
Gets all projects
:return: list of projectids
"""
db.connect()
cursor = db.cursor()
query = ("SELECT projectid from projects")
try:
cursor.execute(query)
projects = cursor.fetchall()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
users = []
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return [val[0] for val in projects]
def update_token_to_null(username, token, ip, fullpath):
"""
search for user's token
:param username: The user's name
:param ip: user's ip address
:param fullpath: user's accessed file path
:timestamp: user's accessed time stamp
:type username: str
:type email: str
:param ip: str
:param fullpath: str
:return: email
"""
db.connect()
cursor = db.cursor(prepared=True)
sql_cmd = """UPDATE users SET verify_token = Null, temp = 0 WHERE username = %s AND verify_token = %s"""
sql_value = (
username,
token,
)
try:
cursor.execute(sql_cmd, sql_value)
logger.log_input_msg(
"A user success update_token_to_null:{}-{}-{}".format(
ip, fullpath, sql_value))
actions = db.commit()
except mysql.connector.Error as err:
logger.log_error_msg(
"Failed executing query update_token_to_null: {}".format(err))
print("Failed executing query update_token_to_null: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return actions