SQLCipher API

Commercial & Enterprise Edition Feature

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.

Setting the Key

PRAGMA key Set the key for use with the database.
sqlite3_key() C function providing an alternative to PRAGMA key.
sqlite3_key_v2() C function providing an alternative to PRAGMA key.

Changing the Key

PRAGMA rekey Change the encryption key for a SQLCipher database.
sqlite3_rekey() C function providing an alternative to PRAGMA rekey.
sqlite3_rekey_v2() C function providing an alternative to PRAGMA rekey.

Key Options

PRAGMA cipher_plaintext_header_size The number of bytes of the database header to leave in plaintext.
PRAGMA cipher_salt The database salt to use formatted as a hex-ecoded binary blob.
PRAGMA kdf_iter Change the number of iterations used with PBKDF2 key derivation.
PRAGMA cipher_kdf_algorithm The key derivation algorithm to use for computing an encryption key.

Configuration (Encryption Settings)

PRAGMA cipher_use_hmac Disable the use of per-page HMAC checks for backwards compatibility with SQLCipher 1.1.x on a specific database.
Memory Security Full memory wiping may be disabled for performance when the feature is not required. This feature is enabled by default.
PRAGMA cipher_add_random Add externally sourced random data to the crypto provider.

Migration and Compatibility

PRAGMA cipher_migrate Convenience function to perform an in-place upgrade from a SQLCipher 1.x, 2.x, or 3.x database to SQLCipher Version 4 default settings.
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_compatibility Force SQLCipher to operate with default settings consistent with that major version number for the current connection.

Performance

PRAGMA cipher_page_size Alter the page size used for the database from the default of 4096 bytes to improve performance for some query types.
PRAGMA cipher_profile Profile queries and their execution time when debugging performance issues.

Utilities

cipher_settings Description of this Item.
PRAGMA cipher_integrity_check Performs an independent verification of each database page based on each page HMAC and provides a report of any invalid pages or errors.
PRAGMA cipher_provider Provides the name of the compiled crypto provider.
PRAGMA cipher_provider_version Provides the version number of the compiled crypto provider.
PRAGMA cipher_version Provides the compiled SQLCipher version number as a string.
PRAGMA cipher_log Write internal log information to the specified target.
PRAGMA cipher_log_level Set the level of internal log information to be logged.

Default PRAGMAs

PRAGMA cipher_default_kdf_iter Change the default number of iterations used with PBKDF2 key derivation.
PRAGMA cipher_default_settings Provides the current runtime settings used when attaching a database.
PRAGMA cipher_default_compatibility Force SQLCipher to operate with the default settings consistent with that major version number as the default for the currently executing process (i.e. all connections opened after the statement executes).
PRAGMA cipher_default_kdf_algorithm The key derivation algorithm to use for computing an encryption key when attaching a database.
PRAGMA cipher_hmac_algorithm The HMAC algorithm used for both HMAC and key derivation.
PRAGMA cipher_default_plaintext_header_size The number of bytes of the database header to leave in plaintext when attaching a database.
PRAGMA cipher_default_page_size Set non-default page size to use when attaching a 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.

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.

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';

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'";

Finally, it is possible to specify an exact byte sequence for the key while also providing a specific database salt to use. Normally, a database salt value is generated randomly by SQLCipher and stored in the first 16 bytes of the database. With this key format an application would provide 96 characters, 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:

  PRAGMA key = "x'98483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C483648101010101010101010101010101010101'";

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
}
  • PRAGMA key should generally be called as the first operation on a database.

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.

int sqlite3_key(
  sqlite3 *db,                   /* Database to be keyed */
  const void *pKey, int nKey     /* The key, and the length of the key in bytes */
);
int sqlite3_key_v2(
  sqlite3 *db,                   /* Database to be keyed */
  const char *zDbName,           /* Name of the database */
  const void *pKey, int nKey     /* The key */
);

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.

sqlite> PRAGMA key = 'old passphrase';
sqlite> PRAGMA rekey = 'new passphrase';
  • 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.

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.

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 */
);

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. An example of setting the plain text header size is below:

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):

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:

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:

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.

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.

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.

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 256,000 PBKDF2 iterations (effectively 512,000 SHA512 operations). PRAGMA kdf_iter can be used to increase or decrease the number of iterations used.

sqlite> PRAGMA key = 'blue valentines';
sqlite> PRAGMA kdf_iter = '10000';
  • 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.

Retrieve or set the KDF algorithm to be used. The default value is PBKDF2_HMAC_SHA512, however PBKDF2HMAC_SHA256, and PBKDF2_HMAC_SHA1 are also supported. An example for utilizing an alternative KDF algorithm is below:

PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA256;

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.

