def check(flag_str, user):
# Check if flag is wrapped in flag{...}
flag = unwrap(flag_str)
# Check if flag is valid
if not exists(flag):
logger.info("^%s^ submitted an invalid flag: '%s'.", user, flag)
return False
# Check if user already has flag
f = db.query_db(
'SELECT * FROM flagsfound WHERE flag_id = ? AND user_id = ?',
(flag, user),
one=True)
if f is not None:
logger.info("^%s^ submitted a flag they have submitted before ('%s').",
user, flag)
return None
# If above complete, mark user as having found the flag
ts = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db.query_db(
'INSERT INTO flagsfound (flag_id, user_id, timestamp) VALUES (?, ?, ?)',
(flag, user, ts))
logger.info("^%s^ found flag '%s'.", user, flag)
return True
def delete(id):
event = get_event(id)
db.query_db('''
DELETE FROM events
WHERE id = ?
''', [id])
logger.info("^%s^ deleted event %d:'%s'.",
flask_login.current_user.username, id, event.name)
def create_team(name, event_id):
try:
db.query_db(
'INSERT INTO teams (slug, name, event_id) VALUES (?, ?, ?)',
(generate_slug(name), name, event_id))
except db.IntegrityError:
return False
return True
def create_user(username_str, display_name, password):
username = username_str.lower()
pw_hash = werkzeug.security.generate_password_hash(password)
db.query_db(
'INSERT INTO users(username, displayname, password) VALUES(?, ?, ?)',
(username, display_name, pw_hash))
logger.info("User account ^%s^ created with display name '%s'.", username,
display_name)
return User(username, display_name, 0)
def set_perm(self, perm):
if perm is None:
db.query_db(
'UPDATE users SET permission = NULL WHERE username = ?',
[self.username])
self.perm = None
return
if perm < 0 or perm > 10:
raise ValueError('Permission value out of range (0-10).')
db.query_db('UPDATE users SET permission = ? WHERE username = ?',
(perm, self.username))
self.perm = perm
def add_user_to_team(self, user_id, team_slug):
# Check team exists
t = self.get_team(team_slug)
if t is None:
return False
try:
db.query_db(
'INSERT INTO teamusers (team_slug, event_id, user_id) VALUES (?, ?, ?)',
(team_slug, self.id, user_id))
except db.IntegrityError:
return False
return True
def create(id, name, teams, active):
if (active is 1) and (get_active()
is not None): # Deactive current active event
db.query_db('UPDATE events SET active = 0 WHERE active = 1')
# Insert new event
db.query_db(
'''
INSERT INTO events (id, name, has_teams, active)
VALUES (?, ?, ?, ?)
''', (id, name, teams, active))
logger.info("^%s^ added an event %d:'%s'.",
flask_login.current_user.username, id, name)
def update(id, name, teams, active):
if (active is 1) and (get_active()
is not None): # Deactive current active event
db.query_db('UPDATE events SET active = 0 WHERE active = 1')
# Update record
db.query_db(
'''
UPDATE events
SET name = ?, has_teams = ?, active = ?
WHERE id = ?
''', (name, teams, active, id))
logger.info("^%s^ updated the %d:'%s' event.",
flask_login.current_user.username, id, name)
def get_all(user=None):
if user is None:
res = db.query_db('SELECT * FROM flags')
else:
res = db.query_db('SELECT * FROM flags WHERE user = ?',
[user.get_id()])
flags = []
if res is not None:
for f in res:
flags.append(
Flag(f['flag'], f['value'], event.get_event(f['event_id']),
f['notes']))
return flags
def get_all():
ranks = db.query_db('SELECT * FROM ranks ORDER BY score ASC')
rlist = []
if ranks is not None:
for r in ranks:
rlist.append(Rank(r['rank'], r['score']))
return rlist
def get_team(self, team_slug):
q = db.query_db('SELECT * FROM teams WHERE slug = ? AND event_id = ?',
(team_slug, self.id),
one=True)
if q is None:
return None
return team.Team(q['name'], q['event_id'])
def exists(username):
u = db.query_db('SELECT * FROM users WHERE username = ?',
[username.lower()])
if u:
return True
else:
return False
def get_users_found(self):
res = db.query_db('SELECT * FROM flagsfound WHERE flag_id = ?',
[self.flag])
users = []
for u in res:
users.append(user.get_user(u['user_id']))
return users
def found_count(self):
return db.query_db('''
SELECT COUNT(*)
FROM flagsfound
WHERE flag_id = ?
''', [self.flag],
one=True)[0]
def has_teams(self):
teams = db.query_db('SELECT has_teams FROM events WHERE id = ?',
[self.id],
one=True)[0]
if teams is not None and teams == 1:
return True
return False
def is_active(self):
teams = db.query_db('SELECT active FROM events WHERE id = ?',
[self.id],
one=True)[0]
if teams is not None and teams == 1:
return True
return False
def add(flag_str, value, event_id, notes, user_id):
flag = unwrap(flag_str) # Unwrap flag notation
if event_id is None:
db.query_db(
'''
INSERT INTO flags (flag, hash, value, notes, user)
VALUES (?, ?, ?, ?, ?)
''', (flag, _compute_hash(flag), value, notes, user_id))
else:
db.query_db(
'''
INSERT INTO flags (flag, hash, value, event_id, notes, user)
VALUES (?, ?, ?, ?, ?, ?)
''', (flag, _compute_hash(flag), value, event_id, notes, user_id))
logger.info("^%s^ added the flag '%s'.", flask_login.current_user.get_id(),
flag)
def get_all():
return make_list_from_query(
db.query_db('''
SELECT e.id AS id, e.name AS name
FROM events e
ORDER BY e.id DESC
'''))
def get_user(username):
u = db.query_db('SELECT * FROM users WHERE username = ?',
[username.lower()],
one=True)
if u is None:
return None
else:
return User(u['username'], u['displayname'], u['permission'])
def get_event(id):
return make_event_from_row(
db.query_db('''
SELECT e.id AS id, e.name AS name
FROM events e
WHERE e.id = ?
''', [id],
one=True))
def update(flag, value, event_id, notes):
if event_id is None:
db.query_db(
'''
UPDATE flags
SET value = ?, event_id = NULL, notes = ?
WHERE flag = ?
''', (value, notes, flag))
else:
db.query_db(
'''
UPDATE flags
SET value = ?, event_id = ?, notes = ?
WHERE flag = ?
''', (value, event_id, notes, flag))
logger.info("^%s^ updated the flag '%s'.",
flask_login.current_user.get_id(), flag)
def load_user(id):
user = db.query_db('SELECT * FROM users WHERE username = ?', [id],
one=True)
if user is None:
return None
perm = user['permission']
if perm is None:
perm = 0
return User(user['username'], user['displayname'], perm)
def get_timestamp(self, username):
res = db.query_db(
'SELECT * FROM flagsfound WHERE flag_id = ? AND user_id = ?',
(self.flag, username),
one=True)
if res is None or res['timestamp'] is None:
return None
return datetime.datetime.strptime(res['timestamp'],
'%Y-%m-%d %H:%M:%S')
def get_flag(flag_str):
flag = unwrap(flag_str)
f = db.query_db('SELECT * FROM flags WHERE flag = ?', [flag], one=True)
if f is None:
return None
else:
if f['event_id']:
return Flag(f['flag'], f['value'], event.get_event(f['event_id']),
f['notes'])
return Flag(f['flag'], f['value'], notes=f['notes'])
def check_login(username_str, password):
username = username_str.lower()
if tracker.user.exists(username):
pw_hash = db.query_db('SELECT password FROM users WHERE username = ?',
[username],
one=True)['password']
valid = werkzeug.security.check_password_hash(pw_hash, password)
if valid:
return tracker.user.get_user(username)
return False
def get_num_flags(self):
num = db.query_db('''
SELECT COUNT(*)
FROM flags f
WHERE f.event_id = ?
GROUP BY f.event_id
''', [self.id],
one=True)
if num is None:
return 0
return num[0]
def get_num_members(self):
num = db.query_db('''
SELECT COUNT(*)
FROM teamusers tu
WHERE tu.event_id = ? AND tu.team_slug = ?
GROUP BY tu.team_slug
''', (self.event_id, self.get_slug()),
one=True)
if num is None:
return 0
return num[0]
def get_users_team(self, user_id):
q = db.query_db('''
SELECT t.name AS name, t.event_id AS event_id
FROM teams t
LEFT JOIN teamusers tu ON t.slug = tu.team_slug AND t.event_id = tu.event_id
WHERE tu.user_id = ?
AND t.event_id = ?
''', (user_id, self.id),
one=True)
if q is None:
return None
return team.Team(q['name'], q['event_id'])
def by_user(user_id):
return make_list_from_query(
db.query_db(
'''
SELECT e.id AS id, e.name AS name
FROM events e
LEFT JOIN flags f ON f.event_id = e.id
LEFT JOIN flagsfound ff ON ff.flag_id = f.flag
LEFT JOIN users u ON u.username = ff.user_id
WHERE u.username IS NOT NULL AND u.username = ?
GROUP BY e.id
''', [user_id]))
def get_num_points(self):
num = db.query_db('''
SELECT SUM(f.value)
FROM events e
LEFT JOIN flags f ON f.event_id = e.id
WHERE e.id = ?
GROUP BY e.id;
''', [self.id],
one=True)[0]
if num is None:
return 0
return num
