SQLCipher API

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 key
Set the key for use with the database
PRAGMA cipher
Change the encryption algorithm, key size, or mode
PRAGMA kdf_iter
Change the number of iterations used with PBKDF2 key derivation
PRAGMA cipher_default_kdf_iter
Change the default number of iterations used with PBKDF2 key derivation
PRAGMA cipher_page_size
Alter the page size used for the database from the default of 1024 bytes to improve performance for some query types.
PRAGMA cipher_use_hmac
Disable the use of per-page HMAC checks for backwards compatibility with SQLCipher 1.1.x on a specific database
PRAGMA cipher_default_use_hmac
Alter the default behavior for whether per-page HMAC will be used the next time a SQLCipher database is opened.
PRAGMA rekey
Change the encryption key for a SQLCipher database
ATTACH
Attach a new or existing database to the main database using a specific key.
sqlcipher_export()
Convenience function that can duplicate a database contents to an attached database with different settings.
PRAGMA cipher_migrate
Convenience function to perform an in-place upgrade from a SQLCipher 1.x or 2.x database to SQLCipher Version 3 default settings.
PRAGMA cipher_add_random
Add externally sourced random data to the crypto provider.
sqlite3_key()
C function providing an alternative to PRAGMA key
sqlite3_rekey()
C function providing an alternative to PRAGMA rekey

PRAGMA key 

The process of creating a new, encrypted database is called “keying” the database. SQLCipher uses just-in-time key derivation at the point it is first needed for an operation. This means that the key (and any options) must be set before the first operation on the database. As soon as the database is touched (e.g. SELECT, CREATE TABLE, UPDATE, etc.) and pages need to be read or written, the key is prepared for use.

Example 1: Passphrase with Key Derivation

The key itself can be a passphrase, which is converted to a key using PBKDF2 key derivation. The result is used as the encryption key for the database.

sqlite> PRAGMA key = 'passphrase';

Example 2: Raw Key Data (Without Key Derivation)

Alternatively, it is possible to specify an exact byte sequence using a blob literal. With this method, it is the calling application's responsibility to ensure that the data provided is a 64 character hex string, which will be converted directly to 32 bytes (256 bits) of key data.

sqlite> PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";

Testing the Key

When opening an existing database, PRAGMA key will not immediately throw an error if the key provided is incorrect. To test that the database can be successfully opened with the provided key, it is necessary to perform some operation on the database (i.e. read from it) and confirm it is success.

The easiest way to do this is select off the sqlite_master table, which will attempt to read the first page of the database and will parse the schema.

sqlite> PRAGMA key = 'passphrase';
sqlite> SELECT count(*) FROM sqlite_master; -- if this throws an error, the key was incorrect. If it succeeds and returns a numeric value, the key is correct;

The same check can be implemented in C code

sqlite3_key(database, "test123", 7);
if (sqlite3_exec(database, "SELECT count(*) FROM sqlite_master;", NULL, NULL, NULL) == SQLITE_OK) {
  // key is correct.
} else {
  // key is incorrect
}

Implementation Notes

  • PRAGMA key should generally be called as the first operation on a database.

PRAGMA cipher

SQLCipher uses aes-256-cbc as the default cipher and mode of operation. It is possible to change this, though not generally recommended, using PRAGMA cipher.

Example

sqlite> PRAGMA key = 'blue valentines';
sqlite> PRAGMA cipher = 'aes-256-cfb';

Implementation Notes

  • PRAGMA cipher must be called after PRAGMA key and before the first actual database operation or it will have no effect.
  • If a non-default value is used PRAGMA cipher to create a database, it must also be called every time that database is opened.

PRAGMA kdf_iter

As previously noted, SQLCipher uses PBKDF2 key derivation to strengthen the key and make it resistent to brute force and dictionary attacks. The default configuration uses 64,000 PBKDF2 iterations (effectively 256,000 SHA1 operations). PRAGMA kdf_iter can be used to increase or decrease the number of iterations used.

Example

sqlite> PRAGMA key = 'blue valentines';
sqlite> PRAGMA kdf_iter = '10000';

Implementation Notes

  • PRAGMA kdf_iter must be called after PRAGMA key and before the first actual database operation or it will have no effect.
  • If a non-default value is used PRAGMA kdf_iter to create a database, it must also be called every time that database is opened.
  • We do not recommend reducing the number of iterations if a passphrase is in use.

PRAGMA cipher_default_kdf_iter

