Claude on Question 19, Sep 20

SQL Injection: A Developer Briefing

Root cause (the one thing to internalize)

SQL injection happens when untrusted data is concatenated into a query string, so the database parser can no longer distinguish "data" from "code." Every class below is a variation on that single flaw. It is not an input-validation problem at heart — it's a code/data separation problem.

// Vulnerable: input becomes part of the query text
String q = "SELECT * FROM users WHERE email = '" + email + "'";

// Safe: input is bound as a value, never parsed as SQL
PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM users WHERE email = ?");
ps.setString(1, email);

Classification 1: By how the attacker gets results back

This axis matters mostly for detection and testing, since it determines what a pentester or scanner will observe.

In-band SQLi (results come back in the app's normal response)

Union-based — attacker appends UNION SELECT to graft a second result set onto the legitimate one, pulling arbitrary tables into the page. Requires the attacker to match column count and compatible types.

Error-based — the attacker triggers a database error that leaks data inside the error message (e.g. type-conversion errors in SQL Server, XPATH syntax error tricks in MySQL, utl_inaddr style errors in Oracle). This is why verbose DB errors must never reach production responses.

Inferential / "blind" SQLi (no data in the response, only behavior)

Boolean-based — the response differs between a true and false condition (AND 1=1 vs AND 1=2), letting the attacker extract data one bit at a time. Even a generic "invalid login" vs "server error" difference is enough.

Time-based — the attacker conditionally delays the response (SLEEP(), WAITFOR DELAY, pg_sleep()). Works even when the response is byte-identical. Slow, but fully automatable.

Key point for developers: generic error pages do not fix SQL injection. Blind techniques extract entire databases through a single boolean signal. "We hide errors" is not a mitigation.

Out-of-band SQLi

Data is exfiltrated over a different channel the DB server initiates — DNS lookups, HTTP requests, UNC path resolution, utl_http, etc. Used when the app is asynchronous or the response is fully suppressed. Mitigated at the network layer by denying egress from database servers.

Classification 2: By where the injection lives

This axis matters most for code review, because it tells you which patterns to hunt for.

First-order (direct)

Input flows from the request straight into a query in the same transaction. Easiest to spot.

Second-order (stored)

Input is safely stored, then later concatenated into a query by a different code path — a batch job, admin report, or cache-warming task. The injection point and the sink are in different files, often owned by different teams. These survive code review because each half looks fine in isolation.

Classic shape: registration stores a username containing a quote; a nightly report builds "... WHERE username = '" + row.username + "'". Treat data from your own database as untrusted at the query-construction boundary.

Injection contexts that parameterization does not cover

Placeholders bind values, not SQL structure. These contexts require different handling and are where most real-world bugs now live:

# Dynamic IN list, done safely
placeholders = ",".join(["%s"] * len(ids))
cur.execute(f"SELECT * FROM orders WHERE id IN ({placeholders})", ids)

# ORDER BY, done safely
SORTABLE = {"name": "u.name", "created": "u.created_at"}
col = SORTABLE.get(request.args.get("sort"), "u.created_at")
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
cur.execute(f"SELECT ... ORDER BY {col} {direction}")

Non-obvious entry points

Anything attacker-controlled is a source, not just form fields: URL/path parameters, JSON and XML bodies, HTTP headers (User-Agent, Referer, X-Forwarded-For in logging code), cookies, file uploads parsed into rows, webhook payloads, and SAML/JWT claims.

Classification 3: By impact escalation

Worth mentioning to developers so "it's just a read-only page" doesn't become an excuse.

• •Stacked / piggybacked queries — ; DROP TABLE ... where the driver permits multiple statements. Many drivers allow this by default (MySQL with allowMultiQueries, some .NET paths, most raw psql-style execution).
• •Authentication and authorization bypass — tampering with WHERE logic to log in as any user or read other tenants' rows. This is the most common real-world outcome, ahead of mass data theft.
• •File read/write — LOAD_FILE, INTO OUTFILE, pg_read_file, BULK INSERT.
• •Command execution — xp_cmdshell (SQL Server), COPY ... PROGRAM (PostgreSQL superuser), UDF loading (MySQL), Java stored procedures (Oracle). SQLi frequently becomes full host compromise.
• •Lateral movement — linked servers, dblink, or credentials stored in DB tables.

Adjacent classes to keep on the radar

• •ORM / query-language injection — HQL/JPQL, Django extra()/raw(), .filter(raw=...), ActiveRecord string conditions, Sequelize literal(). ORMs are safe by default and unsafe on the escape hatches. Grep for those specific APIs.
• •Injection via stored procedures — a procedure that internally builds and EXECs a dynamic string is just as vulnerable, even though the app called it with parameters. Stored procedures are not a mitigation by themselves.
• •NoSQL injection — operator injection in MongoDB ({"$ne": null} in place of a string), server-side JS evaluation. Same root cause, different syntax.
• •ORM/DB driver quirks — charset-based escaping bypasses (the classic GBK/mysql_real_escape_string issue) are why manual escaping is a losing strategy.

Defenses, ranked

• 1.Parameterized queries / prepared statements everywhere. This is the control. Make it non-negotiable in code review.
• 2.Allowlists for anything structural (identifiers, sort order, operators). Map user tokens to server-side constants.
• 3.Least privilege DB accounts. App user gets DML on its own schema — no DDL, no FILE, no superuser, no xp_cmdshell, separate read-only account for reporting. This caps blast radius when defense #1 fails.
• 4.Disable multi-statement execution in driver connection settings.
• 5.No DB errors in responses. Log server-side with a correlation ID.
• 6.Deny outbound network egress from DB hosts to kill out-of-band exfiltration.
• 7.Input validation as defense in depth — type, length, format. Useful, but never the primary control; plenty of valid inputs are also valid payloads.
• 8.WAF as a stopgap and alerting mechanism, not a fix.

Make it enforceable, not aspirational

• •Static analysis (CodeQL, Semgrep) with rules that flag string-built SQL and ORM raw-query APIs; fail the build.
• •A single query-access layer or approved helper library so raw concatenation is easy to spot in diffs.
• •Lint rule banning f-strings / template literals / + adjacent to SQL keywords.
• •DAST or sqlmap runs against staging in CI for regression coverage.
• •Code review checklist item: "Is any part of this query built from a variable? If yes, is it a bound value or an allowlisted identifier?"

One-slide summary for the team

Every SQLi class comes from concatenating data into query text. Parameterize values, allowlist identifiers, and assume data from your own database is untrusted. Hiding errors doesn't help — blind and time-based techniques extract everything anyway. Least-privilege DB accounts are what limit the damage when someone slips up.

If it would help, I can turn this into a language-specific version (Java/Spring, .NET/EF, Python/Django, Node/TypeScript) with the exact safe and unsafe API patterns for your stack, or draft the Semgrep rules and PR checklist.