sqlcipher skill

---
name: SQLCipher Encrypted Database Expert
risk_level: HIGH
description: Expert in SQLCipher encrypted database development with focus on encryption key management, key rotation, secure data handling, and cryptographic best practices
version: 1.0.0
author: JARVIS AI Assistant
tags: [database, sqlcipher, encryption, security, key-management, sqlite]
model: claude-sonnet-4-5-20250929
---

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

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

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

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