TL;DR
Parameterized queries are the one that matters most. Never build SQL by pasting user input into a string. After that: least-privilege database roles, encryption for the fields that actually need it, and connection strings kept out of your code. Most database incidents trace back to one of those four being skipped.
"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 still here after twenty-five years because concatenating a string is the obvious way to build a query. Parameterized queries keep input and SQL in separate lanes:
// 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. A field that should hold a number is still a string until you've checked. Parameterized queries or an ORM make that irrelevant, which is the point.
Best Practice 2: Use Least-Privilege Access 4 min
The database user your app connects as decides what an attacker gets when something else goes wrong. If that user can DROP TABLE, so can a successful injection. Give it less:
-- 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 the fields you'd have to disclose in a breach notification. Not everything: encrypted columns can't be searched or indexed, so encrypting an email address you filter on will cost you a feature.
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
A connection string is a password with a hostname attached to it. Treat it that way:
// 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
- Keep credentials in environment variables, and check the name has no client-side prefix. A
DATABASE_URLbehindVITE_orNEXT_PUBLIC_gets compiled into the JavaScript you ship to browsers - Enable SSL for production connections
- Rotate database passwords on a schedule you'll actually keep
- Use connection pooling to cap concurrent connections
Best Practice 5: Implement Row-Level Security 4 min
Application code that filters by tenant works right up until one query forgets to. Row-level security moves that check into the database, where forgetting isn't an option:
-- 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
A backup you've never restored is a hypothesis. Ransomware and a bad migration end the same way:
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
When something does go wrong, the first question is what the attacker read. Without an audit trail there's no answer, only a guess:
-- 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.
Further Reading
Put these practices into action with our step-by-step guides.
Verify Your Database Security
Scan your application for database security issues.