Database Security Audit

• Goal: Review every DB interaction in the repository and detect common weaknesses (SQL injection, credentials in source, missing TLS, weak pool/timeouts, PII leakage, multi-tenant isolation, etc.).
• Output: A structured Markdown audit report written to audits/DATABASE_SECURITY_AUDIT_REPORT.md with findings, evidence, remediation steps, and a prioritized plan.

jeremy@MACSTUDIO Express-login-demo % claude

* Welcome to Claude Code!

/help for help, /status for your current setup

cwd: /Users/jeremy/Repos/Claude Code Course/Express-login-demo

> Try "how does <filepath> work?"

? for shortcuts

In INPUT_VALIDATION_SECURITY_REPORT.md

Verify:

1. Parameterized queries or ORM usage
   - Ensure all queries use placeholders (`?`, `$1`, `:param`) or ORM bindings.
   - Flag any direct string concatenation in queries as CRITICAL.

2. Connection string security
   - No passwords or secrets hardcoded in code or committed to source control.
   - Use environment variables or a secrets manager.

3. Database user permissions (principle of least privilege)
   - Application account has only the rights it needs (no SUPERUSER, DROP, GRANT).
   - Separate accounts for read-only, migrations, and admin.

4. Sensitive data encryption at rest
   - Verify disk-level/database-native encryption.
   - Critical fields (SSN, tokens) use column-level encryption or tokenization.

5. PII handling compliance
   - Minimize PII, redact in logs, and handle per GDPR/CCPA.

6. Query timeout configurations
   - Timeouts at driver and DB server level to prevent runaway queries.

7. Connection pool settings
   - Pool min/max configured; idle timeouts enforced.

8. Transaction handling for consistency
   - Multi-step operations wrapped in transactions when required.

9. Audit logging for sensitive operations
10. NoSQL injection / operator sanitization (where applicable)
11. Row/tenant isolation
12. Least-privilege networking (ACLs, VPCs)
13. TLS in transit & certificate validation
14. Secret management & rotation
15. Schema & integrity controls
16. Field-level minimization (avoid SELECT *)
17. Pagination & query limits
18. Backup/restore security
19. Data retention & deletion policies
20. Migrations safety & rollback plans
21. ORM raw-query escape-hatch review
22. LIKE / regex input handling
23. Query timeouts & resource guards
24. Audit & monitoring depth
25. PII in logs/metrics

• The tool scanned JS files to locate DB usage patterns (pool.query, client.query, raw SQL strings), pool usage, SSL/TLS config, and logging that might expose PII.
• Grep-style checks look for driver usage, TLS keywords, console logging, and secret-like strings in .env.

# find DB query usage
Search(pattern: "pool\.query|query\(", glob: "**/*.js", output_mode: "content")

# check for TLS/SSL config in configs and server files
Bash(grep -r "ssl\\|tls\\|encrypt" /path/to/project/config/ /path/to/project/routes/ /path/to/project/server.js 2>/dev/null || echo "No SSL/TLS configuration found")

# check for common logging calls
Bash(grep -r "console\\.log\\|console\\.error" /path/to/project/ --include="*.js" --exclude-dir=node_modules)

# search for secrets in .env
Bash(grep -r "password\\|secret\\|token\\|key" /path/to/project/.env)

# console logging occurrences
/config/database.js:  console.log('Connected to PostgreSQL database');
/config/database.js:  console.error('Database connection error:', err);
/server.js:  console.log(`Server running on port ${PORT}`);

# .env contents (placeholders found)
.env:JWT_SECRET=your_jwt_secret_key_here
.env:DB_PASSWORD=your_db_password

• Strengths: the app uses parameterized queries ($1 style) and bcrypt for password hashing; input validation is present (express-validator). These reduce injection and credential storage risk.
• Major gaps to fix before production: missing TLS for DB connections, credentials present in development files, absent connection pool limits and statement timeouts, and lack of DB audit logging / RLS controls where applicable.
• Risk score (pre-remediation): 6.5 / 10 (Medium-High). With prioritized fixes applied, target score: ~2.5 / 10 (Low Risk).

​

Critical findings (selected)

• Strengths: the app uses parameterized queries ($1 style) and bcrypt for password hashing; input validation is present (express-validator). These reduce injection and credential storage risk.
• Major gaps to fix before production: missing TLS for DB connections, credentials present in development files, absent connection pool limits and statement timeouts, and lack of DB audit logging / RLS controls where applicable.
• Risk score (pre-remediation): 6.5 / 10 (Medium-High). With prioritized fixes applied, target score: ~2.5 / 10 (Low Risk).

​

CRITICAL: No TLS/SSL Encryption in Transit

• Title: Database connections lack encryption
• Severity: Critical
• CWE: CWE-319 (Cleartext Transmission of Sensitive Information)
• Evidence: config/database.js:3-9 — pool configuration does not include SSL settings; connections default to plaintext TCP.

// config/database.js (evidence excerpt)
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  // missing ssl: { rejectUnauthorized: true } for production
});

