Sqlcipher Encrypted Database Expert

SQLCipher Encrypted Database Expert

0. Mandatory Reading Protocol

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

---

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

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.

7.2 OWASP Mapping

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

SQLCipher Advanced Patterns

Performance Optimization

Optimal PRAGMA Configuration

pub fn configure_for_performance(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        -- Cache size: negative = KB, positive = pages
        PRAGMA cache_size = -64000;  -- 64 MB cache

        -- WAL mode for concurrent reads
        PRAGMA journal_mode = WAL;

        -- Synchronous: NORMAL is good balance
        PRAGMA synchronous = NORMAL;

        -- Memory-mapped I/O
        PRAGMA mmap_size = 268435456;  -- 256 MB

        -- Temporary storage in memory
        PRAGMA temp_store = MEMORY;

        -- Page size (must match cipher_page_size)
        PRAGMA page_size = 4096;

        -- SQLCipher specific
        PRAGMA cipher_page_size = 4096;
        PRAGMA cipher_memory_security = ON;
    ")?;

    Ok(())
}

// For high-throughput writes
pub fn configure_for_writes(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA wal_autocheckpoint = 1000;  -- Checkpoint every 1000 pages
    ")?;

    Ok(())
}

// For read-heavy workloads
pub fn configure_for_reads(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        PRAGMA cache_size = -128000;  -- 128 MB cache
        PRAGMA mmap_size = 1073741824;  -- 1 GB mmap
        PRAGMA query_only = ON;  -- Read-only optimization
    ")?;

    Ok(())
}

Benchmarking Encryption Overhead

pub fn benchmark_encryption_overhead(
    plaintext_db: &Path,
    encrypted_db: &Path,
    key: &Zeroizing<String>
) -> BenchmarkResults {
    use std::time::Instant;

    let mut results = BenchmarkResults::default();

    // Benchmark plaintext
    let plain_conn = Connection::open(plaintext_db).unwrap();
    let start = Instant::now();
    for _ in 0..1000 {
        plain_conn.execute("INSERT INTO test (data) VALUES (?1)", ["test"]).unwrap();
    }
    results.plaintext_write_ms = start.elapsed().as_millis();

    // Benchmark encrypted
    let enc_conn = Connection::open(encrypted_db).unwrap();
    enc_conn.pragma_update(None, "key", key.as_str()).unwrap();
    let start = Instant::now();
    for _ in 0..1000 {
        enc_conn.execute("INSERT INTO test (data) VALUES (?1)", ["test"]).unwrap();
    }
    results.encrypted_write_ms = start.elapsed().as_millis();

    results.overhead_percent =
        ((results.encrypted_write_ms as f64 / results.plaintext_write_ms as f64) - 1.0) * 100.0;

    results
}

#[derive(Default)]
pub struct BenchmarkResults {
    pub plaintext_write_ms: u128,
    pub encrypted_write_ms: u128,
    pub overhead_percent: f64,
}

---

Backup Strategies

Encrypted Backup with Different Key

