Result Day: Protecting Marks Under a One-Week Deadline
A school publishes Class XII board results on a home-grown PHP portal at http://results.example.edu. The login page builds SQL as:
SELECT * FROM users WHERE username='$u' AND password='$p'
Teachers share one 'teacher' account because the vendor charges per account. Results are fetched by /result.php?roll=1234567, and roll numbers are sequential. On result day 4,000 students refresh the page; the server slows, and a newspaper reports that a student changed the URL to see a classmate's marks. Logs show one IP requesting roll numbers 1200000 to 1299999 in order. The principal wants a one-week fix before the next exam. Budget is Rs 15,000 and one part-time assistant who can work 20 hours that week.
1. Rank the three issues - SQL injection, URL tampering/insecure direct object reference, and missing rate limiting - by the damage each can cause on result day, and justify the ranking. Then give the minimum code-level fix for each that the assistant can implement in a week.
Read the worked answer
Rank: (1) SQL injection. It can bypass login, dump the users table and run UPDATE or DELETE against results; one crafted password field can expose or alter every record. (2) URL tampering/IDOR. It leaks one student's marks per guessed roll, but sequential rolls make enumeration cheap; the damage is privacy and trust, not whole-database compromise. (3) Missing rate limiting. It enables both enumeration and load collapse; on its own it does not alter data, but it multiplies the other two.
Fixes: replace string-built SQL with a prepared statement and bind parameters - $stmt = $pdo->prepare('SELECT ... WHERE username = ? AND password = ?'); never concatenate. For result.php, do not trust the roll parameter: after login, read the roll from the server-side session and compare it with the requested roll, or better, fetch by session user id only. For rate limiting, add a per-session and per-IP counter with a 429 after, say, 10 result requests per minute, and log failures. The assistant should do prepared statements and the session check first because they remove the two data-exposure paths.
2. The portal runs over HTTP and stores passwords as MD5. Explain what an attacker on the school Wi-Fi can do with each weakness, and whether switching to HTTPS alone makes the MD5 storage acceptable. Recommend a sequence of fixes within Rs 15,000.
Read the worked answer
On school Wi-Fi, HTTP lets an attacker capture the session cookie and replay it, or read the teacher password as it is typed. MD5 storage means a stolen users table can be cracked quickly, especially if unsalted; reused passwords then expose other accounts. HTTPS alone does not make MD5 acceptable: TLS protects data in transit, not the database at rest.
Sequence within the budget: (1) obtain a free TLS certificate and force HTTPS redirects, then set cookies HttpOnly and Secure; (2) replace MD5 with PHP password_hash() (bcrypt or Argon2) and password_verify(), and force a password reset; (3) give each teacher a separate account - shared accounts destroy audit logs and make it impossible to tell who changed a mark. The vendor's per-account fee is a real constraint, so ask for a teacher-role account or a small fixed number of named accounts; do not keep one shared credential. The certificate and hashing cost nothing but time; spend the Rs 15,000 on the assistant's hours and a backup.
3. The newspaper wants proof the breach is fixed. Propose two log or test evidence items the school should keep, and explain why deleting the 1200000–1299999 log entries would be a mistake.
Read the worked answer
Evidence items: (a) a timestamped test showing result.php?roll=... for a roll not belonging to the logged-in student returns 403, while the student's own roll returns 200; (b) a before/after log excerpt showing the enumerating IP now receives 429 after 10 requests per minute, plus a prepared-statement test where entering ' OR '1'='1 in the login fails. Keep the original logs.
Deleting the 1200000–1299999 entries would destroy the only record of which accounts and rolls were accessed, make it impossible to notify affected students, and look like concealment. Retain them, restrict access, and record who reviewed them.