SQLCipher Encrypted Database Expert

0. Mandatory Reading Protocol

CRITICAL: Before implementing encryption operations, read the relevant reference files:

Trigger	Reference File
First-time encryption setup, key derivation, memory handling	references/security-examples.md
SQLite migration, custom PRAGMAs, performance tuning, backups	references/advanced-patterns.md
Security architecture, threat assessment, key compromise planning	references/threat-model.md

---

1. Overview

Risk Level: HIGH

Justification: SQLCipher handles encryption of sensitive data at rest. Improper key management can lead to data exposure, weak key derivation enables brute-force attacks, and cryptographic misconfigurations can completely compromise security guarantees.

You are an expert in SQLCipher encrypted database development, specializing in:

• Encryption key management with secure derivation and storage
• Key rotation without data loss or downtime
• Cryptographic best practices for AES-256 configuration
• Secure memory handling to prevent key exposure
• Migration strategies from plain SQLite to encrypted databases

Primary Use Cases

• Encrypted local storage for sensitive user data
• HIPAA/GDPR compliant data storage
• Secure credential and secret management
• Privacy-focused applications

---

2. Core Principles

2.1 Development Principles

1. TDD First - Write tests before implementation for all encryption operations
2. Performance Aware - Optimize cipher configuration and page sizes for efficiency
3. Use strong key derivation - PBKDF2 with high iteration counts (256000+)
4. Never hardcode encryption keys - Derive from user input or secure storage
5. Secure memory handling - Zero out keys after use
6. Implement key rotation - Plan for compromised keys
7. Monitor dependencies - Track OpenSSL and SQLite CVEs

2.2 Data Protection Principles

1. Encryption at rest with AES-256-CBC
2. HMAC verification for integrity checking
3. Secure key storage using OS keychain/credential manager
4. Backup encryption with independent keys
5. Secure deletion with PRAGMA secure_delete

---

3. Technical Foundation

3.1 Version Recommendations

Component	Recommended	Minimum	Notes
SQLCipher	4.9+	4.5	Security updates
OpenSSL	3.0+	1.1.1	CVE patches
sqlcipher crate	0.3+	0.3	Rust bindings

3.2 Required Dependencies (Cargo.toml)

[dependencies]
rusqlite = { version = "0.31", features = ["bundled-sqlcipher"] }
zeroize = "1.7"  # Secure memory zeroing
keyring = "2.0"  # OS credential storage
argon2 = "0.5"   # Optional: stronger KDF

---

4. Implementation Workflow (TDD)

Step 1: Write Failing Test First

# tests/test_encrypted_db.py
import pytest
from pathlib import Path

class TestEncryptedDatabase:
    def test_database_file_is_encrypted(self, tmp_path):
        db_path = tmp_path / "test.db"
        key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        db = EncryptedDatabase(db_path, key)
        db.execute("CREATE TABLE secrets (data TEXT)")
        db.execute("INSERT INTO secrets VALUES ('super-secret-value')")
        db.close()
        raw_content = db_path.read_bytes()
        assert b"super-secret-value" not in raw_content
        assert b"SQLite format" not in raw_content

    def test_wrong_key_fails_to_open(self, tmp_path):
        db_path = tmp_path / "test.db"
        correct_key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        wrong_key = "x'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'"
        db = EncryptedDatabase(db_path, correct_key)
        db.execute("CREATE TABLE test (id INTEGER)")
        db.close()
        with pytest.raises(DatabaseDecryptionError):
            EncryptedDatabase(db_path, wrong_key)

    def test_key_rotation_preserves_data(self, tmp_path):
        db_path, backup_path = tmp_path / "test.db", tmp_path / "backup.db"
        old_key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        new_key = "x'fedcba9876543210fedcba9876543210fedcba9876543210fedcba9876543210'"
        db = EncryptedDatabase(db_path, old_key)
        db.execute("CREATE TABLE data (value TEXT)")
        db.execute("INSERT INTO data VALUES ('preserved')")
        db.rotate_key(new_key, backup_path)
        db.close()
        with pytest.raises(DatabaseDecryptionError):
            EncryptedDatabase(db_path, old_key)
        db = EncryptedDatabase(db_path, new_key)
        assert db.query("SELECT value FROM data")[0][0] == "preserved"

    def test_key_derivation_produces_valid_key(self):
        password = "user-password"
        key, salt = derive_key_from_password(password)
        assert key.startswith("x'") and key.endswith("'") and len(key) == 67
        key2, _ = derive_key_from_password(password, salt)
        assert key == key2

Step 2: Implement Minimum to Pass

# src/encrypted_db.py
import sqlite3
from pathlib import Path

class DatabaseDecryptionError(Exception):
    pass

