def scoreboard(): # get the latest round db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute( 'SELECT id AS rnd, timestamp FROM rounds ORDER BY id DESC LIMIT 1') res = cur.fetchone() rnd = res['rnd'] - 1 if res is not None and res['rnd'] else 0 # get the time left until the next round date_now = datetime.datetime.now() seconds_left = app.config['ROUND_DURATION'] if rnd >= 1: # get seconds left till new round seconds_left = max( ((res['timestamp'] + datetime.timedelta(seconds=app.config['ROUND_DURATION'])) - date_now).seconds, 0) # get all the other stuff out of the cached function scoreboard_data = _scoreboard(rnd) return render_template('scoreboard.html', rnd=rnd, time_left=seconds_left, **scoreboard_data)
def edit_challenge(id): if request.method == 'POST': form = ctforge.forms.ChallengeForm() if form.validate_on_submit(): query_handler( ('UPDATE challenges ' 'SET name = %s, description = %s, flag = %s, points = %s, ' ' active = %s, writeup = %s, writeup_template = %s ' 'WHERE id = %s'), [ form.name.data, form.description.data, form.flag.data, form.points.data, form.active.data, form.writeup.data, form.writeup_template.data, id ]) else: flash_errors(form) else: db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute('SELECT * FROM challenges WHERE id = %s', [id]) challenge = cur.fetchone() if challenge is None: flash('Invalid challenge!', 'error') else: form = ctforge.forms.ChallengeForm(**challenge) return render_template('admin/data.html', form=form, target='challenge', action='edit') return redirect(url_for('admin', tab='challenges'))
def writeup(id, md=0): """Display the provided writeup.""" db_conn = get_db_connection() with db_conn.cursor() as cur: # get the writeup data if it exists cur.execute(( 'SELECT W.id AS id, W.writeup AS writeup, W.timestamp AS timestamp, ' ' U.id AS user_id, U.name AS user_name, U.surname AS user_surname, ' ' C.id AS challenge_id, C.name AS challenge_name, C.points AS challenge_points ' 'FROM writeups AS W ' 'JOIN users AS U ON W.user_id = U.id ' 'JOIN challenges AS C ON W.challenge_id = C.id ' 'WHERE W.id = %s'), [id]) writeup = cur.fetchone() # grant access to the author or admin if writeup is not None and (writeup['user_id'] == current_user.id or current_user.admin): with db_conn.cursor() as cur: cur.execute(('SELECT id, timestamp FROM writeups ' 'WHERE user_id = %s AND challenge_id = %s' 'ORDER BY timestamp DESC'), [writeup['user_id'], writeup['challenge_id']]) writeups = cur.fetchall() return render_template('writeup.html', writeup=writeup, writeups=writeups, md=md) abort(404)
def get(mail): """Return a User instance by querying the database.""" db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute('SELECT * FROM users WHERE mail = %s', [mail]) res = cur.fetchone() return User(**res) if res else None
def service(name): """Display information about a service.""" db_conn = get_db_connection() with db_conn.cursor() as cur: # get service data if the service exists cur.execute('SELECT * FROM services WHERE name = %s', [name]) service = cur.fetchone() if service is None: abort(404) return render_template('service.html', service=service)
def teams(): """Print teams data.""" db_conn = get_db_connection() with db_conn.cursor() as cur: # get teams cur.execute('SELECT id, name FROM teams ORDER BY id') teams = cur.fetchall() # get users cur.execute(('SELECT id, team_id, name, surname ' 'FROM users ORDER BY id')) users = cur.fetchall() return render_template('teams.html', teams=teams, users=users)
def edit_user(id): if request.method == 'POST': form = ctforge.forms.UserForm() if form.validate_on_submit(): if form.password.data: # update the password query_handler( ('UPDATE users ' 'SET team_id = %s, name = %s, surname = %s, ' ' mail = %s, password = %s, admin = %s, hidden = %s ' 'WHERE id = %s'), (form.team_id.data, form.name.data, form.surname.data, form.mail.data, bcrypt.hashpw(form.password.data, bcrypt.gensalt()), form.admin.data, form.hidden.data, id)) else: query_handler( ('UPDATE users ' 'SET team_id = %s, name = %s, surname = %s, ' ' mail = %s, admin = %s, hidden = %s ' 'WHERE id = %s'), (form.team_id.data, form.name.data, form.surname.data, form.mail.data, form.admin.data, form.hidden.data, id)) else: flash_errors(form) else: db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute( ('SELECT id, team_id, name, surname, mail, admin, hidden ' 'FROM users ' 'WHERE id = %s'), [id]) user = cur.fetchone() if user is None: flash('Invalid user!', 'error') else: form = ctforge.forms.UserForm(**user) return render_template('admin/data.html', form=form, target='user', action='edit') return redirect(url_for('admin', tab='users'))
def admin(tab='users'): db_conn = get_db_connection() with db_conn.cursor() as cur: # get the users cur.execute('SELECT * FROM users') users = cur.fetchall() with db_conn.cursor() as cur: # get the teams cur.execute('SELECT * FROM teams') teams = cur.fetchall() with db_conn.cursor() as cur: # get the services cur.execute('SELECT * FROM services') services = cur.fetchall() with db_conn.cursor() as cur: # get the challenges cur.execute('SELECT * FROM challenges') challenges = cur.fetchall() with db_conn.cursor() as cur: # get the challenge writeups cur.execute(( 'SELECT W.id AS id, C.id AS challenge_id, U.id AS user_id, U.mail AS mail, ' ' U.name AS name, U.surname AS surname, C.name AS challenge, ' ' W.timestamp AS timestamp, E.feedback IS NOT NULL AS feedback, ' ' E.grade AS grade, W.timestamp > E.timestamp AS updated ' 'FROM (SELECT user_id, challenge_id, MAX(id) AS id' ' FROM writeups GROUP BY user_id, challenge_id) AS WT ' 'JOIN writeups AS W ON WT.id = W.id ' 'JOIN users AS U ON W.user_id = U.id ' 'JOIN challenges AS C ON W.challenge_id = C.id ' 'LEFT JOIN challenges_evaluations AS E ON U.id = E.user_id AND C.id = E.challenge_id' )) evaluations = cur.fetchall() return render_template('admin/index.html', users=users, teams=teams, services=services, challenges=challenges, evaluations=evaluations, tab=tab)
def team(): """Render a page with useful information about one's team.""" db_conn = get_db_connection() cur = db_conn.cursor() # get the user's own team cur.execute('SELECT * FROM teams WHERE id = %s', [current_user.team_id]) team = cur.fetchone() if team is None: cur.close() flash('Your team id is does not match any team', 'error') return redirect(url_for('index')) # get the members of the user's own team cur.execute('SELECT * FROM users WHERE team_id = %s', [current_user.team_id]) members = cur.fetchall() # for each service get the number of attacks suffered and inflicted the # user's team cur.execute(( 'SELECT S.name AS service_name, ' ' COUNT(A.flag) AS suffered, ' ' (SELECT COUNT(A1.flag) ' ' FROM active_flags AS F1 ' ' JOIN service_attacks AS A1 ON F1.flag = A1.flag ' ' WHERE A1.team_id = F.team_id AND F1.service_id = F.service_id ' ' ) AS inflicted ' 'FROM services AS S ' 'JOIN active_flags AS F ON S.id = F.service_id ' 'LEFT JOIN service_attacks AS A ON F.flag = A.flag ' 'WHERE F.team_id = %s ' 'GROUP BY F.team_id, F.service_id, S.name'), [current_user.team_id]) attacks = cur.fetchall() cur.close() return render_template('team.html', team=team, members=members, attacks=attacks)
def edit_service(id): if request.method == 'POST': form = ctforge.forms.ServiceForm() if form.validate_on_submit(): query_handler(( 'UPDATE services SET name = %s, description = %s, active = %s ' 'WHERE id = %s'), (form.name.data, form.description.data, form.active.data, id)) else: flash_errors(form) else: db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute('SELECT * FROM services WHERE id = %s', [id]) service = cur.fetchone() if service is None: flash('Invalid service!', 'error') else: form = ctforge.forms.ServiceForm(**service) return render_template('admin/data.html', form=form, target='service', action='edit') return redirect(url_for('admin', tab='services'))
def edit_team(id): if request.method == 'POST': form = ctforge.forms.TeamForm() if form.validate_on_submit(): query_handler( ('UPDATE teams SET ip = %s, name = %s, token = %s, poc = %s ' 'WHERE id = %s'), (form.ip.data, form.name.data, form.token.data, form.poc.data, id)) else: flash_errors(form) else: db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute('SELECT * FROM teams WHERE id = %s', [id]) team = cur.fetchone() if team is None: flash('Invalid team!', 'error') else: form = ctforge.forms.TeamForm(**team) return render_template('admin/data.html', form=form, target='team', action='edit') return redirect(url_for('admin', tab='teams'))
def _challenges(): db_conn = get_db_connection() cur = db_conn.cursor() # get the challenges cur.execute('SELECT * FROM challenges') res = cur.fetchall() chals = {c['id']: c for c in res} if len(res) != 0 else dict() # get only the users who solved at least one challenge that are not admin # and not hidden, sorted by timestamp. Along with the users get the # information about the solved challenges cur.execute( ('SELECT U.id AS user_id, U.name AS name, U.surname AS surname, ' ' U.admin AS admin, U.hidden AS hidden, ' ' CA.challenge_id AS challenge_id, CA.timestamp AS timestamp ' 'FROM users AS U JOIN challenge_attacks AS CA ' 'ON U.id = CA.user_id ' 'WHERE NOT admin AND NOT hidden ' 'ORDER BY timestamp ASC ')) challenge_attacks = cur.fetchall() cur.close() # map user id to a string representing his name and surname users = dict() # map the pair challenge id and user id to the timestamp attacks = dict() for ca in challenge_attacks: users[ca['user_id']] = '{} {}'.format(ca['name'], ca['surname']) attacks[(ca['challenge_id'], ca['user_id'])] = ca['timestamp'] # compute the bonus: +3 for firt shot, +2 to second and +1 to third bonus_aux = dict() for (c, u), t in attacks.items(): try: bonus_aux[c].append((u, t)) except KeyError: bonus_aux[c] = [(u, t)] bonus = dict() for c in bonus_aux.keys(): bonus_aux[c] = sorted(bonus_aux[c], key=lambda x: x[1]) for i in range(len(bonus_aux[c])): bonus[(c, bonus_aux[c][i][0])] = 3 - i if i >= 2: break # compute the scoreboard as a list of dictionaries scoreboard = [] for u, uv in users.items(): score = {'user': uv, 'points': 0} score['challenges'] = dict() for c, cv in chals.items(): try: timestamp = attacks[(c, u)] # only add the bonus points if the challenge score is > 0 points = cv['points'] if points > 0: points += bonus.get((c, u), 0) score['points'] += points except KeyError: timestamp = None points = 0 score['challenges'][c] = {'timestamp': timestamp, 'points': points} scoreboard.append(score) # sort the scoreboard by total points scoreboard = sorted(scoreboard, key=lambda x: x['points'], reverse=True) # charts computation graph_template = { "type": "serial", "categoryField": "date", "dataDateFormat": "YYYY-MM-DD HH:NN:SS", "startDuration": 0.5, "startEffect": "easeOutSine", "fontFamily": "Monda", "fontSize": 14, "theme": "dark", "categoryAxis": { "minPeriod": "ss", "parseDates": True }, "chartCursor": { "enabled": True, "categoryBalloonDateFormat": "JJ:NN:SS", "categoryBalloonText": "[[category]]" }, "chartScrollbar": { "enabled": True, "dragIcon": "dragIconRectSmall" }, "trendLines": [], "graphs": [], "guides": [], "valueAxes": [{ "id": "ValueAxis-1", "title": "" }], "allLabels": [], "balloon": {}, "legend": { "enabled": True, "useGraphSettings": True, "color": "#D4D4D4", }, "titles": [{ "id": "Title-1", "size": 15, "text": "" }], "dataProvider": [] } date_start = app.config['DATE_START'] date_now = datetime.datetime.now() challenges_graph_dict = {c_id: [] for c_id in chals.keys()} # compute the chart of points over time for each user users_graphs = [] users_data_provider = [] for i, board_entry in enumerate(scoreboard): users_graphs.append({ "id": "mygraph-{}".format(i), "title": board_entry['user'], "valueField": "column-{}".format(i), "type": "line", "lineThickness": 3, "balloonText": "[[title]] [[value]]pts" }) user_points = [[date_start.timestamp(), 0]] for chal_id, chal in board_entry['challenges'].items(): if chal['timestamp'] is not None: user_points.append( [chal['timestamp'].timestamp(), chal['points']]) challenges_graph_dict[chal_id].append( [chal['timestamp'].timestamp(), 1]) # sort the list by date user_points = sorted(user_points, key=lambda x: x[0]) # finally add the current date to the list user_points.append([date_now.timestamp(), 0]) # perform the sum over all the points piled up by the current user for j in range(1, len(user_points)): user_points[j][1] += user_points[j - 1][1] # finally add the newly created list to the data_provider list for ts, pt in user_points: users_data_provider.append({ "date": datetime.datetime.fromtimestamp( int(ts)).strftime('%Y-%m-%d %H:%M:%S'), "column-{}".format(i): pt }) users_graph = deepcopy(graph_template) users_graph['titles'][0]['text'] = 'Players' users_graph['valueAxes'][0]['title'] = 'Points' users_graph['graphs'] = users_graphs users_graph['dataProvider'] = sorted(users_data_provider, key=lambda x: x['date']) # compute the cart of challenge solvers over time for each challenge challenges_graphs = [] challenges_data_provider = [] for i, chal in enumerate(chals.values()): challenges_graphs.append({ "id": "mygraph-{}".format(i), "title": chal['name'], "valueField": "column-{}".format(i), "type": "line", "lineThickness": 3, "balloonText": "[[title]] solved by [[value]]" }) for i, chal in enumerate(challenges_graph_dict.values()): chal_aux = chal chal_aux.append([date_now.timestamp(), 0]) chal_aux = sorted(chal_aux, key=lambda x: x[0]) for j in range(1, len(chal_aux)): chal_aux[j][1] += chal_aux[j - 1][1] # finally add the newly created list to the data_provider list for ts, solvers in chal_aux: challenges_data_provider.append({ "date": datetime.datetime.fromtimestamp( int(ts)).strftime('%Y-%m-%d %H:%M:%S'), "column-{}".format(i): solvers }) challenges_graph = deepcopy(graph_template) challenges_graph['titles'][0]['text'] = 'Challenges' challenges_graph['valueAxes'][0]['title'] = 'Solvers' challenges_graph['graphs'] = challenges_graphs challenges_graph['dataProvider'] = challenges_data_provider return { 'challenges': chals, 'scoreboard': scoreboard, 'users_graph': users_graph, 'challenges_graph': challenges_graph }
def submit(): """Flag submitter service.""" team_token = None # get the token associated with user's team if current_user.is_authenticated: db_conn = get_db_connection() with db_conn.cursor() as cur: cur.execute('SELECT token FROM teams WHERE id = %s', [current_user.team_id]) res = cur.fetchone() team_token = res['token'] if res is not None else None # initialize the flag form form = ctforge.forms.ServiceFlagForm(csrf_enabled=False) if request.method == 'POST': # process the form if form.validate_on_submit(): team_token = form.team_token.data flag = form.flag.data try: db_conn = db_connect() db_conn.autocommit = False cur = db_conn.cursor() # get the team associated with the retrieved token cur.execute('SELECT id FROM teams WHERE token = %s', [team_token]) res = cur.fetchone() if res is None: raise ctforge.exceptions.InvalidToken() team_id = res['id'] # get the flag that the user is trying to submit, if valid # (i.e. active and not one of the flags of his team) cur.execute(('SELECT service_id FROM active_flags ' 'WHERE flag = %s AND team_id != %s'), [flag, team_id]) res = cur.fetchone() if res is None: raise ctforge.exceptions.InvalidFlag() service_id = res['service_id'] # check whether the team's service is well-functioning or not cur.execute(('SELECT I.successful, I.timestamp ' 'FROM active_flags AS A ' 'JOIN integrity_checks AS I ' 'ON A.flag = I.flag ' 'WHERE A.team_id = %s AND A.service_id = %s ' 'ORDER BY I.timestamp DESC LIMIT 1'), [team_id, service_id]) res = cur.fetchone() if res is None or res['successful'] != 1: raise ctforge.exceptions.ServiceCorrupted() # store the attack in the database cur.execute(('INSERT INTO service_attacks (team_id, flag) ' 'VALUES (%s, %s) '), [team_id, flag]) db_conn.commit() flash('Flag accepted!', 'success') except psycopg2.IntegrityError: # this exception is raised not only on duplicated entry, but also # when key constraint fails db_conn.rollback() flash('Duplicated flag!', 'error') except psycopg2.Error as e: db_conn.rollback() error_msg = 'Unknown database error: {}'.format(e) flash(error_msg, 'error') app.logger.error(error_msg) except ctforge.exceptions.InvalidToken: db_conn.rollback() flash('The provided token does not match any team!', 'error') except ctforge.exceptions.InvalidFlag: db_conn.rollback() flash('The submitted flag is invalid!', 'error') except ctforge.exceptions.ServiceCorrupted: db_conn.rollback() flash( 'Your service is corrupted, fix it before submitting flags!', 'error') except Exception as e: # this should never occur, but we keep it for safety reasons error_msg = 'Unknown error: {}'.format(e) flash(error_msg, 'error') app.logger.error(error_msg) finally: cur.close() db_conn.close() else: flash_errors(form) return render_template('submit.html', form=form, team_token=team_token)
def _scoreboard(rnd): db_conn = get_db_connection() cur = db_conn.cursor() # retrieve the service table cur.execute('SELECT id, name, active FROM services') services = cur.fetchall() # retrieve the latest score of each team along with the team names' cur.execute(('SELECT T.id, T.name, T.ip, S.attack, S.defense ' 'FROM scores as S JOIN teams as T ON S.team_id = T.id ' 'WHERE round = %s'), [rnd]) results = cur.fetchall() # start populating the board, it's a dictionary of dictionaries, see # the initialization below to grasp the structure board = {} for r in results: board[r['id']] = { 'team': r['name'], 'ip': r['ip'], 'id': r['id'], 'attack': r['attack'], 'defense': r['defense'], 'ratio_attack': 0, 'ratio_defense': 0, 'position': 0, 'services': {}, 'attack_scores': [], 'defense_scores': [], 'total_scores': [] } # get services status cur.execute(( 'SELECT F.team_id, F.service_id, C.successful, MAX(C.timestamp) AS timestamp ' 'FROM active_flags AS F ' 'LEFT JOIN integrity_checks AS C ON ' ' (F.flag = C.flag AND C.timestamp = (SELECT MAX(timestamp) ' ' FROM integrity_checks ' ' WHERE flag = F.flag)) ' 'GROUP BY F.team_id, F.service_id, C.successful')) services_status = cur.fetchall() for ss in services_status: board[ss['team_id']]['services'][ss['service_id']] = (ss['successful'], ss['timestamp']) # set default values for team_id in board: for service in services: try: _ = board[team_id]['services'][service['id']] except KeyError: board[team_id]['services'][service['id']] = (2, '???') # normalize scores avoiding divisions by 0. If the score table is empty # (it shouldn't, we can initialize it with 0s) assume the max scores to # be 0. The scoreboard will anyway result empty since the teams are # extracted from the score table if len(board): max_attack = max(max(team['attack'] for team in board.values()), 1) max_defense = max(max(team['defense'] for team in board.values()), 1) else: max_attack = max_defense = 0 # get the scores of all the teams during the whole game to create some # nice graphs cur.execute('SELECT * FROM scores ORDER BY round') scores = cur.fetchall() cur.close() for s in scores: board[s['team_id']]['attack_scores'].append( [int(s['round']), int(s['attack'])]) board[s['team_id']]['defense_scores'].append( [int(s['round']), int(s['defense'])]) board[s['team_id']]['total_scores'].append( [int(s['round']), int(0.6 * s['attack'] + 0.4 * s['defense'])]) for team in board.values(): team['ratio_attack'] = team['attack'] * 100 / max_attack team['ratio_defense'] = team['defense'] * 100 / max_defense team[ 'score'] = 0.6 * team['ratio_attack'] + 0.4 * team['ratio_defense'] # sort the board in descending order with respect to the score: the # sorted structure is a list of board values, we just leave out the # team id sorted_board = sorted([t[1] for t in board.items()], key=lambda x: x['score'], reverse=True) # add a position index to each team for i, team in enumerate(sorted_board): team['position'] = i + 1 # fill graph lists attack_graph = [] defense_graph = [] total_graph = [] labels = [] for team in sorted_board: labels.append(team['team']) attack_graph.append(team['attack_scores']) defense_graph.append(team['defense_scores']) total_graph.append(team['total_scores']) return { 'services': services, 'board': sorted_board, 'labels': labels, 'attack_graph': attack_graph, 'defense_graph': defense_graph, 'total_graph': total_graph, 'min_x': 0, 'max_x': rnd, 'min_y': 0, 'max_y': None }