sqlite> PRAGMA key = 'blue valentines';
sqlite> PRAGMA cipher_use_hmac = OFF;
  • 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.

Full memory wiping may be disabled for performance when the feature is not required. This feature is enabled by default. An example for disabling the memory wiping feature is below:

PRAGMA cipher_memory_security = OFF;

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'";

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 legacy SQLCipher database to the newest format. SQLCipher will upgrade the database in place:

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

This PRAGMA will return a single row with the value 0 after successful completion of the migration process. The migrated database will remain open and use the same filename.

The cipher_migrate PRAGMA can upgrade standard SQLCipher version 1, 2, and 3 databases to the latest version. 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.

The cipher_migrate PRAGMA is potentially expensive because it needs to attempt to open the database for each version to determine the appropriate settings. Therefore an application should NOT call the PRAGMA every time a database is opened. Instead, an application can use the following recommended process for opening and upgrading databases:

  1. Attempt to open and access the database as normal by keying the database and attempting a query
  2. If SQLCipher throws an error on first access, close the database handle. Then open it and run PRAGMA cipher_migrate (e.g. in the case of Android you can use the postKey hook). This will attempt to upgrade the database.
  3. Check the result of the update by retrieving the row value result.
  4. If the migration succeeds a row with a single column value of 0 is returned, the upgrade was successful and your application can continue to use the connection for the remainder of the application lifecycle.
  5. If the key is incorrect then the PRAGMA will return a single non-zero column value, meaning that the key material is incorrect or the settings of the database were not consistent with defaults for previous SQLCipher versions (i.e. custom settings were used that require manual migration).

This process performs optimally in the standard case when the database has already been migrated. It has a slight slowdown in the event that the key material is incorrect because the key may be derived multiple times to attempt migration, but that usually acceptable in most cases.

In the event that incorrect keys are a common situation, and thus the performance hit for migrating in step 2 is not acceptable, then the application should statefully tracking the current SQLCipher database version in an preference or through some other means.

Note: When opening a database connection to run PRAGMA cipher_migrate, you must include the SQLITE_OPEN_CREATE flag as the migration process will temporarily attach a new database during the migration process.

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.

$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'test2.db' AS db2 KEY "x'10483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C4836481'";
$ ./sqlcipher encrypted.db
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY ''; -- empty key will disable encryption
  • 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).
  • 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 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. For additional information and discussion on this subject please refer to this post on how to encrypt a plaintext database using 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.

$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;
$ ./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;
$ ./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;
$ ./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;
$ ./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;
  • sqlcipher_export does not alter the user_version of the target database. Applications are free to do this themselves.

Calling this PRAGMA and passing in 1, 2, 3, or 4 will cause SQLCipher to operate with default settings consistent with that major version number for the current connection, e.g. the following will cause SQLCipher to treat the current database as a SQLCipher 3.x database

PRAGMA cipher_compatibility = 3;

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 4096 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. The value for cipher_page_size must be a power of two between 512 and 65536 inclusive.

sqlite> PRAGMA KEY = 'testkey';
sqlite> PRAGMA cipher_page_size = 8192;
  • 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.

The PRAGMA cipher_profile command allows for profiling queries and their respective execution time in milliseconds. The command can accept four values, stdout, stderr, a file name of your choosing, or off to terminate profile capture. An example of the usage:

$ ./sqlcipher example.db
sqlite> PRAGMA cipher_profile='sqlcipher.log';
sqlite> CREATE TABLE t1(a,b);
sqlite> INSERT INTO t1(a,b) VALUES('one for the money', 'two for the show');
sqlite> PRAGMA cipher_profile=off;

The above will generate a profile report representing the queries that were executed along with their respective wall-clock timing.

Provides the current runtime settings used to configure the database connection.

PRAGMA cipher_settings;

Performs an independent verification of the database based on the HMAC stored with each page, providing a report of any invalid pages or errors. This PRAGMA operates without invoking any database-internal logic so the caller can verify the “envelope” of each page in the file. Pages identified as invalid were likely modified after they were written to permanent storage by SQLCipher. In order for this PRAGMA to work the database must have the HMAC setting enabled, and the provided key must be correct. The PRAGMA will return one row per error condition. If no results are returned then the database was found to be externally consistent.

sqlite> pragma key = 'testkey';
sqlite> pragma cipher_integrity_check;
HMAC verification failed for page 4
HMAC verification failed for page 9
page 240 has an invalid size of 1 bytes

Retrieve or set the HMAC algorithm to be used. The default value is HMAC_SHA512, however HMAC_SHA256, and HMAC_SHA1 are also supported. An example for utilizing an alternative HMAC algorithm is below:

PRAGMA cipher_hmac_algorithm = HMAC_SHA256;

