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))