Techfest Registration Script: Injection, Duplicates and a Report That Shows Zero
Vidya Niketan Junior College's Techfest registration page was written in a hurry by a Class XII student. register.php reads name, email, class and event from a POST form, then runs:
$sql = "INSERT INTO participants (name, email, event) VALUES ('$name', '$email', '$event')";
and confirms with echo "Thank you, $name! You are registered for $event.";
The stack is PHP 8.2 with MySQL 8, magic quotes off, no framework or ORM, hosted on the college subdomain over plain HTTP. Within two days: a visitor's confirmation page popped a JavaScript alert box instead of printing a name; the participants table holds three rows for the same email address; one row has event = 'Fake Event', which is not in the dropdown; and the coordinator's "Web Design" report shows 40 registrations accepted by the form but 0 rows in the database, because the dropdown's option values were copied from another form and hold the numbers 1 to 5 while every report filters on the event name.
1. Identify the defects revealed by the JavaScript alert and the 'Fake Event' row, and give the concrete fix for each.
Read the worked answer
Alert box = stored cross-site scripting. The script echoes $name back into HTML without encoding, so a name containing a script tag executes in the browser of whoever views the page. Fix output encoding: htmlspecialchars($name, ENT_QUOTES, 'UTF-8') on every echoed value, including $event. Encoding at output, not stripping at input, is the rule - stripping corrupts legitimate names like O'Brien.
'Fake Event' = no server-side validation. The form may only offer five options, but the browser is the user's machine; anyone can POST any string. Fix with a whitelist checked on the server, against a list or, better, an events table: if (!in_array($event, $allowedEvents, true)) { reject }.
The SQL itself is injectable. String concatenation means a name such as x'); DROP TABLE participants;-- becomes SQL. Fix with a prepared statement: $stmt = $pdo->prepare('INSERT INTO participants (name,email,event) VALUES (?,?,?)'); $stmt->execute([$name,$email,$event]); - the driver sends values separately, so data is never parsed as SQL.
Also missing. filter_var($email, FILTER_VALIDATE_EMAIL), trimming, length limits matching the column widths, and a CSRF token so other sites cannot post to register.php on a logged-in student's behalf.
2. Three duplicate rows for one email are in the table. Explain where the uniqueness rule should live and how the script should respond when it is violated.
Read the worked answer
Put the rule in the database. Duplicates exist because nothing stops them: the script inserts unconditionally and the table has no constraint. Add the constraint that matches the business rule - UNIQUE(email) if one student registers once, or UNIQUE(email, event) if a student may enter several events. The choice is a policy decision and must be made explicitly, because it decides whether a second event is a duplicate or a legitimate row.
Handle the violation in PHP. With PDO, catch the integrity-constraint error and show a helpful message rather than a stack trace:
catch (PDOException $e) {
if ($e->getCode() === '23000') {
echo 'This email is already registered for that event.';
} else { throw $e; }
}
Under mysqli the same condition is errno 1062.
Why a SELECT-then-INSERT check is not enough. Two requests can both run the SELECT, both see nothing, and both INSERT. Only the database constraint is atomic under concurrency. A pre-check is worth having for the friendly message on the common case, but it cannot replace the index.
Cleanup. Decide which of the three rows is authoritative before deleting the others, or merge them, and keep the unique index creation for a quiet period so the ALTER does not fail on existing duplicates. Use utf8mb4 so Devanagari names store correctly.
3. The report shows 0 rows for 'Web Design' although 40 registrations were accepted. Diagnose the mismatch and give the durable fix. Then explain what plain HTTP exposes in this form.
Read the worked answer
Diagnosis. The form sends the option's value, which is a number (1–5), so the event column stores '3', not 'Web Design'. Every report filters WHERE event = 'Web Design', which matches nothing. The bug is not in the report - it is in storing a display label from an untrusted source in the same column that reports depend on. The data is also unrepairable at a glance, because '3' only maps back through the original form.
Durable fix. Normalise: an events(event_id INT PRIMARY KEY, event_name VARCHAR(60)) table, and participants holds event_id as a foreign key. The dropdown posts the id, the server validates it against the table, and any report joins for the name: SELECT e.event_name, COUNT(*) FROM participants p JOIN events e ON e.event_id = p.event_id GROUP BY e.event_name; Renaming an event then changes one row instead of every report. Never fix this by string-matching labels in PHP.
Plain HTTP. The POST body carries name, email and class in clear text across the college Wi-Fi; anyone on the same network can read or alter it, and the session cookie can be captured and replayed, letting one student submit as another. Enable TLS with a free certificate, redirect HTTP to HTTPS, and set the session cookie Secure, HttpOnly and SameSite=Lax.