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