Answers · Keeping it safe

Why does escaping the quotes in user input not stop SQL injection?

In shortEscaping quotes only cleans one context; SQL injection stops only when query text and values travel separately, so the value is never parsed as code.

Escaping quotes does not stop SQL injection because the database receives one query string and parses all of it for structure; it has no record of which characters you wrote and which came from a user. A quote is only one way to cross from data into syntax, so cleaning it addresses the example rather than the cause. The cause is assembling trusted query text and untrusted input into one string and handing that string to a parser. It is the joining that creates the ambiguity, not the characters in the input.

q = \text{SELECT id, name FROM users WHERE id = } + x x = \text{1 OR 1=1} q = \text{SELECT id, name FROM users WHERE id = 1 OR 1=1}

Suppose the users table has four rows, with ids 1, 2, 3 and 4. For the intended id 1, the condition $id = 1$ is true for one row and false for the other three, so one row comes back. For the supplied text, the condition is $id = 1 OR 1=1$ : for id 2 it is $2 = 1 OR 1 = 1$ , which is false or true, hence true; the same holds for ids 3 and 4. The result is $1 + 3 = 4$ rows, not 1. The input contains no quotation mark, so escaping quotation marks changes nothing.

The quote is not the boundary

The natural guess is that the danger is a stray $'$ and that removing or escaping it makes the input inert. That guess is about one context, a string literal inside a query. An injection point can be a numeric comparison, an identifier, a sort direction, a limit, or a value that is stored and later reused in a different query. Escaping is defined relative to the exact parser and the exact position; it is not a property of the value. A value escaped for a string literal is still text when it lands where a number or a name is expected, and a value escaped for one database dialect may not mean the same thing in another. A blacklist of words fails for the same reason: it guesses at spellings of syntax in one dialect while the parser accepts whatever its grammar accepts.

s = p \times c = 3 \times 20 = 60

The count of places to get it wrong

With three interpreters in a system and twenty call sites for each, there are $3 \times 20 = 60$ places where a string is assembled and handed to a parser. Escaping has to be right at every one of them, every time the surrounding string is edited, by every person who edits it. The structural fix changes the count at each site that adopts it: the query text is fixed and the values are bound afterwards, so there is no assembled string for the parser to guess about. A prepared statement with placeholders does this. The value may contain quotes, spaces, $OR$ , comment markers, or anything else, and the database still treats it as one value.

FIG 1The value crosses separately

python

cursor.execute(
    "SELECT id, name FROM users WHERE id = ?",
    (user_id,)
)

The query text is fixed and the value is bound afterwards, so no parser ever receives the value as part of the query string.

The value crosses separately

Notice what the fixed call does not do. It does not validate the input, strip characters, or ask the programmer to remember a rule at the call site. The placeholder marks where a value will go; the driver sends the query text and the value as separate items. The parser builds the structure from the query text alone, then uses the supplied item as a value. That is why a value that passed no validation at all is still not syntax: no parser ever sees it as part of the query string. The value can be any bytes the database accepts.

Two cases do not fit the placeholder pattern, and pretending otherwise causes the next incident. A table name, a column name, or a sort direction is structure, not a value, so it cannot be bound. For those, use a permitted list and map the supplied token to a known-good identifier; never paste the supplied text into the query. Rich text from users is the other case: markup is the point, so escaping is not enough. It needs a parser that builds a document and emits only known-safe elements, which is adopted as a library rather than written as a function.

Validation still belongs in the system, but not as the injection defence. Checking that an age is a number or that a date parses catches mistakes and keeps data clean. It does not make concatenation safe: a value that passed every rule is still a value, and if it is joined into a string that something parses for structure, the problem is unchanged. The test is not whether the input looks dangerous. The test is whether the value was ever part of a string that a parser read as structure. That distinction survives a change of database, language or framework.

Worth keeping

Escaping is correct only for one parser and one position, so it cannot be a property of the value.
Injection stops when the query text is fixed and the values are bound separately, because no parser receives the value as part of a string.
A value that passed validation is still a value and can still become syntax if it is concatenated into a parsed string.

Where this goes next

This question is one step of The Mistakes Behind Most Breaches, a course that works through its subject from the beginning. The page that teaches this part of it is When Data Becomes an Instruction, and it reads the whole way through whether or not you have an account.

What an account changes is the practice: problems standing under every idea, marked with a reason rather than a tick, in a copy of the course that is yours.

Read that page The whole course