Provides the name of the compiled crypto provider. The database must be keyed before requesting the name of the crypto provider.

Provides the version number provided from the compiled crypto provider. This value, if known, is available only after the database has been keyed.

Returns the compiled SQLCipher version number as a string. An example of the usage:

$ ./sqlcipher example.db
sqlite> PRAGMA cipher_version;
3.3.1

Instructs SQLCipher to log internal debugging and operational information to the sepecified log target. Available targets are stdout (for Standard Output), stderr (for Standard Error), logcat (Android only), or a file name. In the case of a file name, the file will be created if it does not exist, and appended to if it does exist. This pragma should be used in conjunction with PRAGMA cipher_log_level in order to set the types of log messages that will be output. An example of the usage follows:

$ ./sqlcipher example.db
sqlite> PRAGMA key = 'test';
sqlite> PRAGMA cipher_log = stderr;
0
sqlite> PRAGMA cipher_log_level = WARN;
2

This PRAGMA selects the log level to use for internal logging. The available options are ERROR, WARN, INFO, DEBUG, and TRACE. Note that each level is more verbose than the last, and particularly with DEBUG and TRACE the logging system will generate a significant log volume. This pragma should be used in conjunction with PRAGMA cipher_log to set the output target for log messages. An example of the usage follows:

sqlite> pragma cipher_log = stderr;
0
sqlite> pragma cipher_log_level = DEBUG;
8
sqlite> pragma key = 'test';
2022-03-03 09:43:59.684: sqlite3_key_v2: db=0x55fd1259e4d0 zDb=(null)
2022-03-03 09:43:59.684: sqlite3CodecAttach: db=0x55fd1259e4d0, nDb=0
2022-03-03 09:43:59.684: sqlite3CodecAttach: calling sqlcipher_activate()
2022-03-03 09:43:59.684: sqlcipher_activate: calling sqlcipher_register_provider(0x55fd12629590)
2022-03-03 09:43:59.684: sqlcipher_activate: called sqlcipher_register_provider(0x55fd12629590)
2022-03-03 09:43:59.684: sqlite3CodecAttach: calling sqlcipher_codec_ctx_init()
...

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.

./sqlcipher sqlcipher2.0.db
sqlite> PRAGMA cipher_default_kdf_iter = 4000;
sqlite> PRAGMA key = 's3cr37';
  • 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.

Provides the current “default” runtime settings used when attaching a database.

PRAGMA cipher_default_settings;

Calling this PRAGMA and passing in 1, 2, 3, or 4 will cause SQLCipher to operate with the default settings consistent with that major version number as the default for the currently executing process (i.e. all connections opened after the statement executes). For example, the following will cause SQLCipher to treat all newly opened databases as a SQLCipher 2.x databases:

PRAGMA cipher_default_compatibility = 2;

Retrieve or set the KDF algorithm to be used when attaching a database. The default value is PBKDF2_HMAC_SHA512, however PBKDF2_HMAC_SHA256, and PBKDF2_HMAC_SHA1 are also supported. An example for utilizing an alternative KDF algorithm is below:

PRAGMA cipher_default_kdf_algorithm = PBKDF2_HMAC_SHA256;

Retrieve or set the HMAC algorithm to be used when attaching a database. The default value is HMAC_SHA512, however HMAC_SHA256, and HMAC_SHA1 are also supported. An example for utilizing an alternative HMAC algorithm is below:

PRAGMA cipher_default_hmac_algorithm = HMAC_SHA256;

Allocates a portion of the database header which will not be encrypted to allow identification as a SQLite database when attaching a 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. This behaves identically to PRAGMA cipher_plaintext_header_size except that once the default is set, the value will be used for all subsequent SQLCipher operations. An example of setting the plain text header size is below:

PRAGMA cipher_default_plaintext_header_size = 32;

To attach a database that was generated with a non-default cipher_page_size, the PRAGMA cipher_default_page_size must be set in order to override the default page size during the attach phase. The value for cipher_default_page_size must be a power of two between 512 and 65536 inclusive.

$ ./sqlcipher foo.db
sqlite> PRAGMA key = 'foo';
sqlite> PRAGMA cipher_page_size = 8192;
sqlite> CREATE TABLE t1(a,b);
sqlite> INSERT INTO t1(a,b) values('one for the money', 'two for the show');
sqlite> .q

$ ./sqlcipher bar.db
sqlite> PRAGMA cipher_default_page_size = 8192;
sqlite> PRAGMA key = 'bar';
sqlite> ATTACH DATABASE 'foo.db' as foo KEY 'foo';
sqlite> SELECT count(*) FROM foo.t1;

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.

./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 will be keyed with password 's3cr37'
  • 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