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
}
