def user_confirm(data, stage): confirmation_id = data["id"] with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: LOGGER.info("CONFIRM: {}".format(confirmation_id)) cursor.execute("SELECT user_id FROM confirmations where id=%s;", (confirmation_id, )) response = cursor.fetchone() if not response: return api_response( 409, "invalid confirmation or confirmation already completed") user_id = response[0] cursor.execute("DELETE FROM confirmations where id=%s;", (confirmation_id, )) cursor.execute( "UPDATE users SET date_confirmed=now() where id=%s;", (user_id, )) cursor.execute("SELECT email FROM users where id=%s;", (user_id, )) email = cursor.fetchone()[0] psql.commit() LOGGER.info("EMAIL: {}".format(email)) body = ("Your registration to DEF CON CTF Quals is complete.\n\n" "https://scoreboard.oooverflow.io/\n") send_email( "OOO Account Registration <*****@*****.**>", email, "[OOO] Registration Complete", body, stage=stage, ) return api_response(200, "confirmation complete")
def challenge_open(event, _context): if not event: return api_response(422, "data must be provided") challenge_id = event.get("id", None) if not challenge_id: return api_response(422, "invalid challenge id") with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute( "SELECT category_id, flag_hash, description, " "tags FROM unopened_challenges WHERE id=%s", (challenge_id, ), ) result = cursor.fetchone() if not result: return api_response( 400, "that challenge does not exist or was already opened") cursor.execute("DELETE FROM unopened_challenges WHERE id=%s", (challenge_id, )) cursor.execute( "INSERT INTO challenges VALUES (%s, now(), %s, %s, %s, %s);", (challenge_id, *result), ) psql.commit() return api_response(201)
def token(data, stage): now = int(time.time()) if now >= COMPETITION_END: return api_response(400, "the competition is over") email = data["email"] with psql_connection(SECRETS["DB_PASSWORD"], SECRETS["DB_USERNAME"]) as psql: with psql.cursor() as cursor: LOGGER.info("USER LOGIN {}".format(email)) cursor.execute( "SELECT id, team_name FROM users where " "date_confirmed IS NOT NULL AND " "lower(email)=%s AND password=crypt(%s, password);", (email.lower(), data["password"]), ) response = cursor.fetchone() if not response: return api_response(401, "invalid credentials") expire_time = min(COMPETITION_END, now + TWELVE_HOURS) payload = {"exp": expire_time, "nbf": now, "user_id": response[0]} token = jwt.encode(payload, SECRETS["JWT_SECRET"], algorithm="HS256").decode("utf-8") return api_response(200, {"team": response[1], "token": token})
def token(data, stage): now = int(time.time()) if stage == 'prod' and now < COMPETITION_START: return api_response(400, 'the competition has not yet started') if now >= COMPETITION_END: return api_response(400, 'the competition is over') email = data['email'] with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: LOGGER.info('USER LOGIN {}'.format(email)) cursor.execute( 'SELECT id, team_name FROM users where ' 'date_confirmed IS NOT NULL AND ' 'lower(email)=%s AND password=crypt(%s, password);', (email, data['password'])) response = cursor.fetchone() if not response: return api_response(401, 'invalid credentials') if stage == 'prod': now = max(now, COMPETITION_START) expire_time = min(COMPETITION_END, now + TWELVE_HOURS) payload = {'exp': expire_time, 'nbf': now, 'user_id': response[0]} token = jwt.encode(payload, SECRETS['JWT_SECRET'], algorithm='HS256').decode('utf-8') return api_response(200, {'team': response[1], 'token': token})
def wrapped(event, _context=None, **headers): if validate_data: data = parse_json_request(event) else: data = event["pathParameters"] if data is None: return api_response(422, "invalid request") log_request(data) parameters = {} for parameter, validator in validators.items(): result = validator(data.get(parameter)) if result is False: return api_response(422, "invalid {}".format(parameter)) elif isinstance(result, dict): return api_response(**result) elif not isinstance(result, bool): return api_response(422, result) parameters[parameter] = data[parameter] del data[parameter] if data: return api_response(422, "unexpected {}".format(list(data)[0])) return function(parameters, event["requestContext"]["stage"], **headers)
def user_register(data, stage, app_url): team_id = data['ctf_time_team_id'] team_id = None if team_id == '' else int(team_id) email = data['email'] password = data['password'] team_name = data['team_name'] with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: LOGGER.info('USER REGISTER {}'.format(email)) try: cursor.execute( 'INSERT INTO users VALUES (DEFAULT, now(), ' 'NULL, %s, crypt(%s, gen_salt(\'bf\', 10)), ' '%s, %s)', (email, password, team_name, team_id)) except psycopg2.IntegrityError as exception: if 'email' in exception.diag.constraint_name: return api_response(409, 'duplicate email') return api_response(409, 'duplicate team name') cursor.execute('SELECT id FROM users where email=%s;', (email, )) user_id = cursor.fetchone()[0] confirmation_id = str(uuid.uuid4()) cursor.execute('INSERT INTO confirmations VALUES (%s, %s);', (confirmation_id, user_id)) psql.commit() confirmation_url = '{}/#/confirm/{}'.format(app_url, confirmation_id) body = 'Please confirm your account creation:\n\n{}\n'.format( confirmation_url) send_email('OOO Account Registration <*****@*****.**>', email, '[OOO] Please Confirm Your Registration', body, stage=stage) return api_response(201)
def user_confirm(data, stage): confirmation_id = data['id'] with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: LOGGER.info('CONFIRM: {}'.format(confirmation_id)) cursor.execute('SELECT user_id FROM confirmations where id=%s;', (confirmation_id, )) response = cursor.fetchone() if not response: return api_response( 409, 'invalid confirmation or confirmation' ' already completed') user_id = response[0] cursor.execute('DELETE FROM confirmations where id=%s;', (confirmation_id, )) cursor.execute( 'UPDATE users SET date_confirmed=now() ' 'where id=%s;', (user_id, )) cursor.execute('SELECT email FROM users where id=%s;', (user_id, )) email = cursor.fetchone()[0] psql.commit() LOGGER.info('EMAIL: {}'.format(email)) body = ('Your registration to DEF CON 2018 CTF Quals is complete.\n\n' 'https://scoreboard.oooverflow.io/\n') send_email('OOO Account Registration <*****@*****.**>', email, '[OOO] Registration Complete', body, stage=stage) return api_response(200, 'confirmation complete')
def token_refresh(data, _stage): payload = jwt.decode(data["token"], verify=False) now = int(time.time()) updated = payload["user_updated"] user_id = payload["user_id"] with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: LOGGER.info("TOKEN REFRESH {}".format(payload["user_id"])) cursor.execute( "SELECT 1 FROM users where extract(epoch from date_updated)=%s AND id=%s;", (updated, user_id), ) response = cursor.fetchone() if not response: return api_response(401, f"cannot find matching user ({updated})") access_payload = { "exp": min(COMPETITION_END, now + ACCESS_TOKEN_DURATION), "nbf": now, "token_type": "access", "user_id": payload["user_id"], } access_token = jwt.encode(access_payload, JWT_SECRET, algorithm="HS256").decode("utf-8") return api_response(200, {"access_token": access_token})
def challenges_set(event, context): if not event: return api_response(422, "data for the scoreboard must be provided") if not isinstance(event, list): return api_response(422, "invalid scoreboard data") if "-dev-" not in context.function_name and int( time.time()) > COMPETITION_START: LOGGER.error("Cannot set challenges once the competition has started") return api_response(400, "competition has already started") categories = {} challenge_values = [] try: for challenge in event: categories[challenge["category"]] = None challenge_values.append(challenge["id"]) challenge_values.append(challenge["title"]) challenge_values.append(challenge["description"]) challenge_values.append(challenge["category"]) challenge_values.append(challenge["flag_hash"]) challenge_values.append(", ".join(sorted(challenge["tags"]))) except (KeyError, TypeError): return api_response(422, "invalid scoreboard data") categories_sql = ", ".join(["(DEFAULT, now(), %s)"] * len(categories)) challenges_sql = ", ".join(["(%s, now(), %s, %s, %s, %s, %s)"] * len(event)) with psql_connection(SECRETS["DB_PASSWORD"], SECRETS["DB_USERNAME"]) as psql: with psql.cursor() as cursor: LOGGER.info("Empty challenges and categories tables") cursor.execute("TRUNCATE categories, challenges, solves, " "submissions, unopened_challenges;") LOGGER.info("Add categories") cursor.execute( "INSERT INTO categories VALUES {};".format(categories_sql), tuple(categories), ) LOGGER.info("Get category IDs") cursor.execute("SELECT id, name FROM categories;") results = cursor.fetchall() for category_id, category_name in results: categories[category_name] = category_id # Replace category name with category_id for index in range(3, len(challenge_values), 6): challenge_values[index] = categories[challenge_values[index]] LOGGER.info("Add challenges") cursor.execute( "INSERT INTO unopened_challenges VALUES {};".format( challenges_sql), tuple(challenge_values), ) psql.commit() return api_response(201, "unopened_challenges set")
def challenge(data, stage): with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: cursor.execute('SELECT description FROM challenges where id=%s', (data['id'], )) result = cursor.fetchone() if not result: return api_response(404) return api_response(200, result[0])
def challenge(data, _stage): with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute("SELECT description FROM challenges where id=%s", (data["id"], )) result = cursor.fetchone() if not result: return api_response(404) return api_response(200, result[0])
def challenge(data, stage): with psql_connection(SECRETS["DB_PASSWORD"], SECRETS["DB_USERNAME"]) as psql: with psql.cursor() as cursor: cursor.execute("SELECT description FROM challenges where id=%s", (data["id"], )) result = cursor.fetchone() if not result: return api_response(404) return api_response(200, result[0])
def challenges_set(event, context): if not event: return api_response(422, 'data for the scoreboard must be provided') if not isinstance(event, list): return api_response(422, 'invalid scoreboard data') if '-dev-' not in context.function_name and \ int(time.time()) > COMPETITION_START: LOGGER.error('Cannot set challenges once the competition has started') return api_response(400, 'competition has already started') categories = {} challenge_values = [] try: for challenge in event: categories[challenge['category']] = None challenge_values.append(challenge['id']) challenge_values.append(challenge['title']) challenge_values.append(challenge['description']) challenge_values.append(challenge['category']) challenge_values.append(challenge['flag_hash']) challenge_values.append(', '.join(sorted(challenge['tags']))) except (KeyError, TypeError): return api_response(422, 'invalid scoreboard data') categories_sql = ', '.join(['(DEFAULT, now(), %s)'] * len(categories)) challenges_sql = ', '.join(['(%s, now(), %s, %s, %s, %s, %s)'] * len(event)) with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: LOGGER.info('Empty challenges and categories tables') cursor.execute('TRUNCATE categories, challenges, solves, ' 'submissions, unopened_challenges;') LOGGER.info('Add categories') cursor.execute( 'INSERT INTO categories VALUES {};'.format(categories_sql), tuple(categories)) LOGGER.info('Get category IDs') cursor.execute('SELECT id, name FROM categories;') results = cursor.fetchall() for category_id, category_name in results: categories[category_name] = category_id # Replace challenge name with challenge_id for index in range(3, len(challenge_values), 6): challenge_values[index] = categories[challenge_values[index]] LOGGER.info('Add challenges') cursor.execute( 'INSERT INTO unopened_challenges VALUES {};'.format( challenges_sql), tuple(challenge_values)) psql.commit() return api_response(201, 'unopened_challenges set')
def challenges_add(event, _context): if not event: return api_response(422, "data for the scoreboard must be provided") if not isinstance(event, list): return api_response(422, "invalid scoreboard data") with psql_connection(SECRETS["DB_PASSWORD"], SECRETS["DB_USERNAME"]) as psql: with psql.cursor() as cursor: LOGGER.info("Get category IDs") cursor.execute("SELECT id, name FROM categories;") categories = {x[1]: x[0] for x in cursor.fetchall()} LOGGER.info("Get opened challenges") cursor.execute("SELECT name FROM challenges;") existing_challenges = [x[0] for x in cursor.fetchall()] LOGGER.info("Get unopened challenges") cursor.execute("SELECT name FROM unopened_challenges;") existing_challenges.extend(x[0] for x in cursor.fetchall()) LOGGER.info(f"Existing challenges: {sorted(existing_challenges)}") new_challenges = [] challenge_values = [] try: for challenge in event: if challenge["id"] in existing_challenges: continue new_challenges.append(challenge["id"]) challenge_values.append(challenge["id"]) challenge_values.append(challenge["title"]) challenge_values.append(challenge["description"]) challenge_values.append(categories[challenge["category"]]) challenge_values.append(challenge["flag_hash"]) challenge_values.append(", ".join(sorted( challenge["tags"]))) except (KeyError, TypeError): return api_response(422, "invalid scoreboard data") if not new_challenges: return api_response(304) LOGGER.info(f"Adding challenges: {sorted(new_challenges)}") challenges_sql = ", ".join(["(%s, now(), %s, %s, %s, %s, %s)"] * len(new_challenges)) cursor.execute( "INSERT INTO unopened_challenges VALUES {};".format( challenges_sql), tuple(challenge_values), ) psql.commit() return api_response(201, "added {} challenge(s)".format(len(new_challenges)))
def challenge_delete(event, _context): if not event: return api_response(422, "data must be provided") challenge_id = event.get("id", None) if not challenge_id: return api_response(422, "invalid challenge id") with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute("DELETE FROM unopened_challenges WHERE id=%s", (challenge_id, )) if cursor.rowcount != 1: return api_response( 400, "that challenge does not exist or was already opened") psql.commit() return api_response(200)
def challenges(_event, _context): with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute( "SELECT challenges.id, " "challenges.tags, categories.name, " "EXTRACT(EPOCH from challenges.date_created) FROM " "challenges JOIN categories ON category_id = categories.id " "ORDER BY challenges.date_created ASC;") open_challenge_data = cursor.fetchall() cursor.execute("SELECT challenge_id, team_name," "EXTRACT(EPOCH FROM solves.date_created) FROM " "solves JOIN users ON user_id=id;") solves = cursor.fetchall() cursor.execute( "SELECT categories.name, count(unopened_challenges.id) FROM " "unopened_challenges JOIN categories ON " "category_id=categories.id GROUP BY categories.name;") unopened_by_category = dict(cursor.fetchall()) return api_response( 200, { "open": open_challenge_data, "solves": solves, "unopened_by_category": unopened_by_category, }, log_message=False, )
def wrapped(event, _context): result = {} for variable, header in headers.items(): result[variable] = event["headers"].get(header, None) if not result[variable]: return api_response(422, "{} header missing".format(header)) return function(event, **result)
def wrapped(data, stage, **headers): message = '!'.join([str(data[x]) for x in fields]) digest = hashlib.sha256(message.encode()).hexdigest() if not digest.startswith(prefix): return api_response(422, 'incorrect nonce') del data['nonce'] del data['timestamp'] return function(data, stage, **headers)
def token(data, _stage): now = int(time.time()) if now >= COMPETITION_END: return api_response(400, "the competition is over") email = data["email"] with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: LOGGER.info("USER LOGIN {}".format(email)) cursor.execute( "SELECT date_updated, id, team_name FROM users where " "date_confirmed IS NOT NULL AND " "lower(email)=%s AND password=crypt(%s, password);", (email.lower(), data["password"]), ) response = cursor.fetchone() if not response: return api_response(401, "invalid credentials") access_payload = { "exp": min(COMPETITION_END, now + ACCESS_TOKEN_DURATION), "nbf": now, "token_type": "access", "user_id": response[1], } access_token = jwt.encode(access_payload, JWT_SECRET, algorithm="HS256").decode("utf-8") refresh_payload = { "exp": COMPETITION_END, "nbf": now, "token_type": "refresh", "user_id": response[1], "user_updated": datetime.timestamp(response[0]), } refresh_token = jwt.encode(refresh_payload, JWT_SECRET, algorithm="HS256").decode("utf-8") return api_response( 200, { "access_token": access_token, "refresh_token": refresh_token, "team": response[2], }, )
def challenge_update(event, _context): if not event: return api_response(422, "data must be provided") challenge_id = event["id"] description = event["description"] flag_hash = event["flag_hash"] with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute("SELECT 1 FROM challenges WHERE id=%s", (challenge_id, )) result = cursor.fetchone() table = "challenges" if result else "unopened_challenges" cursor.execute( f"UPDATE {table} set description=%s, flag_hash=%s where id=%s;", (description, flag_hash, challenge_id), ) psql.commit() return api_response(200)
def migrate(event, context): prod = '-dev-' not in context.function_name reset = event.get('reset') if prod and reset: reset = False LOGGER.warn('Cannot reset the prod environment') with psql_connection(SECRETS['DB_PASSWORD']) as psql: result = migrations.run_migrations(psql, reset_db=reset) psql.commit() return api_response(200, result)
def migrate(event, context): prod = "-dev-" not in context.function_name reset = event.get("reset") if prod and reset: reset = False LOGGER.warn("Cannot reset the prod environment") with psql_connection(SECRETS["DB_PASSWORD"], SECRETS["DB_USERNAME"]) as psql: result = migrations.run_migrations(psql, reset_db=reset) psql.commit() return api_response(200, result)
def user_reset_password(event, _context): if not event: return api_response(422, "data must be provided") email = event.get("email", None) password = event.get("password", None) if not email or not password: return api_response(422, "both email and password must be set") if not valid_password(password, None): return api_response(422, "invalid password") with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute( "UPDATE users set date_updated=now(), password=crypt(%s, gen_salt('bf', 10)) where lower(email)=%s;", (password, email.lower()), ) if cursor.rowcount != 1: return api_response(422, "invalid email") psql.commit() return api_response(200, "password updated")
def user_register(data, stage, app_url): team_id = data["ctf_time_team_id"] team_id = None if team_id == "" else int(team_id) email = data["email"] password = data["password"] team_name = data["team_name"] with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: LOGGER.info("USER REGISTER {}".format(email)) try: cursor.execute( "INSERT INTO users (id, date_created, date_updated, ctf_time_team_id, " "email, password, team_name) VALUES (DEFAULT, now(), now(), %s, %s, " "crypt(%s, gen_salt('bf', 10)), %s)", (team_id, email, password, team_name), ) except psycopg2.IntegrityError as exception: if "email" in exception.diag.constraint_name: return api_response(409, "duplicate email") return api_response(409, "duplicate team name") cursor.execute("SELECT id FROM users where email=%s;", (email, )) user_id = cursor.fetchone()[0] confirmation_id = str(uuid.uuid4()) cursor.execute( "INSERT INTO confirmations (id, user_id) VALUES (%s, %s);", (confirmation_id, user_id), ) psql.commit() confirmation_url = "{}/#/confirm/{}".format(app_url, confirmation_id) body = "Please confirm your account creation:\n\n{}\n".format( confirmation_url) send_email( "OOO Account Registration <*****@*****.**>", email, "[OOO] Please Confirm Your Registration", body, stage=stage, ) return api_response(201)
def teams(_event, _context): with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute(""" SELECT DISTINCT team_name, ctf_time_team_id FROM users JOIN solves ON user_id=id WHERE ctf_time_team_id IS NOT NULL """) mapping = dict(cursor.fetchall()) return api_response(200, {"teams": mapping}, log_message=False)
def users(_event, _context): with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: cursor.execute('SELECT id, email, team_name, ctf_time_team_id ' 'FROM users ORDER BY id;') for row in cursor.fetchall(): print(row) cursor.execute('SELECT * FROM confirmations ORDER BY user_id;') result = cursor.fetchall() if result: print('Pending confirmations') pprint(result) return api_response(200)
def migrate(event, context): production = "-development-" not in context.function_name reset = event.get("reset") if production and reset: reset = False LOGGER.warn("Cannot reset the production environment") if reset: with psql_connection(DB_PASSWORD, "scoreboard", reset=True) as psql: migrations.reset(psql) with psql_connection(DB_PASSWORD, "scoreboard") as psql: result = migrations.run_migrations(psql) psql.commit() return api_response(200, result)
def wrapped(data, stage, **headers): message = "!".join([str(data[x]) for x in fields]) digest = hashlib.sha256(message.encode()).hexdigest() passed = False if callable(prefix): passed = prefix(digest, data) else: passed = digest.startswith(prefix) if not passed: return api_response(422, "incorrect nonce") del data["nonce"] del data["timestamp"] return function(data, stage, **headers)
def challenge_open(event, _context): if not event: return api_response(422, 'data must be provided') challenge_id = event.get('id', None) if not challenge_id: return api_response(422, 'invalid challenge id') with psql_connection(SECRETS['DB_PASSWORD']) as psql: with psql.cursor() as cursor: cursor.execute( 'SELECT name, description, category_id, flag_hash, ' 'tags FROM unopened_challenges WHERE id=%s', (challenge_id, )) result = cursor.fetchone() if not result: return api_response( 400, 'that challenge does not exist or was already opened') cursor.execute('DELETE FROM unopened_challenges WHERE id=%s', (challenge_id, )) cursor.execute( 'INSERT INTO challenges VALUES (%s, now(), %s, %s, ' '%s, %s, %s);', (challenge_id, *result)) psql.commit() return api_response(201)
def users(_event, _context): with psql_connection(DB_PASSWORD, "scoreboard") as psql: with psql.cursor() as cursor: cursor.execute( "SELECT date_created, date_confirmed, email, ctf_time_team_id, team_name " "FROM users ORDER BY date_created;") users = cursor.fetchall() if users: confirmed_users = len([x for x in users if x[1] is not None]) unconfirmed_users = len(users) - confirmed_users print(f" Confirmed Users: {confirmed_users}") if unconfirmed_users > 0: print(f"Unconfirmed Users: {unconfirmed_users}") print(f"Last {min(32, len(users))} Registered Users:") for user in users[-32:]: unconfirmed = "" if user[1] else "unconfirmed" created = str(user[0])[:19] print( f"{created} {unconfirmed:11s} {user[2]:32s} {str(user[3] or ''):>5s} {user[4]}" ) return api_response(200)