Most application bugs that involve databases are not exotic. They come from string-built queries, overly powerful database accounts, missing transactions and queries that run without limits. The ten practices below apply to any relational database and any programming language.
1. Always use parameterized queries
Never build SQL by concatenating user input into a string. Use placeholders and pass values separately, so the driver sends them as data rather than as SQL:
cursor.execute(
"SELECT id, name FROM customers WHERE email = %s",
(email,)
)Parameterized queries prevent SQL injection and also handle quoting, escaping and types correctly. Placeholder syntax varies by driver (?, %s, $1, named parameters), but the principle is the same.
2. Handle dynamic identifiers with allow-lists
Placeholders work for values, not for table or column names. If users can choose a sort column, map their choice to a fixed list of allowed column names in your code and reject anything else. Never pass raw input into ORDER BY or a table name.
3. Connect with a least-privilege account
The application's database user should have only the permissions it needs, for example read and write on specific tables, and not rights to drop tables or manage users. Use a separate account for migrations. If the application is compromised, limited permissions limit the damage.
4. Keep credentials out of code
Store connection strings and passwords in environment variables or a secrets manager, not in source files or version control. Rotate credentials when people leave or when a leak is suspected, and use TLS for connections that cross a network.
5. Use transactions for multi-step changes
When several statements must succeed or fail together, such as moving money between accounts or creating an order and its line items, wrap them in a transaction. Keep transactions short: do not wait for user input or slow external calls while a transaction holds locks.
6. Set timeouts
Configure connection timeouts and statement timeouts so a slow query cannot hang a request indefinitely or exhaust the connection pool. Most databases and drivers support a per-session or per-query limit. A clear timeout error is better than a request that never returns.
7. Limit result sets
Queries that return unbounded results eventually return too much. Paginate lists, select only the columns you need instead of SELECT *, and use keyset pagination (for example WHERE id > last_seen_id ORDER BY id LIMIT 50) for large tables, since large OFFSET values get slower as they grow.
8. Handle errors deliberately
Distinguish errors you can retry, such as deadlocks, serialization failures and dropped connections, from errors you cannot, such as constraint violations caused by bad input. Retry only the former, with a limit and backoff, and only when the operation is safe to repeat. Do not expose raw database error messages to users; log them for developers.
9. Let the database enforce integrity
Application validation is useful, but constraints in the database are the last line of defence. Use primary keys, foreign keys, unique constraints, NOT NULL and check constraints. They prevent bad data even when a bug, a script or a second application bypasses your validation code.
10. Review query plans for hot paths
For queries that run often or touch large tables, look at the execution plan with EXPLAIN or your database's equivalent. Confirm that indexes are used and that the query does not scan more rows than necessary. ORMs can generate inefficient SQL, so log and review the actual statements in development.
Key takeaways
- Parameterize every value and allow-list every dynamic identifier.
- Use least-privilege accounts and keep credentials out of code.
- Wrap related writes in short transactions and set timeouts.
- Limit results, retry only safe errors and let constraints protect your data.