Injection has been on the OWASP Top 10, in some form, since the list began, and the fix has been known for just as long: don’t build SQL by gluing strings together; send values as parameters. Interviewers still ask about it, but a good answer goes past “use prepared statements”. They want to hear why parameters work, what they can’t do, and the edge cases where code that uses parameters is still vulnerable or still wrong.
The injection demo below runs in Python’s built-in sqlite3 module so you can reproduce it without a server. The PostgreSQL examples were run on PostgreSQL 16.15.
The bug: input that becomes code
A lookup function that formats the email into the query:
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, password_hash TEXT, is_admin INTEGER);
INSERT INTO users VALUES (1,'ana@example.com','$2b$12$abc',1),(2,'ben@example.com','$2b$12$def',0);
""")
def find_unsafe(email):
sql = f"SELECT id, email FROM users WHERE email = '{email}'"
return db.execute(sql).fetchall()With normal input it works. With x' OR '1'='1, the quote in the input closes the string literal early and the rest becomes SQL:
SELECT id, email FROM users WHERE email = 'x' OR '1'='1'
[(1, 'ana@example.com'), (2, 'ben@example.com')]Every user comes back. A slightly more ambitious input, x' UNION SELECT id, password_hash FROM users --, appends a second query and comments out the trailing quote:
SELECT id, email FROM users WHERE email = 'x' UNION SELECT id, password_hash FROM users --'
[(1, '$2b$12$abc'), (2, '$2b$12$def')]Now the endpoint returns password hashes in the email column. In a real attack the same technique reads other tables, and on databases or drivers that allow stacked statements, '; DROP TABLE users; -- becomes possible too. The root cause in every case is the same: the database received one string and couldn’t tell which parts the developer wrote and which parts the user typed.
The fix: send values separately
def find_safe(email):
return db.execute("SELECT id, email FROM users WHERE email = ?", (email,)).fetchall()Both malicious inputs now return []. The query text is fixed and contains a placeholder. The value travels separately, and the database treats it as a single string value no matter what characters it contains. There’s no quoting to escape from because the value was never inside quotes in the SQL text.
PostgreSQL does this at the protocol level. In the extended query protocol, the client sends the statement with $1, $2 placeholders in a Parse message and the values in a separate Bind message. You can see the same separation with SQL-level PREPARE:
PREPARE by_email(text) AS
SELECT id, email FROM users WHERE email = $1;
EXECUTE by_email('x'' OR ''1''=''1'); -- 0 rows: it's just an odd email address
EXECUTE by_email('ben@example.com'); -- 1 rowIn application code it looks like this (node-postgres shown; the snippet is illustrative, not run for this article):
const { rows } = await pool.query(
'SELECT id, email FROM users WHERE email = $1',
[req.body.email],
);Escaping is not the same fix. Functions that escape quotes in input can work, but they depend on matching the database’s exact quoting rules, character encoding and settings. Historic bypasses with multibyte encodings and backslash handling are why the advice moved from “escape correctly” to “don’t put values in the SQL text at all”.
What a parameter can’t be
Parameters stand for values: things that could otherwise be a literal like 'ben@example.com' or 42. They can’t stand for table names, column names, keywords or operators, because the statement is parsed before values arrive and the parser has to know its structure.
PREPARE tbl(text) AS SELECT count(*) FROM $1;
-- ERROR: syntax error at or near "$1"That’s the point where developers reach for string formatting again, and where most modern injection bugs live: sortable columns, dynamic filters, multi-tenant schema names, report builders.
ORDER BY $1 is the sneaky one. It doesn’t fail:
PREPARE sorted(text) AS SELECT id, email, created_at FROM users ORDER BY $1;
EXECUTE sorted('created_at'); id | email | created_at
----+--------------------+------------
1 | ana@example.com | 2026-01-05
2 | ben@example.com | 2026-03-01
3 | chen_x@example.com | 2026-02-01Sorted by date, this would be 1, 3, 2. PostgreSQL sorted by the constant string 'created_at', which is identical for every row, so the order is whatever the scan produced. No error, no injection, just a sort that silently does nothing.
For identifiers, use an allowlist in application code: map the user’s choice to a fixed set of known SQL fragments.
const SORTS = { newest: 'created_at DESC, id DESC', email: 'email ASC, id ASC' };
const orderBy = SORTS[req.query.sort] ?? SORTS.newest;
const sql = `SELECT id, email FROM users ORDER BY ${orderBy} LIMIT $1`;The user can only pick a key; the SQL text comes from your code. When identifiers must be dynamic inside the database (in PL/pgSQL functions, for example), use format() with %I for identifiers and %L for literals, which quote correctly:
SELECT format('SELECT count(*) FROM %I WHERE email = %L',
'users; DROP TABLE users', 'o''brien@example.com'); SELECT count(*) FROM "users; DROP TABLE users" WHERE email = 'o''brien@example.com'The would-be injection becomes a strangely named table that doesn’t exist. Better still, pass values with EXECUTE ... USING and keep format for identifiers only.
Parameterized but still wrong
IN lists. Building IN ($1, $2, $3...) dynamically is fine but fiddly. In PostgreSQL, pass an array: WHERE id = ANY($1) with $1 as int[]. EXECUTE ids('{1,3}') returned ids 1 and 3, and the statement text never changes with the list length.
LIKE wildcards. WHERE email LIKE '%' || $1 || '%' is injection-safe, but % and _ in the value are still wildcards. A search for _ matched all three emails, not just chen_x@example.com. That’s a correctness bug, and with big tables a performance one, since a user can submit patterns that force expensive scans. Escape the wildcards before binding (PostgreSQL’s default LIKE escape character is backslash):
WHERE email LIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'With that, searching _ returned only chen_x@example.com. Many teams do the escaping in application code instead; either is fine as long as it happens.
ORM escape hatches. sequelize.literal, Django’s .extra() and RawSQL, ActiveRecord’s string where("name = '#{x}'"), and SQLAlchemy’s text() with f-strings all bring string building back. Code review should treat any raw-SQL API with interpolation as a red flag.
Second-order injection. A value stored safely with parameters can be dangerous later if another piece of code reads it back and formats it into SQL, such as a saved report filter. Parameterize at every point where SQL is built, not only at the edge where input arrives.
Prepared statements and plans
Preparing a statement also lets PostgreSQL reuse its plan. For the first five executions it builds a custom plan using the actual values. After that it compares costs and may switch to a generic plan that works for any value. That’s usually a win, but generic plans can’t use value-specific knowledge: a partial index on status = 'pending' can’t be used for status = $1, and a skewed column (one tenant with 90% of rows) may get a plan that’s wrong for some values. plan_cache_mode (auto, force_custom_plan, force_generic_plan) controls this per session or per role. The partial index guide shows a generic plan ignoring an index that a custom plan uses.
Connection poolers matter too. Named prepared statements belong to one server connection, so PgBouncer in transaction mode historically broke them. PgBouncer 1.21 added support for protocol-level prepared statements (max_prepared_statements); with older versions, drivers had to disable server-side preparation. Plain parameterized queries using unnamed statements work either way.
Interview practice: the admin search endpoint
An internal admin page lets staff search orders. The handler builds SQL like this:
let sql = `SELECT * FROM orders WHERE 1=1`;
if (q.status) sql += ` AND status = '${q.status}'`;
if (q.customer) sql += ` AND customer_name ILIKE '%${q.customer}%'`;
sql += ` ORDER BY ${q.sort || 'created_at'} ${q.dir || 'DESC'} LIMIT ${q.limit || 50}`;The team says it’s fine because only staff can reach it. Review it and propose a fix.
Answer
Every interpolation is injectable: status, customer, sort, dir and limit. “Only staff can reach it” isn’t a defence. Staff accounts get phished, cross-site request forgery can drive an internal page from a staff browser, and the database user for this endpoint probably has broad read access, so a UNION injection exposes everything.
The fix splits values from structure. Values become parameters: collect conditions and parameters in arrays, AND status = $1, AND customer_name ILIKE $2 with the value '%' + escapeLike(q.customer) + '%' so wildcards in names behave. limit becomes a parameter after parsing it as an integer and clamping it to, say, 1 to 200. sort and dir can’t be parameters, so they go through an allowlist: map sort to one of a few known column names, dir to exactly ASC or DESC, and add id as a tiebreaker. I’d also run the endpoint as a database role with read access only to the tables it needs, set a statement_timeout for it, and log the final parameterized SQL (without values) so reviewers can see the query shapes. A query builder such as Knex or Kysely would make the dynamic conditions safer to write, but the allowlist for identifiers is still needed there.