Part 5 — Building it properly › Chapter 34

Dynamic SQL and SQL injection

How injection actually works, why parameters are not string escaping, and safe dynamic SQL when you must.

From the advert

“Conoscenza dei principi di sicurezza dei dati e GDPR” — injection is the oldest vulnerability on the OWASP list and it is still in the top three, because the mistake is so easy to make and so invisible once made.

How injection actually works

This is why parameterisation is not a stricter kind of escaping: the value cannot become code, because the code was already decided before it arrived.

🧒 Simple

You dictate a letter to somebody and say “write: Dear Mr Rossi”. If they write exactly that, fine. But if halfway through you say “…and now stop writing and go and burn the filing cabinet”, and they do it — that is the bug. They could not tell your instructions apart from the words you wanted written down.

The vulnerable pattern, and the two inputs everybody is shown once and then never forgets.

-- The vulnerable pattern, in any language:
sql = "SELECT * FROM users WHERE email = '" + input + "'"

-- input:  x' OR '1'='1
-- becomes: SELECT * FROM users WHERE email = 'x' OR '1'='1'    ← every row

-- input:  x'; DROP TABLE users; --
-- becomes: SELECT * FROM users WHERE email = 'x'; DROP TABLE users; --'

What is actually going wrong here

This is pseudocode showing string building, then the two inputs and the statement each one produces.
The bug is not “the quote was not escaped”. It is that data and instructions travel in the same channel, so the parser cannot tell which characters were meant as SQL and which were meant as a value. Escaping is an attempt to patch that with cleverness, and cleverness loses to a character set you did not think about.
The trailing -- in the second input comments out the rest of the original statement, including the closing quote. That is why it parses at all.
The fix is in the next example, and it is not a better escape function.

🎓 The root cause, stated precisely

Data and code are travelling in the same channel. The parser cannot tell which characters were meant as a value and which as syntax, because by the time it sees them they are one string. Every mitigation is really an attempt to separate the two channels again — and only one of them separates them properly.

Parameters are not escaping

✔ The distinction that answers the question properly

Escaping tries to neutralise dangerous characters in a string that is still going to be concatenated. It is a blacklist, and blacklists lose — to multi-byte character-set tricks, to a missed quote style, to a context you did not anticipate. Parameterisation sends the query and the values separately: the statement is parsed and planned first, and the values are bound to placeholders afterwards, as values, never re-parsed. There is nothing to escape because nothing is ever concatenated. Saying “parameters are a different channel, not better escaping” is the answer that lands.

The fix: a separate channel for values, which is also faster.

-- Correct, everywhere:
SELECT * FROM users WHERE email = ?      -- or $1, @email, :email — the driver decides
-- The driver sends the SQL text and the VALUES in separate protocol fields, so a
-- value can never be parsed as SQL. There is nothing to escape.

-- And it is FASTER: the same statement text means the same cached plan, instead
-- of a new plan for every distinct literal, filling the cache with single-use plans.

The sentence to say in an interview

“Parameters are a different channel, not better escaping.” That is the answer that lands, because it explains why the defence is total rather than probabilistic.
The performance argument is real and often forgotten: with literals, every distinct value produces a distinct statement text and therefore a distinct plan. A cache full of single-use plans is a documented SQL Server pathology.
Parameters cover values only. Identifiers and the shape of the statement are a separate problem — the next example.

⚠ Trap — an ORM does not make you safe

Every ORM has a raw-SQL escape hatch, and every ORM codebase uses it. String interpolation inside db.raw(...), EntityManager.createNativeQuery("... " + x) or an f-string in a Django .extra() is injectable exactly as before. Grep for string concatenation near the word SELECT — that, not the framework's name, is the audit.

The thing parameters cannot do

A parameter can be a value. It can never be a table name, a column name, a sort direction or a keyword — because those change the shape of the statement, which must be fixed before parsing. So this does not work:

What a parameter cannot do, and why it fails so quietly in one of the two cases.

ORDER BY ?          -- binds a STRING: it sorts by a constant and silently does nothing
SELECT * FROM ?     -- a syntax error, because the shape must be known before parsing

Why identifiers cannot be parameters

A parameter supplies a value after the statement has been parsed and planned. A table or column name changes the shape of the statement, which has to be known before either can happen.
ORDER BY ? is the dangerous half: it is valid SQL. Every row gets the same constant sort key, so the result comes back in an arbitrary order and nothing reports an error.
The answer is never to escape an identifier by hand. It is to choose one from a list you wrote — the next example.

🎓 So how do you build a sortable, filterable report?

Allow-list. Map user input to a fixed set of known-good identifiers in code, and if it is not in the map, reject the request. Never sanitise the input — choose from a list you wrote.

The only correct shape for a dynamic sort: an allow-list, with no escaping anywhere.

// The only correct shape. Note there is no escaping anywhere in it.
const SORTABLE = { date: "o.placed_at",              // the map IS the security boundary…
                   total: "o.total_cents",           // …because a key that is not here…
                   customer: "c.name" };             // …cannot produce a column name
const col = SORTABLE[req.query.sort];                // look the user's choice up
if (!col) return badRequest("unknown sort column");  // not in the map → reject the request

const dir = req.query.dir === "asc" ? "ASC" : "DESC";     // also an allow-list, of two values

