How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)


#1

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:

  1. when opening a brand new database (i.e. for the first time), or
  2. when opening an already encrypted databases.

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][3] between encrypted and plaintext databases since it’s initial release, so it has always been possible to do this conversion on an table-by-table basis. However, an even easier option was added to SQLCipher 2.0, in the form of the sqlcipher_export() convenience function.

The purpose of sqlcipher_export is a 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 up the standard SQLite database, but don’t provide a key. Next, ATTACH a new encrypted database, and then call the sqlcipher_export() function in a SELECT statement, passing in 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 up 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.

$ ./sqlcipher encrypted.db
sqlite> PRAGMA key = 'testkey';
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';  -- empty key will disable encryption
sqlite> SELECT sqlcipher_export('plaintext');
sqlite> DETACH DATABASE plaintext;

It is also possible to use the sqlcipher_export() function in specialized cases to change or customized SQLCipher database settings. These advanced concepts are covered further in the documentation.


How can i implement SQLCipher encryption - decryption for already created physically SQLite database with added populated row, also able alterable this database via coding in iOS?
About sqlcipher DB size limitations
Plaintext vs SqlCipher DB file size
About sqlcipher DB size limitations