Claude on Question 19, Sep 11

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.

# 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.

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:

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.

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.