Escaping vs Parameterised Queries

What injection actually is

SQL injection is not about quotes. It is about a value crossing the boundary between data and code. When you build a statement by concatenating strings, the database receives one blob of text and parses all of it as SQL — it has no way to know which characters came from your source file and which came from a form field. If the attacker's characters change the parse tree, they have changed your query. CWE-89 names this precisely: improper neutralisation of special elements used in an SQL command.

The canonical example is a login form. The intended parse tree compares a column to a constant. The injected one compares a column to a constant, then ORs in a tautology, and the WHERE clause stops filtering anything. Everything after that — reading other tables via UNION, stacking a second statement after a semicolon, calling a function that reads the file system — is elaboration on the same move.

Notice what the attacker actually needed. In the classic payload they needed to close the string literal, so a quote was involved, and escaping would have blocked that particular string. That coincidence is the origin of the belief that escaping is the fix. The rest of this guide is about all the places where the attacker never needs a quote at all.

-- The application intends this
SELECT id, role FROM users WHERE email = 'alice@example.com' AND active = 1;

-- Input:  alice@example.com' OR '1'='1
-- Result: the WHERE clause no longer filters
SELECT id, role FROM users WHERE email = 'alice@example.com' OR '1'='1' AND active = 1;

-- Input:  ' UNION SELECT card_number, cvv FROM payments --
SELECT id, role FROM users
WHERE email = '' UNION SELECT card_number, cvv FROM payments -- ' AND active = 1;

Five places escaping does not reach

Numeric contexts are the big one. If the column is an integer, most people write the comparison with no quotes at all — and with no quotes there is no string literal, so quote escaping is a no-op. The input 1 OR 1=1 contains not a single character an escaper would touch, and it disables the filter. The same applies to LIMIT and OFFSET, which take bare integers.

Identifiers are the second. A sortable table endpoint that interpolates a column name into ORDER BY is injectable, and value escaping does not apply because an identifier is not a value. LIKE patterns are the third, and subtler: escaping the quote leaves the percent and underscore wildcards intact, so a user can turn a lookup into a full-table wildcard scan — a denial-of-service rather than a data breach, but a production incident either way.

Second-order injection is the fourth and the one that defeats careful teams. Input is escaped correctly on the way in and stored — so the database holds the raw apostrophes and semicolons, exactly as intended. Later, a report job reads that column and concatenates it into a new query. The value is now trusted data from your own database, nobody escapes it a second time, and it executes. Fifth is character-set trickery: on connections where the client and server disagree about the encoding, a multi-byte sequence can be arranged so that the escaping backslash is swallowed into a valid character and the quote survives — one reason OWASP treats escaping as a last resort rather than a control.

Every one of these disappears when the value never enters the SQL text. That is the argument for parameterisation in a sentence: it is not a better escaper, it is a different mechanism.

Watch it happen: the same payload, concatenated and bound

The clearest way to see the difference is to run both against a real database. The script below creates an in-memory SQLite table with three users and looks one up twice with the identical payload. The first lookup builds the SQL by string formatting; the second passes the payload as a bind parameter.

The output is unambiguous. The concatenated query returns all three rows — the payload closed the literal and the OR made the predicate always true. The bound query returns zero rows, because SQLite compared the name column against a twelve-character string that happens to contain quotes and, unsurprisingly, no user is called that. The value was never parsed as SQL, so there was nothing to neutralise.

That is the whole mechanism. A prepared statement is compiled once from SQL that contains only placeholders; the values are then sent as typed data and slotted into the already-built plan. No amount of punctuation in a value can reach the parser, because the parser has already finished. Escaping tries to make dangerous characters harmless; binding makes the question of dangerous characters irrelevant.

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
cur.executemany("INSERT INTO users (name) VALUES (?)", [("ada",), ("grace",), ("alan",)])

payload = "x' OR '1'='1"

unsafe = "SELECT id, name FROM users WHERE name = '%s'" % payload
print("unsafe SQL :", unsafe)
print("unsafe rows:", cur.execute(unsafe).fetchall())

cur.execute("SELECT id, name FROM users WHERE name = ?", (payload,))
print("bound rows :", cur.fetchall())

# Actual output (Python 3.12.10, SQLite 3.49.1):
# unsafe SQL : SELECT id, name FROM users WHERE name = 'x' OR '1'='1'
# unsafe rows: [(1, 'ada'), (2, 'grace'), (3, 'alan')]
# bound rows : []

Prepared statements in Node and Java

In Node, node-postgres takes numbered placeholders and an array of values; mysql2 takes question marks. mysql2 draws a distinction worth knowing: query() escapes the values client-side and sends a finished string, while execute() sends a true server-side prepared statement with the values on a separate channel. Both defeat value injection, but only execute() keeps the data out of the SQL text — and it lets MySQL cache the plan. Neither protects an identifier: for a dynamic column name mysql2 offers a double question mark, which applies backtick quoting, and you should still gate it behind an allow-list.

In Java the equivalent is PreparedStatement. Build it from a template containing only question marks, then set each parameter by index with the typed setter — setString, setInt, setTimestamp. The typing matters: setInt refuses a value that is not an integer, so the numeric-context hole from the previous section closes automatically. The failure mode in Java code is almost always a plain Statement built from a concatenated string that someone meant to convert later and never did.