In some very specific cases, it is not possible to call PRAGMA kdf_iter as one of the first operations on a database. An example of this is when you want to apply a global default for all database operations, or when trying to ATTACH an older database to the main database. In these cases PRAGMA cipher_default_kdf_iter can be used to globally alter the default number of PBKDF2 iterations used when opening a database.

Example

./sqlcipher sqlcipher2.0.db
sqlite> PRAGMA cipher_default_kdf_iter = 4000;
sqlite> PRAGMA key = 's3cr37'; 

Implementation Notes

  • PRAGMA cipher_default_kdf_iter can be called at any time, before or after opening a database. However, it's setting will only take effect on the next database opened.

PRAGMA cipher_page_size

SQLCipher 2 introduced the new PRAGMA cipher_page_size that can be used to adjust the page size for the encrypted database. The default page size is 1024 bytes, but it can be desirable for some applications to use a larger page size for increased performance. For instance, some recent testing shows that increasing the page size can noticeably improve performance (5-30%) for certain queries that manipulate a large number of pages (e.g. selects without an index, large inserts in a transaction, big deletes).

To adjust the page size, call the pragma immediately after setting the key for the first time and each subsequent time that you open the database.

Example

sqlite> PRAGMA KEY = 'testkey';
sqlite> PRAGMA cipher_page_size = 4096;

Implementation Notes

  • PRAGMA cipher_page_size must be called after PRAGMA key and before the first actual database operation or it will have no effect.
  • If a non-default value is used PRAGMA cipher_page_size to create a database, it must also be called every time that database is opened.

PRAGMA rekey

To change the key on an existing encrypted database, it must first be unlocked with the current encryption key. Once the database is readable and writeable, PRAGMA rekey can be used to re-encrypt every page in the database with a new key.

Example

sqlite> PRAGMA key = 'old passphrase';
sqlite> PRAGMA rekey = 'new passphrase';

Implementation Notes

  • PRAGMA rekey must be called after PRAGMA key. It can be called at any time once the database is readable.
  • PRAGMA rekey can not be used to encrypted a standard SQLite database! It is only useful for changing the key on an existing database. Instead, use sqlcipher_export() to encrypt a plaintext database.
  • Previous versions of SQLCipher provided a PRAGMA rekey_cipher and code>PRAGMA rekey_kdf_iter. These are deprecated and should not be used. Instead, use sqlcipher_export().

PRAGMA cipher_use_hmac

SQLCipher 2.0 introduced a per-page HMAC to validate that the page data has not be tampered with. By default, when creating or opening a database using SQLCipher 2, SQLCipher will attempt to use an HMAC check. This change in database format means that SQLCipher 2 can't operate on version 1.1.x databases by default. Thus, in order to provide backward compatibility with SQLCipher 1.1.x, PRAGMA cipher_use_hmac can be used to disable the HMAC functionality on specific databases.

Example

sqlite> PRAGMA key = 'blue valentines';
sqlite> PRAGMA cipher_use_hmac = OFF;

Implementation Notes

  • PRAGMA cipher_use_hmac must be called immediately after PRAGMA key and before the first actual database operation or it will have no effect.
  • If a non-default value is used PRAGMA cipher_use_hmac to create a database, it must also be called every time that database is opened.

PRAGMA cipher_default_use_hmac

In some very specific cases, it is not possible to call PRAGMA cipher_use_hmac as one of the first operations on a database. An example of this is when trying to ATTACH a 1.1.x database to the main database. In these cases PRAGMA cipher_default_use_hmac can be used to globally alter the default use of HMAC when opening a database.

Example

./sqlcipher sqlcipher2.0.db
sqlite> PRAGMA key = 's3cr37'; -- opens using default setting, with HMAC on
sqlite> PRAGMA cipher_default_use_hmac = OFF;
sqlite> ATTACH DATABASE '1.1.x.db' AS remote key 's3cr37'; -- next open operation, the default for HMAC is off, and this database

Implementation Notes

  • PRAGMA cipher_default_use_hmac can be called at any time, before or after opening a database. However, it's setting will only take effect on the next database opened.
  • See this blog post for further details and usage examples.

ATTACH

It is possible to use a special KEY parameter to the ATTACH statement to specify that a database should be attached encrypted using a specific encryption key. This is very useful for copying and migrating data between databases.

Example 1: Attach an Encrypted Database to a Plaintext Database

$ ./sqlcipher plaintext.db 
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey'; 

Example 2: Attach an Encrypted Database using a Hex Key

