Quick Start (5 min): Encrypting SQLite with SQLCipher
Six months ago, our desktop client failed a routine security audit. Sensitive customer tokens sat in plain SQLite files on user drives. Anyone with a hex editor could open the .db file and read session tokens in plain ASCII. We swapped our storage engine to SQLCipher, and by the end of the day, our local data was locked behind 256-bit AES encryption.
For Python and desktop environments, install the binary distribution:
pip install sqlcipher3-binary
Securing a new database takes two lines of setup immediately after opening the connection:
import sqlcipher3
# Open connection to the database file
conn = sqlcipher3.connect("secure_app.db")
cursor = conn.cursor()
# Set the encryption key before executing any queries
cursor.execute("PRAGMA key = 'my-super-secret-passphrase';")
# Create tables and insert records as normal
cursor.execute("CREATE TABLE secrets (id INTEGER PRIMARY KEY, token TEXT);")
cursor.execute("INSERT INTO secrets (token) VALUES ('oauth_access_token_xyz');")
conn.commit()
conn.close()
Try inspecting secure_app.db with standard SQLite tools. The CLI immediately aborts with file is not a database. Tables, indexes, schemas, and WAL journals remain fully scrambled until you supply the right key.
Architecture: Page-Level Cryptography
Standard SQLite writes raw data to 4,096-byte pages. SQLCipher intercepts this process at SQLite’s Virtual File System (VFS) layer. Before any page touches disk, SQLCipher wraps it in an authenticated crypto container.
How SQLCipher Protects Every Page
SQLCipher encrypts databases page by page rather than running a streaming cipher over the entire file. This keeps SQLite fast. Reading row 50,000 takes the exact same seek time as vanilla SQLite, because the engine decrypts only the target page.
- Cipher Algorithm: 256-bit AES in CBC (Cipher Block Chaining) mode.
- Per-Page Initialization Vector (IV): Every page stores a unique 16-byte random IV. Two pages containing identical data will produce completely different ciphertext on disk.
- HMAC Integrity Checks: SQLCipher 4 reserves 64 bytes at the end of each 4KB page for an HMAC-SHA512 signature. The engine verifies this digest before attempting decryption. If an attacker flips a single bit on disk, SQLCipher rejects the page instantly instead of returning corrupted data.
Key Derivation via PBKDF2
SQLCipher never uses your passphrase directly as an encryption key. Instead, it feeds your string into PBKDF2-HMAC-SHA512 alongside a 16-byte salt stored in the database header. In SQLCipher 4, this function runs 256,000 iterations by default. That computational cost protects offline files against brute-force dictionary attacks.
Production Workflows: Migrations, Raw Keys, and Mobile
Basic tutorials cover simple setups, but production deployments bring distinct challenges.
Migrating Plaintext Databases to SQLCipher
You cannot encrypt an existing SQLite file in place using PRAGMA key. Instead, attach the unencrypted database to a blank, encrypted target and copy it with sqlcipher_export():
import sqlcipher3
# Initialize the encrypted target
conn = sqlcipher3.connect("encrypted.db")
cursor = conn.cursor()
cursor.execute("PRAGMA key = 'ProductionPassphrase2026!';")
# Attach the legacy cleartext database
cursor.execute("ATTACH DATABASE 'legacy_plaintext.db' AS plaintext KEY '';")
# Export the entire schema and all rows
cursor.execute("SELECT sqlcipher_export('main', 'plaintext');")
conn.commit()
# Clean up the legacy attachment
cursor.execute("DETACH DATABASE plaintext;")
conn.close()
Bypassing PBKDF2 with Raw Hex Keys
Those 256,000 PBKDF2 rounds take real CPU time. If your app already pulls a secure 256-bit key from the OS Keychain or Android Keystore, skip key derivation entirely. Pass a 64-character raw hex string prefixed with x':
# Supply a 32-byte (256-bit) raw key to skip PBKDF2 entirely
raw_hex = "x'2DD29CA851E3B39E8A29A26A30F2AC8A2DD29CA851E3B39E8A29A26A30F2AC8A'"
cursor.execute(f"PRAGMA key = \"{raw_hex}\";")
Mobile Integration (Android Room)
Avoid compiling custom OpenSSL binaries on Android. Use Zetetic’s official Android bindings alongside Android Jetpack Room:
val passphrase = getPassphraseFromKeystore()
val factory = SupportFactory(SQLiteDatabase.getBytes(passphrase.toCharArray()))
val db = Room.databaseBuilder(context, AppDatabase::class.java, "secure.db")
.openHelperFactory(factory)
.build()
Field Notes from 6 Months in Production
1. Cache Your Open Connections
On mid-tier Android phones (like a Snapdragon 778G), 256,000 PBKDF2 rounds take roughly 85 to 130 milliseconds. On an Apple M2, that drop is closer to 8 milliseconds. Never open and close database handles inside high-frequency UI loops. Keep a singleton connection alive for the application’s lifecycle.
2. Tune the Page Cache
Once decrypted, pages live in RAM cleartext within SQLite’s memory cache. Read queries on cached pages run at native SQLite speeds. Expand your cache budget to prevent repeated disk I/O and crypto overhead:
-- Allocate roughly 20MB for the in-memory page cache
PRAGMA cache_size = -20000;
3. Stream Bulk Seed Data
Offline seeding often begins with raw customer or product dumps. When converting source CSV feeds into clean JSON fixtures, zero-knowledge browser utilities like the CSV to JSON tool on toolcraft.app (toolcraft.app/en/tools/data/csv-to-json) prevent confidential records from hitting external APIs.
Once transformed, load your records using batched parameter inserts inside explicit transactions. In our benchmarks, wrapping 10,000 inserts inside a single BEGIN TRANSACTION cut write times from 8.4 seconds down to 240 milliseconds.
4. Rotate Keys with PRAGMA rekey
Rotating an encryption key does not require creating a new database file. Run PRAGMA rekey on an authenticated connection:
# Authenticate with the existing key
cursor.execute("PRAGMA key = 'old-passphrase';")
# Confirm authorization succeeds by querying the schema table
cursor.execute("SELECT count(*) FROM sqlite_master;")
# Re-encrypt every page with the new key
cursor.execute("PRAGMA rekey = 'new-strong-passphrase';")
conn.commit()
SQLCipher writes fresh salt to the file header and encrypts existing pages in place. Your data stays safe, even through unplanned process interruptions.
