A prepared statement is a SQL template with placeholders; the user values are bound as typed data. The database parses the template once and binds the values separately - so the parser never sees attacker input as grammar. This is the only defence that actually works.
- L1Fundamentals
- L14Secure coding practices
- Explain what "prepared" means at the wire-protocol level.
- Distinguish a bound parameter from a string-concatenated value.
- Recognise the four wire-protocol messages: parse, bind, execute, sync.
- Implement parameterised queries in your stack of choice.
- Prepared statement
- A SQL template with placeholders ($1, $2, ?). The database parses the template once, plans it once, and binds the placeholder values on each execution.
- Bind step
- The wire-protocol message that sends the placeholder values to the database. The values are typed (int4, text, bool) and never interpreted as SQL grammar.
- Wire protocol
- The byte-level format the database driver uses to talk to the server. PostgreSQL uses the extended query protocol (parse/bind/execute/sync). MySQL uses the COM_STMT_PREPARE/COM_STMT_EXECUTE family.
- Plan reuse
- A prepared statement's plan is computed once and reused across executions with different bound values. The performance win is real (5-30% on hot queries) and is the original motivation for the feature.
The wire-protocol view of parameterisation
Most application developers think of parameterisation as "the ORM method that takes a list of values". The wire-protocol view is sharper. The extended query protocol (PostgreSQL's standard; close cousin in MySQL) has four messages:
- Parse - the client sends the SQL template. The database parses and plans it. The template contains placeholders (
$1,$2); it contains no user data. - Bind - the client sends the placeholder values, each tagged with a type (
int4,text,bool). The values are never combined with the SQL text. - Execute - the database runs the plan with the bound values. The executor applies the filter against the typed values; the parser never sees them.
- Sync - a tiny message that tells the server to flush and respond.
The user value never enters the parser as a token. Even if it contains an apostrophe, a comment, or a full UNION clause, the parser treats it as a value of the declared type. The database is the only place where the SQL text and the values meet, and the protocol guarantees they are kept separate.
Compare the two pipelines
Type any value into the input box. Switch between Bound parameter and String concatenation to see what the database actually receives. The bound-parameter path keeps the user value as a typed bind message; the concatenation path merges it into the SQL text the parser tokenises.
- Parser
Tokenise the template. The user value is not a token - it is a typed placeholder.
SELECT * FROM users WHERE id = $1
- Bind (driver)
The driver sends the value over the wire as a typed int4. The SQL text does not include the value.
BIND: id = 7' OR '1'='1' -- (int4)
- Executor
Database matches the int4 against the column. The string is never evaluated as SQL.
→ 0 rows (id is the literal value, not a WHERE clause)
Why this is the only durable fix
Every other defence in the OWASP cheat sheet - escaping, stored procedures, allowlist validation - has a bypass history. Parameterised queries have not. The wire-protocol separation between code and data is enforced by the database engine itself; there is no regex the attacker can break, no allowlist the developer can forget to update. The 2017 Heartbleed-style surprises that have plagued every other defence have not materialised for parameterisation.
The performance side benefit is real: a prepared statement parses and plans once, then reuses the plan for every execution with new bound values. On hot queries, this is a 5–30% latency win. The original motivation for the feature in PostgreSQL 7.2 (2002) was performance; the security benefit was discovered later.
The five-line fix
// VULNERABLE - string concatenation
const query = "SELECT * FROM users WHERE id = '" + id + "'";
// SAFE - parameterised
const query = 'SELECT * FROM users WHERE id = $1';
const result = await db.query(query, [id]);What to remember
- A prepared statement is a SQL template parsed once; the user values are bound separately as typed data.
- The wire protocol keeps code and data separate at the byte level - the parser never sees the values.
- Every other defence has a bypass history. Parameterisation has not.
- Performance win: parse once, plan once, bind many.
Knowledge check
0/3 answered · 0 correct1.In PostgreSQL's extended query protocol, what is the role of the BIND message?
2.Why does parameterised query syntax have a 5-30% performance advantage on hot queries?
3.Why are bound parameters the only durable SQLi defence?