def find_modules_of_question(question_id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT module_id FROM View_Module_Question WHERE question_id=%s", question_id)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
# Check if this question is a child of another question, triggered by an answer, were the parent belongs to a module.
# We need to do this because sub-questions, triggered by an answer, are not directly linked to a module.
# In order to find the module that the sub-question belongs, we need to find its parent.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT parent FROM Question_has_Child WHERE child=%s", question_id)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
if (len(res) == 0):
cursor.close()
conn.close()
return([])
else:
l_modules = []
for row in res:
modules = find_modules_of_question(row[0])
l_modules.append(modules)
#
f_list_modules = []
for modules in l_modules:
for module in modules:
if module not in f_list_modules:
f_list_modules.append(module)
return(f_list_modules)
cursor.close()
conn.close()
else:
modules = [] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
module = views.module.find_module(row[0], True)[0]
del module['tree']
del module['dependencies']
del module['recommendations']
modules.append(module)
cursor.close()
conn.close()
# 'May the Force be with you, young master'.
return(modules)
def find_dependency(ID):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
views.user.isAuthenticated(request)
# 2. Let's get the answeers for the question from the database.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT ID, moduleID, dependsOn, createdOn, UpdatedOn FROM dependency WHERE ID=%s", ID)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# 2.2. Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return(modules.utils.build_response_json(request.path, 404))
else:
datas = [] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
data = {}
data['id'] = row[0]
data['module_id'] = row[1]
data['depends_on'] = row[2]
data['createdon'] = row[3]
data['updatedon'] = row[4]
datas.append(data)
cursor.close()
conn.close()
# 3. 'May the Force be with you, young master'.
return(modules.utils.build_response_json(request.path, 200, datas))
def get_modules_answers():
if request.method != 'GET': return
# Check if the user has permissions to access this resource
views.user.isAuthenticated(request)
# Let's get the resource from the DB
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT DISTINCT module_id FROM View_Module_Questions_Answers")
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results.
if (len(res) == 0):
cursor.close()
conn.close()
return (modules.utils.build_response_json(request.path, 404))
else:
datas = []
# Module IDs first
for row in res:
module = {}
module['id'] = row[0]
module['answers'] = find_module_answers(module['id'], True)
datas.append(module)
cursor.close()
conn.close()
# 'May the Force be with you, young padawan'.
return (modules.utils.build_response_json(request.path, 200, datas))
def find_sessions_of_user(user_email, internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# Check if the user has permissions to access this resource
if (not internal_call):
views.user.isAuthenticated(request)
# Let's existing sessions from the database.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT session_id, user_id, user_email, moduleID, ended, createdon, updatedon FROM View_User_Sessions WHERE user_email=%s",
user_email)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results.
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return (modules.utils.build_response_json(request.path, 404))
else:
return (None)
else:
datas = [
] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
data = {}
data['id'] = row[0]
data['user_id'] = row[1]
data['user_email'] = row[2]
data['ended'] = row[4]
data['createdOn'] = row[5]
data['updatedOn'] = row[6]
session = find_session_closed(data['id'], True)
if session:
if ("questions" in session):
questions = session['questions']
data['questions'] = questions
if ("recommendations" in session):
recommendations = session['recommendations']
data['recommendations'] = recommendations
module = views.module.find_module(row[3], True)
del module[0]['recommendations']
del module[0]['tree']
if (module): data['module'] = module[0]
datas.append(data)
cursor.close()
conn.close()
# 'May the Force be with you'.
if (not internal_call):
return (modules.utils.build_response_json(request.path, 200,
datas))
else:
return (datas)
def find_users_of_group(group_id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT user_email FROM View_User_Group WHERE group_id=%s",
group_id)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return ([])
else:
users = [
] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
user = views.user.find_user(row[0], True)
users.append(user)
cursor.close()
conn.close()
# 'May the Force be with you, young master'.
return (users)
def post_ref_schedule(current_user):
# retrieve query string parameters from URL
ref_id = current_user.user_id
game_id = request.args.get('game_id', default = None, type = int)
# error check: ensure that both ref_id and game_id are not null
if (ref_id is None or game_id is None):
return jsonify({'message': 'The game_id must be provided'}), 400
# error check: ensure that ref_id is indeed a referee
if current_user.access is not AccessLevel.referee:
return jsonify({'message' : 'Invalid access level, needs a referee'}), 401
# connects to the database
conn = mysql.connect()
cursor = conn.cursor()
# calls for the update_ref_schedule procedure
try:
cursor.callproc('post_ref_schedule',[ref_id, game_id])
except pymysql.MySQLError as err:
errno = err.args[0]
print(f'Error number: {errno}')
if errno == 1452:
return jsonify ({'message': 'game_id or referee_id does not exist'}), 400
if errno == 1062:
return jsonify ({'message': 'That referee_id is already scheduled to that game_id'}), 400
return jsonify({'message': 'Successfully scheduled a referee to a game'}), 201 #created
def find_modules_of_group(group_id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT moduleID FROM Module_Group WHERE groupID=%s",
group_id)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return ([])
else:
modules = [
] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
module = views.module.find_module(row[0], True)[0]
del module['tree']
del module['dependencies']
del module['recommendations']
modules.append(module)
cursor.close()
conn.close()
# 'May the Force be with you, young master'.
return (modules)
def is_admin(email):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
isAuthenticated(request)
# 2. Let's get the groups associated with the parsed user.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT email, administrator FROM User WHERE email=%s AND administrator=1",
email)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# 2.2. Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return (modules.utils.build_response_json(request.path, 404))
else:
cursor.close()
conn.close()
data = {True}
# 3. 'May the Force be with you, young master'.
return (modules.utils.build_response_json(request.path, 200))
def find_recommendation(ID, internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
if (not internal_call): views.user.isAuthenticated(request)
# 2. Let's get the set of available recommendations
results = []
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT ID, content, description, guideFileName, createdon, updatedon FROM Recommendation WHERE ID=%s", ID)
res = cursor.fetchall()
for row in res:
result = {}
result['id'] = row[0]
result['content'] = row[1]
result['description'] = row[2]
result['guide'] = row[3]
result['createdOn'] = row[4]
result['updatedOn'] = row[5]
results.append(result)
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 3. The request was a success, the user 'is in the rabbit hole'
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, results))
else:
return(results)
def delete_recommendation(recommendation_id):
if request.method != 'DELETE': return
# 1. Check if the user has permissions to access this resource
views.user.isAuthenticated(request)
# 2. If any, get the filename of the guide linked to the recommendation.
recommendation_guide = find_recommendation(recommendation_id, True)[0]['guide']
# 3. Connect to the database and delete the resource
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM Recommendation WHERE ID=%s", recommendation_id)
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 3.1. If guide available, remove the file from the server.
if (recommendation_guide):
try:
os.remove(os.path.join(views.file.UPLOAD_DIRECTORY, recommendation_guide))
except Exception as e:
pass # For some reason, the file may not exist.
# 4. The Delete request was a success, the user 'took the blue pill'.
return (modules.utils.build_response_json(request.path, 200))
def delete_user(email):
if request.method != 'DELETE': return
# 1. Check if the user has permissions to access this resource
isAuthenticated(request)
# 2. Let's validate the email, invalid emails from this point are not allowed.
try:
valid = validate_email(email)
except EmailNotValidError as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 400)
# 3. Connect to the database and delete the user
# TODO: Let's build procedures in the DB to delete Users.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM User WHERE email=%s", email)
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 4. The Delete request was a success, the user 'took the blue pill'.
return (modules.utils.build_response_json(request.path, 200))
def delete_module_partial(ID, internal_call=False):
if (not internal_call):
if request.method != 'DELETE': return
# 1. Check if the user has permissions to access this resource
if (not internal_call):
views.user.isAuthenticated(request)
# 2. Connect to the database and delete the resource
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM Module WHERE ID=%s", ID)
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 3. The Delete request was a success, the user 'took the blue pill'.
if (not internal_call):
return (modules.utils.build_response_json(request.path, 200))
else:
return (True)
def find_recommendations_of_module(module_id, internal_call=False):
if (not internal_call):
if request.method != 'GET':
return
# Check if the user has permissions to access this resource
if (not internal_call): views.user.isAuthenticated(request)
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT recommendation_ID, recommendation_content, createdon, updatedon FROM View_Module_Recommendations WHERE module_id=%s", module_id)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return(None)
else:
recommendations = [] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
recommendation = {}
recommendation['id'] = row[0]
recommendation['content'] = row[1]
recommendation['createdon'] = row[2]
recommendation['updatedon'] = row[3]
recommendations.append(recommendation)
# Find questions and answers associated or mapped to a particular recommendation and module
for recommendation in recommendations:
recommendation_id = recommendation['id']
try:
cursor = conn.cursor()
cursor.execute("SELECT recommendation_question_answer_id, question_id, answer_id, createdon, updatedon FROM View_Module_Recommendations_Questions_Answers WHERE module_id=%s and recommendation_id=%s", (module_id, recommendation_id))
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
if (len(res) != 0):
questions_answers = []
for row in res:
question_answer = {}
question_answer['id'] = row[0]
question_answer['question_id'] = row[1]
question_answer['answer_id'] = row[2]
question_answer['createdon'] = row[3]
question_answer['updatedon'] = row[4]
questions_answers.append(question_answer)
cursor.close()
recommendation['questions_answers'] = questions_answers
# 'May the Force be with you, young master'.
conn.close()
if (internal_call):
return(recommendations)
else:
return(modules.utils.build_response_json(request.path, 200, recommendations))
def isAuthenticated(request):
# 1. Check if the token is available on the request header.
headers = dict(request.headers)
# Debug only: print(str(len(headers)))
# 2. Check if the Authorization header name was parsed.
if 'Authorization' not in headers:
raise modules.error_handlers.BadRequest(
request.path,
"Authentication failure - You don't have the permission to access this resource. Please, provide an authorization token.",
403)
parsedToken = headers['Authorization']
# 3. Decode the authorization token to get the User object.
try:
# Decode will raise an exception if anything goes wrong within the decoding process (i.e., perform validation of the JWT).
res_dic = jwt.decode(parsedToken,
JWT_SECRET_TOKEN,
algorithms=['HS256'])
# Get the ID of the user.
userID = int(res_dic['id'])
# Debug only: print(str(json.dumps(res_dic)))
conn = mysql.connect()
cursor = conn.cursor()
# 3.1. Check if the token is not blacklisted, that is if a user was previously logged out from the platform but the token is still 'alive'.
cursor.execute(
"SELECT ID FROM Auth_Token_Blacklist WHERE userID=%s AND token=%s",
(userID, parsedToken))
res = cursor.fetchall()
if (len(res) == 1):
raise modules.error_handlers.BadRequest(
request.path,
"Authentication failure - You don't have the necessary permissions to access this resource. Please, provide an authorization token.",
403)
cursor.close()
cursor = conn.cursor()
# 3.2. Get info of the user
# Check if this user id exists.
cursor.execute("SELECT ID FROM User WHERE id=%s", userID)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 403)
if (len(res) == 1):
cursor.close()
conn.close()
return True # The user is legit, we can let him access the target resource
else:
cursor.close()
conn.close()
raise modules.error_handlers.BadRequest(
request.path,
"Authentication failure - You don't have the necessary permissions to access this resource. Please, provide an authorization token.",
403)
def __get_db(self):
db = mysql.connect(
host=self.host,
user=self.user,
passwd=self.passwd,
db=self.database,
port=self.port,
charset=self.charset)
return db
def get_recommendations(internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
if (not internal_call):
views.user.isAuthenticated(request)
# 2. Let's get the set of available recommendations
recommendations = []
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT ID, content, description, guideFileName, createdon, updatedon FROM Recommendation")
res = cursor.fetchall()
for row in res:
recommendation = {}
recommendation['id'] = row[0]
recommendation['content'] = row[1]
recommendation['description'] = row[2]
recommendation['guide'] = row[3]
recommendation['createdOn'] = row[4]
recommendation['updatedOn'] = row[5]
recommendation['modules'] = []
recommendations.append(recommendation)
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
# 3. Let's get the info about the modules linked to each recommendation.
cursor.close()
for recommendation in recommendations:
try:
cursor = conn.cursor()
cursor.execute("SELECT module_id FROM VIEW_module_recommendations WHERE recommendation_id=%s", recommendation['id'])
res = cursor.fetchall()
for row in res:
module = views.module.find_module(row[0], True)[0]
# Remove unnecessary information from the object
if ("tree" in module): del module['tree']
if ("recommendations" in module): del module['recommendations']
if ("dependencies" in module): del module['dependencies']
recommendation['modules'].append(module)
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
cursor.close()
conn.close()
# 4. The request was a success, the user 'is in the rabbit hole'
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, recommendations))
else:
return(recommendations)
def add_user():
if request.method != 'POST': return
# 1. Let's get our shiny new JSON object and current time.
# - Always start by validating the structure of the json, if not valid send an invalid response.
try:
obj = request.json
date = (datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 400)
# 2. Let's validate the data of our JSON object with a custom function.
if (not modules.utils.valid_json(
obj, {
"email", "psw", "firstName", "lastName", "avatar",
"g-recaptcha-response"
})):
raise modules.error_handlers.BadRequest(
request.path,
"Some required key or value is missing from the JSON object", 400)
# 3. Validate reCAPTCHA
if not is_human(obj['g-recaptcha-response']):
raise modules.error_handlers.BadRequest(
request.path, "reCAPTCHA failure - Bots are not allowed.", 400)
# 4. Let's hash the hell of the password.
hashed_psw = modules.utils.hash_password(obj['psw'])
obj['psw'] = "" # "paranoic mode".
# 5. Check if the user was not previously registered in the DB (i.e., same email)
if (views.user.getUser(obj['email']) is not None):
raise modules.error_handlers.BadRequest(
request.path, "The user with that email already exists", 500)
# 6. Connect to the database and create the new user.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"INSERT INTO User (email, psw, firstName, lastName, avatar, createdon, updatedon) VALUES (%s, %s, %s, %s, %s, %s, %s)",
(obj['email'], hashed_psw, obj['firstName'], obj['lastName'],
obj['avatar'], date, date))
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 7. Authentication success, the user can now choose to 'take the red or blue pill to follow the white rabbit'
return (modules.utils.build_response_json(request.path, 200))
def get_stats_modules(internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# Check if the user has permissions to access this resource
if (not internal_call):
views.user.isAuthenticated(request)
# Let's get the info from the databse.
try:
conn = mysql.connect()
cursor = conn.cursor()
# Top 5 only
cursor.execute("SELECT moduleID, count(*) as occurrences FROM Session GROUP BY moduleID ORDER BY occurrences DESC LIMIT 5")
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return(modules.utils.build_response_json(request.path, 404))
else:
return(None)
else:
stat = {}
stat['size'] = get_number_of_modules()
stat['top'] = []
for row in res:
module = views.module.find_module(row[0], True)
module[0]['occurrences'] = row[1]
del module[0]["createdon"]
del module[0]["updatedon"]
del module[0]["tree"]
del module[0]["recommendations"]
del module[0]["logic_filename"]
del module[0]["description"]
del module[0]["dependencies"]
stat['top'].append(module[0])
cursor.close()
conn.close()
# 'May the Force be with you, young master'.
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, stat))
else:
return(stat)
def find_user(email, internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
if (not internal_call):
isAuthenticated(request)
# 2. Let's validate the email, invalid emails from this point are not allowed.
try:
valid = validate_email(email)
except EmailNotValidError as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 400)
# 3. Let's get the user from the database with the provided [email].
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT ID, email, firstName, lastName, avatar FROM User WHERE email=%s",
email)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Empty results ?
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return (modules.utils.build_response_json(request.path, 404))
else:
return (None)
else:
data = {
} # Create a new nice empty dictionary to be populated with data from the DB.
for row in res:
data['id'] = row[0]
data['email'] = row[1]
data['firstName'] = row[2]
data['lastName'] = row[3]
data['avatar'] = row[4]
cursor.close()
conn.close()
# 4. Return information about the user (except the password) and 'May the Force be with you'.
if (not internal_call):
return (modules.utils.build_response_json(request.path, 200, data))
else:
return (data)
def add_logic_session_recommendation(session_id, recommendation_id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"INSERT INTO Session_Recommendation (sessionID, recommendationID) VALUES (%s, %s)",
(session_id, recommendation_id))
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# The recommendation is linked to the session.
return (True)
def find_dependency_of_module(ID, internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
if (not internal_call): views.user.isAuthenticated(request)
# 2. Let's get the answeers for the question from the database.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT dependency_id, depends_module_id, createdOn, updatedOn FROM View_Module_Dependency WHERE module_ID=%s", ID)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# 2.2. Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return(modules.utils.build_response_json(request.path, 404))
else:
return None
else:
datas = []
for row in res:
data = {}
data['id'] = row[0]
t_module = views.module.find_module(row[1], True)
module = {}
module['id'] = t_module[0]['id']
module['fullname'] = t_module[0]['fullname']
module['displayname'] = t_module[0]['displayname']
module['shortname'] = t_module[0]['shortname']
data['module'] = module
data['createdon'] = row[2]
data['updatedon'] = row[3]
datas.append(data)
cursor.close()
conn.close()
# 3. 'May the Force be with you, young master'.
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, datas))
else:
return(datas)
def clear_expired_blacklisted_JWT(userID):
debug = False
if (debug):
print("Checking blacklisted tokens for user id =" + str(userID))
try:
# Check if this user id exists.
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT token FROM Auth_Token_BlackList WHERE userID=%s", userID)
res = cursor.fetchall()
except Exception as e:
if (debug): print(str(e))
return (False) # 'Houston, we have a problem.'
# Empty results ?
if (len(res) == 0):
cursor.close()
conn.close()
return (True)
else:
i = 0
for row in res:
token = row[0]
if (debug):
print("# Checking token[" + str(i) + "]" + "= " + token)
i = i + 1
try:
# Let's see if the token is expired
res_dic = jwt.verify(token)
if (debug):
print(" - The token is still 'alive'. Nothing to do here.")
except:
if (debug):
print(
" - The token is expired, removing it from the database for user with id "
+ str(userID))
# The token is expired, remove it from the DB
try:
cursor.execute(
"DELETE FROM Auth_Token_Blacklist WHERE token=%s AND userID=%s",
(token, userID))
conn.commit()
except Exception as e:
if (debug): print(str(e))
return (False) # 'Houston, we have a problem.'
return (True)
def find_module_answers(ID, internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# Check if the user has permissions to access this resource
if (not internal_call): views.user.isAuthenticated(request)
# Let's get the answers linked to the current module.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT DISTINCT answer_id, answer, createdon, updatedon FROM View_Module_Answers WHERE module_id=%s",
ID)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results.
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return (modules.utils.build_response_json(request.path, 404))
else:
return (None)
else:
datas = [
] # Create a new nice empty array to be populated with data from the DB.
for row in res:
data = {}
data['id'] = row[0]
data['content'] = row[1]
data['createdon'] = row[2]
data['updatedon'] = row[3]
datas.append(data)
cursor.close()
conn.close()
# 'May the Force be with you, young padawan'.
if (not internal_call):
return (modules.utils.build_response_json(request.path, 200,
datas))
else:
return (datas)
def update_session(ID):
if request.method != 'PUT': return
# 1. Check if the user has permissions to access this resource
views.user.isAuthenticated(request)
# 2. Let's get our shiny new JSON object.
# - Always start by validating the structure of the json, if not valid send an invalid response.
try:
obj = request.json
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 400)
# print(obj)
answer_user_inputted = False
# 3. Let's validate the data of our JSON object with a custom function.
if (not modules.utils.valid_json(obj, {"question_id", "answer_id"})):
if (modules.utils.valid_json(obj, {"question_id", "input"})):
answer_user_inputted = True
else:
raise modules.error_handlers.BadRequest(
request.path,
"Some required key or value is missing from the JSON object",
400)
try:
conn = mysql.connect()
cursor = conn.cursor()
if (not answer_user_inputted):
cursor.execute(
"INSERT INTO Session_User_Answer (sessionID, questionAnswerID) VALUES (%s, (SELECT ID FROM Question_Answer WHERE questionID=%s AND answerID=%s))",
(ID, obj['question_id'], obj['answer_id']))
else:
cursor.execute(
"INSERT INTO Session_User_Answer (sessionID, questionID, input) VALUES (%s, %s, %s)",
(ID, obj['question_id'], obj['input']))
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 5. The Update request was a success, the user 'is in the rabbit hole'
return (modules.utils.build_response_json(request.path, 200))
def end_session(ID):
if request.method != 'PUT': return
# 1. Check if the user has permissions to access this resource.
views.user.isAuthenticated(request)
# 2. End a session by defining the flag ended to one.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("UPDATE Session SET ended=1 WHERE ID=%s", ID)
conn.commit()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
finally:
cursor.close()
conn.close()
# 3. The request was a success, let's generate the recommendations.
return find_recommendations(request, ID)
def get_stats_sessions(internal_call=False):
if (not internal_call):
if request.method != 'GET': return
# Check if the user has permissions to access this resource
if (not internal_call):
views.user.isAuthenticated(request)
# Let's get the info from the databse.
try:
conn = mysql.connect()
cursor = conn.cursor()
# Number of session in the Last 7 days sessions
cursor.execute("SELECT date(createdOn) as day, COUNT(*) as occurrences FROM session WHERE createdon >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) GROUP BY day")
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return(modules.utils.build_response_json(request.path, 404))
else:
return(None)
else:
stat = {}
stat['size'] = get_number_of_sessions()
stat['top'] = []
for row in res:
date = {}
date['date'] = row[0]
date['occurrences'] = row[1]
stat['top'].append(date)
cursor.close()
conn.close()
# 'May the Force be with you, young master'.
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, stat))
else:
return(stat)
def find_question_answers_2(question_id, answer_id, internal_call=False):
if (request.method != 'GET' and not internal_call): return
# 1. Check if the user has permissions to access this resource
if (not internal_call): views.user.isAuthenticated(request)
# 2. Let's get the answeers for the question from the database.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT question_answer_id, question_id, question, answer_id, answer FROM view_question_answer where question_id=%s and answer_id=%s", (question_id, answer_id))
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# 2.2. Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
if (not internal_call):
return(modules.utils.build_response_json(request.path, 404))
else:
return(None)
else:
datas = [] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
data = {}
data['question_answer_id'] = row[0]
data['question_id'] = row[1]
data['question_content'] = row[2]
data['answer_id'] = row[3]
data['answer_content'] = row[4]
datas.append(data)
cursor.close()
conn.close()
# 3. 'May the Force be with you, young master'.
if (not internal_call):
return(modules.utils.build_response_json(request.path, 200, datas))
else:
return(datas)
def get_modules():
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
views.user.isAuthenticated(request)
# 2. Let's get the modules from the database.
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT ID, typeID, shortname, fullname, displayname, logicfilename, description, avatar, createdon, updatedon FROM Module"
)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# 2.1. Check for empty results.
if (len(res) == 0):
cursor.close()
conn.close()
return (modules.utils.build_response_json(request.path, 404))
else:
datas = [
] # Create a new nice empty array of dictionaries to be populated with data from the DB.
for row in res:
data = {}
data['id'] = row[0]
data['type_id'] = row[1]
data['shortname'] = row[2]
data['fullname'] = row[3]
data['displayname'] = row[4]
data['logic_filename'] = row[5]
data['description'] = row[6]
data['avatar'] = row[7]
data['createdon'] = row[8]
data['updatedon'] = row[9]
datas.append(data)
cursor.close()
conn.close()
# 3. 'May the Force be with you, young padawan'.
return (modules.utils.build_response_json(request.path, 200, datas))
def get_number_of_sessions():
size = 0
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(id) as size FROM Module")
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Check for empty results
if (len(res) == 0):
cursor.close()
conn.close()
return(size)
else:
for row in res:
size = row[0]
break
return(size)
def _init_database():
"""Initialze the database tables"""
database = mysql.connect()
cursor = database.cursor(raw=True, buffered=False)
# Wipe the database and setup new one
with open("/home/ictf/ictf-framework/database/support/database.sql",
"r") as file_:
setup_script = file_.read()
# The library returns a generator for each statement, we need to eval all
# of them.
list(cursor.execute(setup_script, multi=True))
# Create the game
game_id = random.randint(0, 1000000)
cursor.execute("INSERT INTO game (id) VALUES (%s)", (game_id, ))
database.commit()
def find_user_groups(email):
if request.method != 'GET': return
# 1. Check if the user has permissions to access this resource
isAuthenticated(request)
# 2. Let's validate the email, invalid emails from this point are not allowed.
try:
valid = validate_email(email)
except EmailNotValidError as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 400)
# 3. Let's get the user from the database with the provided [email].
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT user_id, user_email, user_group FROM View_User_Group WHERE user_email=%s",
email)
res = cursor.fetchall()
except Exception as e:
raise modules.error_handlers.BadRequest(request.path, str(e), 500)
# Empty results ?
if (len(res) == 0):
cursor.close()
conn.close()
return (modules.utils.build_response_json(request.path, 404))
else:
datas = []
for row in res:
data = {
} # Create a new nice empty dictionary to be populated with data from the DB.
data['user_id'] = row[0]
data['user_email'] = row[1]
data['user_group'] = row[2]
datas.append(data)
cursor.close()
conn.close()
# 4. Return information about the user (except the password) and 'May the Force be with you'.
return (modules.utils.build_response_json(request.path, 200, datas))
