The Double-Booked Sports Hall
Three sixth-form students are building a web-based booking system for their school's sports hall. Users select a date and a one-hour slot, then submit. The system stores bookings in a single table in a free cloud database. During testing, two browsers submitted the same slot at almost the same moment and both bookings were saved. A separate bug allows a user to type yesterday's date into the URL and create a past booking. Passwords are currently stored as plain text in the database, and the team has no automated tests; they test by clicking through the site manually. The project is due in two weeks, and the team is deciding whether to rewrite the booking logic or add validation and tests around the existing code.
1. Explain how the double-booking bug occurred and propose a fix at the database level. Include why an application-only check is insufficient.
Read the worked answer
The bug is a race condition. Both browsers followed the same sequence: read the bookings table, see that the slot was free, and then insert. Because the read and the insert are separate operations, one request can read before the other writes, and both then insert successfully. The application-level check 'is this slot free?' is not atomic, so it cannot prevent the interleaving.
An application-only check is insufficient because two requests can both pass the check before either writes. Adding a delay or a JavaScript confirmation does not fix it. The robust fix is a database constraint: create a unique constraint on the combination of date and slot (and any resource identifier, such as pitch number, if the hall can be split). The second insert then fails with a constraint violation, and the application catches that error and shows 'slot already booked'. This pushes the guarantee into the database, where it applies to every client.
If the schema cannot be changed in time, a transaction with row locking or an atomic conditional insert would also work, but the unique constraint is simpler and more reliable. The team should test with two simultaneous requests, not sequential clicks, because sequential testing will never reproduce the bug.
2. The team wants to fix the past-date bug and the plain-text password problem. Distinguish validation from verification, give a specific validation rule for the date, and explain how passwords should be stored and checked.
Read the worked answer
Validation checks that input meets defined rules before it is accepted: for example, that a date is a real date, in the correct format, and within the allowed booking window. Verification checks that data matches reality or an existing record: for example, that a password matches the stored credential, or that a user's email address is confirmed by a link. Validation can be done on the client and must also be done on the server; verification always requires the server and the stored data.
A suitable date rule is: parse the submitted date on the server in the school's timezone; reject it if it is earlier than today, if it is more than, say, 60 days in the future, or if it does not fall on a school day. The client-side date picker is a convenience only, because an attacker can send a request directly to the URL.
Passwords should never be stored in plain text or encrypted reversibly. Store a salted hash using a slow algorithm such as bcrypt, Argon2 or PBKDF2, with a unique salt per user. On login, hash the submitted password with the stored salt and compare in constant time. The database should hold only the hash and salt. This means a database leak does not reveal the passwords, and the team never needs to know the original password.
3. Propose a testing strategy for the two weeks remaining. Include at least three specific test cases and explain how you would prioritise them.
Read the worked answer
The team should prioritise data integrity and security over cosmetic testing, because the double-booking and password problems are serious and the deadline is close.
Priority order:
- Fix the unique constraint and test it with two concurrent requests for the same date and slot. Expected result: exactly one booking is saved and the other receives a clear error. This cannot be tested by clicking sequentially.
- Add server-side date validation and test yesterday's date, today's date, a date beyond the booking window, and an invalid date such as 31 February. Expected: yesterday and out-of-window are rejected; today at a valid slot is accepted.
- Inspect the database after registration to confirm that no plain-text password appears and that a bcrypt or Argon2 hash is stored. Then test that the correct password logs in and a wrong one fails.
These should be automated as integration tests where possible, because they exercise the database and the server together. Manual exploratory testing can then cover the user interface. Two weeks is enough for these three if the team resists rewriting everything. The judgement is to add targeted validation and tests around the existing code rather than start a risky rewrite.