GPT on Question 18, Sep 22

SQL injection occurs when untrusted input is interpreted as part of a SQL command 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: Database errors reveal schema details, query structure, or data.
• •UNION-based: An injected UNION causes data from another query to be included in the application’s normal response.

2. Inferential (“blind”) SQL injection

The application does not directly return database output, but its behavior reveals whether injected conditions are true.

• •Boolean-based: Differences in page content, status codes, or result counts reveal true/false outcomes.
• •Time-based: Conditional database delays reveal information through response timing.

3. Out-of-band SQL injection

The database is induced to communicate through another channel, such as DNS or HTTP. This depends on database features, network access, and permissions. Egress filtering greatly limits it.

4. Stacked-query injection

Input terminates the intended statement and adds another SQL statement. Whether this works depends on the database and driver allowing multiple statements.

5. Second-order SQL injection

Malicious input is stored safely at first, then later retrieved and concatenated into a different SQL statement. Validation only at initial entry does not prevent this; every query construction point must be safe.

6. Injection into non-value query elements

Parameters generally protect values, but applications sometimes construct SQL dynamically for:

• •Table or column names
• •ORDER BY fields and sort direction
• •Operators
• •SQL keywords or clauses

These elements usually cannot be bound as ordinary parameters. Use strict allowlists that map user choices to developer-defined SQL fragments.

7. Dynamic SQL inside stored procedures

Stored procedures are not automatically safe. Procedures that concatenate input into SQL strings can still be injectable. Use bind parameters in the procedure and avoid dynamic SQL where possible.

Key defenses

• 1.Use prepared statements/parameterized queries everywhere.
  ```text
  SELECT * FROM users WHERE email = ?
  ```
  Bind the email separately; do not build the query with string concatenation.

• 2.Use allowlists for identifiers and query structure.
  ```text
  requested sort = "date" → predefined column created_at
  ```

• 3.Avoid relying on escaping alone. It is database-, encoding-, and context-sensitive.

• 4.Apply least privilege. Application accounts should not have schema-administration, filesystem, or unnecessary network permissions.

• 5.Suppress detailed database errors in client responses, while retaining them in protected logs.

• 6.Review every SQL construction point, including background jobs, reporting tools, stored procedures, and code that consumes previously stored data.

• 7.Test both visible and blind behavior, including response content, status codes, errors, and timing—preferably with automated security tests integrated into CI.

The central developer rule is: data must never be assembled into SQL syntax via concatenation or interpolation.