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

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

2.2 Data Protection Principles

Encryption at rest with AES-256-CBC
HMAC verification for integrity checking
Secure key storage using OS keychain/credential manager
Backup encryption with independent keys
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)

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

python

# 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

python

# 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

bash

# 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

rust

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

rust

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

rust

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

rust

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

python

# 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

python

# 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

python

# 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

python

# 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

python

# 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

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

rust

// 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

rust

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

Weak Key Derivation

rust

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

Missing Verification

rust

// 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

rust

// 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

Search AI Tools

Install this agent skill to your Project

SKILL.md

SQLCipher Encrypted Database Expert

0. Mandatory Reading Protocol

1. Overview

Primary Use Cases

2. Core Principles

2.1 Development Principles

2.2 Data Protection Principles

3. Technical Foundation

3.1 Version Recommendations

3.2 Required Dependencies (Cargo.toml)

4. Implementation Workflow (TDD)

Step 1: Write Failing Test First

Step 2: Implement Minimum to Pass

Step 3: Refactor and Optimize

Step 4: Run Full Verification

5. Implementation Patterns

5.1 Encrypted Database Initialization

5.2 Secure Key Derivation

5.3 OS Keychain Integration

5.4 Key Rotation Implementation

6. Performance Patterns

6.1 Page Size Optimization

6.2 Cipher Configuration Tuning

6.3 Connection and Key Caching

6.4 WAL Mode with Encryption

6.5 Memory Security Trade-offs

7. Security Standards

7.1 Vulnerability Landscape

7.2 OWASP Mapping

7.3 Key Management Rules

8. Common Mistakes

Hardcoded Keys

Weak Key Derivation

Missing Verification

Insecure Backups

9. Pre-Implementation Checklist

Phase 1: Before Writing Code

Phase 2: During Implementation

Phase 3: Before Committing

10. Summary

References