GPT on Question 19, Sep 12

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

1. In-band SQL injection

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

• •UNION-based: Manipulates a query to append results from another compatible query.
• •Error-based: Causes database errors that reveal schema, data, or query details.

Risk indicator: Detailed database errors displayed to users.

2. Blind or inferential SQL injection

The application does not directly return database data, so the attacker infers it from behavior.

• •Boolean-based: Changes a condition and observes differences in page content, status codes, or response size.
• •Time-based: Introduces database operations that delay a response when a condition is true.

Risk indicator: User input changes whether a page appears normally or how long it takes to respond.

3. Out-of-band SQL injection

The database is induced to communicate through a separate channel, such as DNS or an outbound network request. This is less common and depends on database features, permissions, and network access.

Risk indicator: Database servers have unnecessary outbound network connectivity or powerful extension features enabled.

4. Stacked or piggybacked queries

If the driver permits multiple statements in one call, injected input may terminate the intended statement and add another.

Risk indicator: Multi-statement execution is enabled unnecessarily, especially with highly privileged database accounts.

5. Second-order SQL injection

Malicious input is stored safely at first but later retrieved and concatenated into a new SQL statement. Validation at initial entry therefore does not prevent exploitation at the later execution point.

Risk indicator: Data read from the database is treated as trusted when constructing another query.

6. Stored-procedure and dynamic-SQL injection

Stored procedures are not inherently safe. Injection remains possible when they construct SQL strings dynamically from parameters.

Risk indicator: String concatenation inside procedures, functions, reporting systems, or query builders.

Injection contexts developers should recognize

Injection is not limited to quoted text values. It can occur in:

• •Numeric expressions
• •WHERE conditions
• •Table or column names
• •ORDER BY and sort direction
• •LIMIT or pagination clauses
• •Search and reporting filters
• •JSON/XML-related database expressions
• •Administrative and maintenance queries

Parameterized queries generally protect values, but not dynamic identifiers or SQL keywords. For identifiers and sort directions, use strict allowlists or fixed mappings.

Core defenses

• 1.Use parameterized queries/prepared statements everywhere.
• 2.Never construct SQL by concatenating input, including input previously stored in the database.
• 3.Allowlist dynamic identifiers and query structure.
• 4.Use least-privileged database accounts; separate read-only and write roles where practical.
• 5.Disable unnecessary multi-statement, extension, file, and outbound-network capabilities.
• 6.Return generic errors to clients while logging details securely.
• 7.Test every execution path, including stored procedures, background jobs, exports, and reporting tools.
• 8.Treat escaping as a legacy fallback, not the primary defense; it is database-, encoding-, and context-dependent.

A concise message for developers is: “Keep data separate from SQL syntax at every point where a statement is executed.”