Sensitive data columns use encryption (at-rest encryption beyond platform defaults)

Platform-level encryption at rest protects the physical disk from theft but does not protect sensitive columns from a compromised database user or a SQL injection attack that reads data directly from tables. SSNs, government IDs, health diagnoses, and biometrics stored as plaintext TEXT columns are fully readable to any session with SELECT access. CWE-311 (missing encryption of sensitive data) and OWASP A02 both require that sensitive fields are protected at the application layer — the database encryption key and the application encryption key must be separately managed so that compromising one does not expose the other.

Encrypt sensitive fields (SSNs, government IDs, health data) using AES-256-GCM before writing to the database. Store the encryption key in a secrets manager, not in source code. For payments, store only Stripe token references — never raw card numbers.

// lib/encryption.ts — AES-256-GCM field encryption
import { createCipheriv, createDecipheriv, randomBytes } from 'crypto'
const KEY = Buffer.from(process.env.FIELD_ENCRYPTION_KEY!, 'hex') // 32-byte hex key

export function encrypt(plaintext: string): string {
  const iv = randomBytes(12)
  const cipher = createCipheriv('aes-256-gcm', KEY, iv)
  const enc = Buffer.concat([cipher.update(plaintext, 'utf8'), cipher.final()])
  return `${iv.toString('hex')}:${cipher.getAuthTag().toString('hex')}:${enc.toString('hex')}`
}

export function decrypt(ciphertext: string): string {
  const [ivHex, tagHex, dataHex] = ciphertext.split(':')
  const d = createDecipheriv('aes-256-gcm', KEY, Buffer.from(ivHex!, 'hex'))
  d.setAuthTag(Buffer.from(tagHex!, 'hex'))
  return d.update(Buffer.from(dataHex!, 'hex')) + d.final('utf8')
}
// Generate key: openssl rand -hex 32

• ID: database-design-operations.security-access.sensitive-data-encryption

• Severity: medium

• What to look for: Count every column that stores sensitive personal information and classify each as "encrypted" or "plaintext": SSNs, tax IDs, government IDs, financial account numbers, health data (diagnoses, medications), biometrics, full payment card numbers (PCI-DSS prohibits storing raw card numbers — only store tokenized references). Note that email addresses are PII but typically don't require application-level encryption unless the application deals with sensitive topics (healthcare, legal, financial services). Check for application-level encryption: search for encrypt, createCipheriv, AES, pgp_sym_encrypt, encryption libraries (@anthropic/crypto, node-forge, libsodium-wrappers), or ORM encryption extensions. Check whether the database platform has encryption at rest enabled — Supabase, AWS RDS, Google Cloud SQL all enable encryption at rest by default (verify in their configuration). Check that encryption keys are stored separately from the codebase (environment variables, secrets manager).

• Pass criteria: Fewer than 1 sensitive column stored in plaintext without encryption. Sensitive columns (SSN, government IDs, health data, raw financial account numbers) have application-level encryption, OR the application stores only tokenized references to sensitive data (e.g., Stripe payment method ID, not a card number). Platform encryption at rest is confirmed enabled. Encryption keys are stored in environment variables or a secrets manager, never in code.

• Fail criteria: SSNs, government IDs, or health data stored in plaintext. Full payment card numbers stored in any form (Stripe handles card storage — your DB should only have pm_xxxxx token references). Encryption logic exists but the key is hardcoded in the encryption utility.

• Skip (N/A) when: Application stores no sensitive personal data beyond email and password hash (which bcrypt handles appropriately). No SSNs, government IDs, health data, or raw financial account numbers stored.

• Detail on fail: Specify what sensitive data lacks protection. Example: "'kyc_profiles' table stores SSN and tax_id columns as plaintext TEXT — no application-level encryption found." or "'health_records' table stores diagnosis and medication columns as plain strings — no field-level encryption.".

• Remediation: Add application-level encryption for highly sensitive fields:

  // lib/encryption.ts — AES-256-GCM field encryption
  import { createCipheriv, createDecipheriv, randomBytes } from 'crypto'
  
  const ALGORITHM = 'aes-256-gcm'
  // Load from secrets manager or environment variable — NEVER hardcode
  const KEY = Buffer.from(process.env.FIELD_ENCRYPTION_KEY!, 'hex') // 32-byte key
  
  export function encrypt(plaintext: string): string {
    const iv = randomBytes(12)
    const cipher = createCipheriv(ALGORITHM, KEY, iv)
    const encrypted = Buffer.concat([cipher.update(plaintext, 'utf8'), cipher.final()])
    const tag = cipher.getAuthTag()
    return `${iv.toString('hex')}:${tag.toString('hex')}:${encrypted.toString('hex')}`
  }
  
  export function decrypt(ciphertext: string): string {
    const [ivHex, tagHex, dataHex] = ciphertext.split(':')
    const decipher = createDecipheriv(ALGORITHM, KEY, Buffer.from(ivHex!, 'hex'))
    decipher.setAuthTag(Buffer.from(tagHex!, 'hex'))
    return decipher.update(Buffer.from(dataHex!, 'hex')) + decipher.final('utf8')
  }
  
  // Generate encryption key: openssl rand -hex 32
  // Store in secrets manager (AWS Secrets Manager, Vercel env vars, etc.)
  

  For payments: never store card data — use Stripe's token system and store only the PaymentMethod ID.