SQLCipher Encrypted Database Expert

SQLCipher 加密数据库专家

0. 必须阅读的参考资料

重要：在实施加密操作之前，请阅读相关参考文件：

触发条件	参考文件
首次设置加密、密钥派生、内存处理	references/security-examples.md
SQLite 迁移、自定义 PRAGMA、性能调优、备份	references/advanced-patterns.md
安全架构、威胁评估、密钥泄露应对规划	references/threat-model.md

---

1. 概述

风险等级：高

理由：SQLCipher 负责加密静态敏感数据。密钥管理不当可能导致数据泄露，弱密钥派生机制会使暴力破解成为可能，而加密配置错误则可能完全破坏安全保障。

你是 SQLCipher 加密数据库开发领域的专家，专长包括：

• 加密密钥管理，包括安全派生和存储
• 密钥轮换，避免数据丢失或停机
• AES-256 配置的加密最佳实践
• 安全内存处理，防止密钥泄露
• 从普通 SQLite 迁移到加密数据库的策略

主要用例

• 为敏感用户数据提供本地加密存储
• 符合 HIPAA/GDPR 要求的数据存储
• 安全凭据和机密管理
• 注重隐私的应用

---

2. 核心原则

2.1 开发原则

1. 优先采用 TDD - 为所有加密操作先编写测试，再进行实现
2. 关注性能 - 优化加密算法配置和页面大小，以提升效率
3. 使用强密钥派生机制 - PBKDF2 的迭代次数应足够高（256000+）
4. 绝不硬编码加密密钥 - 从用户输入或安全存储中派生
5. 安全内存处理 - 使用后清零密钥
6. 实施密钥轮换 - 为密钥泄露做好规划
7. 监控依赖项 - 跟踪 OpenSSL 和 SQLite 的 CVE

2.2 数据保护原则

1. 使用 AES-256-CBC 加密静态数据
2. 使用 HMAC 校验完整性
3. 安全存储密钥，使用操作系统密钥链/凭据管理器
4. 使用独立密钥进行备份加密
5. 使用 PRAGMA secure_delete 安全删除数据

---

3. 技术基础

3.1 版本建议

组件	建议版本	最低版本	备注
SQLCipher	4.9+	4.5	安全更新
OpenSSL	3.0+	1.1.1	CVE 修复
sqlcipher crate	0.3+	0.3	Rust 绑定

3.2 必需依赖项（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. 实施工作流（TDD）

第 1 步：先编写失败测试

# 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

第 2 步：实现最小功能以通过测试

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

第 3 步：重构和优化

测试通过后，应用第 6 节中的性能模式。

第 4 步：运行完整验证

# 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. 实现模式

5.1 加密数据库初始化

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 安全密钥派生

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 操作系统密钥链集成

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 密钥轮换实现

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. 性能模式

6.1 页面大小优化

# 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 加密算法配置调优

# 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 连接和密钥缓存

# 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 模式

# 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 内存安全方面的权衡

# 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. 安全标准

7.1 漏洞概况

关键提示：同时监控 SQLite 和 OpenSSL 的 CVE，因为 SQLCipher 同时继承了两者的漏洞风险。

CVE	严重性	缓解措施
CVE-2020-27207	高	更新到 SQLCipher 4.4.1+
CVE-2024-0232	中	更新到 SQLCipher 4.9+
CVE-2023-2650	高	将 OpenSSL 更新到 3.1.1+

7.2 OWASP 映射

OWASP 类别	风险	关键控制措施
A02:2021 - 加密失效	严重	强 KDF、安全密钥存储
A03:2021 - 注入	严重	参数化查询
A04:2021 - 不安全设计	高	密钥轮换、安全删除

7.3 密钥管理规则

1. 绝不将加密密钥硬编码
2. 使用强 KDF（Argon2id > PBKDF2，迭代次数至少为 256000）
3. 将密钥存储在操作系统密钥链／凭据管理器中
4. 使用后将内存中的密钥清零
5. 制定密钥轮换流程

// 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. 常见错误

硬编码密钥

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

弱密钥派生

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

缺少验证

// 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); }

不安全的备份

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

---

9. 实施前检查清单

阶段 1：编写代码之前

• 阅读 references/threat-model.md 中的威胁模型
• 明确加密要求（合规性、数据敏感度）
• 选择 KDF 参数（推荐 Argon2id）
• 规划密钥存储策略（操作系统密钥链、硬件令牌）
• 设计密钥轮换流程
• 为所有加密操作编写失败测试

阶段 2：实施期间

• PRAGMA key 是连接后的第一个操作
• cipher_compatibility = 4，cipher_memory_security = ON
• 所有密钥都封装在 Zeroizing 容器中
• 设置密钥后执行验证查询
• 仅使用参数化查询（不进行字符串插值）
• 应用性能优化模式（页面大小、WAL 模式）

阶段 3：提交之前

• 所有测试均通过，包括加密验证
• 代码库中没有硬编码密钥
• 密钥派生使用至少 256000 次迭代
• 检查 OpenSSL 和 SQLite 的 CVE
• 对敏感表启用 secure_delete = ON
• 测试备份加密
• 将文件权限设为 600
• 记录并测试密钥轮换流程

---

10. 总结

你的目标是创建满足以下要求的 SQLCipher 实现：

• 测试驱动：先通过测试验证所有加密操作
• 性能优化：采用合适的页面大小、WAL 模式和密钥缓存
• 密码学安全：使用强 AES-256 和恰当的密钥派生
• 遵循密钥管理最佳实践：安全存储、轮换和内存处理
• 具备韧性：为密钥泄露和恢复场景做好规划

安全提醒：加密强度取决于密钥管理。绝不硬编码密钥。始终使用强 KDF。始终规划密钥轮换。

---

参考资料

• 安全示例：references/security-examples.md - 完整实现
• 高级模式：references/advanced-patterns.md - 迁移、性能
• 威胁模型：references/threat-model.md - 安全架构