Database Security Patterns for Web Applications

Databases are the crown jewels of any system. One injection flaw, and attackers can walk away with everything. Protecting this layer requires multiple, overlapping defenses.

---

1. Parameterize Queries

Never concatenate strings into queries. Use parameterized statements and prepared queries.

// Vulnerable
const user = await db.query(`SELECT * FROM users WHERE email = '${email}'`);

// Secure
const user = await db.query('SELECT * FROM users WHERE email = ?', [email]);

---

2. Role-Based Access Control (RBAC)

Map DB roles to application permissions. A reporting service should not be able to DELETE.

-- Create role-specific database users
CREATE ROLE app_read_only;
CREATE ROLE app_write_user;

GRANT SELECT ON users TO app_read_only;
GRANT SELECT, INSERT, UPDATE ON users TO app_write_user;

---

3. Encrypt Sensitive Fields

Passwords are hashed, but fields like SSN, phone numbers, and tokens should be encrypted with AES-256-GCM or similar.

const crypto = require('crypto');

function encrypt(text) {
  const iv = crypto.randomBytes(16);
  const cipher = crypto.createCipheriv('aes-256-gcm', process.env.ENCRYPTION_KEY, iv);
  let encrypted = cipher.update(text, 'utf8', 'hex');
  encrypted += cipher.final('hex');
  const tag = cipher.getAuthTag().toString('hex');
  return `${iv.toString('hex')}:${tag}:${encrypted}`;
}

---

4. Monitor Access

Audit logs reveal who accessed what, when, and how often. Unusual activity should raise alarms.

-- Example PostgreSQL auditing extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';

---

5. Build Defense in Depth

Application validation, role isolation, encryption, and monitoring together create true resilience.

---

Databases demand respect and paranoia. A secure schema is the difference between integrity and irreversible breach.

---

I help teams architect databases that resist both casual attacks and advanced threats.

Curious? Explore how: kodex.studio