The defensive mindset. Six rules cover ~95% of real-world SQLi fixes; the rest is case-by-case engineering judgement. This lesson walks through each rule, why it works, and why the broken-sounding alternatives do not.
- L1Fundamentals
- L2How databases work
- Apply the six rules that cover 95% of real SQLi fixes.
- Recognise why escaping and apostrophe blocking are not safe defaults.
- Use a server-side identifier allowlist for dynamic table, column, and sort names.
- Configure a least-privilege application database user.
- Identifier allowlist
- A small, server-side set of valid table/column/sort names. Used when the user must choose a name at runtime and the SQL grammar cannot accept a parameter for that position.
- Least privilege
- The application database user has only the grants it needs. SELECT on app tables, no read on information_schema, no DDL, no write except in narrowly-scoped migration paths.
- Defence-in-depth
- Multiple, independent layers of defence. If one layer fails, the next catches. Escaping as a fallback to parameterisation is defence-in-depth; escaping alone is not.
- CWE-89 mitigation
- The MITRE-defined strategies for closing SQLi. Rank-ordered: parameterised queries (most effective), stored procedures, allowlist validation, escaping (least effective).
Six rules, ranked by effectiveness
The MITRE CWE-89 mitigation list ranks parameterised queries first, allowlist validation second, escaping last. Here is the engineering-flavoured version of that list:
- Parameterise every query. Bound parameters are the only safe default.
- Allowlist identifiers. When the user must choose a table, column, or sort name, validate against a small server-side set.
- Least-privilege DB user. The application cannot read
information_schema, cannot write except where business logic requires it, cannot escalate. - Production does not reflect DB errors. A generic 500 in production means the error-based channel is closed.
- Escape as a fallback, not as the primary defence. Use escaping for the cases where parameterisation is genuinely impossible - and treat the escaping as a backstop, not as a fix.
- Block the apostrophe is never the primary defence. Naive filters fail on UTF-8 multibyte sequences and on backslash-escaped quotes.
Check the rules that apply
The checklist is interactive. Check every rule your codebase actually follows. The simulator scores you on the rules that are both checked and genuinely safe - checking "block the apostrophe" costs points, not earns them.
Check the rules that apply to your codebase. The simulator scores you on the rules that are both checked and actually safe.
Bound parameter. The driver and database treat the value as data, not code.
SQL grammar does not allow parameters for identifiers. The only safe approach is a server-side allowlist.
The application user cannot read information_schema, cannot write, cannot drop. A successful SQLi can only read app data.
Escaping is brittle, version-dependent, and has bypass history. Use it as defence-in-depth, never as the primary defence.
MySQL's default allows backslash-escaped quotes; UTF-8 multibyte sequences bypass naive filters. Apostrophe blocking is a 5-minute measure that fails on real attackers.
A generic 500 in production means the error-based and verbose-error channels are closed. Error-based extraction fails.
The pre-commit checklist that catches what tools miss
Static analysis catches 70–80% of SQLi. The remainder is in the code the linter cannot follow: dynamic ORDER BY columns, JSON path filters, identifier names lifted from a config, raw query builders hidden behind an ORM. A human review checklist that asks "what reaches a SQL query, and how is it bound?" catches the rest. The checklist above is a good starting point; a code review should require an answer to those two questions for every changed line that touches a database driver.
The five-line fix
A practical starter pattern for a Node.js / TypeScript codebase:
// db/safe.ts
import { db } from './db';
const SAFE_TABLES = new Set(['users', 'orders', 'products']);
export async function safeFind(table: string, id: number) {
if (!SAFE_TABLES.has(table)) {
throw new Error('Unknown table');
}
// Identifier is allowlisted. The id is bound.
return db.query(`SELECT * FROM ${table} WHERE id = $1`, [id]);
}What to remember
- Parameterise, allowlist, least-privilege, generic errors, escape as a backstop. In that order.
- Apostrophe blocking is never the primary defence - naive filters fail on UTF-8 multibyte and backslash-escaped quotes.
- Static analysis catches 70-80% of SQLi. The rest needs a human review checklist that asks "what reaches the database, and how is it bound?"
Knowledge check
0/3 answered · 0 correct1.Why is allowlist validation the right tool for dynamic ORDER BY columns, when parameterisation cannot work?
2.The application user has GRANT SELECT on every table in the database, including the migration logs. A SQLi is found. What is the blast radius?
3.A junior developer proposes blocking the apostrophe character as the primary defence. What is the strongest counter-argument?