Defeating SQL Injection

The whole vulnerability is one line of string concatenation:

$email = $_POST['email'];
$sql = "SELECT id, role FROM users WHERE email = '$email' AND active = 1";
$user = $pdo->query($sql)->fetch();

Send admin@shop.com' -- as the email: the quote closes your string early, -- comments out the rest of the line including the active check, and you are logged in as admin. Send ' OR '1'='1 and the clause becomes email = '' OR '1'='1', which is true for every row — the first of which is usually the oldest administrator account. The database decides what is structure and what is data by parsing the text you sent; if user input lives inside that text, the user is co-authoring the query. Quoting the value in your SQL ('$email') does not help, because the attacker supplies their own quotes.

Interviewers ask what an attacker can do with that power, because extraction is not limited to logging in:

Time-based blind is the one that ends arguments: even with every error suppressed and every page identical, the attacker still reads your database one SLEEP() at a time. This is why OWASP groups XSS, SQL injection, and command injection under one entry — A03:2021: Injection — and why the fix is structural rather than cosmetic.

The structural fix is a prepared statement: submit the query shape first, let the server parse it, and deliver values afterwards as data.

$stmt = $pdo->prepare('SELECT id, role FROM users WHERE email = :email AND active = 1');
$stmt->execute([':email' => $_POST['email']]);   // "x' OR '1'='1" is just a value
$user = $stmt->fetch();

By the time the value exists, the structure is already decided — no quote in the payload can close a string, add a clause, or comment anything out, including payloads nobody has invented yet. That property is the reason parameterization beats every input filter: a filter must recognize every bad pattern, and a prepared statement does not recognize anything, it removes the grammar from the channel. For binding types, emulated versus native prepares, and what still needs a whitelist after binding, see Prepared Statements and Binding.

Escaping output for HTML — Escaping Output to Prevent XSS — follows the same rule for a different parser. The rest of this topic covers the defenses attackers actually slip past, and what caps the damage when one query escapes your review.

Every defense that works by recognizing bad input has a surprise waiting, because the attacker writes the next pattern. These ten show up repeatedly in audits; each row names the defense people actually write, and why it fails.

Read the table by column. Rows 1–8 are defeated by not letting user input into syntax at all: prepare() plus execute() handles them identically, including payloads that do not exist yet — that is the difference between a defense and a guess. Row 9 is not injection but a wrong-answer bug: escape the pattern metacharacters (%, _, \) in the *data* before binding it. Row 10 is the one that survives parameterization, because the SQL grammar has no hole where a value belongs in ORDER BY — the counter is a whitelist your code owns:

const SORTABLE = ['price' => 'price ASC', 'newest' => 'created_at DESC', 'name' => 'name ASC'];
$order = SORTABLE[$_GET['sort'] ?? 'newest'] ?? SORTABLE['newest'];  // a key, never raw text
$pdo->prepare("SELECT * FROM products ORDER BY $order");

Two corollaries worth saying out loud in an interview. First, escaping libraries (addslashes, mysql_escape_string) are charset-dependent point-in-time tricks — row 4 is twenty years old and still works against hand-rolled setups; parameterization is negotiated once per connection and holds everywhere. Second, a WAF rule or input filter is a detection layer that reduces noise, not a security boundary: attackers route around blocked words, and the query you forgot to prepare is still open the moment the rule is tuned for performance.

The next chapter is about the cases where injection still gets through despite all of this — what the database account can touch, what your error page says, and how much of the blast radius you can delete in advance.

Assume a query slipped through review. Three decisions you made months earlier determine whether that incident is a bug report or a breach.

1. Numeric fragments get cast, not quoted. Anything that is a number by contract should stop being a string before it can touch a query string:

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 1],
]);
if ($id === false || $id === null) {
    http_response_code(422);
    exit('id must be a positive integer');
}
// "1 OR 1=1" never gets here; had it, (int)'1 OR 1=1' === 1 — the parse stops at the first non-digit
$stmt = $pdo->prepare('SELECT * FROM orders WHERE id = :id');
$stmt->execute([':id' => $id]);

Casting removes risk from numeric query fragments — WHERE id =, LIMIT, OFFSET, category_id = — because after the cast there is no input left, only an integer. The cast must happen at the boundary, before the value enters any variable that could later be interpolated, or you are relying on the next developer to remember. Validate-and-reject beats cast-and-hope: a 422 on garbage input tells you a probe is happening, while a silent (int) cast of abc → 0 quietly returns the wrong rows.

2. Least privilege caps what a successful injection can touch. The web application's database account should be the one that runs your queries and nothing else:

CREATE USER 'shop_app'@'10.0.%' IDENTIFIED BY '…';
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'shop_app'@'10.0.%';
-- deliberately absent: DROP, ALTER, GRANT, FILE, SUPER, PROCESS

With that grant in place, a perfect UNION SELECT from information_schema may still read your application tables — that is the incident — but DROP TABLE, GRANT, LOAD_FILE(), and writes outside shop.* fail with command denied to user. Schema migrations run under a second account whose credentials exist only in CI, never in the web app's environment. This is the same reasoning as Escaping Output to Prevent XSS: assume one layer fails, and design the layer underneath so the failure is bounded.

3. Your error page must not be the attacker's oracle. Error-based extraction works because the database's message reached the browser:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // full detail, always
try {
    $order = $repo->find($id);
} catch (PDOException $e) {
    error_log($e->getMessage() . ' query=' . $e->getTraceAsString()); // detail goes to the log
    http_response_code(500);
    echo 'We could not complete that request.';                       // nothing parseable to the client
}

Returning SQLSTATE[42000]: syntax error, unexpected 'FROM' to production hands over column names, table names, and version hints — the raw material for rows 2 and 3 of the previous chapter's table. Logs keep the full text with the failing input fingerprint, so you can spot the probing pattern (ORDER BY 7, repeated SLEEP) before it becomes a data pull; alerting on query-error spikes is cheap and catches blind attempts early.

Then the interview question that separates candidates: "You have a query where the user picks the sort column — prepared statement?" Answer plainly: no, binding cannot stand in for an identifier; you whitelist labels to columns, then prepare the rest. And the follow-up: "Is a WAF enough?" No — it is a filtering layer in front of an application that must still keep user data out of SQL structure.

Next, CSRF Tokens for Forms: an attacker who needs no injection at all, only your victim's logged-in browser.