# SQL Injection: A Developer Briefing
## The Root Cause
Every SQL injection has the same underlying flaw: **untrusted data is concatenated into a query string, so the database parses attacker-controlled text as SQL syntax rather than as a value.** All the "classes" below are variations on how that happens or how an attacker extracts results — the fix converges on the same principle.
---
## Classification by Data Retrieval Method
This is the most common taxonomy, and it matters because it determines how detectable an attack is in your logs.
### 1. In-Band (results come back through the same channel)
**Union-based** — the attacker appends `UNION SELECT` to graft their own result set onto yours.
```sql
-- Query: SELECT name, price FROM products WHERE id = '$id'
-- Input: 1' UNION SELECT username, password FROM users--
```
**Error-based** — the attacker forces database errors that leak data in the message. Classic examples include `CONVERT()` type-conversion errors in SQL Server, `EXTRACTVALUE()`/`XMLType` in MySQL/Oracle, or `CAST()` failures in PostgreSQL.
> Practical note: verbose database errors reaching the client dramatically accelerate exploitation. Generic error pages are a cheap, high-value mitigation — though never a fix.
### 2. Inferential / "Blind" (no data returned directly)
The attacker infers data one bit at a time by observing application behavior.
**Boolean-based** — inject a condition and watch whether the page changes:
```sql
' AND SUBSTRING((SELECT password FROM users LIMIT 1),1,1)='a'--
```
**Time-based** — inject a conditional delay and measure response time:
```sql
' AND IF(SUBSTRING(...)='a', SLEEP(5), 0)--
```
Uses `SLEEP()` (MySQL), `WAITFOR DELAY` (SQL Server), `pg_sleep()` (PostgreSQL), `DBMS_LOCK.SLEEP` (Oracle).
Blind injection is slow but fully sufficient to dump a database with automation. **Never dismiss a blind finding as low severity.**
### 3. Out-of-Band
Data is exfiltrated over a separate channel — DNS lookups, HTTP requests — using functions like `LOAD_FILE`/`UTL_HTTP`/`xp_dirtree`. This is the attacker's fallback when responses are unreliable or asynchronous, and it bypasses most response-based monitoring. Egress filtering on database hosts helps here.
---
## Classification by Injection Context
This taxonomy is more useful for developers, because it explains *why parameterization sometimes isn't available* and where bugs actually cluster.
| Context | Example | Parameterizable? |
|---|---|---|
| String literal | `WHERE name = ?` | Yes |
| Numeric literal | `WHERE id = ?` | Yes |
| `LIKE` pattern | `WHERE name LIKE ?` | Yes (but escape `%` and `_` in the value) |
| `IN` clause | `WHERE id IN (?,?,?)` | Yes — generate one placeholder per element |
| Column/table identifier | `ORDER BY $col` | **No** — must use an allowlist |
| Sort direction | `ORDER BY x $dir` | **No** — map to `ASC`/`DESC` explicitly |
| `LIMIT`/`OFFSET` | varies by driver | Often yes; otherwise cast to integer |
| Dynamic SQL inside a stored procedure | `EXEC(@sql)` | Depends — see below |
**The critical takeaway:** identifiers cannot be bound as parameters. Any sorting, filtering, or pivoting feature that lets users choose a column name is a high-risk area requiring strict allowlist validation against a hardcoded set — not escaping, not regex denylists.
---
## Second-Order (Stored) SQL Injection
Payload is stored safely (via a parameterized insert), then later read from the database and concatenated into a *different* query — often in a report generator, batch job, or admin panel.
This class defeats input-boundary thinking entirely. It's why the rule is **parameterize at every query**, not "sanitize at the edge." Data from your own database is not inherently trusted data.
## Injection Inside Stored Procedures
A common misconception is that stored procedures are inherently safe. They are only safe if they don't build dynamic SQL internally:
```sql
-- Vulnerable stored procedure
CREATE PROCEDURE Search @term NVARCHAR(100) AS
EXEC('SELECT * FROM items WHERE name LIKE ''%' + @term + '%''')
```
If dynamic SQL is unavoidable, use `sp_executesql` with parameters (SQL Server) or `EXECUTE ... USING` with `format(%L)`/`quote_ident` (PostgreSQL).
## ORM and Query-Builder Injection
ORMs are safe by default but nearly all provide escape hatches that reintroduce risk:
- **Hibernate/JPA** — HQL string concatenation, `createNativeQuery`
- **Django** — `.extra()`, `.raw()`, `RawSQL`
- **Sequelize/Knex** — `sequelize.query()`, `.whereRaw()`
- **ActiveRecord** — string conditions, `.order()`, `.pluck()` with interpolation
Treat these methods as flagged constructs in code review and SAST rules.
---
## Prevention: Ordered by Effectiveness
1. **Parameterized queries / prepared statements everywhere.** Not string escaping. Not stored procedures alone. Parameterization separates code from data at the protocol level, which is why it's categorically stronger than filtering.
2. **Allowlist validation for identifiers.** Map user input to known-good column names through a dictionary or enum. Reject anything unmatched.
3. **Least privilege on database accounts.** The app account should not own its schema, should have no `FILE`/`xp_cmdshell`/`COPY FROM PROGRAM` rights, and read-heavy services should use read-only credentials. This converts many full compromises into limited data disclosure.
4. **Generic error responses** with detailed logging server-side only.
5. **Input validation as defense in depth** — type, length, format. Useful, but never the primary control; it fails on legitimate inputs containing quotes (`O'Brien`) and on contexts you didn't anticipate.
6. **Automated detection in CI** — SAST for concatenation patterns and flagged ORM methods, DAST for runtime confirmation.
7. **WAF as a final compensating layer**, not a fix. Signature evasion is well-documented.
## Misconceptions Worth Correcting Explicitly
- "It's an internal/admin-only endpoint" — insider threat and post-authentication lateral movement are primary attack paths.
- "We escape single quotes" — fails in numeric contexts, fails against multi-byte charset attacks, fails for identifiers.
- "We use an ORM" — true until someone uses `.raw()` under deadline pressure.
- "Blind SQLi is low risk" — automation makes it equivalent to full disclosure.
- "The input is validated on the frontend" — client-side validation is a UX feature, not a security control.
---
A good closing exercise for the briefing: have the team grep the codebase for string concatenation adjacent to SQL keywords and for the ORM escape-hatch methods listed above. The results are usually more numerous than expected, and it makes the abstract concrete.