def get_puzzler_icons(): db = get_db() cursor = db.cursor() sql_query = ''' SELECT I.IconID, I.IconPath, I.IconDescription, I.Default, I.Color, I.AccentColor FROM icons AS I ''' cursor.execute(sql_query) data = cursor.fetchall() to_return = [] for d in data: to_add = { 'IconID': d[0], 'IconPath': d[1], 'IconDescription': d[2], 'Default': d[3], 'Color': d[4], 'AccentColor': d[5] } to_return.append(to_add) cursor.close() return jsonify(to_return)
def get_medals(): try: username = request.json['Username'] except: abort(500, 'Username not found') db = get_db() cursor = db.cursor() sql_query = ''' SELECT UserID FROM users WHERE Username=%(username)s; ''' query_model = {"username": username} cursor.execute(sql_query, query_model) data = cursor.fetchall() if len(data) == 0: abort(500, 'Username does not exist') cursor.close() cursor = db.cursor() user_id = (data[0])[0] sql_query = ''' SELECT Type, BronzeMedals, SilverMedals, GoldMedals FROM userMedals WHERE UserID=%(user_id)s; ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data = cursor.fetchall() cursor.close() return jsonify(data)
def validate_user(validation_id): db = get_db() cursor = db.cursor() query_params = {'validation_id': validation_id} sql_query = ''' SELECT UserID FROM validations WHERE ValidationID = %(validation_id)s ''' cursor.execute(sql_query, query_params) data = cursor.fetchall() if len(data) != 1: return jsonify({"validated": False}) user_id = (data[0])[0] query_params = {'validation_id': validation_id, 'user_id': user_id} sql_query = ''' UPDATE users SET Validated = 1 WHERE UserID = %(user_id)s ''' cursor.execute(sql_query, query_params) db.commit() sql_query = ''' DELETE FROM validations WHERE ValidationID = %(validation_id)s ''' cursor.execute(sql_query, query_params) db.commit() return jsonify({"validated": True})
def get_all_users(): db = get_db() cursor = db.cursor() sql_query = 'SELECT * FROM users' cursor.execute(sql_query) data = cursor.fetchall() return jsonify(data)
def get_completed_daily_challenges(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return '-1' except: return '-1' db = get_db() cursor = db.cursor() sql_query = ''' SELECT Difficulty FROM dailyChallengesCompleted WHERE UserID = %(user_id)s ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data = cursor.fetchall() cursor.close() completed = [] for d in data: completed.append(d[0]) return jsonify(completed)
def post_poll(): """Insert a Poll and its options.""" db_session = get_db() data = request.get_json(force=True) # Check if description was sent if 'poll_description' not in data: abort(400, description='"poll_description" was not sent') # Check if options were sent if 'options' not in data: abort(400, description='The poll options were not sent') # Check if "options" property is an array if type(data['options']) is not list: abort(400, description='"options" must be an array') poll = Poll(description=data['poll_description']) for option in data['options']: PollOption(description=option, poll=poll) db_session.add(poll) db_session.commit() return (jsonify({"poll_id": poll.id}), 201)
def change_password(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: abort(500, 'Token verification failed') except: abort(500, 'Token verification failed') try: old_password = request.json["OldPassword"] except: abort(500, 'OldPassword not found') try: new_password = request.json["NewPassword"] except: abort(500, 'NewPassword not found') check_is_valid_password(new_password) if (new_password == old_password): abort(400, 'New password cannot be the same as the old password') db = get_db() cursor = db.cursor() sql_query = ''' SELECT Password FROM users WHERE UserID=%(user_id)s; ''' query_model = { "user_id": user_id, "new_password": argon2.using(time_cost=160, memory_cost=10240, parallelism=8).hash(new_password) } cursor.execute(sql_query, query_model) data = cursor.fetchall() if len(data) == 0: abort(500, 'UserID does not exist') cursor.close() old_hashed_password = (data[0])[0] if not argon2.verify(old_password, old_hashed_password): abort(400, 'OldPassword not correct') cursor = db.cursor() sql_query = ''' UPDATE users SET Password=%(new_password)s WHERE UserID=%(user_id)s; ''' cursor.execute(sql_query, query_model) db.commit() cursor.close() return jsonify({"Accept": True})
def set_puzzler_icon(): try: puzzler_icon_id = request.json["PuzzlerIconID"] except: abort(500) try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: abort(500) except: abort(500) db = get_db() # This query returns all icons the user has permission to use cursor = db.cursor() sql_query = ''' SELECT IconID FROM icons I WHERE I.Default UNION SELECT IconID FROM userIcons UI WHERE UI.UserID = %(user_id)s ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data2 = cursor.fetchall() found = False for d2 in data2: if d2[0] == puzzler_icon_id: found = True cursor.close() # The user does not have access to this puzzler icon if not found: return jsonify({}) cursor = db.cursor() sql_query = ''' UPDATE accountData SET PuzzlerIcon = %(puzzler_icon_id)s WHERE UserID = %(user_id)s ''' query_model = {"user_id": user_id, "puzzler_icon_id": puzzler_icon_id} cursor.execute(sql_query, query_model) db.commit() cursor.close() return jsonify({})
def get_user_data(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return jsonify({}) except: return jsonify({}) db = get_db() cursor = db.cursor() sql_query = ''' SELECT U.UserID, U.Username, U.Role, AD.XP, AD.PuzzlerIcon FROM users AS U INNER JOIN accountData AS AD ON AD.UserID = U.UserID WHERE U.UserID=%(user_id)s ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data = cursor.fetchall() row = data[0] cursor.close() cursor = db.cursor() sql_query = ''' SELECT IconID FROM icons I WHERE I.Default UNION SELECT IconID FROM userIcons UI WHERE UI.UserID = %(user_id)s ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data2 = cursor.fetchall() unlocked_icons = [] for d2 in data2: unlocked_icons.append(d2[0]) to_return = { 'userId': row[0], 'username': row[1], 'role': row[2], 'xp': row[3], 'puzzlerIcon': row[4], 'unlockedIcons': unlocked_icons } return jsonify(to_return)
def create_app(test_config=None): """ Create and configure an instance of the Flask application """ app = Flask(__name__) if test_config is None: app.config['MONGO_URI'] = os.getenv('MONGO_URI', MONGO_URI) else: app.config['MONGO_URI'] = os.getenv('MONGO_URI_TESTS', MONGO_URI_TESTS) app.config['PRESERVE_CONTEXT_ON_EXCEPTION'] = False app_context = app.app_context() app_context.push() # initialize database from api import database database.get_db() # apply the blueprints to the app from api import users, auth, flights app.register_blueprint(auth.BP) app.register_blueprint(users.BP) app.register_blueprint(flights.BP) return app
def compute_vote(option_id): """Compute a vote for the given option.""" db_session = get_db() option = PollOption.query.get(option_id) # Check if option exists if option is None: abort(404, description='Poll option was not found') # Add a vote for the option option.votes += 1 db_session.commit() return jsonify({'option_id':option.id})
def get_num_entries(): try: game_id = request.json["GameID"] except: abort(500, "GameID not found") try: difficulty = request.json["Difficulty"] except: abort(500, "Difficulty not found") try: leaderboard = request.json["Leaderboard"] except: abort(500, "Leaderboard not found") db = get_db() cursor = db.cursor() if leaderboard == 0: sql_query = ''' SELECT COUNT(*) FROM dailyLeaderboards AS L WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ''' elif leaderboard == 1: sql_query = ''' SELECT COUNT(*) FROM weeklyLeaderboards AS L WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ''' elif leaderboard == 2: sql_query = ''' SELECT COUNT(*) FROM monthlyLeaderboards AS L WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ''' query_model = {"game_id": game_id, "difficulty": difficulty} cursor.execute(sql_query, query_model) data = cursor.fetchall() to_return = {"NumEntries": data[0][0]} return jsonify(to_return)
def get_more_match_history(): try: username = request.json["Username"] except: abort(500, "Username not found") try: offset = request.json["Offset"] except: abort(500, "Offset not found") db = get_db() cursor = db.cursor() sql_query = ''' SELECT MH.GameID, MH.Difficulty, MH.Date AS TimeCompleted, MH.TimeElapsed AS Time, MH.Seed FROM users AS U INNER JOIN matchHistory AS MH ON U.UserID = MH.UserID WHERE U.Username = %(username)s ORDER BY MH.Date DESC LIMIT 10 OFFSET %(offset)s ''' query_model = {"username": username, "offset": offset} cursor.execute(sql_query, query_model) data = cursor.fetchall() match_history = [] for d in data: model = { "GameID": d[0], "Difficulty": d[1], "TimeCompleted": str(d[2]), "TimeElapsed": convert_to_puzzle_hub_date(d[3]), "Seed": d[4] } match_history.append(model) cursor.close() return jsonify(match_history)
def get_level(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return jsonify({'xp': 0}) except: return jsonify({'xp': 0}) db = get_db() cursor = db.cursor() sql_query = ''' SELECT XP FROM accountData WHERE UserID=%(user_id)s; ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data = cursor.fetchall() return jsonify({'xp': int((data[0])[0])})
def get_username(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return jsonify({'username': ''}) except: return jsonify({'username': ''}) db = get_db() cursor = db.cursor() sql_query = ''' SELECT Username FROM users WHERE UserID=%(user_id)s; ''' query_model = {"user_id": user_id} cursor.execute(sql_query, query_model) data = cursor.fetchall() return jsonify({'username': (data[0])[0]})
def poll_info(poll_id): """Return info about the given poll.""" db_session = get_db() poll_schema = PollSchema() poll = Poll.query.get(poll_id) # Check if poll was found if poll is None: abort(404, description='Poll was not found') # Add the number of views poll.views += 1 db_session.commit() # Serialize Poll poll = poll_schema.dump(poll) return jsonify(poll)
def get_daily_challenges(): db = get_db() cursor = db.cursor() sql_query = ''' SELECT Length, Relay, Difficulty FROM dailyChallenges ''' cursor.execute(sql_query) data = cursor.fetchall() challenges = [] for d in data: model = { "Length": d[0], "Relay": d[1], "Difficulty": d[2], "XPReward": calculate_xp_reward(d[0], d[2]) } challenges.append(model) cursor.close() return jsonify(challenges)
def login(): try: username = request.json["Username"] except: return jsonify({'Accept': False, 'Token': 'Username not found'}) try: password = request.json["Password"] except: return jsonify({'Accept': False, 'Token': 'Password not found'}) db = get_db() cursor = db.cursor() sql_query = ''' SELECT Password, UserID, NOW(3), Validated AS CurDate FROM users WHERE Username=%(username)s; ''' query_model = {"username": username} cursor.execute(sql_query, query_model) data = cursor.fetchall() if len(data) == 0: return jsonify({'Accept': False, 'Token': 'Username not found'}) hashed_pw = (data[0])[0] validated = (data[0])[3] if not validated or not argon2.verify(password, hashed_pw): return jsonify({'Accept': False, 'Token': ''}) else: user_id = (data[0])[1] curr_date = (data[0])[2] to_encrypt = json.dumps({ "user_id": user_id, "time_issued": str(curr_date) }) encrypted_token = encrypt_token(to_encrypt).decode() return jsonify({'Accept': True, 'Token': encrypted_token})
def complete_daily_challenge(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return '-1' except: return '-1' try: difficulty = request.json["Difficulty"] except: abort(500, "difficulty not found") db = get_db() cursor = db.cursor() sql_query = ''' SELECT Length, Relay, Difficulty FROM dailyChallenges WHERE Difficulty = %(difficulty)s ''' query_model = {"difficulty": difficulty} cursor.execute(sql_query, query_model) data = cursor.fetchall() length = (data[0])[0] difficulty = (data[0])[2] xp_reward = calculate_xp_reward(length, difficulty) query_model = { "difficulty": difficulty, "user_id": user_id, "xp_reward": xp_reward } cursor.close() cursor = db.cursor() sql_query = ''' INSERT INTO dailyChallengesCompleted (UserID, Difficulty) VALUES (%(user_id)s, %(difficulty)s) ''' cursor.execute(sql_query, query_model) cursor.close() cursor = db.cursor() if difficulty == 1: sql_query = ''' UPDATE accountData SET XP = XP + %(xp_reward)s, EasyDailiesCompleted = EasyDailiesCompleted + 1 WHERE UserID = %(user_id)s ''' elif difficulty == 2: sql_query = ''' UPDATE accountData SET XP = XP + %(xp_reward)s, MediumDailiesCompleted = MediumDailiesCompleted + 1 WHERE UserID = %(user_id)s ''' elif difficulty == 3: sql_query = ''' UPDATE accountData SET XP = XP + %(xp_reward)s, HardDailiesCompleted = HardDailiesCompleted + 1 WHERE UserID = %(user_id)s ''' elif difficulty == 4: sql_query = ''' UPDATE accountData SET XP = XP + %(xp_reward)s, ExtremeDailiesCompleted = ExtremeDailiesCompleted + 1 WHERE UserID = %(user_id)s ''' cursor.execute(sql_query, query_model) cursor.close() db.commit() return jsonify({"success": True})
def request_password_reset(): db = get_db() post_data = request.json try: email_address = post_data["Email"] except: abort(500, "Email not found") cursor = db.cursor() sql_query = ''' SELECT UserID FROM users WHERE Email = %(Email)s ''' cursor.execute(sql_query, post_data) data = cursor.fetchall() if len(data) == 0: print('rejecting') return jsonify({ "message": "Please check your email for a link to reset your password" }) user_id = (data[0])[0] reset_id = uuid.uuid4() reset_entry = {"user_id": str(user_id), "reset_id": str(reset_id)} sql_query = ''' SELECT * FROM passwordResets WHERE UserID = %(user_id)s ''' cursor.execute(sql_query, reset_entry) data = cursor.fetchall() if len(data) == 0: sql_query = ''' INSERT INTO passwordResets(UserID, ValidationID) VALUES (%(user_id)s, %(reset_id)s) ''' cursor.execute(sql_query, reset_entry) db.commit() else: sql_query = ''' UPDATE passwordResets SET ValidationID = %(reset_id)s WHERE UserID = %(user_id)s ''' cursor.execute(sql_query, reset_entry) db.commit() reset_url = "https://puzzlehub.io/ResetPassword;code=" + str(reset_id) SENDER = "*****@*****.**" SENDERNAME = "No Reply" RECIPIENT = str(email_address) USERNAME_SMTP = json_data['email_username'] PASSWORD_SMTP = json_data['email_password'] HOST = "email-smtp.us-east-1.amazonaws.com" PORT = 587 SUBJECT = "Reset Your Puzzle Hub Password" BODY_TEXT = ''' Someone has requested a password reset for your account on Puzzle Hub. If this was you, you can set a new password here: \n\n ''' + reset_url + '''\n\n If you don't want to change your password or didn't request this, just ignore and delete this message. To keep your account secure, please don't forward this email to anyone. Happy Puzzling! ''' msg = MIMEMultipart('alternative') msg['Subject'] = SUBJECT msg['From'] = email.utils.formataddr((SENDERNAME, SENDER)) msg['To'] = RECIPIENT # Record the MIME types of both parts - text/plain and text/html. part1 = MIMEText(BODY_TEXT, 'plain') # Attach parts into message container. # According to RFC 2046, the last part of a multipart message, in this case # the HTML message, is best and preferred. msg.attach(part1) # Try to send the message. try: server = smtplib.SMTP(HOST, PORT) server.ehlo() server.starttls() server.ehlo() server.login(USERNAME_SMTP, PASSWORD_SMTP) server.sendmail(SENDER, RECIPIENT, msg.as_string()) server.close() except Exception as e: print("Error: ", e) return jsonify({ "message": "Please check your email for a link to reset your password" })
def get_footer(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) except: return jsonify([]) try: game_id = request.json["GameID"] except: abort(500, "GameID not found") try: difficulty = request.json["Difficulty"] except: abort(500, "Difficulty not found") try: leaderboard = request.json["Leaderboard"] except: abort(500, "Leaderboard not found") db = get_db() cursor = db.cursor() if leaderboard == 0: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM dailyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 0 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' elif leaderboard == 1: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM weeklyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 1 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' elif leaderboard == 2: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM monthlyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 2 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' query_model = { "game_id": game_id, "difficulty": difficulty, "user_id": user_id } cursor.execute(sql_query, query_model) data = cursor.fetchall() to_return = [] for d in data: model = { "username": d[0], "time": convert_to_puzzle_hub_date(d[1]), "role": str(d[2]), "bronzeMedals": d[3], "silverMedals": d[4], "goldMedals": d[5], "position": 0 } to_return.append(model) return jsonify(to_return)
def get_leaderboards_siege(): try: user_id = 13 except: user_id = -1 try: position = 0 except: position = 0 try: num_entries = 25 except: num_entries = 25 try: game_id = 5 except: abort(500, "GameID not found") try: difficulty = 1 except: abort(500, "Difficulty not found") try: leaderboard = 2 except: abort(500, "Leaderboard not found") db = get_db() cursor = db.cursor() if leaderboard == 0: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM dailyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 0 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' elif leaderboard == 1: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM weeklyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 1 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' elif leaderboard == 2: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM monthlyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 2 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' query_model = { "game_id": game_id, "difficulty": difficulty, "num_entries": num_entries, "position": position } cursor.execute(sql_query, query_model) data = cursor.fetchall() to_return = [] for d in data: position = position + 1 model = { "username": d[0], "time": convert_to_puzzle_hub_date(d[1]), "role": str(d[2]), "bronzeMedals": d[3], "silverMedals": d[4], "goldMedals": d[5], "position": position } to_return.append(model) if user_id != -1: cursor = db.cursor() if leaderboard == 0: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM dailyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 0 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' elif leaderboard == 1: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM weeklyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 1 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' elif leaderboard == 2: sql_query = ''' SELECT Username, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM monthlyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 2 WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND U.UserID = %(user_id)s ''' query_model = { "game_id": game_id, "difficulty": difficulty, "user_id": user_id } cursor.execute(sql_query, query_model) data = cursor.fetchall() for d in data: model = { "username": d[0], "time": convert_to_puzzle_hub_date(d[1]), "role": str(d[2]), "bronzeMedals": d[3], "silverMedals": d[4], "goldMedals": d[5], "position": 0 } to_return.append(model) return jsonify(to_return)
def get_personal_best(): try: user_id = get_user_id(xstr(request.headers.get('PuzzleHubToken'))) if user_id == -1: return '-1' except: return '-1' try: game_id = request.json["GameID"] except: abort(500, "GameID not found") try: difficulty = request.json["Difficulty"] except: abort(500, "difficulty not found") db = get_db() cursor = db.cursor() sql_query_1 = ''' SELECT TimeElapsed FROM dailyLeaderboards WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND UserID = %(user_id)s ''' sql_query_2 = ''' SELECT TimeElapsed FROM weeklyLeaderboards WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND UserID = %(user_id)s ''' sql_query_3 = ''' SELECT TimeElapsed FROM monthlyLeaderboards WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s AND UserID = %(user_id)s ''' query_model = { "game_id": game_id, "difficulty": difficulty, "user_id": user_id } cursor.execute(sql_query_1, query_model) data_1 = cursor.fetchall() cursor.close() cursor = db.cursor() cursor.execute(sql_query_2, query_model) data_2 = cursor.fetchall() cursor.close() cursor = db.cursor() cursor.execute(sql_query_3, query_model) data_3 = cursor.fetchall() cursor.close() daily_time = "N/A" weekly_time = "N/A" monthly_time = "N/A" if len(data_1) != 0: daily_time = convert_to_puzzle_hub_date((data_1[0])[0]) if len(data_2) != 0: weekly_time = convert_to_puzzle_hub_date((data_2[0])[0]) if len(data_3) != 0: monthly_time = convert_to_puzzle_hub_date((data_3[0])[0]) model = { "daily": daily_time, "weekly": weekly_time, "monthly": monthly_time } return jsonify(model)
def get_leaderboards(): try: position = request.json["Position"] except: position = 0 try: num_entries = request.json["NumEntries"] if num_entries > 25: num_entries = 25 elif num_entries < 1: num_entries = 1 except: num_entries = 25 try: game_id = request.json["GameID"] except: abort(500, "GameID not found") try: difficulty = request.json["Difficulty"] except: abort(500, "Difficulty not found") try: leaderboard = request.json["Leaderboard"] except: abort(500, "Leaderboard not found") db = get_db() cursor = db.cursor() if leaderboard == 0: sql_query = ''' SELECT Username, XP, PuzzlerIcon, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM dailyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 0 INNER JOIN accountData AS AD ON AD.UserID = L.UserID WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' elif leaderboard == 1: sql_query = ''' SELECT Username, XP, PuzzlerIcon, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM weeklyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 1 INNER JOIN accountData AS AD ON AD.UserID = L.UserID WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' elif leaderboard == 2: sql_query = ''' SELECT Username, XP, PuzzlerIcon, TimeElapsed, Role, BronzeMedals, SilverMedals, GoldMedals FROM monthlyLeaderboards AS L INNER JOIN users AS U ON U.UserID = L.UserID INNER JOIN userMedals AS UM ON UM.UserID = L.UserID AND UM.Type = 2 INNER JOIN accountData AS AD ON AD.UserID = L.UserID WHERE GameID = %(game_id)s AND Difficulty = %(difficulty)s ORDER BY TimeElapsed LIMIT %(num_entries)s OFFSET %(position)s ''' query_model = { "game_id": game_id, "difficulty": difficulty, "num_entries": num_entries, "position": position } cursor.execute(sql_query, query_model) data = cursor.fetchall() to_return = [] for d in data: position = position + 1 model = { "username": d[0], "XP": d[1], "puzzlerIcon": d[2], "time": convert_to_puzzle_hub_date(d[3]), "role": str(d[4]), "bronzeMedals": d[5], "silverMedals": d[6], "goldMedals": d[7], "position": position } to_return.append(model) return jsonify(to_return)
def get_profile_data(): try: username = request.json["Username"] except: abort(500, "Username not found") db = get_db() cursor = db.cursor() sql_query = ''' SELECT MH.GameID, MH.Difficulty, MH.Date AS TimeCompleted, MH.TimeElapsed AS Time, MH.Seed FROM users AS U INNER JOIN matchHistory AS MH ON U.UserID = MH.UserID WHERE U.Username = %(username)s ORDER BY MH.Date DESC LIMIT 10 ''' query_model = {"username": username} cursor.execute(sql_query, query_model) data = cursor.fetchall() match_history = [] for d in data: model = { "GameID": d[0], "Difficulty": d[1], "TimeCompleted": str(d[2]), "TimeElapsed": convert_to_puzzle_hub_date(d[3]), "Seed": d[4] } match_history.append(model) cursor.close() cursor = db.cursor() sql_query = ''' SELECT M1.BronzeMedals AS DailyBronzeMedals, M2.BronzeMedals AS WeeklyBronzeMedals, M3.BronzeMedals AS MonthlyBronzeMedals, M1.SilverMedals AS DailySilverMedals, M2.SilverMedals AS WeeklySilverMedals, M3.SilverMedals AS MonthlySilverMedals, M1.GoldMedals AS DailyGoldMedals, M2.GoldMedals AS WeeklyGoldMedals, M3.GoldMedals AS MonthlyGoldMedals, U.Username FROM users AS U INNER JOIN userMedals AS M1 ON M1.UserID = U.UserID AND M1.Type = 0 INNER JOIN userMedals AS M2 ON M2.UserID = U.UserID AND M2.Type = 1 INNER JOIN userMedals AS M3 ON M3.UserID = U.UserID AND M3.Type = 2 WHERE U.Username = %(username)s ''' cursor.execute(sql_query, query_model) data = cursor.fetchall() try: d = data[0] except: return jsonify([]) cursor.close() cursor = db.cursor() sql_query = ''' SELECT AD.XP, AD.PuzzlerIcon, U.Role, AD.EasyDailiesCompleted, AD.MediumDailiesCompleted, AD.HardDailiesCompleted, AD.ExtremeDailiesCompleted FROM accountData AS AD INNER JOIN users AS U ON U.UserID = AD.UserID WHERE U.Username = %(username)s ''' cursor.execute(sql_query, query_model) data = cursor.fetchall() ad = data[0] cursor.close() cursor = db.cursor() sql_query = ''' SELECT GP.GameID, GP.Difficulty, GP.GamesPlayed FROM gamesPlayed AS GP INNER JOIN users AS U ON U.UserID = GP.UserID WHERE U.Username = %(username)s ''' cursor.execute(sql_query, query_model) data = cursor.fetchall() games_played = [] for entry in data: to_append = { "GameID": entry[0], "Difficulty": entry[1], "GamesPlayed": entry[2] } games_played.append(to_append) to_return = { "DailyGoldMedals": d[6], "DailySilverMedals": d[3], "DailyBronzeMedals": d[0], "WeeklyGoldMedals": d[7], "WeeklySilverMedals": d[4], "WeeklyBronzeMedals": d[1], "MonthlyGoldMedals": d[8], "MonthlySilverMedals": d[5], "MonthlyBronzeMedals": d[2], "XP": ad[0], "PuzzlerIcon": ad[1], "Role": ad[2], "EasyDailies": ad[3], "MediumDailies": ad[4], "HardDailies": ad[5], "ExtremeDailies": ad[6], "MatchHistory": match_history, "GamesPlayed": games_played, "Username": d[9] } return jsonify(to_return)
def register_user(): db = get_db() post_data = request.json try: username = post_data["Username"] password = post_data["Password"] email_address = post_data["Email"] except BadRequestKeyError: abort(400, "ERROR: malformed post request") try: token = post_data["Token"] except: return jsonify({ "success": False, "message": "Failed to retrieve reCAPTCHA token, please refresh and try again. If this issue persists, please email [email protected]" }) try: refer = post_data["Refer"] except: refer = '' captcha_model = { "secret": "6Ldx55wUAAAAAMQyfKUezVAoZM7MpPq3UReBo4qp", "response": str(token) } r = requests.post('https://www.google.com/recaptcha/api/siteverify', captcha_model) json_response = r.json() if not json_response["success"]: return jsonify({ "success": False, "message": "reCAPTCHA verification failed. Please refresh and try again. If this issue persists, please email [email protected]" }) # check that the username meets our guidelines if len(username) > 12: return jsonify({ "success": False, "message": "Username length must be no more than 12 characters." }) if not username.isalnum(): return jsonify({ "success": False, "message": "Username must be alpha numeric." }) repeat_char = re.compile(r'(.)\1\1\1\1\1') # test that the password meets our guidelines if len(password) < 8: return jsonify({ "success": False, "message": "Password length too short" }) if len(password) > 64: return jsonify({ "success": False, "message": "Password length too long" }) if repeat_char.match(password) is not None: return jsonify({ "success": False, "message": "Password has repeating characters" }) if check_digits(password): return jsonify({ "success": False, "message": "Password has incrementing numbers" }) # Uncomment this if we're gonna store sensitive data # if is_pwned_password(password): # return jsonify({"success":False,"message":"This password has been leaked in a known data breach. Please use a different password. For more info see https://haveibeenpwned.com/Passwords"}) # test that the email is valid if check_valid_email(email_address) is False: return jsonify({"success": False, "message": "Email is invalid."}) # hash the password password_hash = argon2.using(time_cost=160, memory_cost=10240, parallelism=8).hash(password) cursor = db.cursor() # --------------------------------------------------------------- # check if an account already exists with the given username sql_query = ''' SELECT * FROM users WHERE Username = %(Username)s ''' cursor.execute(sql_query, post_data) data = cursor.fetchall() if len(data) > 0: return jsonify({ "success": False, "message": "This username is already taken!" }) # check if an account already exists with the given email sql_query = ''' SELECT * FROM users WHERE Email = %(Email)s ''' cursor.execute(sql_query, post_data) data = cursor.fetchall() if len(data) > 0: return jsonify({ "success": False, "message": "This email is already taken!" }) # --------------------------------------------------------------- #add to table sql_query = ''' INSERT INTO users (Username, Email, Password, Refer) VALUES (%(username)s, %(email)s, %(password)s, %(refer)s) ''' user_entry = { "username": str(username), "email": str(email_address), "password": str(password_hash), "refer": str(refer) } cursor.execute(sql_query, user_entry) db.commit() user_id = cursor.lastrowid #generate validation id validation_id = uuid.uuid4() sql_query = ''' INSERT INTO validations(UserID, ValidationID) VALUES (%(user_id)s, %(validation_id)s) ''' validation_entry = { "user_id": str(user_id), "validation_id": str(validation_id) } cursor.execute(sql_query, validation_entry) db.commit() sql_query = ''' INSERT INTO userMedals(UserID, Type, BronzeMedals, SilverMedals, GoldMedals) VALUES (%(user_id)s, 0, 0, 0, 0), (%(user_id)s, 1, 0, 0, 0), (%(user_id)s, 2, 0, 0, 0) ''' cursor.execute(sql_query, validation_entry) db.commit() sql_query = ''' INSERT INTO accountData(UserID, XP, PuzzlerIcon) VALUES (%(user_id)s, 0, 0) ''' cursor.execute(sql_query, validation_entry) db.commit() validation_url = "https://puzzlehub.io/EmailVerify;code=" + str( validation_id) # Send validation url SENDER = "*****@*****.**" SENDERNAME = "No Reply" RECIPIENT = str(email_address) USERNAME_SMTP = json_data['email_username'] PASSWORD_SMTP = json_data['email_password'] HOST = "email-smtp.us-east-1.amazonaws.com" PORT = 587 SUBJECT = "Puzzle Hub Email Verification" BODY_TEXT = ''' Thank you for registering for an account on puzzlehub.io! Please click the following link to verify your account \n\n ''' + validation_url msg = MIMEMultipart('alternative') msg['Subject'] = SUBJECT msg['From'] = email.utils.formataddr((SENDERNAME, SENDER)) msg['To'] = RECIPIENT # Record the MIME types of both parts - text/plain and text/html. part1 = MIMEText(BODY_TEXT, 'plain') # Attach parts into message container. # According to RFC 2046, the last part of a multipart message, in this case # the HTML message, is best and preferred. msg.attach(part1) # Try to send the message. try: server = smtplib.SMTP(HOST, PORT) server.ehlo() server.starttls() server.ehlo() server.login(USERNAME_SMTP, PASSWORD_SMTP) server.sendmail(SENDER, RECIPIENT, msg.as_string()) server.close() except Exception as e: print("Error: ", e) return jsonify({"success": True})
def change_password_with_code(): db = get_db() post_data = request.json try: code = post_data["Code"] password = post_data["NewPassword"] except: abort(500, "Missing required post data") repeat_char = re.compile(r'(.)\1\1\1\1\1') # test that the password meets our guidelines if len(password) < 8: return jsonify({ "success": False, "message": "Password length too short" }) if len(password) > 64: return jsonify({ "success": False, "message": "Password length too long" }) if repeat_char.match(password) is not None: return jsonify({ "success": False, "message": "Password has repeating characters" }) if check_digits(password): return jsonify({ "success": False, "message": "Password has incrementing numbers" }) if is_pwned_password(password): return jsonify({ "success": False, "message": "This password has been leaked in a known data breach. Please use a different password" }) password_hash = argon2.using(time_cost=160, memory_cost=10240, parallelism=8).hash(password) cursor = db.cursor() sql_query = ''' SELECT UserID FROM passwordResets WHERE ValidationID = %(Code)s ''' cursor.execute(sql_query, post_data) data = cursor.fetchall() if len(data) == 0: return jsonify({ "success": True, "message": "Success! You can now log in" }) user_id = (data[0])[0] sql_query = ''' UPDATE users SET Password = %(hash)s WHERE UserID = %(user_id)s ''' query_model = {"hash": str(password_hash), "user_id": user_id} cursor.execute(sql_query, query_model) db.commit() return jsonify({"success": True, "message": "Success! You can now log in"})