# SQL Injection: Developer Briefing
## The Root Cause
Every SQL injection vulnerability comes from the same mistake: **untrusted data is concatenated into a query string, so the database parser cannot distinguish your intended code from attacker-supplied data.**
```python
# Vulnerable
query = "SELECT * FROM users WHERE email = '" + email + "'"
```
If `email` is `' OR 1=1 --`, the parser sees new SQL syntax, not a string value. Everything below is a variation on this single theme.
---
## Classification by Exfiltration Channel
This axis describes *how an attacker gets data back out*. It matters mostly for detection and testing.
### In-band
**Error-based** — The application returns database error messages. Attackers coerce data into the error text (e.g., type-conversion errors in SQL Server, `extractvalue()`/`updatexml()` tricks in MySQL). Cheap and fast for the attacker; the fix is generic error pages plus the real remediation below.
**Union-based** — Attacker appends `UNION SELECT` to graft their own result set onto yours. Requires matching column count and compatible types, which attackers determine by iterating. Yields bulk data quickly.
### Inferential (Blind)
No data comes back directly, but the application behaves differently depending on a condition the attacker controls.
**Boolean-based** — Compare responses for `AND 1=1` versus `AND 1=2`. A difference in page content, status code, or length leaks one bit per request. Attackers automate this to extract data character by character.
**Time-based** — Uses `SLEEP()`, `pg_sleep()`, `WAITFOR DELAY`, or `dbms_pipe.receive_message()`. Response latency carries the bit. Works even when the response is completely identical, which is why "we return a generic 500" is not a mitigation.
### Out-of-band
The database itself makes a network request carrying the data: DNS lookups or HTTP calls via `xp_dirtree`, `UTL_HTTP`, `LOAD_FILE('\\\\attacker\\share')`, etc. Bypasses blind-injection slowness entirely and evades response-based monitoring. Egress filtering on database hosts is a real control here.
### Stacked / Batched queries
Where the driver permits multiple statements per call (`; DROP TABLE ...`), impact escalates from read to write and DDL. Support varies: common in SQL Server and PHP/MySQL with `mysqli_multi_query`, generally not permitted in default PostgreSQL and MySQL client configs. Don't rely on this as protection.
---
## Classification by Injection Context
**This is the axis your developers actually need**, because it determines which fix applies.
| Context | Example | Parameterizable? |
|---|---|---|
| String literal | `WHERE name = '$x'` | Yes |
| Numeric literal | `WHERE id = $x` | Yes |
| `LIKE` pattern | `WHERE name LIKE '%$x%'` | Yes, but wildcards need escaping |
| `IN` list | `WHERE id IN ($x)` | Yes — generate N placeholders |
| Identifier (table/column) | `ORDER BY $x` | **No** — allowlist required |
| `ORDER BY` direction | `ORDER BY name $x` | **No** — allowlist |
| `LIMIT` / `OFFSET` | `LIMIT $x` | Driver-dependent |
| Whole clause fragments | dynamic `WHERE` builders | **No** — structural allowlist |
The identifier cases are where most experienced teams still get caught. Placeholders bind *values*, not schema objects. If a sort column comes from the user, you must map it through a fixed dictionary:
```python
SORT_COLUMNS = {"name": "u.name", "created": "u.created_at"}
col = SORT_COLUMNS.get(request.args.get("sort"), "u.name")
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
```
---
## Cases That Bypass "We Use an ORM"
**Raw query escape hatches** — `session.execute()`, `.raw()`, `.extra()`, `queryRaw`, `@Query` with string concatenation. These are ordinary SQLi. Grep for them in review.
**ORM/HQL/JPQL injection** — The abstraction language has its own parser with the same confusion problem. `session.createQuery("FROM User WHERE name = '" + n + "'")` is injectable.
**Stored procedures** — Not inherently safe. A procedure that builds dynamic SQL internally with `EXEC('...' + @param)` is vulnerable regardless of how safely you called it. Audit the procedure bodies, and prefer `sp_executesql` with parameters.
**Second-order (stored) injection** — Input is stored safely via a parameterized insert, then later read from the database and concatenated into a query by a different code path — a reporting job, an admin view, a migration script. The trust boundary is the query construction site, not the HTTP handler. Data from your own database is untrusted if a user put it there.
**Escaping functions** — Character-set mismatches have historically defeated them (the classic `mysql_real_escape_string` failure when the connection charset is GBK). Escaping is also easy to apply inconsistently. Treat it as a last resort for contexts where parameterization is impossible, not a general strategy.
**NoSQL equivalents** — Worth mentioning if your stack includes MongoDB: passing a parsed JSON object where a scalar is expected lets an attacker inject `{"$ne": null}` or `$where` JavaScript. Same root cause, different syntax. Validate types.
---
## Remediation Priority
**1. Parameterized queries, everywhere, by default.** Not string formatting, not escaping. The query template is fixed at development time; values bind separately.
```java
PreparedStatement ps = conn.prepareStatement(
"SELECT id, email FROM users WHERE email = ? AND tenant_id = ?");
ps.setString(1, email);
ps.setLong(2, tenantId);
```
Note that some languages make the unsafe path look like the safe one. In Python, `cursor.execute(sql % val)` and `cursor.execute(f"...{val}")` are vulnerable; `cursor.execute(sql, (val,))` is not. Make sure the team knows the difference in your specific driver.
**2. Allowlist all structural input.** Anything that becomes an identifier, keyword, or clause goes through a fixed map. Cast pagination values to integers with bounds.
**3. Least privilege on database accounts.** The application account should not own its schema, should not have `FILE`/`xp_cmdshell`/`COPY FROM PROGRAM`, and read-heavy services should use read-only credentials. This converts a full compromise into a data-read incident.
**4. Suppress error detail in production.** Generic error pages, logs to a sink users can't read. This removes error-based injection and slows reconnaissance — but it does not stop blind or out-of-band attacks, so never treat it as the fix.
**5. Input validation as defense in depth.** Type, length, and format checks reduce surface area and catch bugs. They are not a substitute for parameterization; blocklisting keywords like `UNION` or `SELECT` is actively counterproductive because it breaks legitimate input and fails to comprehensive encoding and comment-based evasion.
**6. Egress restrictions on database hosts.** Blocks out-of-band channels.
**7. WAF last.** Useful for buying time during patching and for alerting. Bypasses are well documented and cheap. It is not a remediation item you close a ticket with.
---
## Practical Review and Testing Guidance
**Static analysis** — Enable taint-tracking rules (CodeQL, Semgrep, SonarQube) in CI and fail the build on new findings. This catches the majority of straightforward cases.
**Code review patterns to flag** — String concatenation or interpolation adjacent to SQL keywords; any raw-query method; dynamic SQL inside stored procedures; query builders assembling `WHERE` fragments from request data.
**Dynamic testing** — Run `sqlmap` against non-production instances as part of your security testing cycle. Include blind and time-based modes, since those are the cases manual testing most often misses.
**Coverage gaps to target explicitly** — Non-HTTP inputs (message queues, file imports, webhooks, SAML/JWT claims), internal admin tooling, reporting and analytics paths, and anything that reads from the database and re-queries. These are systematically under-tested relative to the main request path.
A reasonable standard to hold the team to: **if a query string is built at runtime from anything other than compile-time constants and allowlisted tokens, it needs a justification in review.**