Claude on Question 19, Sep 14

SQL Injection: A Developer Briefing

The Root Cause (lead with this)

Every SQL injection bug has the same cause: data crosses into the code channel. When you build a query by concatenating strings, the database parser cannot distinguish your intended SQL structure from attacker-supplied text. Everything below is a variation on that single defect.

// The bug, in one line
String q = "SELECT * FROM users WHERE email = '" + email + "'";
// email = "x' OR '1'='1" → the WHERE clause is now always true

Taxonomy by Data Retrieval Method

1. In-band SQLi

Results come back over the same channel as the application response.

Error-based — The attacker induces database errors that leak schema, data, or query structure in the response. Depends on verbose error messages reaching the client.

UNION-based — The attacker appends UNION SELECT to pull rows from arbitrary tables into the existing result set. Requires a query whose output is rendered and a matching column count/type.

Developer takeaway: never return raw DB errors to clients; log them server-side with a correlation ID.

2. Inferential (Blind) SQLi

No data comes back directly. The attacker infers it one bit at a time.

Boolean-based — Injected conditions change the response (200 vs. 404, "login failed" vs. "invalid user"). The attacker walks through data character by character.

Time-based — Injected conditional delays (SLEEP, pg_sleep, WAITFOR DELAY) turn response latency into the oracle. Works even when the response body never varies.

Developer takeaway: "we don't display query results, so we're safe" is false. Blind SQLi extracts entire databases; it's just slower.

3. Out-of-band SQLi

The database itself is coerced into making an outbound network request (DNS lookup, HTTP call, UNC path, xp_dirtree, UTL_HTTP) carrying the stolen data. Used when in-band and timing channels are unusable.

Developer takeaway: egress filtering on DB hosts is a real mitigation layer. Database servers rarely need outbound internet access.

4. Second-order (Stored) SQLi

The payload is stored safely on write, then unsafely concatenated on a later read by a different code path — often a batch job, admin report, or migration script.

This is the class most often missed, because the vulnerable sink is far from the untrusted source. Your injection review cannot stop at request handlers; it must cover every place SQL is assembled, including cron jobs and internal tooling.

Taxonomy by Injection Context

This dimension matters more for remediation than the categories above, because it determines whether parameterization is even available.

The identifier cases are where most surviving injection bugs live, because developers correctly parameterize values, hit a case where placeholders are rejected by the driver, and fall back to concatenation.

Correct pattern for identifiers:

SORT_COLUMNS = {"name": "u.name", "created": "u.created_at"}
col = SORT_COLUMNS.get(request.args.get("sort"), "u.created_at")
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
query = f"SELECT ... ORDER BY {col} {direction}"  # safe: values never user-controlled

Map user input to a fixed set of known-good values. Do not escape or sanitize identifiers — enumerate them.

Where It Hides in Modern Stacks

• •ORMs are safe for standard operations but expose raw escape hatches: .extra(), .raw(), session.execute(text(...)), createQuery with concatenation. HQL/JPQL injection is a real class.
• •Stored procedures are not inherently safe. A procedure that builds and EXECs dynamic SQL from its parameters is just as vulnerable. Look for sp_executesql, EXECUTE IMMEDIATE, EXEC(@sql).
• •Query builders often allow raw fragments in whereRaw, havingRaw, orderByRaw.
• •Non-obvious sources: HTTP headers, cookies, User-Agent in analytics tables, webhook payloads, XML/JSON body fields, file uploads parsed into SQL, message queue consumers.

What Actually Fixes It

Primary control — parameterized queries, everywhere, without exception. Prepared statements with bound parameters send structure and data over separate channels. This is not a mitigation that reduces risk; it eliminates the vulnerability class for value contexts.

For identifier contexts — allowlist mapping, as shown above.

Defense in depth:

• •Least privilege: application accounts should not own schemas, hold FILE/xp_cmdshell rights, or read tables they don't need. This caps blast radius when a bug slips through.
• •Generic error responses to clients; detailed logging server-side.
• •Egress filtering on database hosts to break out-of-band channels.
• •Input validation as a secondary layer only — type checks, length limits, format constraints. Never as the primary defense, and never via blocklists of keywords or quote-escaping routines.

Anti-patterns to reject in code review:

• •Hand-rolled escapeString() / stripQuotes() helpers
• •Blocklists of UNION, --, SELECT, etc.
• •"The field is a dropdown, so it's constrained" — clients are attacker-controlled
• •"Internal endpoint, not internet-facing" — assume post-compromise lateral movement
• •WAF rules cited as the reason a finding was closed

Suggested Follow-Ups for Your Team

• 1.Grep for the sinks, not the sources: execute(, raw(, text(, Raw, EXEC, string formatting adjacent to SELECT/INSERT/UPDATE/DELETE. Inventory every result.
• 2.Add a lint/CI rule that fails builds on string-interpolated SQL — semgrep, CodeQL, or your SAST tool of choice all have off-the-shelf rules.
• 3.Include batch jobs and internal admin tooling in scope for second-order bugs.
• 4.Review DB grants for every application service account.
• 5.Have the team work through PortSwigger Web Security Academy's SQLi labs — free, hands-on, and covers every class above in an authorized environment. Pair it with the OWASP Query Parameterization Cheat Sheet as the reference for correct patterns per language.

The one-sentence version for the team: if user input can influence the SQL string itself rather than being passed as a bound parameter or mapped through an allowlist, it's a finding.