The conclusion: structure goes through the compiler, data goes through parameters
SQL injection is not caused by "a quote that wasn't filtered"; it happens when query structure and user data are concatenated into one string. Let the code own the structure and let data travel only through parameters, and injection has nowhere to stand. What follows runs from "why it works" to "why parameterization works" to "which patterns only look safe".
Why the attack works: input → sink → impact
| Input | Sink | Impact |
|---|---|---|
' OR '1'='1' -- |
A WHERE clause in login or lookup queries | The condition is always true; auth bypass or unauthorised reads |
' UNION SELECT ... |
A SELECT result set | Rows from other tables appended to the response |
| A function that raises an error, or a malformed type | Error message reflected to the client | Table names, column names, and SQL fragments leak in the error |
| Boolean or timing payloads | Differences in response body or latency | Read arbitrary data one bit at a time |
' OR 1=1 -- stored first |
A second concatenation in admin tools, jobs, or reports | Harmless as stored text, detonated when the backend runs it |
The principle is one sentence: the database treats input as part of SQL syntax, so the attacker can rewrite the statement structure, not merely one value.
Sinks that get overlooked
Developers watch the login form; the real entry points are places that "look numeric or fixed":
order by,limit, filter field names — values can be parameterized, but column names cannot;- report builders, exports, and audit search;
LIKEpatterns;- second queries triggered by cron jobs, queue consumers, and admin panels;
tenant_idtaken from a request header in multi-tenant systems;- dynamic SQL inside database functions and stored procedures.
Why parameterization breaks the chain
The point is not "escaping more thoroughly" but that data never enters parsing:
- The application sends the statement with placeholders first; the database parses it into a syntax tree, where a placeholder is a value token in the grammar.
- The parameters arrive afterwards as separate messages; the database only type-checks and binds them, never lexing or parsing them again.
- So
' OR 1=1 --reaches the database as an ordinary string — quotes, spaces, and comment markers cannot change an already-fixed syntax tree.
Escaping always keeps data inside the same string, so its safety depends on the escape rules covering every exception of the parser. Parameterization moves data out of the grammar, removing the precondition for injection. One patches holes; the other makes the hole impossible.
// ❌ Structure and data in one string
db.query(`SELECT * FROM users WHERE email = '${email}'`)
// ✅ Structure fixed, data separate
db.query('SELECT * FROM users WHERE email = $1', [email])
Parameterization also fixes two adjacent problems: the database type-checks values against placeholders, removing the guesswork; and prepared statements reuse the parsed result, cutting repeated parsing.
Three "parameterized-looking" mistakes
ORM raw escape hatches
ORMs are safe by default but always ship an escape hatch, and the hatch usually swallows a string:
// ❌ Interpolation inside literal / raw / whereRaw bypasses parameterization
User.findAll({ where: sequelize.literal(`role = '${role}'`) })
db.query(`SELECT * FROM users WHERE name LIKE '%${term}%'`)
Sort fields are subtler because they are not in the WHERE clause:
// ❌ The field name comes from the user; the ORM splices it into SQL
User.findAll({ order: [[req.query.sort, 'DESC']] })
// ✅ Allowlist mapping with a safe fallback
const SORT = { name: 'name', created: 'created_at' }
const col = SORT[req.query.sort] ?? 'created_at'
User.findAll({ order: [[col, 'DESC']] })
Second-order concatenation: query one is safe, query two is not
Parameterization protects only the statement it appears in. If code reads a value and then concatenates it into another SQL string — report generation, audit search, batch updates, dynamic SQL in procedures — query two's risk has nothing to do with query one.
const rows = await db.query('SELECT name FROM tags WHERE id = $1', [id]) // ✅ Safe
await db.query(`UPDATE reports SET filter = '${rows[0].name}'`) // ❌ From the database, still untrusted
The test is not "where did the value come from" but "did it end up inside SQL text". Historical rows, third-party responses, and admin-entered content can all carry a payload from an earlier injection.
Wide characters: escaping breaks under certain charsets
This is the classic counterexample to the escaping approach. In GBK, addslashes inserts a backslash 0x5c before a quote, but 0xbf 0x5c is a valid double-byte GBK character. An attacker submits %bf%27; after escaping it becomes %bf%5c%27, the parser consumes bf 5c as one character, the backslash disappears, and the quote is exposed again.
The conditions are precise:
- The connection charset is a double-byte encoding such as GBK, GB2312, or SJIS;
- The defence relies on an escape function (
addslashes, a hand-writtenreplace); - Client and server charset declarations disagree (for example, a missing
SET NAMES).
The fix is not a different escape function but dropping the escape-first design: parameterize, or set the charset explicitly and pair it with allowlisting. LIKE has a cousin bug — a literal % or _ in user input must be escaped, not left to shape the match.
What to do where you cannot parameterize
Column names, table names, and sort directions do not accept placeholders, so use an allowlist map: treat user input as a dictionary key, fall back to a default when missing, and never splice the input itself.
// ✅ Allowlist plus default; input never enters SQL text
const SORT_FIELDS = { name: 'name', created: 'created_at', price: 'price' }
const sortField = SORT_FIELDS[req.query.sort] || 'created_at'
const orderDir = req.query.order === 'desc' ? 'DESC' : 'ASC'
db.query(`SELECT * FROM products ORDER BY ${sortField} ${orderDir} LIMIT $1`, [limit])
Whether LIMIT can be parameterized depends on the database and driver — verify in your project. Where it cannot, coerce to an integer and clamp it to a sane range before it reaches SQL.
Least privilege: it caps the damage after a breach
Parameterization lowers the chance of injection; least privilege lowers the loss once it happens. Neither replaces the other:
| Account | Privileges | Purpose |
|---|---|---|
| Application | SELECT / INSERT / UPDATE on business tables only | Serving requests |
| Migration | DDL (create, alter, index) | Deployments and migrations, stored separately |
| Reporting | Read-only, a read replica when possible | Analytics and exports |
| Admin | Full | Manual operations, never in application config |
Three more rules: disable detailed error output in production; do not expose the database to the public internet; audit access to sensitive tables.
A fixed example: searchable list
// ❌ Concatenation opens sort, filter, and pagination at once
app.get('/api/products', (req, res) => {
const sql = `SELECT * FROM products WHERE name LIKE '%${req.query.q}%'
ORDER BY ${req.query.sort} LIMIT ${req.query.limit}`
db.query(sql, (err, rows) => res.json(rows))
})
// ✅ Filter as a parameter, sort via allowlist, pagination coerced to a number
app.get('/api/products', async (req, res) => {
const q = String(req.query.q ?? '')
const sortField = SORT_FIELDS[req.query.sort] || 'created_at'
const limit = Math.min(Math.max(parseInt(req.query.limit, 10) || 20, 1), 100)
const rows = await db.query(
'SELECT * FROM products WHERE name LIKE $1 ORDER BY ' + sortField + ' DESC LIMIT $2',
[`%${q}%`, limit]
)
res.json(rows)
})
Note that sortField is still concatenated on purpose: column names cannot be parameterized, so its safety comes from being a constant out of the allowlist, never from user input. If a literal % in q must match, escape it inside the parameter value.
Pre-launch checks
- [ ] Every SQL string is a code constant; user input appears only in parameter positions
- [ ] ORM
raw/literal/whereRaw/orderByall pass through an allowlist - [ ] No code path concatenates one query's results into another SQL statement
- [ ] Column names, table names, and sort directions come only from mapping tables
- [ ] The application account has no DDL rights; reporting uses a read-only account
- [ ] Production never echoes SQL errors; exceptions surface as generic messages
Reproducible results
Run the same query against an in-memory SQLite database and the difference between concatenation and parameterization is immediate:
import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE users (name TEXT, role TEXT)")
db.execute("INSERT INTO users VALUES ('alice', 'user')")
q = "' OR 1=1 --"
db.execute(f"SELECT * FROM users WHERE name = '{q}'") # Injection: the condition is always true; every user returns
db.execute("SELECT * FROM users WHERE name = ?", (q,)) # Parameterized: q stays a string; nothing returns
The concatenated query makes WHERE always true and returns every user; the parameterized one treats q as a string and returns nothing. What changed is statement structure, not a value. To inspect structure before debugging, use the SQL formatter; syntax lives in the SQL reference.