GPT on Question 19, Oct 3

SQL injection (SQLi) occurs when untrusted input is interpreted as part of a SQL statement rather than as data. The core cause is usually dynamic SQL built through string concatenation.

Main classes of SQL injection

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 query combines attacker-selected data with the application’s normal result set.

Warning signs: Detailed SQL errors, unexpected columns or rows, unusual query syntax in logs.

2. Blind or inferential SQL injection

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

• •Boolean-based: Responses differ depending on whether an injected condition is true or false.
• •Time-based: Deliberate database delays indicate whether a condition is true.

Warning signs: Repeated nearly identical requests, subtle response differences, or unexplained database/request delays.

3. Out-of-band SQL injection

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

Warning signs: Unexpected DNS lookups or outbound connections from database servers.

4. Stacked-query injection

If the database driver permits multiple statements in one call, attacker-controlled input may append an additional statement. Potential impact includes modifying or deleting data or changing permissions.

5. Second-order or stored SQL injection

Malicious-looking input is stored safely at first, but later retrieved and concatenated into a new SQL statement. Validation at the original entry point alone may therefore be insufficient.

Example: A stored profile field is later used unsafely in an administrative report query.

Injection can occur in different SQL contexts

Developers should consider more than quoted strings. Injection can affect:

• •Numeric values
• •Search filters
• •ORDER BY columns or sort direction
• •Table or column names
• •LIMIT/pagination clauses
• •Stored procedures and dynamically generated SQL
• •ORM “raw query” features

Parameters generally protect values, not SQL identifiers or keywords. Dynamic identifiers must be selected from a strict allowlist.

Primary prevention

• 1.Use parameterized queries/prepared statements
  ```sql
  SELECT id, name FROM users WHERE email = ?
  ```
  Bind the email separately; do not concatenate it into the SQL text.

• 2.Avoid dynamic SQL wherever possible
  Be especially cautious with stored procedures that internally concatenate SQL.

• 3.Allowlist non-parameterizable elements
  ```text
  requested sort = "name" → approved SQL identifier "name"
  anything else → reject
  ```

• 4.Use least-privileged database accounts
  Application accounts should not own schemas, administer users, or access unrelated data.

• 5.Return generic errors to clients
  Log diagnostic details securely, but do not expose SQL statements, stack traces, or database errors.

• 6.Treat escaping as a fallback, not the main defense
  Correct escaping varies by database, encoding, driver, and context.

• 7.Test continuously
  Include code review, static analysis, dependency scanning, integration tests, and authorized security testing.

Unsafe vs. safe pattern

Unsafe

sql = "SELECT * FROM users WHERE email = '" + email + "'"
db.execute(sql)

Safe

db.execute(
    "SELECT * FROM users WHERE email = ?",
    [email]
)

A concise message for developers: Never let user-controlled data alter SQL structure. Bind values as parameters, allowlist identifiers, and run the database account with minimal privileges.