GPT on Question 19, Sep 14

SQL injection occurs when untrusted input is interpreted as part of a SQL statement rather than as data. Developers should recognize these major classes:

1. In-band SQL injection

The attacker obtains results through the same application channel used for the request.

• •Error-based: Database errors reveal schema details, query structure, or data.
• •UNION-based: A manipulated query combines attacker-selected results with the application’s normal result set.

2. Inferential (“blind”) SQL injection

The application does not directly display database results, but its behavior reveals whether an injected condition was true.

• •Boolean-based: Differences in page content, status codes, or result counts reveal information.
• •Time-based: Deliberate database delays make response timing encode information.

“Blind” injection can still extract substantial data; it is merely slower.

3. Out-of-band SQL injection

The database causes information to leave through another channel, such as a DNS or network request. This depends on database features, privileges, and network access, but can bypass applications that expose little feedback.

4. Stacked-query or piggybacked injection

If the database driver permits multiple statements, attacker-controlled input may append an additional statement. The impact can include modification or deletion of data, not just disclosure.

5. Second-order SQL injection

Malicious input is safely stored initially, then later retrieved and concatenated into another SQL query. Validation at the original entry point does not prevent this; every query construction point must be safe.

6. Injection in non-value query elements

Parameters generally protect values, but many applications dynamically construct other SQL elements, such as:

• •Table or column names
• •Sort direction
• •Operators
• •ORDER BY clauses
• •Partial filters or raw query fragments

These elements usually cannot be bound as ordinary parameters and must instead be selected from strict allowlists.

Primary defenses

• 1.Use prepared statements and bound parameters everywhere. Never build SQL by concatenating, interpolating, or formatting user-controlled values.
• 2.Allowlist dynamic identifiers and query options. Map client choices to predefined SQL fragments.
• 3.Avoid raw SQL escape hatches in ORMs. ORMs are safe only when their parameterized APIs are used correctly.
• 4.Treat stored procedures carefully. They remain vulnerable if they construct dynamic SQL internally.
• 5.Apply least privilege. Application accounts should not have schema-administration or unnecessary file/network permissions.
• 6.Suppress detailed database errors in production while retaining secure server-side logs.
• 7.Test all input paths, including headers, cookies, JSON fields, background jobs, imported files, and previously stored data.
• 8.Use code review and automated security testing. A WAF can provide defense in depth but is not a substitute for parameterization.

A useful rule for developers is: SQL structure must come from trusted code; untrusted input must enter only through bound parameters or explicit allowlist mappings.