We often field questions about how SQLCipher encryption works. In one common scenario, a developer wants to convert an existing standard SQLite database to an encrypted SQLCipher database. For example, this might be a requirement for an application that was not previously using SQLCipher and must convert an insecure database to use SQLCipher full database encryption during an upgrade.
In such cases, developers may initially attempt to open a standard SQLite database and then call sqlite3_key or PRAGMA key, thinking that SQLCipher will open the existing database and rewrite it using encryption. That is not possible, however, and such attempts will be met with a “file is encrypted or is not a database” error (code 26 / SQLITE_NOTADB).
The reason is that SQLCipher encryption works on a per-page basis for efficiency, and sqlite3_key and PRAGMA key can only be used:
It follows that the correct way to convert an existing database to use SQLCipher is to create a new, empty, encrypted database, then copy the data from the insecure database to the encrypted database. Once complete, an application can simply remove the original plaintext database and use the encrypted copy from then on.
SQLCipher has supported ATTACH between encrypted and plaintext databases since its initial release, so it has always been possible to do this conversion on a table-by-table basis. However, an even easier option was added in SQLCipher 2.0 in the form of the sqlcipher_export() convenience function.
The purpose of sqlcipher_export() is to duplicate the entire contents of one database into another attached database. It includes all database objects: the schema, triggers, virtual tables, and all data. This makes it very easy to migrate from a standard non-encrypted SQLite database to a SQLCipher encrypted database, or back in the other direction.
To use sqlcipher_export() to encrypt an existing database, first open the standard SQLite database, but do not provide a key. Next, ATTACH a new encrypted database, and then call sqlcipher_export() in a SELECT statement, passing the name of the attached database you want to write the main database schema and data to.
$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'newkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;
Finally, securely delete the existing plaintext database, and then open the new encrypted database as usual using sqlite3_key or PRAGMA key.
The same process also works in reverse to create a plaintext, fully decrypted, copy of an encrypted SQLCipher database that can be opened with standard SQLite. Pass an empty key to ATTACH DATABASE to disable encryption for the output database.
$ ./sqlcipher encrypted.db
sqlite> PRAGMA key = 'testkey';
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';
sqlite> SELECT sqlcipher_export('plaintext');
sqlite> DETACH DATABASE plaintext;
If you are using SQLCipher Commercial or Enterprise Edition from the sqlcipher command-line shell, apply the license code using PRAGMA cipher_license before running any other commands, or the sqlcipher_export() call will fail with a “Runtime error: access to … is prohibited” or SQLITE_AUTH (23) error.
$ ./sqlcipher plaintext.db
sqlite> PRAGMA cipher_license = 'license-code-goes-here';
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'newkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;
You can also add the PRAGMA cipher_license statement to a .sqliterc file in your home directory (for example, ~/.sqliterc on macOS and Linux, or C:\Users\username\.sqliterc on Windows). The shell reads that file at startup and applies the license automatically, so you do not need to paste it each time.
The sqlcipher_export() function can also be used in specialized cases to change or customize SQLCipher database settings. These advanced uses are covered further in the API documentation.