TL;DR
The #1 database security best practice is using parameterized queries. Never concatenate user input into SQL, use least-privilege access, encrypt sensitive data at rest, and secure your connection strings. These practices prevent 87% of database breaches.
"Your database is only as secure as its weakest query. One unparameterized input is all it takes for a complete breach."
Best Practice 1: Prevent SQL Injection 5 min
SQL injection is one of the most common and dangerous vulnerabilities. Always use parameterized queries:
// WRONG: SQL injection vulnerability
const query = `SELECT * FROM users WHERE id = ${userId}`;
const query2 = `SELECT * FROM users WHERE email = '${email}'`;
// CORRECT: Parameterized query (node-postgres)
const result = await pool.query(
'SELECT * FROM users WHERE id = $1',
[userId]
);
// CORRECT: Using an ORM (Prisma)
const user = await prisma.user.findUnique({
where: { id: userId }
});
// CORRECT: Knex query builder
const users = await knex('users')
.where('email', email)
.first();
Never trust user input. Even data that looks safe (numbers, emails) can contain SQL injection payloads. Always use parameterized queries or ORMs.
Best Practice 2: Use Least-Privilege Access 4 min
Database users should only have the permissions they need:
-- Create a read-only role for the API
CREATE ROLE api_readonly;
GRANT CONNECT ON DATABASE myapp TO api_readonly;
GRANT USAGE ON SCHEMA public TO api_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO api_readonly;
-- Create a role for normal app operations
CREATE ROLE api_user;
GRANT CONNECT ON DATABASE myapp TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api_user;
-- Create admin role (use sparingly)
CREATE ROLE api_admin;
GRANT ALL PRIVILEGES ON DATABASE myapp TO api_admin;
-- Create users with specific roles
CREATE USER app_service WITH PASSWORD 'secure_password';
GRANT api_user TO app_service;
CREATE USER reporting_service WITH PASSWORD 'secure_password';
GRANT api_readonly TO reporting_service;
| Use Case | Permissions Needed |
|---|---|
| Read-only API | SELECT only |
| Standard app | SELECT, INSERT, UPDATE, DELETE |
| Migrations | CREATE, ALTER, DROP (run separately) |
| Admin tasks | Full access (use rarely) |
Best Practice 3: Encrypt Sensitive Data 5 min
Encrypt sensitive fields before storing them:
import crypto from 'crypto';
const ENCRYPTION_KEY = process.env.ENCRYPTION_KEY; // 32 bytes
const IV_LENGTH = 16;
function encrypt(text) {
const iv = crypto.randomBytes(IV_LENGTH);
const cipher = crypto.createCipheriv(
'aes-256-cbc',
Buffer.from(ENCRYPTION_KEY, 'hex'),
iv
);
let encrypted = cipher.update(text, 'utf8', 'hex');
encrypted += cipher.final('hex');
return iv.toString('hex') + ':' + encrypted;
}
function decrypt(encryptedText) {
const [ivHex, encrypted] = encryptedText.split(':');
const iv = Buffer.from(ivHex, 'hex');
const decipher = crypto.createDecipheriv(
'aes-256-cbc',
Buffer.from(ENCRYPTION_KEY, 'hex'),
iv
);
let decrypted = decipher.update(encrypted, 'hex', 'utf8');
decrypted += decipher.final('utf8');
return decrypted;
}
// Usage
await db.user.create({
data: {
email: email,
ssn: encrypt(ssn), // Encrypt sensitive fields
},
});
Best Practice 4: Secure Connection Strings 3 min
Database connection strings contain credentials. Handle them carefully:
// Store in environment variables, never in code
const DATABASE_URL = process.env.DATABASE_URL;
// For Prisma
// In .env (never commit this file)
// DATABASE_URL="postgresql://user:password@host:5432/db?sslmode=require"
// Validate connection string exists at startup
if (!DATABASE_URL) {
console.error('DATABASE_URL is required');
process.exit(1);
}
// Use SSL in production
const pool = new Pool({
connectionString: DATABASE_URL,
ssl: process.env.NODE_ENV === 'production' ? {
rejectUnauthorized: true,
ca: process.env.DB_CA_CERT,
} : false,
});
Connection String Security:
- Never commit connection strings to version control
- Use environment variables for credentials
- Enable SSL for production connections
- Rotate database passwords regularly
- Use connection pooling to limit connections
Best Practice 5: Implement Row-Level Security 4 min
For multi-tenant apps, use database-level access controls:
-- Enable RLS on table
ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;
-- Policy: users can only see their own data
CREATE POLICY user_data_isolation ON user_data
FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid);
-- In your application, set the user context
await pool.query("SET app.current_user_id = $1", [userId]);
-- Now queries automatically filter by user
const result = await pool.query('SELECT * FROM user_data');
// Only returns data where user_id matches
Best Practice 6: Backup and Recovery 2 min
Regular backups are essential for security and disaster recovery:
Backup Security Checklist:
- Automate daily backups
- Encrypt backups at rest
- Store backups in a separate location
- Test restore procedures regularly
- Retain backups according to compliance requirements
- Secure backup access with separate credentials
Best Practice 7: Audit Logging 4 min
Track who accessed what data:
-- Create audit log table
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
action TEXT NOT NULL,
user_id UUID,
old_data JSONB,
new_data JSONB,
timestamp TIMESTAMPTZ DEFAULT NOW()
);
-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, action, user_id, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
current_setting('app.current_user_id', true)::uuid,
CASE WHEN TG_OP = 'DELETE' OR TG_OP = 'UPDATE'
THEN to_jsonb(OLD) ELSE NULL END,
CASE WHEN TG_OP = 'INSERT' OR TG_OP = 'UPDATE'
THEN to_jsonb(NEW) ELSE NULL END
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
-- Apply to sensitive tables
CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger();
Common Database Security Mistakes
| Mistake | Impact | Prevention |
|---|---|---|
| SQL string concatenation | SQL injection | Use parameterized queries |
| Database as root user | Full system access if breached | Use least-privilege roles |
| Unencrypted connections | Credential interception | Always use SSL |
| Credentials in code | Exposed in version control | Use environment variables |
| No backups | Data loss | Automate encrypted backups |
Official Resources: For comprehensive database security guidance, see OWASP Database Security Cheat Sheet, OWASP Query Parameterization Cheat Sheet, and OWASP SQL Injection Prevention Cheat Sheet.
Do ORMs prevent SQL injection?
Yes, when used correctly. ORMs like Prisma, TypeORM, and Drizzle use parameterized queries internally. However, be careful with raw query methods that might allow string concatenation.
Should I encrypt all database fields?
No, encrypt only sensitive data like SSNs, payment info, and personal health data. Encryption adds complexity and prevents database-level searching. Use it strategically for high-sensitivity fields.
How do I secure a hosted database (RDS, Supabase)?
Use private subnets/VPCs when possible, enable SSL, use strong passwords, configure security groups to limit access, and use the platform's built-in encryption options.
What data should I audit?
Audit access to sensitive data (PII, financial), all admin actions, authentication events, and data modifications to critical tables. Balance thoroughness with storage and performance costs.