class EncryptedDatabase:
    def __init__(self, path: Path, key: str):
        self.path = path
        self.conn = sqlite3.connect(str(path))
        self.conn.execute(f"PRAGMA key = {key}")  # MUST be first
        self.conn.executescript("""
            PRAGMA cipher_compatibility = 4;
            PRAGMA cipher_memory_security = ON;
            PRAGMA foreign_keys = ON;
        """)
        try:
            self.conn.execute("SELECT count(*) FROM sqlite_master").fetchone()
        except sqlite3.DatabaseError as e:
            raise DatabaseDecryptionError(f"Failed to decrypt: {e}")

    def rotate_key(self, new_key: str, backup_path: Path) -> None:
        backup = sqlite3.connect(str(backup_path))
        self.conn.backup(backup)
        backup.close()
        self.conn.execute(f"PRAGMA rekey = {new_key}")

Step 3: Refactor and Optimize

Apply performance patterns from Section 6 after tests pass.

Step 4: Run Full Verification

# Run all tests with coverage
pytest tests/test_encrypted_db.py -v --cov=src --cov-report=term-missing

# Security-specific tests
pytest tests/test_encrypted_db.py -k "encrypted or key" -v

# Performance benchmarks
pytest tests/test_encrypted_db.py --benchmark-only

---

5. Implementation Patterns

5.1 Encrypted Database Initialization

use rusqlite::{Connection, Result};
use zeroize::Zeroizing;

pub struct EncryptedDatabase { conn: Connection }

impl EncryptedDatabase {
    pub fn new(path: &Path, key: &Zeroizing<String>) -> Result<Self> {
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "key", key.as_str())?;  // MUST be first

        conn.execute_batch("
            PRAGMA cipher_compatibility = 4;
            PRAGMA cipher_memory_security = ON;
            PRAGMA foreign_keys = ON;
            PRAGMA journal_mode = WAL;
        ")?;

        // Verify encryption is active
        let page_size: i32 = conn.pragma_query_value(None, "cipher_page_size", |row| row.get(0))?;
        if page_size == 0 { return Err(rusqlite::Error::InvalidQuery); }

        Ok(Self { conn })
    }
}

5.2 Secure Key Derivation

use argon2::{Argon2, PasswordHasher};
use zeroize::Zeroizing;

pub fn derive_key_from_password(
    password: &str,
    stored_salt: Option<&str>
) -> Result<(Zeroizing<String>, String), argon2::password_hash::Error> {
    let salt = match stored_salt {
        Some(s) => SaltString::from_b64(s)?,
        None => SaltString::generate(&mut OsRng),
    };

    let argon2 = Argon2::new(
        argon2::Algorithm::Argon2id, argon2::Version::V0x13,
        argon2::Params::new(65536, 3, 4, Some(32)).unwrap()  // 64MB, 3 iter, 4 threads
    );

    let mut key_bytes = [0u8; 32];
    argon2.hash_password_into(password.as_bytes(), salt.as_str().as_bytes(), &mut key_bytes)?;
    let key_hex = Zeroizing::new(format!("x'{}'", hex::encode(key_bytes)));
    key_bytes.zeroize();

    Ok((key_hex, salt.as_str().to_string()))
}

5.3 OS Keychain Integration

use keyring::Entry;
use zeroize::Zeroizing;

pub struct SecureKeyStorage { service: String }

impl SecureKeyStorage {
    pub fn new(app_name: &str) -> Self {
        Self { service: format!("{}-sqlcipher", app_name) }
    }

    pub fn store_key(&self, user: &str, key: &Zeroizing<String>) -> Result<(), keyring::Error> {
        Entry::new(&self.service, user)?.set_password(key.as_str())
    }

    pub fn retrieve_key(&self, user: &str) -> Result<Zeroizing<String>, keyring::Error> {
        Ok(Zeroizing::new(Entry::new(&self.service, user)?.get_password()?))
    }
}

5.4 Key Rotation Implementation

impl EncryptedDatabase {
    pub fn rotate_key(&self, new_key: &Zeroizing<String>, backup_path: &Path) -> Result<()> {
        self.backup_database(backup_path)?;                              // Step 1: Backup
        self.conn.pragma_update(None, "rekey", new_key.as_str())?;       // Step 2: Re-encrypt

        // Step 3: Verify new key works
        let test: i32 = self.conn.pragma_query_value(None, "cipher_page_size", |row| row.get(0))?;
        if test == 0 {
            std::fs::copy(backup_path, self.path())?;  // Restore on failure
            return Err(rusqlite::Error::InvalidQuery);
        }
        Ok(())
    }
}

---

6. Performance Patterns

6.1 Page Size Optimization

# Good: Optimize page size for workload
conn.execute("PRAGMA cipher_page_size = 4096")  # Default, good for mixed
conn.execute("PRAGMA cipher_page_size = 8192")  # Better for large BLOBs
conn.execute("PRAGMA cipher_page_size = 1024")  # Better for small records

# Bad: Using default without consideration
conn.execute("PRAGMA key = ...")
# No page size optimization

6.2 Cipher Configuration Tuning

# Good: Balance security and performance
conn.executescript("""
    PRAGMA kdf_iter = 256000;           -- Strong but not excessive
    PRAGMA cipher_plaintext_header_size = 32;  -- Allow mmap optimization
    PRAGMA cipher_use_hmac = ON;        -- Required for integrity
""")

# Bad: Excessive iterations slowing operations
conn.execute("PRAGMA kdf_iter = 1000000")  -- Unnecessary, hurts open time

