SQL Injection as a Failure of the Tokenization Boundary

A SQL statement reaches the database as a single string. Inside that string, the characters ' and -- and ; carry grammatical meaning: they open a literal, begin a comment, terminate a statement. The database has no channel by which a caller can mark a substring as “this part is data”. Once a value has been spliced into the statement text, the only thing distinguishing it from the surrounding syntax is the quoting that the application applied before splicing — and quoting is a lexical convention that the parser will happily undo.

That is the whole of the defect. The interesting part is why the fix works, and where the fix does not reach.

The parser sees one token stream

A SQL front end runs a lexer over the statement text and produces a token stream, then a grammar over that stream. In the lexer, a single quote switches to literal-scanning mode; the literal ends at the next single quote, or at a doubled quote, which represents one literal quote character. A -- outside a literal starts a comment that runs to end of line. A ; outside a literal ends the statement.

Consider a login check written by concatenation:

sql = "SELECT id FROM users WHERE name = '" + name + "' AND pw_hash = '" + digest + "'"

With name set to alice, the lexer produces: keyword, keyword, keyword, keyword, operator, literal alice, keyword, and so on. With name set to x' OR '1'='1, the lexer sees the literal x, then a keyword OR, then a literal 1, an operator =, a literal 1, and the trailing ' from the template closes nothing because the template’s own quote was consumed early. The grammar accepts the result, because WHERE (name = 'x') OR ('1'='1') AND ... is a well-formed boolean expression. Nothing was “escaped through”; the attacker simply supplied syntax, and the parser did what parsers do.

The crucial observation is that the parser’s behaviour is correct at every step. There is no bug in the database. The application built a statement whose grammar differed from the one it intended, and the database executed the statement it was given.

Why escaping is dialect-dependent and therefore fragile

The intuitive repair is to escape dangerous characters in the value before concatenation: double every single quote, strip backslashes, remove semicolons. This works exactly as far as the escaping function agrees with the lexer about what is special. That agreement is not guaranteed across dialects.

Standard SQL recognises the doubled quote as an escaped quote inside a literal. MySQL, by default, additionally treats a backslash as an escape introducer inside string literals, so \' is a literal quote. A MySQL server can be started with NO_BACKSLASH_ESCAPES enabled, at which point \' is a backslash followed by a terminating quote, and an escaper written for the default mode now produces a premature literal close. The same server, the same query, a different mode: the escaper is wrong.

Multi-byte character sets produce a related failure without any mode change. When a connection uses a variable-width encoding such as GBK, a byte pair can be consumed by the lexer as one character, and the second byte of a value ending in a character whose trailing byte is 0x5C (the backslash) merges with the escaper’s inserted backslash. The lexer sees the merged character as ordinary text and the following quote as a genuine terminator. Escaping performed on bytes without knowledge of the connection’s character set cannot be correct in general.

Both failures share a shape: the escaper models the lexer imperfectly. Every additional dialect feature — dollar-quoted strings in PostgreSQL, bracket-quoted identifiers in SQL Server, q'[...]' quoting in Oracle — is another place where the model can drift from the implementation. An escaper that is right for one dialect and version is a liability when the application is later pointed at another.

Separating transport from syntax

The robust fix removes the concatenation rather than trying to make it safe. A parameterised query sends the statement text once, with placeholders in the positions where values belong, and sends the values on a separate path. The server parses the statement text before it has seen any value, so the parse tree is fixed; values are then attached to the tree’s parameter nodes as data.

In the PostgreSQL wire protocol this is the extended query flow: a Parse message carries the statement text with $1, $2 markers, a Bind message carries the actual values with their type OIDs, and Execute runs the already-parsed plan. MySQL’s binary protocol has the equivalent split in COM_STMT_PREPARE and COM_STMT_EXECUTE. Because the statement text is parsed alone, a value containing ' OR '1'='1 is attached as a string and compared as a string; it cannot become a boolean expression, because the grammar was resolved before the value existed.

That limit is worth following further: the positions a placeholder cannot occupy are the ones where a dynamic query can still be assembled from user input.

The same effect is available without a server-side prepared statement. Client libraries that interpolate parameters locally, quoting each value according to the driver’s own knowledge of the connection’s encoding and server mode, achieve the same separation provided the quoting is correct. This is why driver-level parameter binding is preferred over application-level escaping: the driver is the component that actually knows the wire encoding, and it is maintained against the server it talks to.

What placeholders cannot occupy

The parameter channel binds values, and a placeholder stands in a value position. It cannot stand in a syntactic position, because the grammar is already resolved. This is a hard limit, not a limitation of any particular library.

An identifier is a syntactic position. SELECT * FROM ? is not expressible; a table name, a column name, and a sort direction are all tokens that the parser must see at parse time. An application that lets a user choose a sort column must therefore map the user’s input onto a fixed set of known identifiers rather than passing it through. The mapping is the security control: input is compared against an allowlist, and only a matched entry’s literal text is placed into the statement.

The same applies to LIMIT and OFFSET in some engines. PostgreSQL accepts a parameter in both positions, since its grammar permits an expression there; engines that require a literal integer do not, and the usual workaround of casting the value to an integer in application code is the correct one, because an integer cast removes all syntax significance.

Where the boundary is lost again

Parameterisation protects the statement it is applied to. It does not protect statements built later from data that was stored earlier. A row inserted through a parameterised INSERT is stored as data, but if a reporting feature later reads that row and concatenates it into a new statement, the value crosses back over the boundary and the second statement is injectable. This ordering — store safely, retrieve and concatenate — is what makes second-order injection easy to miss during review: the write path looks correct.

Stored procedures do not change the analysis. A procedure whose body uses EXECUTE or EXECUTE IMMEDIATE on a string assembled from its arguments reintroduces concatenation inside the database, and the caller’s parameterisation is irrelevant to it. Conversely, a procedure that uses static SQL with its own parameters is safe for the same reason a client-side prepared statement is safe.

The general rule: at every point where text is assembled into a statement, ask whether the assembly happens before or after parsing. If before, the assembled text is the statement and its provenance is irrelevant to the parser. If after, the value occupies a slot the grammar has already fixed. There is no third case, and no escaping function that creates one.

Related boundaries

The identical structure appears wherever a language embeds another. HTML output encoding is the same problem with a different lexer, and template injection is the same problem with an expression evaluator instead of a SQL grammar. Recognising the pattern is more durable than memorising per-language escape lists, because the lists change with every dialect release and the pattern does not.