SQLCipher Encrypted Database Expert — Claude Code skill
The SQLCipher Encrypted Database Expert skill guides SQLCipher database work, from key handling and rotation to SQLite migrations. Use it when building applications that store sensitive data locally.
Unlicense 216 installs ★ 48 updated 2025-12-06
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a claude-codeWhat SQLCipher Encrypted Database Expert does
The skill recommends a test-first workflow. Its examples check that database files are encrypted, the wrong key fails, key rotation preserves data, and password-based derivation returns a usable key. The implementation examples use Python, SQLite, and Rust.
Its security guidance covers AES-256-CBC, HMAC integrity checks, OS credential storage, and clearing keys from memory after use. The Rust dependency example includes rusqlite with bundled-sqlcipher, zeroize, and keyring; argon2 is an optional choice. Before changing keys, migrating a database, or assessing threats, the skill directs the agent to read the matching file under references/. Hardcoding encryption keys goes against its stated guidance.
What it helps with
- encrypt a local SQLite database with SQLCipher
- test that a wrong key cannot open a database
- rotate a key while preserving data and making a backup
- derive a database key from a password and salt
- verify that database contents are not stored in plaintext
Example prompts for the agent
- Design a SQLCipher database and write tests for its encryption behavior first.
- Add SQLCipher key rotation with a backup, then test that the data survives.
- Plan a migration from plain SQLite to SQLCipher and list the checks it needs.
How to install SQLCipher Encrypted Database Expert
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a claude-code2Manually: put the files in place
~/.claude/skills/sqlcipher/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/sqlcipher/
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a codex2Manually: put the files in place
.agents/skills/sqlcipher/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a cursor2Manually: put the files in place
.cursor/skills/sqlcipher/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install martinholovsky/claude-skills-generator skills/sqlcipher2Manually: put the files in place
.github/skills/sqlcipher/SKILL.md1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a gemini-cli2Platform CLI
gemini skills install https://github.com/martinholovsky/claude-skills-generator --path skills/sqlcipherManually: put the files in place
.devin/skills/sqlcipher/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about SQLCipher Encrypted Database Expert
How do I install SQLCipher Encrypted Database Expert in Claude Code?
Run npx skills add martinholovsky/claude-skills-generator --skill sqlcipher-encrypted-database-expert -a claude-code, then npx skills add martinholovsky/claude-skills-generator --skill sqlcipher -a claude-code. The skill file is at ~/.claude/skills/sqlcipher/SKILL.md.
What does SQLCipher Encrypted Database Expert do?
It guides implementation and testing for SQLCipher databases, including key management, rotation, and migration from SQLite. The workflow starts with tests and stresses keeping encryption keys out of source code.
Which Rust dependencies does the skill use?
Its Cargo.toml example uses rusqlite with bundled-sqlcipher, zeroize, and keyring. It lists argon2 as an optional dependency.
Source text
SKILL.md · Unlicense
---
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
© JARVIS AI Assistant · Unlicense · martinholovsky/claude-skills-generator