The Primary Defence

Chapter 7

The Primary Defence: Parameterized Queries

Prepared statements — the one real fix, and why it works

Six chapters of attacks, all the same root cause and all ending with the same sentence: "...impossible under parameterized queries." This is that chapter. Parameterized queries (a.k.a. prepared statements with bound parameters) are the defence against SQL injection — not one option among several, but the actual fix that removes the vulnerability rather than papering over it. Everything else (Chapter 8) is defence in depth on top.

The Core Idea: Separate the Query From the Data

Recall the root cause (Chapter 1): SQLi happens because the query and the data are the same string, so the database can't tell which characters are code and which are input. Parameterization fixes this at the source by sending them on two separate channels:

You send the query text with placeholders (? or :name) where values go — and only the placeholders, no values.
You send the values separately, bound to those placeholders.
The database parses and plans the query first (with placeholders), then slots the values in as pure data — after parsing is already done.

The key insight: the query is parsed BEFORE the data is ever attached

This is the whole reason it works. With concatenation, the input is part of the string the parser reads, so a ' in the input can change the query's structure. With a prepared statement, the database receives and parses the query template while the placeholders are still empty — the structure (which clauses, which tables, how many conditions) is locked in before your value exists in the picture. When the value is then bound, parsing is over; the value can only ever be data filling a slot, never new SQL syntax. A username of ' OR '1'='1 becomes a search for a user literally named "' OR '1'='1" — the quote is just a character. There is no breakout because there is nothing left to break out of.

Before & After

// VULNERABLE — query and data concatenated into one string
const q = "SELECT * FROM users WHERE username = '" + user + "' AND password = '" + pass + "'";
db.query(q);

// SAFE — placeholders + separately-bound values
db.query(
  "SELECT * FROM users WHERE username = ? AND password = ?",
  [user, pass]   // bound as data — never parsed as SQL
);

The Same Fix Across Languages

Stack	Parameterized form
Node (mysql2/pg)	`db.query("… WHERE id = ?", [id])` / `$1` in pg
PHP (PDO)	`$stmt = $pdo->prepare("… = ?"); $stmt->execute([$id]);`
Python (sqlite3/psycopg)	`cur.execute("… = ?", (id,))` / `%s`
Java (JDBC)	`PreparedStatement` + `setString(1, id)`
C# (ADO.NET)	`cmd.Parameters.AddWithValue("@id", id)`

A placeholder is not the same as string-formatting — the classic fatal mistake

The single most common way developers think they're parameterizing but aren't: building the query string with the language's string interpolation/formatting and passing it to a "query" function. db.query(f"… WHERE id = {id}") (Python f-string), db.query(`… = ${id}`) (JS template literal), "… = " + id, or sprintf/%-formatting — these all produce a concatenated string with the value already baked in, exactly like the vulnerable version. The value must be passed as a separate argument to the driver (the [id] array, the execute() tuple, setString) so the driver does the binding. If the value is inside the query string, it's not parameterized — no matter how clean it looks.

What Parameters Can — and Can't — Bind

Placeholders bind values (the things in WHERE x = ?, VALUES (?), SET col = ?). They cannot bind identifiers — table names, column names, ORDER BY directions, LIMIT in some drivers — because those are part of the query structure, decided at parse time. This matters for dynamic queries:

// you CAN parameterize the value:
"… WHERE id = ?"            // fine

// you CANNOT parameterize a column/table/direction this way:
"… ORDER BY ?"              // the sort COLUMN can't be a bound value

For dynamic identifiers, use an allowlist — never concatenate user input

When you genuinely need a user-influenced table/column/sort (a sortable table, a tab selector), you can't bind it as a parameter — so the safe pattern is an allowlist: map the user's input to a fixed set of known-good identifiers you control, and use the mapped value. const col = {name:'name', date:'created_at'}[req.query.sort] ?? 'name'; — the user picks a key, never the literal column name. This is the one place dynamic SQL is unavoidable, and the rule is strict: identifiers come from your allowlist, values come from parameters; user input is never directly placed into the query structure.

Why This Beats Escaping

Escaping (manually backslashing or doubling quotes in input) tries to make input safe to concatenate. It's strictly worse than parameterization and only a last resort (Chapter 8), because: it's easy to forget on one query out of hundreds; it's database- and charset-specific (multi-byte encoding tricks have bypassed escaping); it does nothing for numeric context (no quotes to escape, Chapter 2); and it corrupts legitimate data. Parameterization sidesteps all of it — there's nothing to escape because the data never enters the SQL text. Prefer parameterized queries; treat escaping as a fallback for the rare case you truly can't parameterize.

A Bonus: Performance and Clarity

Parameterized queries aren't only safer — they're often faster and cleaner. The database can parse and cache the query plan once and reuse it for many different parameter values (no re-parsing per request), and the code is more readable (no quote-juggling). Security, performance, and clarity all point the same way: there is essentially no reason to build queries by concatenation. The rule is simply: parameterize every query, every time.

Hands-On Exercises

Exercise 1

Explain why parameterized queries stop SQLi at the root, focusing on the "parse before bind" mechanism. Show the vulnerable concatenated login query and its parameterized fix, and trace what happens to a ' OR '1'='1 username in each.

📄 View solution

Exercise 2

Identify which of these are actually parameterized and which are still vulnerable, and why: (a) db.query(`SELECT … WHERE id = ${id}`); (b) db.query("SELECT … WHERE id = ?", [id]); (c) cur.execute("… = %s" % name); (d) cur.execute("… = %s", (name,)). State the rule that distinguishes them.

📄 View solution

Exercise 3

A sortable table lets the user choose the sort column via ?sort=. Explain why you can't fix this with a bound parameter, and write the correct allowlist-based approach. Then explain why parameterization is preferred over escaping, covering numeric context and multi-byte bypasses.

📄 View solution

Chapter 7 Quick Reference

Parameterized queries / prepared statements are the fix — they remove the vulnerability, not mask it
Send the query (with placeholders) and the values on separate channels; bind values as data
Why it works: the DB parses the query before the data is bound, so input can only ever be a value, never SQL syntax — no breakout possible
Same fix everywhere: ?/$1/:name/%s + a separate values argument (Node, PHP PDO, Python, JDBC, ADO.NET)
String-formatting ≠ parameterizing — f-strings, template literals, +, sprintf bake the value into the query string → still vulnerable
Parameters bind values, not identifiers (table/column/ORDER BY) — for those, use an allowlist, never concatenation
Prefer parameterization over escaping — escaping is fragile (charset/multi-byte), useless for numeric context, easy to forget; last resort only
Bonus: prepared statements are often faster (cached plan) and clearer — no reason to concatenate
Next chapter: defence in depth — validation, least privilege, ORMs/stored procedures, and where escaping/WAFs fit