Claude on Question 19, Sep 19

SQL Injection: A Developer Briefing

What it is, briefly

SQL injection occurs when untrusted input is concatenated into a SQL statement so that the input changes the structure of the query rather than just supplying a value. The database can't tell the difference between the code your developer wrote and the code an attacker supplied — it's all just one string by the time it reaches the parser.

Classification by how the attacker gets results back

This is the classic taxonomy (OWASP / common pentest terminology).

1. In-band SQLi — results return through the same channel

a) Error-based
The application leaks database error messages to the response. Attackers deliberately trigger type-conversion or XML-parsing errors that embed query results in the error text. Extremely efficient for data extraction.

Mitigation signal: verbose DB errors reaching users is both an enabler and an information leak in its own right.

b) UNION-based
The attacker appends a UNION SELECT to pull rows from other tables into the original result set. Requires matching column count and compatible types, which attackers enumerate quickly. Works wherever query results are rendered to the page.

2. Inferential (blind) SQLi — no data returned directly

The attacker infers data one bit at a time by observing application behaviour.

a) Boolean-based / content-based
Inject a condition and compare responses. "Page renders normally" vs. "page shows generic error or empty result" is a one-bit oracle. Combined with substring and character-comparison functions, this reads out arbitrary data.

b) Time-based
When responses are identical regardless of truth value, the attacker injects a conditional delay (SLEEP, WAITFOR DELAY, pg_sleep, or a heavy computation) and measures response time. Slow but reliable.

Key point for developers: blind SQLi is fully exploitable. "We don't display query results or errors" is not a mitigation — it only changes the attacker's extraction rate.

3. Out-of-band SQLi

The attacker makes the database server initiate an outbound connection carrying the data — DNS lookups, HTTP requests, UNC path resolution, xp_dirtree, UTL_HTTP, LOAD_FILE to a share. Used when in-band channels are blocked or responses are asynchronous.

Mitigation signal: database servers should have no outbound network egress.

4. Second-order (stored) SQLi

Input is safely stored on the way in, then unsafely concatenated into a query later — by a different code path, a batch job, an admin panel, or a reporting process. This is the one that defeats "we sanitize at the perimeter" thinking, because the payload arrives at the vulnerable sink from the database, which developers wrongly treat as trusted.

Common real-world shape: a username is parameterised at registration, then interpolated into a dynamic query by a nightly job.

Classification by injection context

This matters more day-to-day, because it determines whether parameterisation can save you.

The identifier cases are where most modern SQLi lives, because ORMs and prepared statements have largely handled the literal cases. Any feature offering "sort by any column" or "filter on dynamic field" is a red flag in code review.

Related classes worth naming

• •Stacked queries — where the driver permits ; and multiple statements, turning a read primitive into a write/DDL primitive. Disable multi-statement execution where your driver allows it.
• •ORM injection / HQL-JPQL injection — ORMs expose raw-fragment APIs (.whereRaw(), createQuery(string), @Query with concatenation, Django .extra()/.raw()). Using an ORM is not itself a control.
• •Stored procedure injection — procedures are not inherently safe; dynamic SQL built inside a procedure (EXEC, EXECUTE IMMEDIATE, sp_executesql with concatenation) is just as vulnerable.
• •NoSQL / ORM operator injection — conceptually adjacent; untrusted objects reaching a query builder can inject operators like $ne, $gt, $where. Worth covering in the same session since the root cause is identical.

The defence hierarchy (what to actually tell the team)

• 1.Parameterised queries / prepared statements, everywhere, as the default. Not "as a best practice" — as the only accepted way to pass values.
• 2.Allowlist validation for anything that can't be parameterised. Map user input to a fixed set of known-good identifiers; never pass the input through, even escaped. {"name": "users.name", "date": "users.created_at"} style lookup tables.
• 3.Escaping as a last resort only. Context-specific, error-prone, and charset-dependent. Treat any escaping-based fix in a PR as a design smell.
• 4.Least privilege on DB accounts. Separate read and write roles, no DDL, no FILE/xp_cmdshell-class privileges, no superuser. This caps the blast radius of the bug you missed.
• 5.No outbound network access from DB servers. Kills out-of-band extraction.
• 6.Generic error pages; log details server-side. Removes error-based channels and reduces recon.
• 7.Treat data read from your own database as untrusted. This is the second-order defence and the one most often missing.

Practical enforcement

• •Add a lint/SAST rule that flags string concatenation and f-strings/interpolation reaching query-execution sinks. Most static analysers do this well and the false-positive rate is manageable.
• •Grep for the raw-query escape hatches in your ORM and require a reviewer sign-off on each occurrence.
• •Include one blind and one second-order case in your security test suite, since those are the ones normal functional testing never surfaces.
• •Consider a WAF only as detection and buying-time telemetry, never as the fix.

If it's useful, I can put together the vulnerable-vs-fixed code pairs in whatever stack your team uses (Java/JDBC, .NET, Node, Python, Go), or a short code-review checklist they can keep next to the PR template.