pub fn create_encrypted_backup(
    conn: &Connection,
    backup_path: &Path,
    backup_key: &Zeroizing<String>
) -> Result<()> {
    let attach_sql = format!(
        "ATTACH DATABASE '{}' AS backup KEY {}",
        backup_path.display(),
        backup_key.as_str()
    );

    conn.execute_batch(&format!("
        {};

        -- Configure backup database
        PRAGMA backup.cipher_compatibility = 4;
        PRAGMA backup.kdf_iter = 256000;

        -- Export
        SELECT sqlcipher_export('backup');

        -- Detach
        DETACH DATABASE backup;
    ", attach_sql))?;

    Ok(())
}

Incremental Backup with WAL

pub fn backup_wal_checkpoint(
    conn: &Connection,
    backup_dir: &Path
) -> Result<()> {
    // Force a checkpoint to ensure all WAL data is in main database
    conn.pragma_update(None, "wal_checkpoint", "TRUNCATE")?;

    // Now the main .db file contains all data
    // Copy the main file (already encrypted)
    let timestamp = chrono::Utc::now().format("%Y%m%d_%H%M%S");
    let backup_path = backup_dir.join(format!("backup_{}.db", timestamp));

    std::fs::copy(
        conn.path().unwrap(),
        &backup_path
    )?;

    Ok(())
}

---

Multi-Database Patterns

Attached Encrypted Databases

pub struct MultiDatabaseManager {
    main_conn: Connection,
}

impl MultiDatabaseManager {
    pub fn new(main_path: &Path, main_key: &Zeroizing<String>) -> Result<Self> {
        let conn = Connection::open(main_path)?;
        conn.pragma_update(None, "key", main_key.as_str())?;

        Ok(Self { main_conn: conn })
    }

    /// Attach another encrypted database
    pub fn attach(
        &self,
        path: &Path,
        alias: &str,
        key: &Zeroizing<String>
    ) -> Result<()> {
        // Validate alias (prevent SQL injection)
        if !alias.chars().all(|c| c.is_alphanumeric() || c == '_') {
            return Err(rusqlite::Error::InvalidParameterName("Invalid alias".into()));
        }

        let sql = format!(
            "ATTACH DATABASE '{}' AS {} KEY {}",
            path.display(),
            alias,
            key.as_str()
        );

        self.main_conn.execute_batch(&sql)?;
        Ok(())
    }

    /// Query across databases
    pub fn query_cross_db(&self, sql: &str, params: &[&dyn rusqlite::ToSql]) -> Result<Vec<Row>> {
        let mut stmt = self.main_conn.prepare(sql)?;
        // Execute query that can reference main.table and alias.table
        stmt.query_map(params, |row| {
            // Map results
            Ok(row)
        })?.collect()
    }

    pub fn detach(&self, alias: &str) -> Result<()> {
        if !alias.chars().all(|c| c.is_alphanumeric() || c == '_') {
            return Err(rusqlite::Error::InvalidParameterName("Invalid alias".into()));
        }

        self.main_conn.execute(&format!("DETACH DATABASE {}", alias), [])?;
        Ok(())
    }
}

---

Connection Pooling for Encrypted Databases

R2D2 Pool with Encryption

use r2d2::{Pool, PooledConnection, CustomizeConnection};
use r2d2_sqlite::SqliteConnectionManager;
use zeroize::Zeroizing;

struct EncryptionInitializer {
    key: Zeroizing<String>,
}

impl CustomizeConnection<Connection, rusqlite::Error> for EncryptionInitializer {
    fn on_acquire(&self, conn: &mut Connection) -> Result<(), rusqlite::Error> {
        // Set encryption key for each connection
        conn.pragma_update(None, "key", self.key.as_str())?;

        // Configure settings
        conn.execute_batch("
            PRAGMA cipher_memory_security = ON;
            PRAGMA foreign_keys = ON;
            PRAGMA journal_mode = WAL;
        ")?;

        Ok(())
    }
}

pub fn create_encrypted_pool(
    path: &std::path::Path,
    key: Zeroizing<String>,
    pool_size: u32
) -> Result<Pool<SqliteConnectionManager>, r2d2::Error> {
    let manager = SqliteConnectionManager::file(path);

    Pool::builder()
        .max_size(pool_size)
        .connection_customizer(Box::new(EncryptionInitializer { key }))
        .build(manager)
}

---

Cipher Settings Reference

SQLCipher 4 Defaults

/// Apply SQLCipher 4 default settings explicitly
pub fn apply_sqlcipher4_defaults(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        -- Encryption algorithm
        PRAGMA cipher = 'aes-256-cbc';

        -- Key derivation
        PRAGMA kdf_algorithm = 'PBKDF2_HMAC_SHA512';
        PRAGMA kdf_iter = 256000;

        -- HMAC for page integrity
        PRAGMA hmac_algorithm = 'HMAC_SHA512';
        PRAGMA hmac_use = ON;

        -- Page size
        PRAGMA cipher_page_size = 4096;

        -- Plaintext header (for compatibility - usually 0)
        PRAGMA cipher_plaintext_header_size = 0;
    ")?;

    Ok(())
}

Custom Cipher Configuration

/// Configure for maximum security (slower)
pub fn apply_maximum_security(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        PRAGMA kdf_iter = 1000000;  -- 1 million iterations
        PRAGMA cipher_memory_security = ON;
        PRAGMA cipher_plaintext_header_size = 0;
    ")?;

    Ok(())
}

/// Configure for better performance (still secure)
pub fn apply_performance_settings(conn: &Connection) -> Result<()> {
    conn.execute_batch("
        PRAGMA kdf_iter = 256000;  -- Standard
        PRAGMA cipher_memory_security = OFF;  -- Slight risk, better performance
        PRAGMA cache_size = -64000;
    ")?;

    Ok(())
}

---

Database Verification

Verify Encryption Status

pub struct EncryptionStatus {
    pub is_encrypted: bool,
    pub cipher: String,
    pub page_size: i32,
    pub kdf_iter: i32,
    pub hmac_enabled: bool,
}

pub fn get_encryption_status(conn: &Connection) -> Result<EncryptionStatus> {
    let cipher_page_size: i32 = conn
        .pragma_query_value(None, "cipher_page_size", |row| row.get(0))
        .unwrap_or(0);

    if cipher_page_size == 0 {
        return Ok(EncryptionStatus {
            is_encrypted: false,
            cipher: String::new(),
            page_size: 0,
            kdf_iter: 0,
            hmac_enabled: false,
        });
    }

    let cipher: String = conn
        .pragma_query_value(None, "cipher", |row| row.get(0))?;
    let kdf_iter: i32 = conn
        .pragma_query_value(None, "kdf_iter", |row| row.get(0))?;
    let hmac_use: i32 = conn
        .pragma_query_value(None, "hmac_use", |row| row.get(0))?;

    Ok(EncryptionStatus {
        is_encrypted: true,
        cipher,
        page_size: cipher_page_size,
        kdf_iter,
        hmac_enabled: hmac_use == 1,
    })
}

Integrity Check

pub fn verify_database_integrity(conn: &Connection) -> Result<bool> {
    let result: String = conn.query_row(
        "SELECT integrity_check FROM pragma_integrity_check LIMIT 1",
        [],
        |row| row.get(0)
    )?;

    Ok(result == "ok")
}

pub fn verify_can_read_data(conn: &Connection) -> Result<bool> {
    // Try to read from sqlite_master
    let count: i32 = conn.query_row(
        "SELECT count(*) FROM sqlite_master",
        [],
        |row| row.get(0)
    )?;

    Ok(count >= 0)
}

---

Error Recovery

Recovering from Key Issues

pub enum RecoveryStrategy {
    RetryWithCachedKey,
    PromptForPassword,
    RestoreFromBackup,
    FailGracefully,
}

pub fn handle_key_error(
    error: &rusqlite::Error,
    backup_available: bool
) -> RecoveryStrategy {
    match error {
        rusqlite::Error::SqliteFailure(err, _) => {
            match err.code {
                // SQLITE_NOTADB - wrong key or not encrypted
                rusqlite::ErrorCode::NotADatabase => {
                    if backup_available {
                        RecoveryStrategy::RestoreFromBackup
                    } else {
                        RecoveryStrategy::PromptForPassword
                    }
                }
                // SQLITE_AUTH - authentication failed
                rusqlite::ErrorCode::AuthorizationForStatementDenied => {
                    RecoveryStrategy::PromptForPassword
                }
                _ => RecoveryStrategy::FailGracefully,
            }
        }
        _ => RecoveryStrategy::FailGracefully,
    }
}

SQLCipher Security Examples

Key Derivation Implementations

Argon2id Key Derivation (Recommended)

use argon2::{Argon2, Algorithm, Version, Params};
use argon2::password_hash::SaltString;
use zeroize::{Zeroize, Zeroizing};
use rand::rngs::OsRng;

pub struct KeyDerivation;

impl KeyDerivation {
    /// Derive database encryption key from user password
    pub fn from_password(
        password: &str,
        stored_salt: Option<&str>
    ) -> Result<(Zeroizing<String>, String), KeyDerivationError> {
        // Use stored salt or generate new one
        let salt = match stored_salt {
            Some(s) => SaltString::from_b64(s)
                .map_err(|_| KeyDerivationError::InvalidSalt)?,
            None => SaltString::generate(&mut OsRng),
        };

        // Configure Argon2id with secure parameters
        let argon2 = Argon2::new(
            Algorithm::Argon2id,  // Hybrid - resistant to side-channel and GPU attacks
            Version::V0x13,
            Params::new(
                65536,   // 64 MB memory cost
                3,       // 3 iterations
                4,       // 4 parallel lanes
                Some(32) // 32 byte (256 bit) output
            ).map_err(|_| KeyDerivationError::InvalidParams)?
        );

        // Derive key bytes
        let mut key_bytes = [0u8; 32];
        argon2.hash_password_into(
            password.as_bytes(),
            salt.as_str().as_bytes(),
            &mut key_bytes
        ).map_err(|_| KeyDerivationError::HashFailed)?;

        // Format for SQLCipher (hex blob)
        let key_hex = Zeroizing::new(format!("x'{}'", hex::encode(key_bytes)));

        // Securely zero the raw key bytes
        key_bytes.zeroize();

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

    /// Derive key from hardware token or secure enclave
    pub fn from_hardware_key(
        hardware_key: &[u8],
        context: &str
    ) -> Result<Zeroizing<String>, KeyDerivationError> {
        use hkdf::Hkdf;
        use sha2::Sha256;

        let hkdf = Hkdf::<Sha256>::new(None, hardware_key);
        let mut key_bytes = [0u8; 32];

        hkdf.expand(context.as_bytes(), &mut key_bytes)
            .map_err(|_| KeyDerivationError::HkdfFailed)?;

        let key_hex = Zeroizing::new(format!("x'{}'", hex::encode(key_bytes)));
        key_bytes.zeroize();

        Ok(key_hex)
    }
}

#[derive(Debug)]
pub enum KeyDerivationError {
    InvalidSalt,
    InvalidParams,
    HashFailed,
    HkdfFailed,
}

PBKDF2 Configuration (SQLCipher Native)

/// Configure SQLCipher's built-in PBKDF2
pub fn configure_pbkdf2(conn: &Connection) -> Result<()> {
    // Set high iteration count for PBKDF2
    // CRITICAL: Default is 256000 in SQLCipher 4, but verify
    conn.pragma_update(None, "kdf_iter", 256000)?;

    // Use PBKDF2-HMAC-SHA512 (default in SQLCipher 4)
    conn.pragma_update(None, "kdf_algorithm", "PBKDF2_HMAC_SHA512")?;

    // Verify settings
    let iter: i32 = conn.pragma_query_value(None, "kdf_iter", |row| row.get(0))?;
    assert!(iter >= 256000, "KDF iterations too low!");

    Ok(())
}

---

Secure Key Storage

OS Keychain Integration

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

pub struct SecureKeyStore {
    service_name: String,
}

impl SecureKeyStore {
    pub fn new(app_name: &str) -> Self {
        Self {
            service_name: format!("{}-encryption", app_name),
        }
    }

    /// Store encryption key in OS keychain
    pub fn store(&self, identifier: &str, key: &Zeroizing<String>) -> Result<(), KeyStoreError> {
        let entry = Entry::new(&self.service_name, identifier)
            .map_err(KeyStoreError::Keyring)?;

        entry.set_password(key.as_str())
            .map_err(KeyStoreError::Keyring)
    }

    /// Retrieve encryption key from OS keychain
    pub fn retrieve(&self, identifier: &str) -> Result<Zeroizing<String>, KeyStoreError> {
        let entry = Entry::new(&self.service_name, identifier)
            .map_err(KeyStoreError::Keyring)?;

        let password = entry.get_password()
            .map_err(KeyStoreError::Keyring)?;

        Ok(Zeroizing::new(password))
    }

    /// Delete key from OS keychain
    pub fn delete(&self, identifier: &str) -> Result<(), KeyStoreError> {
        let entry = Entry::new(&self.service_name, identifier)
            .map_err(KeyStoreError::Keyring)?;

        entry.delete_credential()
            .map_err(KeyStoreError::Keyring)
    }

    /// Check if key exists
    pub fn exists(&self, identifier: &str) -> bool {
        Entry::new(&self.service_name, identifier)
            .and_then(|e| e.get_password())
            .is_ok()
    }
}

#[derive(Debug)]
pub enum KeyStoreError {
    Keyring(keyring::Error),
    NotFound,
}

Key Caching with Secure Memory

use zeroize::{Zeroize, ZeroizeOnDrop};
use std::sync::RwLock;
use std::time::{Duration, Instant};

/// Cached key with automatic expiration and secure cleanup
#[derive(ZeroizeOnDrop)]
struct CachedKey {
    #[zeroize(skip)]
    created_at: Instant,
    key: String,
}

pub struct SecureKeyCache {
    cache: RwLock<Option<CachedKey>>,
    ttl: Duration,
}

impl SecureKeyCache {
    pub fn new(ttl_seconds: u64) -> Self {
        Self {
            cache: RwLock::new(None),
            ttl: Duration::from_secs(ttl_seconds),
        }
    }

    pub fn set(&self, key: Zeroizing<String>) {
        let mut cache = self.cache.write().unwrap();
        *cache = Some(CachedKey {
            created_at: Instant::now(),
            key: key.to_string(),
        });
    }

    pub fn get(&self) -> Option<Zeroizing<String>> {
        let cache = self.cache.read().unwrap();
        cache.as_ref().and_then(|cached| {
            if cached.created_at.elapsed() < self.ttl {
                Some(Zeroizing::new(cached.key.clone()))
            } else {
                None
            }
        })
    }

    pub fn clear(&self) {
        let mut cache = self.cache.write().unwrap();
        if let Some(mut cached) = cache.take() {
            cached.key.zeroize();
        }
    }
}

impl Drop for SecureKeyCache {
    fn drop(&mut self) {
        self.clear();
    }
}

---

Key Rotation

Complete Key Rotation Procedure

use rusqlite::Connection;
use std::path::{Path, PathBuf};
use zeroize::Zeroizing;

pub struct KeyRotationManager {
    db_path: PathBuf,
    backup_dir: PathBuf,
    key_store: SecureKeyStore,
}

impl KeyRotationManager {
    /// Rotate database encryption key with full safety measures
    pub fn rotate_key(
        &self,
        current_key: &Zeroizing<String>,
        new_key: &Zeroizing<String>,
        user_id: &str
    ) -> Result<(), RotationError> {
        // Step 1: Create timestamped backup
        let backup_path = self.create_backup(current_key)?;

        // Step 2: Open database with current key
        let conn = Connection::open(&self.db_path)
            .map_err(RotationError::Database)?;
        conn.pragma_update(None, "key", current_key.as_str())
            .map_err(RotationError::Database)?;

        // Step 3: Verify current key works
        self.verify_encryption(&conn)?;

        // Step 4: Re-encrypt with new key
        conn.pragma_update(None, "rekey", new_key.as_str())
            .map_err(RotationError::Database)?;

        // Step 5: Verify new key works
        drop(conn);
        let conn = Connection::open(&self.db_path)
            .map_err(RotationError::Database)?;
        conn.pragma_update(None, "key", new_key.as_str())
            .map_err(RotationError::Database)?;
        self.verify_encryption(&conn)?;

        // Step 6: Update stored key
        self.key_store.store(user_id, new_key)
            .map_err(|_| RotationError::KeyStorage)?;

        // Step 7: Log rotation event
        self.log_rotation_event(user_id);

        // Step 8: Schedule backup deletion (keep for 7 days)
        self.schedule_backup_cleanup(backup_path, 7);

        Ok(())
    }

    fn create_backup(&self, key: &Zeroizing<String>) -> Result<PathBuf, RotationError> {
        let timestamp = chrono::Utc::now().format("%Y%m%d_%H%M%S");
        let backup_path = self.backup_dir.join(format!("backup_{}.db", timestamp));

        let conn = Connection::open(&self.db_path)
            .map_err(RotationError::Database)?;
        conn.pragma_update(None, "key", key.as_str())
            .map_err(RotationError::Database)?;

        // Create encrypted backup
        let backup_key = self.generate_backup_key()?;
        let attach_sql = format!(
            "ATTACH DATABASE '{}' AS backup KEY {}",
            backup_path.display(),
            backup_key.as_str()
        );

        conn.execute_batch(&format!("
            {};
            SELECT sqlcipher_export('backup');
            DETACH DATABASE backup;
        ", attach_sql)).map_err(RotationError::Database)?;

        // Store backup key
        let backup_id = format!("backup_{}", timestamp);
        self.key_store.store(&backup_id, &backup_key)
            .map_err(|_| RotationError::KeyStorage)?;

        Ok(backup_path)
    }

    fn verify_encryption(&self, conn: &Connection) -> Result<(), RotationError> {
        let page_size: i32 = conn.pragma_query_value(None, "cipher_page_size", |row| row.get(0))
            .map_err(RotationError::Database)?;

        if page_size == 0 {
            return Err(RotationError::EncryptionNotActive);
        }

        // Try to read data
        conn.query_row("SELECT count(*) FROM sqlite_master", [], |_| Ok(()))
            .map_err(RotationError::Database)?;

        Ok(())
    }

    fn generate_backup_key(&self) -> Result<Zeroizing<String>, RotationError> {
        use rand::Rng;
        let mut key_bytes = [0u8; 32];
        rand::thread_rng().fill(&mut key_bytes);
        Ok(Zeroizing::new(format!("x'{}'", hex::encode(key_bytes))))
    }

    fn log_rotation_event(&self, user_id: &str) {
        log::info!(
            target: "security_audit",
            "key_rotation completed for user={} at={}",
            user_id,
            chrono::Utc::now().to_rfc3339()
        );
    }

    fn schedule_backup_cleanup(&self, path: PathBuf, days: u64) {
        // In production, use a proper scheduler
        std::thread::spawn(move || {
            std::thread::sleep(std::time::Duration::from_secs(days * 24 * 60 * 60));
            if path.exists() {
                // Secure delete
                let _ = std::fs::remove_file(path);
            }
        });
    }
}

#[derive(Debug)]
pub enum RotationError {
    Database(rusqlite::Error),
    KeyStorage,
    EncryptionNotActive,
    BackupFailed,
}

---

OpenSSL Security Monitoring

Dependency Version Checking

/// Check SQLCipher and OpenSSL versions for known vulnerabilities
pub fn check_security_versions(conn: &Connection) -> SecurityReport {
    let mut report = SecurityReport::default();

    // Get SQLCipher version
    let cipher_version: String = conn
        .pragma_query_value(None, "cipher_version", |row| row.get(0))
        .unwrap_or_default();

    // Get OpenSSL version (if available)
    let openssl_version = openssl::version::version();

    // Check for known vulnerable versions
    report.sqlcipher_version = cipher_version.clone();
    report.openssl_version = openssl_version.to_string();

    // CVE checks
    if cipher_version.starts_with("4.4.0") || cipher_version.starts_with("4.3") {
        report.warnings.push(
            "SQLCipher version may be affected by CVE-2020-27207. Update to 4.4.1+".into()
        );
    }

    if openssl_version.contains("1.1.1") && !openssl_version.contains("1.1.1w") {
        report.warnings.push(
            "OpenSSL version may be affected by multiple CVEs. Update to 1.1.1w+ or 3.0+".into()
        );
    }

    report
}

#[derive(Default)]
pub struct SecurityReport {
    pub sqlcipher_version: String,
    pub openssl_version: String,
    pub warnings: Vec<String>,
}

---

Secure Memory Handling

Memory Locking and Protection

use zeroize::{Zeroize, ZeroizeOnDrop};
use std::ops::{Deref, DerefMut};

/// A buffer that is locked in memory and zeroed on drop
#[derive(ZeroizeOnDrop)]
pub struct SecureBuffer {
    data: Vec<u8>,
}

impl SecureBuffer {
    pub fn new(size: usize) -> Result<Self, std::io::Error> {
        let mut data = vec![0u8; size];

        // Lock memory to prevent swapping (Unix)
        #[cfg(unix)]
        unsafe {
            libc::mlock(data.as_ptr() as *const libc::c_void, size);
        }

        Ok(Self { data })
    }
}

impl Drop for SecureBuffer {
    fn drop(&mut self) {
        // Unlock memory before zeroing
        #[cfg(unix)]
        unsafe {
            libc::munlock(self.data.as_ptr() as *const libc::c_void, self.data.len());
        }
        // ZeroizeOnDrop handles zeroing
    }
}

impl Deref for SecureBuffer {
    type Target = [u8];
    fn deref(&self) -> &[u8] {
        &self.data
    }
}

impl DerefMut for SecureBuffer {
    fn deref_mut(&mut self) -> &mut [u8] {
        &mut self.data
    }
}

---

Migration from Unencrypted SQLite

Encrypting Existing Database

/// Migrate unencrypted SQLite database to encrypted SQLCipher
pub fn encrypt_existing_database(
    source_path: &Path,
    encrypted_path: &Path,
    key: &Zeroizing<String>
) -> Result<(), MigrationError> {
    // Step 1: Open unencrypted database
    let source = Connection::open(source_path)
        .map_err(MigrationError::Source)?;

    // Step 2: Attach encrypted database
    let attach_sql = format!(
        "ATTACH DATABASE '{}' AS encrypted KEY {}",
        encrypted_path.display(),
        key.as_str()
    );

    source.execute_batch(&attach_sql)
        .map_err(MigrationError::Attach)?;

    // Step 3: Configure encryption settings on new database
    source.execute_batch("
        -- Set SQLCipher 4 compatibility on attached database
        PRAGMA encrypted.cipher_compatibility = 4;
        PRAGMA encrypted.cipher_memory_security = ON;
    ").map_err(MigrationError::Config)?;

    // Step 4: Export all data
    source.execute_batch("SELECT sqlcipher_export('encrypted')")
        .map_err(MigrationError::Export)?;

    // Step 5: Detach
    source.execute_batch("DETACH DATABASE encrypted")
        .map_err(MigrationError::Detach)?;

    // Step 6: Verify encrypted database
    let encrypted = Connection::open(encrypted_path)
        .map_err(MigrationError::Verify)?;
    encrypted.pragma_update(None, "key", key.as_str())
        .map_err(MigrationError::Verify)?;

    let page_size: i32 = encrypted
        .pragma_query_value(None, "cipher_page_size", |row| row.get(0))
        .map_err(MigrationError::Verify)?;

    if page_size == 0 {
        return Err(MigrationError::EncryptionFailed);
    }

    // Step 7: Securely delete original (optional)
    // secure_delete_file(source_path)?;

    Ok(())
}

#[derive(Debug)]
pub enum MigrationError {
    Source(rusqlite::Error),
    Attach(rusqlite::Error),
    Config(rusqlite::Error),
    Export(rusqlite::Error),
    Detach(rusqlite::Error),
    Verify(rusqlite::Error),
    EncryptionFailed,
}

SQLCipher Threat Model

Asset Identification

Primary Assets

1. Encrypted Database Content - User data, credentials, sensitive information 2. Encryption Keys - Master key, backup keys, derived keys 3. Key Derivation Parameters - Salt, iteration count 4. Database Schema - Structure can reveal data types

Secondary Assets

1. Backup Files - May contain historical data 2. WAL/SHM Files - Temporary data during transactions 3. Memory - Keys and decrypted data in RAM 4. Logs - May contain metadata about access patterns

---

Threat Actors

---

Attack Vectors & Mitigations

1. Brute Force Key Derivation

Threat: Attacker attempts to guess password/key through brute force.

Attack Scenario:

# Attacker obtains encrypted database file
# Attempts to brute force the password
for password in wordlist:
    key = pbkdf2(password, salt, iterations)
    if try_decrypt(database, key):
        print(f"Password found: {password}")

Mitigations:

Implementation:

// Use Argon2id with memory-hard parameters
let argon2 = Argon2::new(
    Algorithm::Argon2id,
    Version::V0x13,
    Params::new(65536, 3, 4, Some(32)).unwrap()  // 64MB memory
);

2. Memory Extraction

Threat: Attacker extracts encryption key from process memory.

Attack Scenario:

• Memory dump via debugging
• Cold boot attack
• Malware reading process memory
• Core dump after crash

Mitigations:

Implementation:

use zeroize::Zeroizing;

// Key is automatically zeroed when dropped
let key = Zeroizing::new(derive_key(password)?);
conn.pragma_update(None, "key", key.as_str())?;
// key goes out of scope and is zeroed

// Enable SQLCipher memory security
conn.pragma_update(None, "cipher_memory_security", "ON")?;

3. Side-Channel Attacks

Threat: Attacker infers key material through timing or power analysis.

Attack Scenarios:

• Timing attacks on key comparison
• Cache timing attacks
• Power analysis during encryption

Mitigations:

4. Key Storage Compromise

Threat: Attacker obtains stored key from keychain or file.

Attack Scenario:

# On macOS, if not properly secured
security find-generic-password -s "myapp-encryption" -w

Mitigations:

5. Backup Key Exposure

Threat: Backup files encrypted with weak or exposed keys.

Mitigations:

6. Dependency Vulnerabilities

Threat: CVEs in SQLite or OpenSSL compromise security.

Recent Examples:

• CVE-2020-27207: SQLCipher use-after-free
• CVE-2023-2650: OpenSSL DoS
• CVE-2024-0232: SQLite use-after-free

Mitigations:

7. Unencrypted Artifacts

Threat: Sensitive data leaks through unencrypted temporary files.

Attack Vectors:

• SQLite temp files
• Crash dumps
• Swap space
• Debug logs

Mitigations:

---

Defense in Depth Strategy

Layer 1: Application Security

• Input validation
• Parameterized queries
• Error handling without data leakage

Layer 2: Cryptographic Security

• Strong key derivation (Argon2id)
• AES-256 encryption
• HMAC integrity verification

Layer 3: Key Management

• OS keychain storage
• Memory zeroization
• Key rotation capability

Layer 4: System Security

• File permissions (600)
• Memory locking
• Swap encryption

Layer 5: Operational Security

• Dependency monitoring
• Security logging
• Incident response plan

---

Key Compromise Response Plan

Immediate Actions (0-1 hour)

1. Assess scope - Which keys are compromised? 2. Revoke access - Disable compromised keys if possible 3. Preserve evidence - Log all access attempts

Short-term Actions (1-24 hours)

1. Rotate keys - Generate new keys for all databases 2. Re-encrypt data - Use new keys for all data 3. Notify users - If user data potentially exposed 4. Update stored keys - Replace in all key stores

Long-term Actions (1-7 days)

1. Root cause analysis - How was key compromised? 2. Security improvements - Prevent recurrence 3. Audit access - Review all historical access 4. Documentation - Update security procedures

Key Rotation Procedure

// Emergency key rotation
pub fn emergency_key_rotation(
    db_path: &Path,
    compromised_key: &Zeroizing<String>,
    new_key: &Zeroizing<String>
) -> Result<()> {
    // 1. Create backup first
    let backup_path = create_timestamped_backup(db_path)?;

    // 2. Open with compromised key
    let conn = Connection::open(db_path)?;
    conn.pragma_update(None, "key", compromised_key.as_str())?;

    // 3. Re-encrypt with new key
    conn.pragma_update(None, "rekey", new_key.as_str())?;

    // 4. Verify
    drop(conn);
    let conn = Connection::open(db_path)?;
    conn.pragma_update(None, "key", new_key.as_str())?;
    conn.query_row("SELECT 1", [], |_| Ok(()))?;

    // 5. Update key store
    update_stored_key(new_key)?;

    // 6. Log rotation event
    log_security_event("emergency_key_rotation", "completed");

    Ok(())
}

---

Security Monitoring

Events to Log

pub enum SecurityEvent {
    KeyDerivation { user: String, success: bool },
    DatabaseOpen { path: String, success: bool },
    KeyRotation { user: String, success: bool },
    BackupCreated { path: String },
    AuthenticationFailure { attempts: u32 },
    IntegrityCheckFailed { error: String },
}

pub fn log_security_event(event: SecurityEvent) {
    match event {
        SecurityEvent::AuthenticationFailure { attempts } if attempts > 3 => {
            // Alert on potential brute force
            alert_security_team("Multiple auth failures detected");
        }
        SecurityEvent::IntegrityCheckFailed { error } => {
            // Alert on potential tampering
            alert_security_team(&format!("Database integrity check failed: {}", error));
        }
        _ => {}
    }

    // Always log to audit trail
    audit_log::record(event);
}

Alerting Thresholds

---

Compliance Mapping

GDPR (Article 32)

• [x] Encryption of personal data
• [x] Ability to ensure confidentiality
• [x] Ability to restore data (backups)

HIPAA (Security Rule)

• [x] Encryption at rest
• [x] Access controls
• [x] Audit logging
• [x] Integrity controls

PCI-DSS

• [x] Strong cryptography (AES-256)
• [x] Key management procedures
• [x] Protection of cryptographic keys