def submissions_listing(submission_type):
filters = {}
if submission_type:
filters["type"] = submission_type
curr_page = abs(int(request.args.get("page", 1, type=int)))
results_per_page = 50
page_start = results_per_page * (curr_page - 1)
page_end = results_per_page * (curr_page - 1) + results_per_page
sub_count = Submissions.query.filter_by(**filters).count()
page_count = int(
sub_count / results_per_page) + (sub_count % results_per_page > 0)
Model = get_model()
submissions = (Submissions.query.add_columns(
Submissions.id,
Submissions.type,
Submissions.challenge_id,
Submissions.provided,
Submissions.account_id,
Submissions.date,
Challenges.name.label("challenge_name"),
Model.name.label("team_name"),
).filter_by(**filters).join(Challenges).join(Model).order_by(
Submissions.date.desc()).slice(page_start, page_end).all())
return render_template(
"admin/submissions.html",
submissions=submissions,
page_count=page_count,
curr_page=curr_page,
type=submission_type,
)
def statistics():
update_check()
Model = get_model()
teams_registered = Teams.query.count()
users_registered = Users.query.count()
wrong_count = (Fails.query.join(Model,
Fails.account_id == Model.id).filter(
Model.banned == False,
Model.hidden == False).count())
solve_count = (Solves.query.join(Model,
Solves.account_id == Model.id).filter(
Model.banned == False,
Model.hidden == False).count())
challenge_count = Challenges.query.count()
ip_count = Tracking.query.with_entities(Tracking.ip).distinct().count()
solves_sub = (db.session.query(
Solves.challenge_id,
db.func.count(Solves.challenge_id).label("solves_cnt")).join(
Model, Solves.account_id == Model.id).filter(
Model.banned == False, Model.hidden == False).group_by(
Solves.challenge_id).subquery())
solves = (db.session.query(
solves_sub.columns.challenge_id,
solves_sub.columns.solves_cnt,
Challenges.name,
).join(Challenges, solves_sub.columns.challenge_id == Challenges.id).all())
solve_data = {}
for chal, count, name in solves:
solve_data[name] = count
most_solved = None
least_solved = None
if len(solve_data):
most_solved = max(solve_data, key=solve_data.get)
least_solved = min(solve_data, key=solve_data.get)
db.session.close()
return render_template(
"admin/statistics.html",
user_count=users_registered,
team_count=teams_registered,
ip_count=ip_count,
wrong_count=wrong_count,
solve_count=solve_count,
challenge_count=challenge_count,
solve_data=solve_data,
most_solved=most_solved,
least_solved=least_solved,
)
def get(self):
chals = (
Challenges.query.filter(
or_(Challenges.state != "hidden", Challenges.state != "locked")
)
.order_by(Challenges.value)
.all()
)
Model = get_model()
solves_sub = (
db.session.query(
Solves.challenge_id, db.func.count(Solves.challenge_id).label("solves")
)
.join(Model, Solves.account_id == Model.id)
.filter(Model.banned == False, Model.hidden == False)
.group_by(Solves.challenge_id)
.subquery()
)
solves = (
db.session.query(
solves_sub.columns.challenge_id,
solves_sub.columns.solves,
Challenges.name,
)
.join(Challenges, solves_sub.columns.challenge_id == Challenges.id)
.all()
)
response = []
has_solves = []
for challenge_id, count, name in solves:
challenge = {"id": challenge_id, "name": name, "solves": count}
response.append(challenge)
has_solves.append(challenge_id)
for c in chals:
if c.id not in has_solves:
challenge = {"id": c.id, "name": c.name, "solves": 0}
response.append(challenge)
db.session.close()
return {"success": True, "data": response}
def get(self):
challenges = (
Challenges.query.add_columns("id", "name", "state", "max_attempts")
.order_by(Challenges.value)
.all()
)
Model = get_model()
teams_with_points = (
db.session.query(Solves.account_id)
.join(Model)
.filter(Model.banned == False, Model.hidden == False)
.group_by(Solves.account_id)
.count()
)
percentage_data = []
for challenge in challenges:
solve_count = (
Solves.query.join(Model, Solves.account_id == Model.id)
.filter(
Solves.challenge_id == challenge.id,
Model.banned == False,
Model.hidden == False,
)
.count()
)
if teams_with_points > 0:
percentage = float(solve_count) / float(teams_with_points)
else:
percentage = 0.0
percentage_data.append(
{"id": challenge.id, "name": challenge.name, "percentage": percentage}
)
response = sorted(percentage_data, key=lambda x: x["percentage"], reverse=True)
return {"success": True, "data": response}
def get(self, challenge_id):
response = []
challenge = Challenges.query.filter_by(id=challenge_id).first_or_404()
# TODO: Need a generic challenge visibility call.
# However, it should be stated that a solve on a gated challenge is not considered private.
if challenge.state == "hidden" and is_admin() is False:
abort(404)
Model = get_model()
solves = (Solves.query.join(Model,
Solves.account_id == Model.id).filter(
Solves.challenge_id == challenge_id,
Model.banned == False,
Model.hidden == False,
).order_by(Solves.date.asc()))
freeze = get_config("freeze")
if freeze:
preview = request.args.get("preview")
if (is_admin() is False) or (is_admin() is True and preview):
dt = datetime.datetime.utcfromtimestamp(freeze)
solves = solves.filter(Solves.date < dt)
for solve in solves:
response.append({
"account_id":
solve.account_id,
"name":
solve.account.name,
"date":
isoformat(solve.date),
"account_url":
generate_account_url(account_id=solve.account_id),
})
return {"success": True, "data": response}
def get(self, challenge_id):
if is_admin():
chal = Challenges.query.filter(
Challenges.id == challenge_id).first_or_404()
else:
chal = Challenges.query.filter(
Challenges.id == challenge_id,
and_(Challenges.state != "hidden",
Challenges.state != "locked"),
).first_or_404()
chal_class = get_chal_class(chal.type)
if chal.requirements:
requirements = chal.requirements.get("prerequisites", [])
anonymize = chal.requirements.get("anonymize")
if challenges_visible():
user = get_current_user()
if user:
solve_ids = (Solves.query.with_entities(
Solves.challenge_id).filter_by(
account_id=user.account_id).order_by(
Solves.challenge_id.asc()).all())
else:
# We need to handle the case where a user is viewing challenges anonymously
solve_ids = []
solve_ids = set([value for value, in solve_ids])
prereqs = set(requirements)
if solve_ids >= prereqs or is_admin():
pass
else:
if anonymize:
return {
"success": True,
"data": {
"id": chal.id,
"type": "hidden",
"name": "???",
"value": 0,
"category": "???",
"tags": [],
"template": "",
"script": "",
},
}
abort(403)
else:
abort(403)
tags = [
tag["value"]
for tag in TagSchema("user", many=True).dump(chal.tags).data
]
unlocked_hints = set()
hints = []
if authed():
user = get_current_user()
team = get_current_team()
# TODO: Convert this into a re-useable decorator
if is_admin():
pass
else:
if config.is_teams_mode() and team is None:
abort(403)
unlocked_hints = set([
u.target for u in HintUnlocks.query.filter_by(
type="hints", account_id=user.account_id)
])
files = []
for f in chal.files:
token = {
"user_id": user.id,
"team_id": team.id if team else None,
"file_id": f.id,
}
files.append(
url_for("views.files",
path=f.location,
token=serialize(token)))
else:
files = [
url_for("views.files", path=f.location) for f in chal.files
]
for hint in Hints.query.filter_by(challenge_id=chal.id).all():
if hint.id in unlocked_hints or ctf_ended():
hints.append({
"id": hint.id,
"cost": hint.cost,
"content": hint.content
})
else:
hints.append({"id": hint.id, "cost": hint.cost})
response = chal_class.read(challenge=chal)
Model = get_model()
if scores_visible() is True and accounts_visible() is True:
solves = Solves.query.join(Model,
Solves.account_id == Model.id).filter(
Solves.challenge_id == chal.id,
Model.banned == False,
Model.hidden == False,
)
# Only show solves that happened before freeze time if configured
freeze = get_config("freeze")
if not is_admin() and freeze:
solves = solves.filter(Solves.date < unix_time_to_utc(freeze))
solves = solves.count()
response["solves"] = solves
else:
response["solves"] = None
response["files"] = files
response["tags"] = tags
response["hints"] = hints
db.session.close()
return {"success": True, "data": response}
def get_standings(count=None, admin=False):
"""
Get standings as a list of tuples containing account_id, name, and score e.g. [(account_id, team_name, score)].
Ties are broken by who reached a given score first based on the solve ID. Two users can have the same score but one
user will have a solve ID that is before the others. That user will be considered the tie-winner.
Challenges & Awards with a value of zero are filtered out of the calculations to avoid incorrect tie breaks.
"""
Model = get_model()
scores = (db.session.query(
Solves.account_id.label("account_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(
Solves.account_id))
awards = (db.session.query(
Awards.account_id.label("account_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.account_id))
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias("results")
"""
Sum each of the results by the team id to get their score.
"""
sumscores = (db.session.query(
results.columns.account_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.account_id).subquery())
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.oauth_id.label("oauth_id"),
Model.name.label("name"),
Model.hidden,
Model.banned,
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.oauth_id.label("oauth_id"),
Model.name.label("name"),
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).filter(
Model.banned == False,
Model.hidden == False).order_by(sumscores.columns.score.desc(),
sumscores.columns.id))
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
return standings