// config/database.js (production config example)
const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  ssl: {
    rejectUnauthorized: true, // verify server certificate
    ca: process.env.DB_CA_CERT_BASE64 ? Buffer.from(process.env.DB_CA_CERT_BASE64, 'base64') : undefined
  },
  max: Number(process.env.DB_POOL_MAX || 10),
  idleTimeoutMillis: Number(process.env.DB_IDLE_TIMEOUT_MS || 30000)
  // Note: use `SET statement_timeout` per-connection or configure on the DB server.
});

​

CRITICAL: Hard-coded placeholder credentials found

• Evidence: .env contains JWT_SECRET=your_jwt_secret_key_here and DB_PASSWORD=your_db_password.
• Remediation:
  • Remove real .env from the repo and purge it from Git history (use git filter-repo or git filter-branch).
  • Add .env to .gitignore, and commit a .env.example with placeholders.
  • Store production secrets in a secrets manager (AWS Secrets Manager, HashiCorp Vault, etc.) and inject into CI/CD or runtime environment.

​

HIGH: Test / placeholder bcrypt hash in SQL

• Example:

INSERT INTO users (email, name, password) VALUES
('test@example.com', 'Test User', '$2b$10$K6Kg8Q3.3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q3Q');

• Remediation: Remove or isolate test accounts from production data. Generate unique, properly salted bcrypt hashes for any seeded accounts.

• Missing connection pool limits and driver-level statement timeouts (risk: resource exhaustion).
• No DB audit logging or DDL change monitoring identified.
• No Row-Level Security (RLS) policies if multi-tenancy applies.
• Some logging uses console.log/console.error and may expose PII — sanitize logs and avoid printing credentials.

1. Parameterized queries: SQL uses $1 placeholders (prevents SQL injection).
2. Password hashing: bcrypt used for passwords.
3. Input validation: express-validator used for primary inputs.
4. Connection pooling: pg Pool is in use (needs production limits).
5. Environment variables: credentials are sourced from environment variables (but .env needs secure handling).

1. Enable TLS/SSL for database connections and verify server certificates.
2. Remove .env from repository and add .env to .gitignore. Commit .env.example instead.
3. Generate and store production secrets in a secrets manager.
4. Add connection pool limits, idle timeouts, and statement timeouts.

1. Implement DB audit logging for authentication and privileged operations.
2. Add client-side query timeouts and resource guards.
3. Sanitize error logging to avoid PII leakage.
4. Replace placeholder/test hashes and remove test accounts from prod seeds.

1. Adopt a secrets management solution (AWS Secrets Manager, HashiCorp Vault).
2. Add database monitoring, alerting, and anomaly detection.
3. Implement data retention and deletion policies.
4. Apply Row-Level Security (RLS) if multi-tenancy is required.

• +3: Missing TLS and credential management (critical exposure)
• -2: Strong use of parameterized queries and input validation
• +1: Missing enterprise controls (audit, timeouts)
• +1: Development defaults not hardened for production

• Enforce per-statement timeouts at the client level (pg example):

// set a per-statement timeout (ms)
const client = await pool.connect();
try {
  await client.query('SET statement_timeout = 5000'); // 5s
  const res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
} finally {
  client.release();
}

• Sanitize logs to avoid PII leaks:

// bad
console.error('Auth failed for user:', userEmail, 'password:', password);

// better - log non-PII context only
console.error('Auth failed for user (email masked):', maskEmail(userEmail));

• Use environment-driven pool defaults and sensible production values:

DB_POOL_MAX=20
DB_IDLE_TIMEOUT_MS=30000
DB_STATEMENT_TIMEOUT_MS=5000

• Example .env.example (commit this file; never commit real secrets):

# .env.example
DB_HOST=your-db-host
DB_PORT=5432
DB_NAME=your-db-name
DB_USER=your-db-user
DB_PASSWORD=REPLACE_ME
JWT_SECRET=REPLACE_ME
DB_POOL_MAX=10
DB_IDLE_TIMEOUT_MS=30000
DB_STATEMENT_TIMEOUT_MS=5000

• The Express login demo demonstrates solid foundations (parameterized queries, bcrypt, input validation), which reduce injection and credential storage risk.
• Critical production hardening is required before public deployment: enable TLS, remove sensitive files from source, configure pool/timeouts, introduce audit logging, and adopt a secrets manager.
• Implement the prioritized remediation plan above — these concrete fixes will materially reduce risk quickly.

• Expand scope to API and infrastructure security: ingress controls, network ACLs, and cloud resource configuration.
• Validate database server-side controls (encryption at rest, user roles, audit logging, and backups).

• node-postgres (pg): https://node-postgres.com/
• HashiCorp Vault: https://www.vaultproject.io/
• AWS Secrets Manager: https://aws.amazon.com/secrets-manager/
• bcrypt (npm): https://www.npmjs.com/package/bcrypt
• express-validator: https://express-validator.github.io/docs/
• PostgreSQL SSL/TLS documentation: https://www.postgresql.org/docs/current/ssl-tcp.html