skip to content

Why is binding a value as a query parameter fundamentally different from escaping the quotes in that value before concatenating it, and where does escaping break down across different database engines?

level: middleimportance: must knowfreq 80%

answer

  1. Parse first, values later — structure frozen
  2. Escaping = modelling someone else's lexer
  3. MySQL backslashes vs PG standard_conforming_strings vs T-SQL doubled quotes
  4. Multi-byte charset swallows the backslash
  5. Client-side emulated prepares re-introduce interpolation

basics

~20 s

Binding fixes the statement's structure at parse time, before any value exists, so a value can never become syntax. Escaping tries to neutralise characters afterwards and must model each engine's lexer and session settings exactly — which differ, so it fails.

solid answer

~50 s

Parameter binding is a *structural* defence: the template with placeholders is parsed and planned first, then values arrive over a separate channel and are only ever compared. No value, however hostile, can add a clause, because parsing is already finished. Escaping is a *transformational* defence: it keeps one shared channel and tries to make the value inert by rewriting characters, so its correctness depends on modelling the target lexer exactly. That model differs everywhere. MySQL honours backslash escapes unless `NO_BACKSLASH_ESCAPES` is set; PostgreSQL disables backslash escapes when `standard_conforming_strings` is on and also has dollar-quoting; SQL Server has no backslash escape at all and only doubles apostrophes; Oracle adds `q'[...]'` alternate quoting. Worse, escaping does nothing in unquoted numeric or identifier positions, and multi-byte client encodings such as GBK have historically let a lead byte swallow the escaping backslash. Escaping can be correct; it is correct-by-audit, not correct-by-construction.

code

text · 11 lines
text
BINDING
  t0  client -> engine: PREPARE "... WHERE name = ?"   (no attacker bytes exist yet)
  t1  engine: lex, parse, plan  -> structure now FROZEN
  t2  client -> engine: EXECUTE with value = "' OR 1=1 --"
  t3  engine: compare column to that exact 12-char string. no re-lexing.

ESCAPING
  t0  app rewrites value, guessing the engine's literal grammar
  t1  app concatenates into statement text
  t2  engine lexes the whole thing, attacker bytes included
      -> safety depends on t0's model matching t2's lexer exactly

go deeper

for a junior

State the one-line difference — the statement is parsed before the value arrives, so the value can never become syntax — and that this is why we always bind.

for a middle

Add at least two concrete dialect divergences and the contexts where escaping does not apply at all (numeric, identifier, LIKE patterns).

for a senior

Discuss emulated prepares, charset-related escape absorption, and why a control whose correctness depends on a database session setting is a poor control.

for a principal

Argue the general principle: prefer defences that hold by construction over defences that hold by audit, and design the data-access layer so the unsafe path cannot be expressed.