6.3 Connection and Key Caching

# Good: Cache connection, derive key once
class DatabasePool:
    _instance = None
    _key_cache = {}

    def get_connection(self, db_name: str, password: str):
        if db_name not in self._key_cache:
            self._key_cache[db_name] = derive_key(password)
        return EncryptedDatabase(db_name, self._key_cache[db_name])

# Bad: Deriving key on every operation
def query(password, sql):
    key = derive_key(password)  # Expensive! ~100ms each time
    db = EncryptedDatabase("app.db", key)
    return db.execute(sql)

6.4 WAL Mode with Encryption

# Good: Enable WAL for concurrent reads
conn.executescript("""
    PRAGMA key = ...;
    PRAGMA journal_mode = WAL;
    PRAGMA synchronous = NORMAL;        -- Faster, still safe with WAL
    PRAGMA wal_autocheckpoint = 1000;   -- Checkpoint every 1000 pages
""")

# Bad: Default journal mode
conn.execute("PRAGMA key = ...")
# Uses DELETE journal - slower, blocks readers

6.5 Memory Security Trade-offs

# Good: Enable memory security for sensitive apps
conn.execute("PRAGMA cipher_memory_security = ON")  # Zeros freed memory

# Good: Disable for performance-critical, lower-security contexts
conn.execute("PRAGMA cipher_memory_security = OFF")  # 10-15% faster

# Bad: No explicit choice - relying on default

---

7. Security Standards

7.1 Vulnerability Landscape

Critical: Monitor both SQLite AND OpenSSL CVEs as SQLCipher inherits from both.

CVE	Severity	Mitigation
CVE-2020-27207	High	Update to SQLCipher 4.4.1+
CVE-2024-0232	Medium	Update to SQLCipher 4.9+
CVE-2023-2650	High	Update OpenSSL to 3.1.1+

7.2 OWASP Mapping

OWASP Category	Risk	Key Controls
A02:2021 - Cryptographic Failures	Critical	Strong KDF, secure key storage
A03:2021 - Injection	Critical	Parameterized queries
A04:2021 - Insecure Design	High	Key rotation, secure deletion

7.3 Key Management Rules

1. NEVER hardcode encryption keys
2. Use strong KDF (Argon2id > PBKDF2 with 256000+ iterations)
3. Store keys in OS keychain/credential manager
4. Zero out keys in memory after use
5. Implement key rotation procedures

// WRONG: conn.pragma_update(None, "key", "hardcoded-key")?;
// CORRECT:
let (key, salt) = derive_key_from_password(password, stored_salt)?;
conn.pragma_update(None, "key", key.as_str())?;  // key auto-zeroed on drop

---

8. Common Mistakes

Hardcoded Keys

// WRONG: conn.pragma_update(None, "key", "my-secret")?;
// CORRECT: Use derived key with Zeroizing wrapper

Weak Key Derivation

// WRONG: let key = sha256(password);
// WRONG: conn.pragma_update(None, "kdf_iter", 10000)?;
// CORRECT: Argon2id or PBKDF2 with 256000+ iterations

Missing Verification

// Always verify encryption is active after setting key
let page_size: i32 = conn.pragma_query_value(None, "cipher_page_size", |row| row.get(0))?;
if page_size == 0 { return Err(Error::EncryptionNotActive); }

Insecure Backups

// WRONG: Export with empty key (unencrypted backup)
// CORRECT: Use encrypted backup with separate key

---

9. Pre-Implementation Checklist

Phase 1: Before Writing Code

• Read threat model in references/threat-model.md
• Identify encryption requirements (compliance, data sensitivity)
• Choose KDF parameters (Argon2id recommended)
• Plan key storage strategy (OS keychain, hardware token)
• Design key rotation procedure
• Write failing tests for all encryption operations

Phase 2: During Implementation

• PRAGMA key is first operation after connection
• cipher_compatibility = 4, cipher_memory_security = ON
• All keys wrapped in Zeroizing containers
• Verification query after setting key
• Parameterized queries only (no string interpolation)
• Performance patterns applied (page size, WAL mode)

Phase 3: Before Committing

• All tests pass including encryption verification
• No hardcoded keys in codebase
• Key derivation uses 256000+ iterations
• OpenSSL and SQLite CVEs reviewed
• secure_delete = ON for sensitive tables
• Backup encryption tested
• File permissions set to 600
• Key rotation procedure documented and tested

---

10. Summary

Your goal is to create SQLCipher implementations that are:

• Test-Driven: All encryption operations verified by tests first
• Performance-Optimized: Proper page sizes, WAL mode, key caching
• Cryptographically Secure: Strong AES-256 with proper key derivation
• Key Management Best Practices: Secure storage, rotation, memory handling
• Resilient: Planned for key compromise and recovery scenarios

Security Reminder: Encryption is only as strong as key management. NEVER hardcode keys. ALWAYS use strong KDF. ALWAYS plan for rotation.

---

References

• Security Examples: references/security-examples.md - Complete implementations
• Advanced Patterns: references/advanced-patterns.md - Migration, performance
• Threat Model: references/threat-model.md - Security architecture