The rule that generalises across all of them: the SQL string must be a compile-time constant in your source, or assembled only from fragments your code controls. The moment a runtime value is concatenated into it, you are back to parsing — and note that a JavaScript template literal is concatenation wearing a nicer syntax, which is why it accounts for so many modern findings. If a long generated statement is hard to eyeball for that, run it through the SQL Formatter; an interpolated value is much easier to spot on its own line.

// node-postgres
const { rows } = await client.query(
  "SELECT id, role FROM users WHERE email = $1 AND active = $2",
  [email, true],
);

// mysql2/promise - execute() prepares server-side
const [rows2] = await conn.execute(
  "SELECT id, role FROM users WHERE email = ? AND active = ?",
  [email, 1],
);

// Dynamic sort column: allow-list FIRST, identifier quoting second
const SORTABLE = new Set(["created_at", "email", "last_login"]);
const sort = SORTABLE.has(req.query.sort) ? req.query.sort : "created_at";
const [rows3] = await conn.query(
  "SELECT id FROM users ORDER BY ?? DESC LIMIT ?",
  [sort, 50],
);

// Java, for comparison - typed setters close the numeric hole too:
//   PreparedStatement ps = conn.prepareStatement(
//       "SELECT id, role FROM users WHERE email = ? AND active = ?");
//   ps.setString(1, email);
//   ps.setBoolean(2, true);
//   ResultSet rs = ps.executeQuery();

ORMs are safe by default and unsafe at the seams

Every mainstream ORM and query builder parameterises the queries it generates. Objects saved through Hibernate, the Django ORM, ActiveRecord, Prisma, SQLAlchemy or Knex produce prepared statements with bind variables, and you would have to work to make them do otherwise. For the large majority of an application that is ordinary create-read-update-delete work, using the ORM idiomatically is the whole defence.

The seams are where the raw-SQL escape hatches live: Django's extra() and RawSQL, ActiveRecord's where with a bare interpolated string, SQLAlchemy's text(), Prisma's queryRawUnsafe, Knex's whereRaw. These exist because real reporting queries outgrow the builder, and they hand you back the concatenation problem exactly as it was. Most of them also accept bind parameters — text() takes named placeholders, Prisma's tagged-template raw form parameterises automatically while the Unsafe variant does not, and whereRaw takes a bindings array. Use those forms and the escape hatch stays safe.

Two more seams are easy to miss. Column and table names passed to a builder's orderBy or select are often interpolated rather than bound, because they are identifiers — allow-list them. And stored procedures are not automatically safe: a procedure that builds a string and runs EXECUTE IMMEDIATE or sp_executesql on it is as injectable as any application code, and the injection now lives inside the database where your application-side review will never see it.

When escaping is still the right tool

There are legitimate uses, and pretending otherwise leads people to do something worse. Hand-written migrations and seed files contain literals you typed yourself; there is no user input and no injection risk, and doubling a quote so a company name with an apostrophe inserts cleanly is exactly correct. Ad-hoc analysis in a console is the same. A quick pass through SQL Escape is the right amount of ceremony for that job.

Dynamic identifiers are the genuinely unavoidable case. You cannot bind a table name, so if a multi-tenant job really must build a statement against a schema chosen at runtime, you need identifier quoting: quote_ident() in PostgreSQL, QUOTENAME() in SQL Server, backticks in MySQL. Combine it with an allow-list or a pattern check — quoting stops the identifier from breaking out of its delimiters, but it does not stop someone naming a table you did not intend to touch. PostgreSQL also exposes quote_literal() and the format() function, whose %I and %L specifiers are the cleanest way to write a PL/pgSQL routine that assembles SQL.

The third case is a different destination entirely. A value that is safe in SQL is not automatically safe in HTML, JSON, CSV or a shell command, and each of those needs its own encoding applied at the point of use — HTML Escape for markup, a real CSV writer for spreadsheets. Escaping once at the front door, in the hope of covering every downstream context, is the anti-pattern OWASP spends much of its cheat sheet warning about.

-- PostgreSQL: format() with %I (identifier) and %L (literal)
CREATE FUNCTION purge(schema_name text, cutoff timestamptz) RETURNS void AS $fn$
BEGIN
  EXECUTE format('DELETE FROM %I.events WHERE created_at < %L', schema_name, cutoff);
END;
$fn$ LANGUAGE plpgsql;

-- SQL Server: parameterised dynamic SQL - the identifier is quoted,
-- the value is still a bind parameter
DECLARE @sql nvarchar(max) =
  N'SELECT id FROM ' + QUOTENAME(@table) + N' WHERE email = @email';
EXEC sp_executesql @sql, N'@email nvarchar(320)', @email = @email;

Defence in depth around the parameter

Parameterisation is the control; the rest is containment. Validate input against what it is supposed to be before it reaches the query — an integer id parsed as an integer, an email matched against a pattern, an enum checked against a set. This will not save you on its own, and it is not a substitute for binding, but it turns a class of malformed input into a 400 response instead of a database round trip.

Give the application's database user only the privileges it needs. A web role that cannot drop tables, cannot read the payments table, and cannot execute file-system functions converts a successful injection from a breach into an error in a log. Separate read-only and read-write roles if the application's shape allows it. Turn off detailed database errors in production responses — a stack trace naming the failing column is a free map for anyone probing.

Then make it observable. Log the parameterised statement text (never the parameters, if they may hold secrets), alert on unusual error rates from the database layer, and put a scanner in CI that flags string-built SQL. OWASP's cheat sheet is worth reading end to end once; the short version is that prepared statements are the primary defence, allow-listing handles identifiers, escaping is the last resort, and least privilege is what limits the damage when one of those is missed.