Prevent SQL injection by sending untrusted values through parameterized queries or well-configured data-access APIs, never by building executable SQL through string concatenation. Add allow-listed handling for identifiers that cannot be parameters, minimize database privileges, test owned systems with safe cases, and monitor errors without exposing query details.
Who this is for: Application developers and reviewers responsible for database-backed services they own or have explicit authorization to test.
- Keep query structure fixed and bind every untrusted value through the database driver's parameter interface.
- Handle dynamic table names, columns, and sort directions through explicit application allow lists.
- Limit the application's database role so one query flaw cannot automatically reach every schema or operation.
Find every path to an interpreter
Inventory application code, reports, administrative tools, background jobs, search filters, and migration utilities that issue SQL. Untrusted data includes form fields, route parameters, headers, imported files, stored profile values, and messages received from other services. Previously stored text is not automatically safe when reused in a new query.
Trace values from input to the database call and distinguish data parameters from query identifiers. Review raw-query escape hatches in frameworks and object-relational tools. A library can support safe binding while still allowing an unsafe concatenated fragment, so verify the final API usage rather than trusting the library name.
Parameterize values and constrain structure
Use prepared statements or parameterized driver methods for strings, numbers, dates, lists, and other values. Keep placeholders in the SQL template and pass values separately. Stored procedures are protective only when their internal statements follow the same separation and do not assemble dynamic SQL from unchecked input.
Parameters generally cannot represent a table name, column, or sort keyword. Map user-facing choices to a small set of hardcoded identifiers inside the application. Reject unknown choices. Do not attempt to repair arbitrary identifiers through escaping because parser rules and database modes make that approach fragile.
Reduce database authority
Create a distinct database identity for each application or workload and grant only required schemas, tables, views, and operations. A read-only reporting job should not alter records. The normal web role should not own the schema or administer users. Separate migration authority from runtime credentials and rotate secrets through the approved manager.
Use views or narrowly scoped procedures where they simplify access, but test their effective permissions. Protect backups and replicas with equal care. Database error responses should give users a stable generic message while protected logs retain enough correlation and diagnostics for authorized responders without recording passwords or unnecessary sensitive values.
Verify and maintain the control
Add unit and integration tests that exercise ordinary punctuation, unexpected types, empty values, long values, and rejected identifier choices. Assert the intended rows and authorization outcomes rather than looking only for an error. Use a disposable database with synthetic records and a least-privilege role for active validation.
Static analysis and code search can locate concatenation patterns, while review confirms whether they reach SQL. Do not test public or customer systems without written permission. Track unsafe paths to closure, review database grants, query APIs, logs, backups, and current OWASP guidance at least quarterly, and escalate evidence of real unauthorized access immediately.
Secure an owned order search
An internal application lets authorized support staff filter orders by customer email, status, and one of three approved sort fields.
- Write a fixed query with bound placeholders for email and status rather than joining those values into SQL text.
- Map the three interface sort choices to hardcoded column expressions and reject every other value.
- Run the service under a role that can read approved order views but cannot change schema or access payment secrets.
- Test synthetic records for punctuation, empty filters, invalid sort options, cross-tenant authorization, and database errors.
- Review protected logs, backup recovery, and the incident route before deploying the corrected query.
SQL data-access review checklist
Apply this checklist to each query-building path before release.
- Input map: source, expected type, authorization context, destination query, and sensitivity.
- Query construction: fixed statement, bound values, explicit identifier allow list, and no concatenated fragments.
- Database role: required tables, permitted operations, denied schemas, secret rotation, and migration separation.
- Verification: synthetic database, normal cases, malformed values, denied identifiers, tenant checks, and expected rows.
- Operations: generic client errors, protected diagnostic logs, alerts, backups, restore owner, and escalation contact.
Common mistakes
- Escaping an entire user-supplied query fragment instead of binding values and allow-listing structure.
- Using a database owner account for normal application traffic because it simplifies initial setup.
- Testing only whether malformed input causes an error instead of verifying records, authority, and side effects.
Try one
A reporting endpoint accepts a requested sort column and a date range. Design the secure query boundary and its test evidence.
A good design binds the two dates as typed parameters and maps the requested sort option to a short hardcoded column list. It rejects unknown options, enforces report authorization, and uses a read-only database role limited to necessary views. Tests run against synthetic data in an owned environment and verify allowed ordering, rejected values, tenant isolation, safe errors, useful logs, and no unintended writes.
Sources
- OWASP SQL Injection Prevention Cheat SheetOWASP defensive controls for separating SQL code from untrusted values.
- CISA Secure by DesignCISA guidance for making customer security a core product requirement.