Doc
SQLCipher is based on SQLite, and thus, the majority of the accessible API is identical to the C/C++ interface for SQLite 3. However, SQLCipher does add a number of security specific extensions in the form of PRAGMAs, SQL Functions and C Functions.
PRAGMA cipher_plaintext_header_size
Allocates a portion of the database header which will not be encrypted to allow identification as an SQLite database.
The size must be greater than 0, a multiple of the cipher block size, and less than the usable size of the first database page.
1
PRAGMA cipher_plaintext_header_size = 32;
This PRAGMA is primarily intended for use on iOS when a WAL mode database will be stored in a shared container. In this special case iOS actually examines a database file to determine whether it is an SQLite database in WAL mode. If it is, then iOS extends special privileges, allowing the application process to maintain a file lock on the main database while it is in the background. However, if iOS can’t determine the file type from the header because it contains random data like a SQLCipher database, then iOS will kill the application process when it attempts to background with a file lock.
In order to work around this issue, an iOS developer may provide instruction to SQLCipher to leave a portion of the database header unencrypted (plaintext). In this case SQLCipher will leave the specified number of bytes in the original SQLite file format and will only begin encrypting data after that.
The recommended offset is currently 32 - this is small enough to ensure that sensitive information like schema and data are not exposed, but will ensure that the important SQLite header segments are readable by iOS, i.e. the magic “SQLite Format 3\0” and the database read/write version numbers indicating a database is operating in WAL journal mode (offsets bytes 0 - 19). This will allow iOS to identify the file and will permit an application to background correctly without being killed.
It is important to note that SQLCipher normally stores the database salt (used for encryption and HMAC key derivation) in the first 16 bytes of the database file. When using PRAGMA cipher_plaintext_header_size, SQLCipher no longer has a place to store the salt, thus an application is responsible for managing it externally and providing it to SQLCipher.
The related PRAGMA cipher_salt allows an application to set and retrieve the salt programmatically. For instance, this can be used the first time a database is created to retrieve the randomly generated salt, so it can be stored away by the application (e.g. in the iOS Keychain, preferences, or a separate file). Then the next time a database is opened it can be used to provide the salt value back to SQLCipher in BLOB format (16 bytes, hex encoded):
1
2
3
PRAGMA key = 'test';
PRAGMA cipher_plaintext_header_size = 32;
PRAGMA cipher_salt = "x'01010101010101010101010101010101'";
An alternate means of providing the salt is to use raw key semantics. In this case, an application would provide 96 bytes hex encoded in BLOB format. The first 64 characters (32 bytes) will be used as the raw encryption key, and the remaining 32 characters (16 bytes) will be used as the salt:
1
PRAGMA key = "x'98483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C483648101010101010101010101010101010101'";
Note that PRAGMA cipher_plaintext_header_size must be called each time the database is opened and keyed.
Likewise, the salt must be stored externally to the database by the application and provided for initialization every time the database is opened (with the possible exception of the first time a database is created).
It is possible to migrate a database with an encrypted header to plaintext, however, it requires a very specific set of operations. The application must make note and store off the existing database salt stored in the first 16 bytes of the databse. The database must first be opened normally and an operation must occur to cause the header to be read. Next, PRAGMA cipher_plaintext_header_size should be used to set the size of the plaintext segement. Finally, an operation must occur to cause the header to be immediately re-written using the new plaintext header size (the easiest way to do this is by calling PRAGMA user_version which updates the header). The following example demonstrates the migration process:
1
2
3
4
5
6
7
8
sqlite> pragma key = 'test';
ok
sqlite> select count(*) from sqlite_master; -- trigger header read, currently it is encrypted
1
sqlite> PRAGMA cipher_salt; -- read current salt and save it for later use
5062fa73375626a23af92d3dce504b57
sqlite> PRAGMA cipher_plaintext_header_size = 32;
sqlite> PRAGMA user_version = 1; -- force header write
After that the database can be closed and re-opened using PRAGMA cipher_plaintext_header_size and PRAGMA cipher_salt.
PRAGMA cipher_salt
Retrieve or set the salt value for the database. The format is a 32 character hex string which will be converted into 16 bytes. This PRAGMA allows an application to retrieve or set the database salt value programatically. When used without assigning a value, it will return a hex encoded string of 32 characters, representing the 16 byte salt. When used to set the salt, it should be provided a 32 character hex encoded string using BLOB formatting. An example of explicitly setting a database salt is below.
1
PRAGMA cipher_salt = "x'01010101010101010101010101010101'";
Note that an application will not normally need to use this PRAGMA, as the salt is managed automatically by SQLCipher and stored in the first 16 bytes of the database file. This PRAGMA is should only be used in conjunction with PRAGMA cipher_plaintext_header_size.
sqlcipher_export()
sqlcipher_export is a convenience function that will duplicate the entire contents of the main database to an attached database including the schema, triggers, virtual tables, and all data.
It’s primary function is to make it easy to migrate from a non-encrypted database to an encrypted database, from an encrypted database to a non-encrypted database, or between the various options of encrypted database supported by SQLCipher.
To use sqlcipher_export, simply call the function in a SELECT statement, passing in the name of the target database you want to write the source database schema and data to.
An optional second parameter can be used to provide the source database to use. If the second parameter is not provided, the default source is the main database. The second parameter can be used to copy from an attached database to the main database.
Encrypt a Plaintext Database
$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;
Decrypt a SQLCipher database to a Plaintext Database
1
2
3
4
5
$ ./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;
Convert from a 3.x to 4.x Database
1
2
3
4
5
6
7
8
9
$ ./sqlcipher 1.1.x.db
sqlite> PRAGMA key = 'testkey';
sqlite> PRAGMA cipher_page_size = 1024;
sqlite> PRAGMA kdf_iter = 64000;
sqlite> PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
sqlite> PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
sqlite> ATTACH DATABASE 'sqlcipher-4.db' AS sqlcipher4 KEY 'testkey';
sqlite> SELECT sqlcipher_export('sqlcipher4');
sqlite> DETACH DATABASE sqlcipher4;
Changing Cipher Settings
1
2
3
4
5
6
7
8
$ ./sqlcipher encrypted.db
sqlite> PRAGMA key = 'testkey';
sqlite> ATTACH DATABASE 'newdb.db' AS newdb KEY 'newkey';
sqlite> PRAGMA newdb.cipher_page_size = 4096;
sqlite> PRAGMA newdb.cipher = 'aes-256-cfb';
sqlite> PRAGMA newdb.kdf_iter = 10000;
sqlite> SELECT sqlcipher_export('newdb');
sqlite> DETACH DATABASE newdb;
Copying an attached plaintext database to a new empty encrypted main database
1
2
3
4
5
$ ./sqlcipher encrypted.db
sqlite> PRAGMA key = 'testkey';
sqlite> ATTACH DATABASE 'plain.db' AS plain KEY '';
sqlite> SELECT sqlcipher_export('main', 'plain');
sqlite> DETACH DATABASE plain;