# Defending Against SQLi

> How SQL injection is actually fixed - parameterised queries, input validation, least-privilege database accounts, and the controls that only reduce risk.

<!-- Source: codex/web/sqli/defense -->
<!-- Codex offensive-security reference - codex.athenaos.org -->

import { Aside } from '@astrojs/starlight/components';

Every technique in this section exists because user input reached the query parser as code. This page is the other side: what stops that, what only slows it down, and what a tester should recommend in the report. It's ordered by effectiveness - the first control eliminates the class, the rest are defence in depth.

<Aside type="note">
If you're here to exploit, not fix: the single line that matters is **parameterised queries make almost all of this section impossible**. When you see them in use on a sink, stop testing that sink and move on. The value of knowing the defences is knowing which ones you can still get around (escaping, blocklist WAFs) and which you can't (parameterisation, least privilege limiting the blast radius).
</Aside>

## 1. Parameterised queries (the actual fix)

Also called *prepared statements*. The query structure is sent to the database first, with placeholders; the data is sent separately and can never be reparsed as SQL. This is the only control that removes the vulnerability rather than filtering around it.

```php
// Vulnerable: input concatenated into the query string
$q = "SELECT * FROM logins WHERE username='$u' AND password='$p'";

// Fixed: placeholders bound separately
$stmt = mysqli_prepare($conn, "SELECT * FROM logins WHERE username=? AND password=?");
mysqli_stmt_bind_param($stmt, 'ss', $u, $p);
mysqli_stmt_execute($stmt);
```

The equivalent exists in every stack: `PDO::prepare`, Python DB-API `cursor.execute(sql, params)`, Java `PreparedStatement`, .NET `SqlCommand` with `Parameters.Add`, Go `db.Query(sql, args...)`.

<Aside type="caution">
Parameterisation binds **values**, not identifiers. Table names, column names, and `ORDER BY` directions can't be parameter-bound, so code that interpolates them from user input is still injectable. Those must be validated against an allowlist of known-good names.
</Aside>

## 2. Input validation

Reject input that doesn't match the shape the field requires, server-side, before it reaches any query. Validation is a complement to parameterisation, not a replacement - it narrows the attack surface and catches the identifier case above.

- **Allowlist, not blocklist.** Define what's permitted (`^[A-Za-z0-9 ]+$` for a port code, a known set for an enum) and reject everything else. Blocklisting characters invites [bypasses](../bypasses/).
- Validate type and range: an `id` that should be a positive integer is cast and bounds-checked, so string payloads never survive.
- Do it on the server. Client-side validation is a UX feature and is trivially skipped with a proxy.

## 3. Least-privilege database account

Parameterisation stops the injection; least privilege limits the damage if some other sink is missed. The account the web app connects with should hold only the rights the app actually uses.

```sql
-- Application account that can only read the one table it needs
CREATE USER 'webapp'@'localhost' IDENTIFIED BY '<strong-random>';
GRANT SELECT ON shop.products TO 'webapp'@'localhost';
```

- Never connect as `root` / `sa` / a DBA. Those accounts enable [file read/write and command execution](../file-operations/); a low-privilege account turns a would-be RCE into a limited data read.
- Separate accounts per application and per trust level. Read-only where the app only reads.
- Revoke `FILE` (MySQL), `pg_read_server_files`/`pg_execute_server_program` (PostgreSQL), and disable `xp_cmdshell` (MSSQL) unless genuinely required.

## 4. Controls that only reduce risk

These help, but none closes the hole on its own - treat them as layers, and never as the primary defence in a finding.

- **Escaping** (`mysqli_real_escape_string`, `pg_escape_string`): escapes quotes so they lose their special meaning. Fragile - it depends on correct charset handling and consistent quoting, and misses numeric contexts where no quotes are involved. Prefer parameterisation.
- **Web Application Firewall** (ModSecurity, cloud WAFs): blocks requests matching known SQLi patterns such as `INFORMATION_SCHEMA` or `UNION SELECT`. Buys time and catches mass scanning, but pattern-based rules are routinely evaded - see [Bypasses](../bypasses/). A WAF in front of an injectable app is still an injectable app.
- **Stored procedures**: safe *only* when they themselves use parameterised statements internally; a procedure that concatenates its arguments into dynamic SQL is just as vulnerable.
- **ORMs**: parameterise by default, which is why they help - but raw-query escape hatches (`.raw()`, string-built `WHERE` fragments) reintroduce the bug.

## For the report

When writing SQLi up, lead with the fix that removes it:

1. **Primary:** convert the affected query (and ideally all queries) to parameterised statements / prepared statements.
2. **Supporting:** server-side allowlist validation on the affected parameters; allowlist any dynamic identifiers.
3. **Containment:** reduce the database account's privileges to the minimum the application needs; disable file and command primitives.
4. **Detection:** database error monitoring and query logging - [error-based blind](../blind-error/) extraction is quiet in the HTTP response but noisy in DB error logs, so that log is where it shows up.

Avoid recommending "add a WAF" or "escape input" as the main remediation; both are defence in depth behind parameterisation, and presenting either as the fix leaves the client exposed.

## Related

- [Detection](../detection/) - how the vulnerability is found, i.e. what these controls have to stop
- [File operations](../file-operations/) - the privileges section 3 is designed to deny
- [Bypasses](../bypasses/) - why the layer-4 controls can't be relied on alone