How to encrypt a plaintext SQLite database to use SQLCipher
We’ve fielded several questions on the SQLCipher mailing list recently about how to convert a standard SQLite database to an encrypted SQLCipher database. This is a pretty common requirement for applications with existing databases that need to be converted to use SQLCipher full database encryption. Right now there are two options to meet these needs.
Option 1: Experimental Rekey
We’ve done some work on an experimental branch of the SQLCipher repository to add a function to rekey plaintext databases. This version is based on an older version of SQLite (3.1.17), but it allows you to open a standard database and run “PRAGMA rekey=’passphrase’;” or call sqlite3_rekey() to encrypt it. The code is up under the “rekey-plaintext branch in GitHub":http://github.com/sjlombardo/sqlcipher/tree/rekey-plaintext.
The main drawback to this approach is that it is brittle. The current rekey functionality essentially relies on a modified vacuum command. Unfortunately this approach is not easily portable between versions. The code is out there, but your mileage may vary.
Option 2: Attached Database (Recommended)
SQLCipher 1.1.1 added support for attaching an encrypted database to an unencrypted database to copy data between them. For instance, assume you have an standard unencrypted SQLite database called unencrypted.db with a single table, t1(a,b). To create an encrypted copy you could open up the databases and run the following:
ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; -- create a new encrypted database CREATE TABLE encrypted.t1(a,b); -- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master) INSERT INTO encrypted.t1 SELECT * FROM t1; -- copy data from the existing tables to the new tables in the encrypted database DETACH DATABASE encrypted;
It is fairly trivial to get a list of all schema objects from the sqlite_master table you would just repeat the CREATE TABLE / INSERT cycle once for each table. Then you can delete the unencrypted database and re-open the encrypted version with the sqlite3_key or PRAGMA key. This approach requires more work that just calling rekey, but it is be much more stable across versions.
In the mean time, we are working on finding a way to improve the built in rekey functionality in future versions. Keep an eye out here, or join the SQLCipher mailing list to keep up to date on the latest development.
