SQL injection for defenders
Why concatenated queries turn data into code, the in-band, blind and second-order variants, how SQL injection is found in review and testing, and the fixes that end it: parameterised queries, allow-listed identifiers and least-privilege database accounts.
01Data that becomes code
- Explain the root cause of SQL injection and why escaping is the wrong fix
- Recognise in-band, blind and second-order variants and what each lets an attacker do
- Find injection in code review, SAST and DAST, and spot it in logs
- Fix it with parameterised queries, allow-listed identifiers and least-privilege accounts
Every injection bug has the same shape: a program builds a string in one language (SQL, shell, LDAP, HTML) by pasting in untrusted data, and the interpreter cannot tell where the programmer's code ends and the data begins. With SQL the classic textbook example is a login check built by concatenation.
// VULNERABLE: user input is pasted into the SQL text
const sql = "SELECT id FROM users WHERE email = '" + email + "' AND pw_hash = '" + hash + "'";
// email = alice@example.com' OR '1'='1' --
// becomes:
// SELECT id FROM users WHERE email = 'alice@example.com' OR '1'='1' --' AND pw_hash = '...'
// The quote closes the string, OR '1'='1' is always true, and -- comments out the password check.The attacker never touched the database directly. They supplied a value, and the application turned that value into part of the program. That is why the fix is structural (keep code and data in separate channels) rather than cosmetic (try to neutralise dangerous characters). SQL injection has been understood since the late 1990s and still causes major breaches: the 2023 MOVEit Transfer mass exploitation (CVE-2023-34362) started with a SQL injection in a file-transfer product used by thousands of organisations.
02Variants and what an attacker gets from each
| Variant | How the attacker learns results | Typical impact |
|---|---|---|
| In-band, error-based | Database error messages echoed in the response | Schema discovery, then data extraction |
| In-band, UNION-based | Extra rows appended to the application's own result set | Read any table the account can read, quickly |
| Blind, boolean | Page differs depending on whether an injected condition is true | Same data, one bit per request; slow but automatable |
| Blind, time-based | Injected condition triggers a delay, such as pg_sleep(5) | Works even when responses look identical |
| Out-of-band | Database makes a DNS or HTTP request to the attacker | Exfiltration when nothing comes back in-band |
| Second-order | Payload stored safely, later used unsafely by another query | Survives input validation at the entry point |
Beyond reading data, the impact depends on the database and the account's privileges. Writable injection lets an attacker change prices, create admin users or plant stored XSS. On some systems a highly privileged database account can reach the operating system: SQL Server's xp_cmdshell and PostgreSQL's COPY ... TO PROGRAM for superusers both run commands. That is why the database account an application uses matters as much as the query code.
Teams sometimes downgrade a finding because "no data is returned". Boolean and time-based techniques extract the same data one bit at a time, and automated tools do it in minutes. Treat any injection point as full read access to everything the account can see.
03How SQL injection is found
Defenders find injection the same way attackers do, earlier and with source code. Code review is the most reliable: look for any query text built with concatenation, string formatting or template literals, and trace whether the inserted value can come from a request, a file, a queue message or the database itself.
# Semgrep: flag string-built SQL passed to a driver (simplified)
rules:
- id: sql-string-concat
languages: [python]
severity: ERROR
message: SQL built from strings; use parameters
patterns:
- pattern-either:
- pattern: $CUR.execute("..." + $X, ...)
- pattern: $CUR.execute(f"...", ...)
- pattern: $CUR.execute("..." % $X, ...)
- pattern: $CUR.execute("...".format(...), ...)- SAST with taint tracking (Semgrep, CodeQL) follows data from request handlers to query sinks across functions. It catches second-order paths that grep misses.
- DAST and authorised testing: scanners and tools such as sqlmap send probes and measure differences in content, errors and timing. Run them only against systems you are authorised to test.
- Production signals: a spike in database syntax errors, WAF alerts on quote and comment patterns, and slow-query logs full of
SLEEPorpg_sleepare all signs someone is probing. - ORM escape hatches are where modern injection hides:
raw(),extra(),Sequelize.literal,knex.raw,$queryRawUnsafe. Grep for them specifically.
04The fix: parameters, allow-lists and least privilege
A parameterised query (prepared statement) sends the SQL text and the values separately. The database parses the query structure first, with placeholders, and only then binds the values as data. No value can change the structure, whatever characters it contains. This is the fix; everything else is defence in depth.
# VULNERABLE
cur.execute(f"SELECT * FROM orders WHERE customer_id = {customer_id} AND status = '{status}'")
# FIXED: values travel separately from the SQL text (psycopg placeholder style)
cur.execute(
"SELECT * FROM orders WHERE customer_id = %s AND status = %s",
(customer_id, status),
)Placeholders work for values, not for identifiers or keywords. You cannot bind a column name for ORDER BY or a table name. Those must come from a fixed allow-list in code, so user input selects among known-good options rather than supplying SQL.
const SORTABLE = { created: "created_at", total: "total_cents", status: "status" } as const;
export async function listOrders(db: Pool, customerId: string, sort: string, dir: string) {
const column = SORTABLE[sort as keyof typeof SORTABLE] ?? "created_at"; // identifier from an allow-list
const direction = dir === "asc" ? "ASC" : "DESC"; // keyword from a fixed pair
return db.query(
"SELECT id, total_cents, status FROM orders WHERE customer_id = $1 ORDER BY " + column + " " + direction + " LIMIT 50",
[customerId], // value as a parameter
);
}- Least-privilege database accounts: the web application's account should read and write its own tables only. No superuser, no
FILEprivilege, no access to other schemas, no DDL. - Generic errors to the client and detailed errors to logs. Error messages are an attacker's fastest route to the schema.
- Stored procedures are not automatically safe: a procedure that builds dynamic SQL from its arguments with
EXECis injectable in exactly the same way. - A WAF is a stopgap, useful while a fix ships. Encodings, comments and database-specific syntax bypass signature rules routinely.
05Explaining it well
Interviewers often ask "why not just escape the quotes?". The strong answer: escaping is context- and encoding-dependent and easy to forget once; parameterisation removes the class by keeping code and data in separate channels. Expect a follow-up on ORDER BY columns, where the answer is an allow-list, and on second-order injection, where the answer is that every query must be parameterised, not just the ones touching request data.
"SQL injection happens when untrusted data is pasted into query text, so the database executes it as code. I fix it by making every query parameterised, including ones fed from our own database, because second-order injection comes from stored data. Identifiers like sort columns come from an allow-list. The app's database account gets only the privileges it needs, errors are generic to the client, and we run a taint-tracking SAST rule in CI so string-built SQL fails review."
A developer validates email format at signup and stores it with a parameterised INSERT. Later, a nightly report builds `"... WHERE email = '" + row.email + "'"`. Is this exploitable?
Q1Can an ORM still be vulnerable to SQL injection?+
Yes, through raw query functions, string-built filters, and helpers that accept SQL fragments. The ORM's normal query builder is safe; its escape hatches are not.
Q2Why is input validation not enough?+
Validation depends on the field, and many legitimate values contain quotes, such as names like O'Brien. It is useful defence in depth, but only parameterisation separates code from data.
Q3What is the fastest way to reduce blast radius before fixing every query?+
Lower the application account's database privileges, remove access to tables it does not need, and turn off verbose errors. A WAF rule can buy time on a known endpoint.