Theory
Flat files do not scale
The portal has been storing registrations in a text file. That works for a demo and collapses for a real fest: no easy searching, no sorting, races when 2 people register at once. Real data lives in a DATABASE, and you already know SQL from BCA205 and BCA303.
This lesson connects PHP to MySQL and runs full CRUD from your pages. But it opens with a warning, because this is where PHP applications most often get HACKED: the moment user input touches a SQL query, you must defend against SQL injection, and the defence is prepared statements.
Theory
Connecting, and CRUD
PHP talks to MySQL through 2 common interfaces:
- mysqli: MySQL-specific
- PDO: works across many databases (portable), so many prefer it
Once connected, you run the SQL you know from BCA205:
- INSERT to create, SELECT to read, UPDATE to modify, DELETE to remove
- clauses: WHERE (filter rows), ORDER BY (sort), LIMIT (cap the count)
So SELECT name FROM regs WHERE event = 'garba' ORDER BY name LIMIT 10 fetches the first 10 registrants for Garba Night, alphabetically. The database does the heavy lifting; PHP just sends the query and reads the result. (For document data instead of tables, MongoDB is the NoSQL alternative, covered in BCA503-01.)
Watch out
SQL injection: the attack you must block
Suppose you build a query by CONCATENATING user input:
"SELECT * FROM users WHERE name = '" . $_POST['name'] . "'"
A user types ' OR '1'='1 as their name. The query becomes ... WHERE name = '' OR '1'='1', which is ALWAYS TRUE, dumping every user, or worse. This is SQL injection: user input smuggled in as SQL. It is the classic, devastating web vulnerability, and concatenating input into SQL is how you invite it in.
Practical
The fix: a prepared statement (bound parameter)
<?php
$db = new mysqli("localhost", "root", "", "portal");
// PREPARED STATEMENT: the query structure is fixed first,
// then the value is bound separately and can NEVER become SQL.
$stmt = $db->prepare("SELECT name FROM regs WHERE event = ?");
$stmt->bind_param("s", $_POST["event"]); // 's' = string param
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row["name"]) . "<br>";
}
$stmt->close();
// The ? is a PLACEHOLDER. Even if event is "' OR '1'='1",
// it is treated as a literal value, not as SQL. Injection blocked.
?>
Theory
Why prepared statements are safe
A prepared statement sends the query STRUCTURE to the database FIRST, with ? placeholders where values go. The database compiles that structure, then you BIND the actual values separately. Because the values arrive AFTER the structure is fixed, they can only ever be DATA, never SQL: a bound value of ' OR '1'='1 is searched for as a literal string, matching nothing, instead of altering the query.
That is the whole defence: separate the query's SHAPE from its VALUES. The rule is absolute: never concatenate user input into SQL; always use prepared statements with bound parameters. Examiners specifically want to see this.
Quiz
What is the correct defence against SQL injection when a query uses user input?
- Concatenate the input but make it uppercase first
- Use prepared statements with bound parameters, so user input is treated as data and can never alter the SQL
- Trust the input if the form has client-side validation
- Only use SELECT queries, never INSERT
Show the answer
Use prepared statements with bound parameters, so user input is treated as data and can never alter the SQL
Prepared statements with bound parameters are THE defence: the query structure is fixed with placeholders, values are bound separately, so a malicious input like ' OR '1'='1 is treated as a literal value and cannot change the SQL. Option A does nothing (uppercasing does not stop injection). Option C repeats the fatal myth that client-side checks are security: they can be bypassed, and injection targets the server anyway. Option D misunderstands the problem: injection can affect any query type; restricting to SELECT neither prevents it nor is practical. Separate query shape from values, always.
Think first
Read the attack, then the fix
The vulnerable query concatenates $_POST['name'] and the attacker enters ' OR '1'='1 . Trace what the database receives in the vulnerable version versus the prepared version. Then tap.
Show the answer
VULNERABLE (concatenated): the database receives one merged string: WHERE name = '' OR '1'='1', in which the attacker's quote CLOSES the intended string and the OR '1'='1' becomes part of the SQL LOGIC, always true, so every row returns. The input BECAME code. PREPARED: the database first receives WHERE name = ? (structure fixed), then separately receives the value ' OR '1'='1 as DATA bound to ?; it searches for a name literally equal to that odd string, finds none, and returns nothing. The input stayed data. That is the entire security difference: concatenation lets input become SQL; binding keeps input as a value. Never concatenate.
Watch out
Database traps
Concatenating input into SQL: the injection hole; always use prepared statements.
Echoing DB values raw: still htmlspecialchars() them on output (XSS is separate from injection).
Ignoring connection errors: check the connection succeeded before querying.
Storing plain-text passwords: hash them (password_hash); never store raw passwords.
Forgetting LIMIT on big tables: unbounded SELECTs can return huge result sets.
Theory
Data stored; now make it live
The portal's registrations now live safely in MySQL with full CRUD and injection-proof queries. Next it gets INTERACTIVE without page reloads: AJAX sends requests to your PHP in the background (the technique from BCA405-01) for features like a live registrant search. Then the final Unit 3 lessons rebuild the whole portal on the CodeIgniter MVC framework: the professional structure that ties files, database and routing together.
Summary
Key takeaways
- PHP connects to MySQL via mysqli (MySQL-specific) or PDO (portable across databases).
- CRUD from PHP uses SQL: INSERT, SELECT, UPDATE, DELETE, with WHERE (filter), ORDER BY (sort), LIMIT (cap).
- SQL injection: user input concatenated into a query can alter the SQL (' OR '1'='1 makes conditions always true).
- Defence: prepared statements with bound parameters: the query structure is fixed first, values bound separately as data.
- Never concatenate user input into SQL; bound values can never become SQL.
- Also hash passwords (password_hash) and htmlspecialchars DB values on output.
- Memory hook: separate the query's shape from its values; bind, do not concatenate.