## Two different kinds of defence When untrusted data must reach an interpreter, there are only a few structurally different ways to keep it from becoming instructions, and they are not equally strong. **Separation** sends structure and data over different channels. The interpreter is told the shape of the operation first, and values arrive afterwards in slots that the grammar cannot escape from. **Escaping** keeps one channel and rewrites the data so the interpreter's lexer will read it as inert. **Validation** rejects data that does not look acceptable. **Detection** watches for attempts and alerts. Each rung down trades a guarantee for a heuristic. Parameter binding is the first rung; escaping is the second. ## What binding actually does A prepared or parameterised statement is a template such as `SELECT id FROM users WHERE name = ?`. The engine (or, in some drivers, the client library) parses that template into a syntax tree and — for a server-side prepare — into a plan. At that moment the attacker's bytes are not present anywhere in the process. The subsequent execute call carries the values as typed fields in a protocol message, not as text spliced into a statement. The engine binds each value to its placeholder slot. There is no code path where a bound value is re-lexed, so no content can create a token, close a literal, open a comment, or start a new statement. That is a *property of the mechanism*, independent of the value's content, the client character set, the session settings and the developer's care. This is the reason "parameterise everything" is a rule and not a recommendation: it converts a per-call judgement into an invariant. ## Why escaping is fragile in principle Escaping requires the escaper to be a perfect model of the consumer's lexer. Three assumptions must hold simultaneously: the escaper knows the exact dialect, it knows the runtime settings that change that dialect, and the value ends up in the context the escaper assumed. Each assumption fails in real systems. **Dialects diverge.** SQL's portable escape for an apostrophe inside a literal is to double it (`''`). MySQL additionally accepts C-style backslash escapes (`\'`) by default, so an escaper written for MySQL emits backslashes; PostgreSQL, with `standard_conforming_strings` on (the default in modern versions), treats a backslash as an ordinary character, so a backslash-based escaper leaves the apostrophe live. SQL Server has no backslash escape at all. Oracle offers alternate quoting (`q'[ ... ]'`) with a user-chosen delimiter, a whole extra literal grammar to model. PostgreSQL also has dollar-quoting (`$tag$ ... $tag$`), and every engine has its own comment markers (`--`, `#`, `/* */`). **Settings change the dialect at runtime.** MySQL's `NO_BACKSLASH_ESCAPES` SQL mode flips backslash handling for the session. PostgreSQL's `standard_conforming_strings` does the same in the other direction. A library that escapes without consulting the live connection state can be right on Monday and wrong after a config change — a security control whose correctness depends on a database setting that no one thinks of as security-relevant. **Encoding can eat the escape.** In multi-byte client encodings such as GBK, Big5 or SJIS, some two-byte characters have a second byte equal to `0x5C` — the backslash. A naive byte-wise escaper that inserts a backslash before an apostrophe can produce a byte sequence that the server, decoding in the client charset, reads as one multi-byte character followed by a live apostrophe. The escape has been absorbed. This class of bug is precisely why escaping must be charset-aware and why binding, which never re-lexes, is immune. **Context mismatch.** Escaping quotes assumes the value lands inside a quoted literal. It does nothing at all in an unquoted numeric position (`WHERE id = <value>`), in an identifier position (`ORDER BY <column>`), inside a `LIKE` pattern (where `%` and `_` are wildcards that quote-escaping ignores, enabling denial of service or over-broad matching), or inside a nested dynamic statement built by a stored procedure. ## The honest caveats Binding is not magic in every configuration. Some drivers *emulate* prepared statements client-side, interpolating the values into the statement text themselves before sending it — correctness then depends on that interpolator being charset-aware, which is the escaping problem re-introduced under a safe-sounding name. Know whether your driver prepares server-side and whether emulation is enabled. Binding also protects only the values you actually bind: a statement that is 95% template and 5% concatenated fragment is injectable through that 5%. ## How to say it in an interview Lead with the invariant: parsing happens before the value exists, so structure is immutable by construction. Then contrast: escaping preserves the shared channel and merely tries to neutralise content, which requires an exact model of a lexer that varies by engine, by session setting, and by client encoding. Name two or three concrete divergences — MySQL backslashes versus PostgreSQL `standard_conforming_strings` versus SQL Server's doubled quotes — and finish with the contexts where escaping is not even applicable.

  • Your driver reports that prepared statements are "emulated". What changes for security?
    Emulation means the client library interpolates the values into the statement text itself and sends one complete string, so the server parses attacker-derived bytes after all. Safety then rests on that interpolator being dialect- and charset-aware, which is exactly the escaping problem. It is usually still better than hand-rolled concatenation, but the structural guarantee is gone, so prefer server-side prepares or at minimum ensure the connection charset is one the driver handles safely.
  • A payload is stored safely through a parameterised insert, then a nightly report concatenates that stored value into SQL. Is the system safe?
    No — that is second-order injection. The stored row is not a trusted source; the taint did not expire because it made a round trip through the database. Because the request that plants the payload differs from the one that triggers it, request-scoped scanners and edge filtering miss it entirely. The rule is to bind at every sink regardless of where the value came from, rather than to sanitise once at the perimeter.

Binding is a printed form with boxes: whatever you write in a box stays in the box. Escaping is dictating prose and hoping your carefully pronounced punctuation is transcribed the way you meant — correctness depends entirely on the listener's conventions.

saying these in an interview costs you the question

  • "A good escaping library is equivalent to binding" — equivalent only if it models the live dialect, session settings, charset and context correctly, which is an audit obligation rather than a guarantee.
  • "Prepared statements protect the whole query" — they protect the bound values; any concatenated fragment is still injectable.
  • "Escaping the apostrophe is enough" — useless for numeric and identifier contexts, and it ignores LIKE metacharacters.
  • "All databases escape the same way" — backslash handling alone differs between MySQL, PostgreSQL and SQL Server, and flips with session settings.
  • "Once the value is in the database it is trusted" — second-order injection exploits exactly that assumption.

context

resolving links…