GPT on Question 18, Sep 19

SQL injection (SQLi) 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: Deliberately triggers database errors that disclose schema, query, or data details.
• •UNION-based: Manipulates a query so results from another query are included in the application’s normal response.

2. Inferential or “blind” SQL injection

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

• •Boolean-based: Observes differences in content, status codes, redirects, or response size depending on whether a condition is true or false.
• •Time-based: Uses database operations that conditionally delay execution; response timing reveals whether a condition was true.

3. Out-of-band SQL injection

Results are exfiltrated through a separate channel, such as database-triggered DNS or HTTP traffic. This depends heavily on database features, privileges, and network egress.

4. Stacked-query injection

Input terminates the intended statement and adds one or more additional SQL statements. Whether this works depends on the database driver and whether multi-statement execution is enabled.

5. Second-order SQL injection

Malicious input is initially stored safely, but later retrieved and concatenated into a different SQL statement. Validation only at initial entry may therefore miss it.

6. Injection in non-value query elements

SQLi is not limited to quoted string values. It can occur in:

• •Numeric expressions
• •ORDER BY fields or sort directions
• •Table or column names
• •Search filters
• •Pagination clauses
• •JSON/XML operators
• •Dynamic stored-procedure SQL

These elements often cannot be handled with ordinary value placeholders, so they require strict allowlists or safe query-builder APIs.

Key developer guidance

• 1.Use parameterized queries/prepared statements for every data value.
• 2.Never construct SQL by string concatenation, interpolation, or ad hoc escaping.
• 3.For identifiers or keywords that cannot be parameterized, use a fixed allowlist mapping—for example, map "date" to a hard-coded column expression.
• 4.Treat stored procedures and ORMs as safe only if they do not build dynamic SQL from input.
• 5.Use a least-privileged database account; separate read-only and write roles where possible.
• 6.Disable multi-statement execution and unnecessary database/network features.
• 7.Return generic client errors while logging detailed database errors securely.
• 8.Test all input paths, including headers, cookies, API fields, background jobs, and previously stored data.
• 9.Include SQLi-focused static analysis, dependency scanning, and authenticated dynamic testing in CI/staging.

Escaping can be a defense-in-depth measure, but it is not a reliable substitute for parameterization because escaping rules vary by database, driver, encoding, and SQL mode.