How SQL injection works, and why parameterised queries fix it

What the bug actually is

A query is a string. When you build it by concatenating a value into that string, the database cannot tell the difference between the part you wrote and the part the user wrote. It parses the whole thing as one statement.

So if the value is `1; DROP TABLE users; --`, the database receives two statements, and the second one runs. The escape is not a special character that has to be filtered. It is the absence of a boundary.

// Vulnerable: the name is pasted into the query text
const sql = "SELECT * FROM users WHERE name = '" + name + "'";

// The database sees one statement:
SELECT * FROM users WHERE name = 'x' OR '1'='1'

Why escaping is not the answer

Escaping exists, and it is done correctly by the driver, but hand-rolled escaping is where the bypasses live. The famous ones — the GBK character set in MySQL, the `` escape in PostgreSQL, the difference between a backslash and a quote in a standard string — all come from the same place: somebody wrote their own escape function.

There are also context-specific problems. A value that is safe inside a quoted string is not safe as an identifier, and not safe as a `LIMIT` value, and not safe as a `LIKE` pattern. One escape function cannot cover all of them.

What parameterised queries do

A parameterised query sends the query text and the values over separate channels. The database parses the statement first, decides where a parameter belongs, and then binds the value into that slot as data. A value that contains a quote is just a quote.

The rule that follows is simple: the query text is structure, and it is written by the programmer. Every value, including numbers and identifiers, goes through a parameter.

// Safe: the value never becomes part of the query text
const sql = "SELECT * FROM users WHERE name = ?";
const [rows] = await db.execute(sql, [name]);

// Identifiers cannot be parameterised, so they are
// checked against an allow-list rather than escaped.
const column = ALLOWED_COLUMNS.includes(input) ? input : "id";

The places that are not the database

The interesting part of injection is that the database is only the most visible target. The same mistake of pasting untrusted text into a command appears everywhere, and each context has its own safe form.

• Shell commands — pass the value as a separate argument, never inside a string.
• LDAP filters — escape the value for the filter grammar.
• Templates and HTML — escape for the output context, at the point of output.
• NoSQL — an operator is a value in a document store, so a query object built from user input can be rewritten.
• Log files — a newline in a value can forge a log entry.
• Templates that compile code — a template string built from user input is code execution.

ORMs reduce the risk, and do not remove it

An ORM parameterises its queries by default, which removes the most common injection point. It does not remove the class of bug, because every ORM has a way to escape into raw SQL: a raw query, a literal fragment, a column name taken from a request.

The rule that survives the ORM is the same one: structure comes from the programmer, values come from the user, and the two are never concatenated.

// The escape hatches that reintroduce the bug
db.raw("SELECT * FROM t WHERE id = " + id)   // concatenated
db.raw("SELECT * FROM t WHERE id = ?", [id])  // parameterised