[BUG] Race conditions

[jdabtieu]

Multiple race conditions are possible with SQL checks being done before inserts, in separate transactions. This presents the opportunity for race conditions (e.g. two duplicate users registered at the same time). These should be fixed by enforcing uniqueness in SQL schema, reordering certain queries, and using explicit transactions

Perhaps reorder code from e.g. [select check, select check, insert] to [insert, if fail, select check, select check] This should also reduce the number of SQL queries for the vast number of legitimate requests

These fixes are to be rolled out starting in v4.2.0 and continuing to v4.3.0

List of important routes to be fixed:

• admin:
  • [x] users (data + perms must be synced)
  • [x] updateperms (perm transactions must be atomic)
  • [x] deleteannouncement (500 if ID doesn't exist --> check rval of DELETE query)
• contest:
  • [x] index
  • [x] delete
  • [x] submit, possibly
  • [x] publish contest problem (check rval of UPDATE)
  • [x] edit problem
  • [x] rejudge
  • [x] hide, unhide trolls if the UID doesn't exist
  • [x] create problem, if pid gets taken
  • [x] export
• problem:
  • [x] submit, possibly
  • [x] publish (check rval of update)
  • [x] delete
• application
  • [x] register
  • [x] confirmregister
  • [x] cancelregister trolls if uid doesn't exist
  • [x] create contest, possible (id exists?)
  • [x] create problem, if pid taken

There are other routes that can suffer race conditions, but aren't important enough to fix. Examples include: For paginated pages, it's no big deal if the length is short by 1 or so due to a race, but the data itself must be consistent. Other minor data races that don't result in any inconsistencies/special handling (e.g. if a user's password is reset by two admins at the same time) are also no big deal. Permission checks are also not important (e.g. problem gets published halfway through a user's attempt to access it, or problem gets unpublished halfway through a user's attempt to access it).

[jdabtieu]

cs50 raises ValueError if a unique constraint fails

[jdabtieu]

• users:
  • add UNIQUE to username
  • add UNIQUE to email
  • add UNIQUE to api
• submissions:
  • rename sub_id to id (has no impact except for one template)
• problems:
  • add UNIQUE to id
• contests:
  • add UNIQUE to id
• contest_users
  • add UNIQUE to (contest_id, user_id)
• contest_solved
  • add UNIQUE to (contest_id, user_id, problem_id)
• contest_problems:
  • add UNIQUE to (contest_id, problem_id)
• problem_solved:
  • add UNIQUE to (problem_id, user_id)
• user_perms:
  • removed id