Database Security
Protect your database: SQL injection, access control, encryption, and auditing.
What you'll learn
- SQL injection — the most common database attack and how to prevent it
- Using parameterized queries / prepared statements
- Principle of least privilege with roles and grants
- Encrypting sensitive data at rest and in transit
- Auditing, backup, and recovery best practices
Concept
SQL Injection
SQL injection is the #1 database security risk. It occurs when user input is concatenated into a SQL string: "SELECT * FROM users WHERE name = '" + userInput + "'". If userInput is ' OR '1'='1, the query becomes SELECT * FROM users WHERE name = '' OR '1'='1 — returning every user. Worse, '; DROP TABLE users; -- can destroy your database. The fix is always to use parameterized queries (also called prepared statements), where the database separates the SQL template from the values: query("SELECT * FROM users WHERE name = $1", [userInput]). The database never interprets values as SQL.
Principle of Least Privilege
Create database roles with the minimum permissions needed. The application's database user should not be able to DROP tables or access other databases. Use GRANT SELECT, INSERT, UPDATE ON orders TO app_role — not GRANT ALL ON *.* TO app_role. Separate read-only roles for reporting from read-write roles for the application. Never use the superuser (postgres, root) as your application's database user.
Encryption
- In transit — always connect over TLS/SSL. Never allow unencrypted connections to a production database.
- At rest — enable database-level encryption (Transparent Data Encryption in SQL Server/Oracle, or disk-level encryption with LUKS/eBPF). For specific sensitive columns (SSNs, credit cards), use column-level encryption or a vault service.
- Passwords — never store passwords in plain text or with reversible encryption. Use a password hashing function (bcrypt, scrypt, Argon2) with a salt.
Auditing and Backups
Enable audit logging to track who accessed or modified what data and when. This is essential for compliance (GDPR, HIPAA, SOC 2) and for forensic investigation after an incident. Maintain a backup strategy: full backups regularly, point-in-time recovery (via WAL archiving in PostgreSQL or binlog in MySQL), and test restores — an untested backup is no backup. Store backups encrypted and off-site.
Connection Security
Use connection string secrets from a secrets manager, not hardcoded in source or environment files. Restrict network access to the database (firewall, security groups, private network). Use connection pooling with authentication. Rotate credentials regularly and use short-lived credentials where possible (e.g. IAM database authentication on AWS).
Example
-- WRONG: SQL injection via string concatenation
-- query("SELECT * FROM users WHERE email = '" + email + "'")
-- If email = "x'; DROP TABLE users; --" -> disaster!
-- RIGHT: parameterized query (PostgreSQL / Node.js pg)
-- const res = await pool.query(
-- 'SELECT * FROM users WHERE email = $1', [email]
-- );
-- Create a restricted role for the application
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_random_password';
GRANT SELECT, INSERT, UPDATE ON users, orders, order_items TO app_user;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_user;
-- app_user CANNOT DROP tables or access other databases
-- Create a read-only role for reporting
CREATE ROLE report_reader WITH LOGIN PASSWORD 'another_secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;
-- Force SSL connections (PostgreSQL)
-- In postgresql.conf:
-- ssl = on
-- ssl_cert_file = 'server.crt'
-- ssl_key_file = 'server.key'
-- Enable audit logging (PostgreSQL)
-- log_statement = 'all'
-- log_connections = on
-- log_disconnections = on
Parameterized queries to prevent injection, least-privilege roles (app_user and report_reader), SSL configuration, and audit logging settings.
Try it
- SQL Escaper
Escape special characters — but prefer parameterized queries.
- Password Generator
Generate strong passwords for database roles.
Common mistakes
The mistake
Concatenating user input into SQL strings
The fix
This is SQL injection. ALWAYS use parameterized queries / prepared statements. The database separates code from data, making injection impossible.
The mistake
Using the database superuser (postgres/root) as the app connection
The fix
Create a dedicated role with minimum needed privileges (SELECT, INSERT, UPDATE on specific tables). The superuser can DROP databases and bypass all security.
Practice Database Security
2 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.