Web Application Security Basics: Encrypting Your Database

Why encrypt the database if you already have access controls?

Because access controls assume your database server stays trusted. In reality:

• Backups get leaked or stolen
• Cloud storage buckets are misconfigured
• DBA laptops get compromised
• Cloud providers suffer insider threats
• Attackers dump data via SQL injection or compromised credentials

Encryption protects data at rest, so even if someone gets a copy of your database file, backup, or storage volume, they can't read it without the keys.

Mindset shift: Encryption doesn't replace access control—it complements it. Think of it as "What if they already have the data?"

Two approaches: Full-disk vs. Application-level encryption

You need both, not one or the other.

Full-disk encryption (storage-level)

This encrypts the entire database file or storage volume:

• Pros: Transparent to application, protects all data, easy to enable
• Cons: Decrypts data once it's in memory or being queried, so app-level threats still apply

Examples:

• LUKS for Linux volumes
• AWS EBS encryption
• Azure Disk Encryption
• Transparent Data Encryption (TDE) in SQL Server/Oracle/PostgreSQL

Enable it everywhere:

• Production database servers
• Backup storage
• Development/staging environments (yes, even these)

Application-level encryption (field-level)

This encrypts specific fields before they hit the database:

• Pros: Data stays encrypted even in query results, protects against DB admin access, allows granular key control
• Cons: Requires application changes, can complicate queries/search

Encrypt these fields:

• Passwords (hash, don't encrypt—use bcrypt/argon2)
• PII: SSN, national IDs, phone numbers
• Financial data: credit cards, bank accounts
• API keys, tokens, secrets
• Health records, legal documents

Use AES-256-GCM (authenticated encryption) or AES-256-CBC + HMAC if GCM isn't available. You can find a more detailed guide on encrypting your files here

Key management: the make-or-break factor

Encryption is only as strong as your key management. Common mistakes:

• Hardcoding keys in source code (leaked via Git, Docker images)
• Storing keys in the same database (defeats the purpose)
• Never rotating keys (increases exposure window)
• Using weak random number generators for IVs/keys

Best practices:

1. Use a secrets manager: AWS KMS, HashiCorp Vault, Azure Key Vault, GCP Secret Manager
2. Separate keys from data: Never store encryption keys in the same database or backup
3. Rotate keys regularly: Plan for key rotation (e.g., annually or after incidents)
4. Use strong IVs: Generate a fresh random IV for every encryption operation
5. Audit key access: Log who accessed keys and when

   // Bad: hardcoded key
   $key = 'my-super-secret-key-123';

// Good: from secrets manager $key = $vault->getSecret('db-encryption-key');

***
## Practical implementation patterns
#### Encrypting specific fields
Use a helper function with authenticated encryption:
```php
function encryptField($plaintext, $key) { $iv = random_bytes(16); // 16 bytes for AES $tag = ''; $ciphertext = openssl_encrypt( $plaintext, 'AES-256-GCM', $key, OPENSSL_RAW_DATA, $iv, $tag ); // Store: IV + TAG + CIPHERTEXT (all as hex or base64) return bin2hex($iv) . ':' . bin2hex($tag) . ':' . bin2hex($ciphertext);
}
function decryptField($encrypted, $key) { list($ivHex, $tagHex, $ciphertextHex) = explode(':', $encrypted); $iv = hex2bin($ivHex); $tag = hex2bin($tagHex); $ciphertext = hex2bin($ciphertextHex); return openssl_decrypt( $ciphertext, 'AES-256-GCM', $key, OPENSSL_RAW_DATA, $iv, $tag );
}

Store encrypted values in a TEXT or BLOB column. Never log or expose plaintext in application logs.

Handling searchable encrypted data

Encryption breaks normal queries. Workarounds:

• Deterministic encryption: Use same IV for same plaintext (allows equality checks but weakens security—use sparingly)
• Application-side search: Fetch encrypted data, decrypt in app, then filter (slow but secure)
• Encrypted indexes: Use specialized libraries (e.g., CryptDB, encrypted search indexes)
• Hybrid approach: Store searchable hash (e.g., HMAC) alongside encrypted data for lookups

// Example: searchable HMAC for email lookups
$searchableHash = hash_hmac('sha256', $email, $searchKey);
// Query: WHERE email_hash = ?

Database backups: don't forget these

Backups are often the weakest link:

• Encrypt backup files before storing them (not just the database)
• Use separate keys for backup encryption
• Test restore procedures with encrypted backups
• Rotate backup keys independently from production keys

# Example: encrypt backup before storing in S3
mysqldump -u app -p app_db | gzip | \ openssl enc -aes-256-cbc -salt -pbkdf2 -pass file:backup.key > backup.sql.gz.enc

Performance considerations

Yes, encryption adds overhead—but it's manageable:

• Storage-level encryption: Minimal impact (5-10% on modern hardware with AES-NI)
• Field-level encryption: Only affects specific fields, not entire queries
• Batch operations: Encrypt/decrypt in batches, not per-row in loops
• Caching: Cache decrypted values in trusted server memory (never in shared caches like Redis without encryption)

Reality check: A few milliseconds of encryption time is nothing compared to the cost of a data breach.

Common pitfalls

"I'll encrypt everything"

Over-encryption complicates debugging, searching, and performance. Encrypt only what's sensitive.

"I'll store keys in the database too"

If the database is compromised, both data and keys are exposed. Use external secrets management.

"I'm using HTTPS, so I'm safe"

HTTPS protects data in transit, not at rest. You need both.

"I'll use weak encryption to save performance"

Never use DES, 3DES, RC4, or MD5. Use AES-256-GCM or equivalent.

Testing your encryption

Before deploying:

1. Dump the database: Can you read plaintext from the dump? (You shouldn't)
2. Simulate a breach: Grab a backup file—can you extract sensitive data without keys?
3. Check logs: Are any plaintext values leaking in application or database logs?
4. Penetration test: Have someone try to extract data via SQL injection or backup access

Conclusion

Database encryption is your final safety net. Even with perfect access controls, backups and storage can leak. Encryption ensures that stolen data remains useless to attackers.

Combine this with the access controls from the previous article, and you've built a defense-in-depth strategy that assumes breach—and limits damage when it happens.

Next in the series: Updating Your File Permissions—because even encrypted data is vulnerable if file permissions are misconfigured.