Claude on Question 19, Sep 10

SQL Injection: A Developer Briefing

The Root Cause

Every SQL injection has the same underlying flaw: untrusted data is concatenated into a query string, so the database parses attacker-controlled text as SQL syntax rather than as a value. All the "classes" below are variations on how that happens or how an attacker extracts results — the fix converges on the same principle.

Classification by Data Retrieval Method

This is the most common taxonomy, and it matters because it determines how detectable an attack is in your logs.

1. In-Band (results come back through the same channel)

Union-based — the attacker appends UNION SELECT to graft their own result set onto yours.

-- Query: SELECT name, price FROM products WHERE id = '$id'
-- Input: 1' UNION SELECT username, password FROM users--

Error-based — the attacker forces database errors that leak data in the message. Classic examples include CONVERT() type-conversion errors in SQL Server, EXTRACTVALUE()/XMLType in MySQL/Oracle, or CAST() failures in PostgreSQL.

Practical note: verbose database errors reaching the client dramatically accelerate exploitation. Generic error pages are a cheap, high-value mitigation — though never a fix.

2. Inferential / "Blind" (no data returned directly)

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

Boolean-based — inject a condition and watch whether the page changes:

' AND SUBSTRING((SELECT password FROM users LIMIT 1),1,1)='a'--

Time-based — inject a conditional delay and measure response time:

' AND IF(SUBSTRING(...)='a', SLEEP(5), 0)--

Uses SLEEP() (MySQL), WAITFOR DELAY (SQL Server), pg_sleep() (PostgreSQL), DBMS_LOCK.SLEEP (Oracle).

Blind injection is slow but fully sufficient to dump a database with automation. Never dismiss a blind finding as low severity.

3. Out-of-Band

Data is exfiltrated over a separate channel — DNS lookups, HTTP requests — using functions like LOAD_FILE/UTL_HTTP/xp_dirtree. This is the attacker's fallback when responses are unreliable or asynchronous, and it bypasses most response-based monitoring. Egress filtering on database hosts helps here.

Classification by Injection Context

This taxonomy is more useful for developers, because it explains why parameterization sometimes isn't available and where bugs actually cluster.

The critical takeaway: identifiers cannot be bound as parameters. Any sorting, filtering, or pivoting feature that lets users choose a column name is a high-risk area requiring strict allowlist validation against a hardcoded set — not escaping, not regex denylists.

Second-Order (Stored) SQL Injection

Payload is stored safely (via a parameterized insert), then later read from the database and concatenated into a different query — often in a report generator, batch job, or admin panel.

This class defeats input-boundary thinking entirely. It's why the rule is parameterize at every query, not "sanitize at the edge." Data from your own database is not inherently trusted data.

Injection Inside Stored Procedures

A common misconception is that stored procedures are inherently safe. They are only safe if they don't build dynamic SQL internally:

-- Vulnerable stored procedure
CREATE PROCEDURE Search @term NVARCHAR(100) AS
  EXEC('SELECT * FROM items WHERE name LIKE ''%' + @term + '%''')

If dynamic SQL is unavoidable, use sp_executesql with parameters (SQL Server) or EXECUTE ... USING with format(%L)/quote_ident (PostgreSQL).

ORM and Query-Builder Injection

ORMs are safe by default but nearly all provide escape hatches that reintroduce risk:

• •Hibernate/JPA — HQL string concatenation, createNativeQuery
• •Django — .extra(), .raw(), RawSQL
• •Sequelize/Knex — sequelize.query(), .whereRaw()
• •ActiveRecord — string conditions, .order(), .pluck() with interpolation

Treat these methods as flagged constructs in code review and SAST rules.

Prevention: Ordered by Effectiveness

• 1.Parameterized queries / prepared statements everywhere. Not string escaping. Not stored procedures alone. Parameterization separates code from data at the protocol level, which is why it's categorically stronger than filtering.

• 2.Allowlist validation for identifiers. Map user input to known-good column names through a dictionary or enum. Reject anything unmatched.

• 3.Least privilege on database accounts. The app account should not own its schema, should have no FILE/xp_cmdshell/COPY FROM PROGRAM rights, and read-heavy services should use read-only credentials. This converts many full compromises into limited data disclosure.

• 4.Generic error responses with detailed logging server-side only.

• 5.Input validation as defense in depth — type, length, format. Useful, but never the primary control; it fails on legitimate inputs containing quotes (O'Brien) and on contexts you didn't anticipate.

• 6.Automated detection in CI — SAST for concatenation patterns and flagged ORM methods, DAST for runtime confirmation.

• 7.WAF as a final compensating layer, not a fix. Signature evasion is well-documented.

Misconceptions Worth Correcting Explicitly

• •"It's an internal/admin-only endpoint" — insider threat and post-authentication lateral movement are primary attack paths.
• •"We escape single quotes" — fails in numeric contexts, fails against multi-byte charset attacks, fails for identifiers.
• •"We use an ORM" — true until someone uses .raw() under deadline pressure.
• •"Blind SQLi is low risk" — automation makes it equivalent to full disclosure.
• •"The input is validated on the frontend" — client-side validation is a UX feature, not a security control.

A good closing exercise for the briefing: have the team grep the codebase for string concatenation adjacent to SQL keywords and for the ORM escape-hatch methods listed above. The results are usually more numerous than expected, and it makes the abstract concrete.