SQL Injection Prevention Review for Application Teams
SQL injection happens when data changes query structure. Parameter binding separates values from syntax; escaping is weaker and database-specific.
Find dynamic query seams
Review search filters, sort fields, report builders, administrative consoles, and ORM raw-query escapes. Example: bind customer name as a parameter. Example: choose ORDER BY column from fixed server-side enum because identifiers generally cannot be parameterized.
Query construction checklist
| Area | Safe pattern | Test |
|---|---|---|
| values | prepared statement | quote remains data |
| identifiers | server allowlist | unknown sort rejected |
| privileges | narrow DB account | read path cannot drop table |
| errors | generic response | SQL details absent |
OWASP SQL injection prevention and CWE-89 provide primary guidance.
Decision rule: no request-derived value may alter SQL grammar.
Fix
Replace concatenation with bindings, allowlist unavoidable structural choices, reduce database privileges, and test error paths. ORM use is not proof: raw fragments and unsafe interpolation remain review targets. Source review can find those seams.
Make grammar server-owned
A query template must define SQL structure before request values arrive. Bind values through driver parameters and keep type conversion explicit. Where sort direction, column, or table choice cannot be bound, map a small request enum to fixed server strings; reject every other value. Search raw query APIs, interpolation, migration helpers, reports, and stored procedures for dynamic SQL.
Validate with an actual request and database account, not a code review alone. Quote payloads must remain literal values, unknown sort choices must fail, and low-privilege accounts must be unable to perform unrelated actions. Capture query location, template, parameters, role grants, response, and regression test. Owner: data service owner. Pass: request data never changes query grammar and errors reveal no SQL detail. Fail: query structure changes, privileged action succeeds, or database error leaks. Exceptions state scope, compensating access control, risk approval, remediation owner, and expiry.
Query review example
Safe values use a query template with driver binding; they never concatenate request text. Safe sort selection maps small request enums to fixed server identifiers and fixed direction. Generic escaping helper is not an approval path. Review migrations and maintenance jobs too, because lower-exposure queries may run with elevated credentials.
Query construction proof
Expected output: query location, template ID, bound parameter types, structural enum choice, database role, error response, regression case. This query artifact shows grammar stayed fixed while values remained data.
Worked failure: A report sort parameter requests a column not in server map. Endpoint must reject it rather than concatenate the supplied identifier.
Edge case: Prepared values do not secure dynamic procedure SQL. Audit procedure bodies and maintenance scripts for execution of assembled statements.
Closure test: Run quote payload under the production-equivalent role in a test database. Closure passes when returned data follows literal value semantics and privilege remains narrow.
Dynamic structure boundary
List every query part that is not a bound value: ordering, pagination direction, selected report field, table partition, tenant schema, and procedure name. For each, use a finite server mapping or redesign request shape. Log rejected structural keys with a safe category, never the entire query. Parameter binding also needs correct types: treating a numeric limit as text may preserve injection resistance while still creating resource abuse.
Test code paths that execute outside normal HTTP handling, including background exports, scheduled reconciliation, CLI maintenance, and migration scripts. Those paths often use powerful database roles and receive data from files or queues rather than forms. A useful regression case asserts both query result and database permissions: valid user data returns expected row; attempted structural value fails; forbidden write cannot succeed under read role.
Capture prepared-statement API use in runtime test or driver log without exposing user values. A closure failure occurs if a newly added reporting path interpolates identifier despite primary application queries being bound. Review query builders after upgrades.
Database error handling needs separate evidence from query construction. Trigger a controlled constraint or connection failure and confirm application response does not disclose SQL text, schema names, credentials, or stack details. Preserve full diagnostics only in restricted server logs with request correlation. Query timeouts and row limits also reduce impact from expensive but syntactically valid input. Test a permitted wide search and verify limit behavior matches product expectation without switching to unsafe dynamic SQL.
Final review links each query path to parameter-binding evidence and hostile-input test result. Expected result: user values cannot alter SQL structure or reveal database diagnostics. Edge case: sort filters or column selection use dynamic identifiers; constrain them to an allowlist. Close only after deployed queries and restricted logs demonstrate policy-consistent behavior.