Claude on Question 19, Oct 1

SQL Injection: A Developer Briefing

The Root Cause (say this first)

Every SQL injection has the same cause: untrusted data is concatenated into a query string, so the database parses attacker-supplied text as code instead of data. The database has no way to know which parts of the string you intended as structure and which as values.

// VULNERABLE — the quote in the input terminates the literal
String q = "SELECT * FROM users WHERE email = '" + email + "'";

Input ' OR '1'='1' -- turns a lookup into a full table dump. Everything below is a variation on this single mistake.

Classification 1: By How the Attacker Gets Data Back

This is the taxonomy most tools and pentest reports use.

In-band (results come back in the normal response)

Error-based — The app leaks DBMS error text. Attackers coerce the database into putting data inside the error message (e.g., casting a string to an integer, or EXTRACTVALUE/CONVERT tricks). Fast and high-bandwidth.
Implication: never return raw DB errors to clients. Generic 500 page, full detail to logs.

Union-based — The injection point is in a SELECT, so the attacker appends UNION SELECT to graft their own result set onto yours. Requires matching column count and compatible types, which attackers enumerate quickly.

Inferential / "Blind" (no data in the response, but behavior differs)

Boolean-based — Response differs between a true and false condition (AND 1=1 vs AND 1=2 → different page, different length, different status). The attacker extracts data one bit at a time via substring comparisons.

Time-based — No visible difference at all, so the attacker uses conditional delays (SLEEP, pg_sleep, WAITFOR DELAY, or heavy computation). Slow but works even against write-only or fully silent endpoints.

Key point for your team: "The error page doesn't show anything" is not a mitigation. Blind SQLi is fully automatable — sqlmap does it unattended.

Out-of-band

The database itself makes a network request carrying the data — DNS lookups, UNC path resolution, UTL_HTTP, xp_dirtree. Used when the response channel is unusable or asynchronous. Defense: egress filtering on DB hosts, and don't grant the privileges these functions need.

Classification 2: By Injection Context

This matters more for code review, because it tells you where to look.

The last three rows are where well-intentioned teams still get hit, because "we use prepared statements" is true for the values and false for the structure.

Classification 3: By Timing of Execution

First-order — Input is used in a query within the same request. What scanners find easily.

Second-order / stored — Input is safely stored, then later concatenated into a query by a different code path: a cron job, a report generator, an admin panel, a log viewer. The write endpoint looks clean; the vulnerability is elsewhere. Register a username as admin'--, then trigger the profile-update routine that interpolates it.

Implication: data from your own database is not trusted input. Parameterize everywhere, not just at the HTTP boundary.

Other Variants Worth Naming

• •Stacked queries — Driver allows ; to run multiple statements, enabling DROP TABLE or INSERT. Supported by MySQL (with multi-statement flag), SQL Server, PostgreSQL; not by default in many configurations. Don't rely on that.
• •ORM / HQL / JPQL injection — ORMs are not inherently safe. Concatenation inside createQuery(), Django's .extra()/.raw(), SQLAlchemy text() without bindparams, Rails where("name = '#{x}'") are all injectable.
• •Stored procedure injection — A procedure that builds dynamic SQL internally (EXEC, sp_executesql with concatenation, EXECUTE IMMEDIATE) is just as vulnerable. Calling a proc is not a mitigation by itself.
• •Encoding-based bypasses — Historically, multi-byte charsets (GBK, SJIS) let attackers defeat naive escaping like addslashes. Another reason manual escaping is a losing strategy.
• •NoSQL injection — Not SQL, but the same class of bug: operator injection in MongoDB ({"$ne": null}), JS injection in $where. Same lesson applies.

Impact Ceiling (for the risk conversation)

Don't let anyone treat this as "read-only data leak":

• •Authentication/authorization bypass
• •Arbitrary data modification and deletion
• •File read/write (LOAD_FILE, COPY ... FROM PROGRAM, OPENROWSET, BULK INSERT)
• •Code execution: xp_cmdshell, PostgreSQL COPY ... TO PROGRAM, UDF loading
• •Lateral movement using credentials stored in the DB
• •Pivot into the internal network from the DB host

Remediation, in Priority Order

1. Parameterized queries / prepared statements — everywhere, no exceptions.

// Java
PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM users WHERE email = ?");
ps.setString(1, email);

# Python DB-API — note the comma; this is a tuple, not formatting
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

// .NET
cmd.CommandText = "SELECT * FROM users WHERE email = @email";
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 255).Value = email;

// node-postgres
await client.query('SELECT * FROM users WHERE email = $1', [email]);

2. For identifiers, use an allowlist — never escaping. Dynamic sort columns are the common case:

SORTABLE = {"created_at": "created_at", "name": "last_name"}
col = SORTABLE.get(request.args.get("sort"), "created_at")
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
query = f"SELECT ... ORDER BY {col} {direction}"  # safe: values came from code

The untrusted string is used as a dictionary key, never as SQL text.

3. Least privilege on the DB account. Separate read and write roles, no FILE/SUPERUSER/db_owner, no DDL for application accounts, disable multi-statement execution where the driver allows it. This caps blast radius when something slips through.

4. Input validation as defense in depth, not as the fix. Type-check and range-check (id must be an integer). Reject rather than sanitize. Never ship a blocklist of keywords or characters — they are bypassed with comments, case variation, encoding, and alternate syntax, and they break legitimate input like O'Brien.

5. Suppress error detail in production. Generic messages to users, structured detail to logs.

6. WAF / RASP last. Useful for buying time during patching and for alerting. Not a control you design against.

How to Find It in Your Codebase

• •Grep for the smell: string concatenation or interpolation near execute, query, createQuery, raw, exec. Search for f"SELECT, "SELECT " +, #{ inside query strings, $ inside query template literals.
• •SAST in CI, with taint tracking — catches first-order reliably, second-order less so.
• •DAST / sqlmap against a staging environment, including authenticated routes.
• •Code review rule: any PR introducing a dynamic query string needs an explicit justification and an allowlist.
• •Regression tests: add cases with ', ", --, ;, ' OR 1=1 --, and a Unicode quote variant to your test suite for every query-backed endpoint. These should return "not found," not errors and not extra rows.
• •Lint/ban-list: consider failing the build on .extra(), .raw(), text() without bindparams, or whatever the equivalent is in your stack, with an explicit opt-out annotation requiring review.

The One-Sentence Takeaway

SQL is code; user input is data; the only reliable way to keep them separate is to let the driver do it with bound parameters — and for the cases parameters can't cover (identifiers, sort order), map untrusted input through an allowlist instead of trying to clean it.

If it would help, I can put together language-specific secure-coding examples for your actual stack, a code-review checklist, or a short set of test fixtures your team can drop into CI.