const sql = `SELECT ... ORDER BY ${col} ${dir} LIMIT ?`;  // identifiers come from the map…
db.query(sql, [pageSize]);                                // …and VALUES are still parameters

Why this is safe when string-building normally is not

The user's input is never interpolated. It is used as a key, and the value that goes into the SQL is a constant you wrote. An attacker can choose between three columns, which is exactly the freedom the feature needed.
Never sanitise, choose. A sanitiser is a guess about what is dangerous; an allow-list is a statement about what is permitted, and it fails closed.
dir is an allow-list too, written as a ternary. Interpolating req.query.dir directly would reopen the hole for the sake of four characters.
The page size stays a parameter, because it is a value. Mixing the two — identifiers from a map, values from parameters — is the shape to remember.

Safe dynamic SQL inside the database

Safe dynamic SQL inside the database: sp_executesql takes parameters, EXEC does not.

-- sp_executesql takes parameters. EXEC(@sql) does not — that is the entire
-- difference, and it is why one is safe and the other is not.
DECLARE @sql NVARCHAR(MAX) = N'                     -- N'' is a Unicode literal
  SELECT id, total_cents FROM orders                -- the fixed part of the statement
  WHERE customer_id = @cid AND placed_at >= @from'; -- parameter NAMES in the text, not values

IF @status IS NOT NULL                              -- the SHAPE varies…
  SET @sql += N' AND status = @status';             -- …but no value is ever concatenated

EXEC sp_executesql @sql,                            -- the statement…
     N'@cid INT, @from DATE, @status VARCHAR(12)',  -- …its parameter DECLARATIONS…
     @cid = @customer_id, @from = @from_date, @status = @status;  -- …and the values, separately

-- If an IDENTIFIER genuinely must be dynamic, quote it with the engine's own
-- quoting function — never with your own string manipulation:
SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name);   -- SQL Server
-- PostgreSQL: format('SELECT * FROM %I', tbl)  -- %I quotes an identifier,
-- and %L quotes a literal, for the rare case that is genuinely needed.

The three rules this example encodes

Vary the shape dynamically; never the values. Adding AND status = @status to the text is safe because @status is a name, not a value.
sp_executesql takes a parameter declaration string and the values separately, so the plan can also be cached and reused. EXEC(@sql) takes a string and executes it, with no way to pass values apart from concatenation.
Identifiers that genuinely must vary go through the engine's own quoting — QUOTENAME in SQL Server, format(..., %I) in PostgreSQL, quote_ident in PL/pgSQL. These know the rules for the identifiers that engine accepts; a hand-rolled quote does not.

This is also the fix for the optional-parameter plan problem

WHERE (@p IS NULL OR col = @p) caches one plan for every combination of supplied parameters (chapter 07). Building the statement so it contains only the clauses actually needed produces a different statement text per shape, and therefore a correctly-tailored cached plan for each — while the values remain parameters. Safe dynamic SQL is a performance technique as much as it is a hazard.

Defence in depth

Layer	Does	Does NOT
Parameterised queries	Removes the vulnerability	Help if you concatenate anyway
Least privilege	Limits the damage: an app account with no `DROP` cannot drop	Prevent reading everything the app can read
Input validation	Rejects nonsense early, useful for its own sake	Count as an injection defence — it is a blacklist
Stored procedures with `EXECUTE`-only grants	Removes ad-hoc SQL from the app account entirely	Help if the procedure itself concatenates
A WAF	Blocks known payload shapes; buys time	Fix anything. It is a smoke alarm, not a sprinkler

⚠ Second-order injection

A malicious string is stored safely via a parameterised insert. Later, a report builds a query by concatenating that stored value — and it executes. The input was never "untrusted" at the moment it mattered, because it came from your own database. The lesson: the rule is not "validate user input", it is never concatenate a value into SQL, whatever its origin.

Injection happens because data and code share one channel. Parameters give them two.
Parameterisation is not better escaping. Escaping is a blacklist; parameters are never parsed as syntax.
Parameterised queries are also faster: one cached plan instead of one per literal.
An ORM is not a defence. Its raw-SQL escape hatch is injectable like anything else.
A parameter can be a value, never an identifier, a keyword or a sort direction.
For dynamic identifiers, use an allow-list in code — choose from a map, do not sanitise input.
Inside the database, sp_executesql takes parameters and EXEC(@sql) does not.
Safe dynamic SQL is also the fix for the optional-parameter plan problem.
Second-order injection comes from your own tables. The rule is never to concatenate a value, whatever its origin.

Interview questions this chapter answers

What is SQL injection and how do you prevent it?

User input is concatenated into a statement, so the parser reads it as syntax rather than as a value. The fix is parameterised queries — the SQL and the values travel in separate protocol fields, so nothing is ever re-parsed. Not escaping, which is a blacklist. Then least privilege to limit the blast radius if something else goes wrong.

How do you handle a user-supplied ORDER BY column?

An allow-list. Map the input to a fixed set of known identifiers in code and reject anything not in the map, because a parameter cannot be an identifier — bind it and you sort by a constant, silently. The sort direction gets the same treatment.

Are stored procedures immune to injection?

No. A procedure that builds a string and runs EXEC(@sql) is exactly as vulnerable. What procedures give you is a permission boundary — the application account can be granted EXECUTE and nothing else — which limits damage rather than preventing the flaw.