SQ
SQL Security
SQL security practices for injection prevention, privilege separation, and data protection
Details
Language / Topic
SQLCategory
Security
Rules
balanced
- Always use parameterized queries or prepared statements — never interpolate user input into SQL strings; string concatenation is the root cause of SQL injection.
- Grant application database users the minimum required privileges: `SELECT`, `INSERT`, `UPDATE`, `DELETE` on specific tables only — never `SUPERUSER` or `CREATEDB`.
- Encrypt sensitive columns (PII, payment data) using `pgcrypto` (`pgp_sym_encrypt`) or application-layer encryption before storing — encryption at rest alone does not protect against SQL-level data exposure.
- Create a dedicated read-only database user for reporting and analytics queries — separate from the write user to limit blast radius on credential compromise.
- Use `ROW SECURITY POLICY` (PostgreSQL Row Level Security) to enforce data access controls inside the database rather than relying solely on application-layer filtering.
- Audit privilege grants with `\dp tablename` (psql) or `SELECT * FROM information_schema.role_table_grants` regularly — revoke stale or overly broad grants.
- Store database credentials in a secrets manager (Vault, AWS Secrets Manager) and rotate them periodically — never hardcode credentials in connection strings committed to source control.