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) [ 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

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