SQLite databases, by default, are plain text files. Anyone with file system access can read them. SQLCipher is a popular extension that adds Transparent Data Encryption (TDE) to SQLite, meaning your database file is encrypted at rest, and decrypted on the fly as you query it, all without application code changes.
Let’s see it in action. Imagine you have a regular SQLite database my_database.db with a table users and some sensitive data:
-- Create a regular SQLite database
CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT);
INSERT INTO users (username, password) VALUES ('alice', 's3cr3tP@ssw0rd');
If you open my_database.db with a hex editor, you’d see s3cr3tP@ssw0rd clearly.
Now, let’s create an encrypted version using SQLCipher. You’ll need to compile your application with SQLCipher or use a SQLCipher-enabled SQLite binary. The key difference is how you open the database:
-- Open an encrypted SQLite database with SQLCipher
PRAGMA key = 'my_super_secret_key_123';
CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT);
INSERT INTO users (username, password) VALUES ('alice', 's3cr3tP@ssw0rd');
.save encrypted_database.db
Now, if you try to open encrypted_database.db with a regular SQLite tool or a hex editor, you’ll see gibberish. To access the data, you must provide the correct key:
-- Attempt to open without the key (will fail or show garbage)
-- Open with SQLCipher
PRAGMA key = 'my_super_secret_key_123';
SELECT * FROM users; -- This will now return ('alice', 's3cr3tP@ssw0rd')
SQLCipher works by intercepting read and write operations at the page level. When data is written to disk, it’s encrypted using AES-256 in Cipher Block Chaining (CBC) mode. When data is read, it’s decrypted using the same key. The PRAGMA key command is the primary way to set the encryption key. If you don’t set it, SQLCipher behaves like regular SQLite.
The power of SQLCipher lies in its transparency. Your application code that interacts with SQLite doesn’t need to know about encryption. It just executes standard SQL commands. The SQLCipher library handles the encryption and decryption behind the scenes, provided the correct key is set. This is particularly useful for mobile applications where the database file is often stored on the device and is vulnerable to physical access.
SQLCipher supports several key derivation functions (KDFs) for generating the encryption key from a password. The default is PBKDF2 with 640,000 iterations. You can change this using PRAGMA kdf_iter = <number>;. A higher number of iterations makes brute-force attacks on the password much harder, but also increases the time it takes to open the database. For example, PRAGMA kdf_iter = 280000; uses fewer iterations. You can also specify the KDF algorithm itself with PRAGMA kdf_alg = <alg>; (e.g., pbkdf2, scrypt, argon2).
The most common pitfall with SQLCipher is key management. If you lose the key, your data is irrecoverable. Forgetting the key is the only way to permanently lose your data in SQLCipher. There’s no backdoor, no master key. The key pragma can accept a raw binary key (e.g., PRAGMA key = x'0123456789abcdef0123456789abcdef';) or a password string. When a password string is provided, SQLCipher uses a KDF to derive the actual encryption key. The hexkey pragma can be used to set a raw key directly as a hex string, bypassing KDF.
When you open an existing encrypted database with a new key, you’re not actually re-encrypting the entire database file on disk. Instead, SQLCipher uses a technique called "key wrapping." The old key is used to decrypt pages as they are read, and then the newly provided key is used to encrypt them before they are written back. This means the entire database is effectively re-keyed as it’s accessed.
If you’re migrating from unencrypted SQLite to SQLCipher, you can do it in place. First, open the unencrypted database, set the new key, and then immediately save the database. SQLCipher will handle the encryption.
The next hurdle you’ll likely encounter is understanding the implications of key derivation and how to securely store or manage the keys themselves, especially in distributed or multi-user environments.