$ ./sqlcipher plaintext.db 
sqlite> ATTACH DATABASE 'test2.db' AS db2 KEY "x'10483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C4836481'"; 

Example 3: Attach an Plaintext Database to an Encrypted Database

$ ./sqlcipher encrypted.db 
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY ''; -- empty key will disable encryption

Implementation Notes

  • If no KEY paramater is specified then the attached database will use the exact same raw key and database salt as the main database (or none if the main database is plaintext), but SQLCipher
  • In practice, this means that calling applications should provide the key on the ATTACH parameter when opening any existing databases that may use a different salt.

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 attached database you want to write the main database schema and data to.

Example 1: 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; 

Example 2: Decrypt a SQLCipher database to a Plaintext Database

$ ./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; 

Example 3: Convert from a 1.1.x to 2.0 Database with HMAC

$ ./sqlcipher 1.1.x.db 
sqlite> PRAGMA key = 'testkey'; 
sqlite> PRAGMA cipher_use_hmac = OFF; -- disable HMAC on main database
sqlite> ATTACH DATABASE '2.0.db' AS newdb;  -- new database will use default HMAC setting, ON, with same key as the main database
sqlite> SELECT sqlcipher_export('newdb'); 
sqlite> DETACH DATABASE newdb; 

Example 3: Changing Cipher Settings

$ ./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; 

Implementation Notes:

  • sqlcipher_export does not alter the user_version of the target database. Applications are free to do this themselves.

PRAGMA cipher_migrate

Major versions of SQLCipher have different default settings, and thus existing databases often need to be migrated or upgraded from older settings to a new version. PRAGMA cipher_migrate aids in the conversion from an old SQLCipher database, given that default configurations were previously used during database creation.

Below shows an example of migrating a 2x SQLCipher database to the new 3.0.0 format. SQLCipher will upgrade the database in place:

> ./sqlcipher 2xdatabase.db
> PRAGMA key = 'YourKeyGoesHere';
> PRAGMA cipher_migrate;

The cipher_migrate PRAGMA can upgrade both standard SQLCipher 1.x and 2.x databases. Note that if non-default settings, such as a different cipher or kdf_iter were used in the original database, a manual migration would be required with the use of sqlcipher_export.

PRAGMA cipher_add_random

Add externally sourced entropy to the the entropy pool of the current crypto provider. The format of the data must be provided as a blob literal containing a hex sequence where the value is prefixed with an 'x' followed by a single quote, then the hex sequence, finally terminated with a single quote. Below is an example of adding additional entropy to the entropy pool:


PRAGMA cipher_add_random = "x'deadbaad'";

sqlite3_key() and sqlite3_key_v2()

It is possible to set the key for use with a database handle programmatically without invoking the SQL PRAGMA key interface. This is often desirable when linking SQLCipher in with a C/C++ application. sqlite3_key() is actually called internally by the PRAGMA interface. The sqlite3_key_v2 call performs the same way as sqlite3_key, but sets the encryption key on a named database instead of the main database.

/*
** Specify the key for an encrypted database.  This routine should be
** called right after sqlite3_open().
**
** The code to implement this API is not available in the public release
** of SQLite.
*/
int sqlite3_key(
  sqlite3 *db,                   /* Database to be rekeyed */
  const void *pKey, int nKey     /* The key, and the length of the key in bytes */
);
int sqlite3_key_v2(
  sqlite3 *db,                   /* Database to be rekeyed */
  const char *zDbName,           /* Name of the database */
  const void *pKey, int nKey     /* The key */
);

sqlite3_rekey() sqlite3_rekey_v2()

It is possible to change the key used to encrypt a database programmatically without invoking the SQL PRAGMA rekey interface. sqlite3_rekey() is actually called internally by the PRAGMA interface. The sqlite3_rekey_v2 call performs the same way as sqlite3_rekey, but changes the key on a named database instead of the main database.<

/*
** Change the key on an open database.  If the current database is not
** encrypted, this routine will encrypt it.  If pNew==0 or nNew==0, the
** database is decrypted.
**
** The code to implement this API is not available in the public releasejavascript:noop()
** of SQLite.
*/
int sqlite3_rekey(
  sqlite3 *db,                   /* Database to be rekeyed */
  const void *pKey, int nKey     /* The new key, and the length of the key in bytes */
);
int sqlite3_rekey_v2(
  sqlite3 *db,                   /* Database to be rekeyed */
  const char *zDbName,           /* Name of the database */
  const void *pKey, int nKey     /* The new key */
);