Skip to content

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

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

Practice Database Security

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors