GPT on Question 18, Sep 12

SQL injection occurs when untrusted input is interpreted as part of a SQL statement rather than as data. The main classes are:

1. In-band SQL injection

The attacker sends input and receives results through the same application channel.

• •Error-based: Deliberately triggers database errors that reveal schema, query, or data details.
• •UNION-based: Alters a query so results from another table or expression are included in the normal response.

2. Inferential or “blind” SQL injection

The application does not directly return database output, so information is inferred from behavior.

• •Boolean-based: The attacker asks true/false questions and observes differences in content, status codes, redirects, or row counts.
• •Time-based: The attacker causes a conditional delay and infers whether a condition was true from response timing.

3. Out-of-band SQL injection

Data is returned over a different channel, such as a database server making a DNS or network request. This depends heavily on the DBMS, network access, and database privileges.

4. Stacked-query injection

If the database driver allows multiple statements in one call, injected input may terminate the intended statement and add another. Impact can extend beyond data disclosure to modification or administrative actions.

5. Second-order SQL injection

Malicious text is stored safely at first, then later incorporated unsafely into a different dynamic SQL statement. This is easy to miss because the injection happens in a different request or workflow from the original input.

6. Injection into non-value query elements

Parameters usually protect data values, but applications often construct these elements dynamically:

• •Table or column names
• •ORDER BY fields and sort directions
• •Operators
• •SQL keywords or clauses
• •Lists of columns or expressions

These cannot generally be handled with ordinary value placeholders; use strict allowlists or map user choices to predefined SQL fragments.

7. Dynamic SQL in stored procedures and application code

Stored procedures are not automatically safe. Injection remains possible when either application code or a procedure builds SQL through string concatenation and executes it dynamically.

Root causes developers should recognize

• •Concatenating or interpolating input into SQL
• •Assuming numeric, JSON, header, cookie, or “internal” inputs are trustworthy
• •Escaping input manually instead of using the driver’s parameter API
• •Reusing stored data without treating it as untrusted
• •Giving the application database account excessive privileges
• •Exposing detailed database errors to clients

Primary defenses

• 1.Use parameterized queries or prepared statements everywhere.
• 2.Use allowlists for identifiers and query structure that cannot be parameterized.
• 3.Avoid dynamic SQL where possible; parameterize it when unavoidable.
• 4.Use least-privileged database accounts, separating read-only and write/admin roles.
• 5.Return generic client errors while logging detailed failures securely.
• 6.Validate input for business rules, but do not treat validation as a substitute for parameterization.
• 7.Test every input source, including URL parameters, bodies, headers, cookies, uploaded files, queued messages, and previously stored values.
• 8.Review ORM escape hatches, such as raw-query methods, which commonly reintroduce injection risk.

A concise message for developers is: SQL injection is a query-construction flaw, not merely a bad-character problem. Keep SQL structure fixed and pass untrusted values separately through the database driver.