SQL Functions

SQLite3 Multiple Ciphers defines several SQL functions, which can be used to configure global encryption parameters or specific cipher parameters. They offer the same functionality as the PRAGMA statements, but can be called from any SQL statement expression.

Table of contents

1. Function sqlite3mc_config for global parameters
2. Function sqlite3mc_config for cipher parameters
3. Function sqlite3mc_codec_data

Function sqlite3mc_config for global parameters

This version of the function sqlite3mc_config gets or sets encryption parameters which are relevant for the entire database connection. paramName is the name of the parameter which should be get or set. To set a parameter, the new parameter value is passed into the function as parameter newValue. To get the current parameter value, the parameter newValue is simply omitted.

Parameter names use the following prefixes:

The following parameter names are supported for paramName:

The following table lists the supported cipher identifiers:

Note

• Checking the HMAC on read operations is active by default. With the parameter hmac_check the HMAC check can be disabled in case of trying to recover a corrupted database. It is not recommended to deactivate the HMAC check for regular database operation. Therefore the default can not be changed.
• The legacy mode for WAL journal encryption is off by default. The encryption mode used by all versions up to 1.2.5 is called legacy mode, version 1.3.0 introduced a new encryption mode that provides compatibility with legacy encryption implementations and is less vulnerable to changes in SQLite. It should only be enabled to recover WAL journal files left behind by applications using versions up to 1.2.5.

Examples

-- Get cipher used for the next key or rekey operation
SELECT sqlite3mc_config('cipher');

-- Set cipher used by default for all key and rekey operations
SELECT sqlite3mc_config('default:cipher', 'sqlcipher');

Function sqlite3mc_config for cipher parameters

This version of the function sqlite3mc_config gets or sets cipher configuration parameters which are relevant for the selected cipher scheme. cipherName is the name of the cipher scheme, of which paramName is the name of the requested parameter. To set a parameter, the new parameter value is passed into the function as parameter newValue. To get the current parameter value, the parameter newValue is simply omitted.

Note

• See the related cipher descriptions for the parameter names supported for paramName.

Examples

-- Get number of KDF iterations for the AES-256 cipher
SELECT sqlite3mc_config('aes256cbc', 'kdf_iter');

-- Set number of KDF iterations for the AES-256 cipher to 54321
SELECT sqlite3mc_config('aes256cbc', 'kdf_iter', 54321);

-- Select legacy SQLCipher version 1 encryption scheme
SELECT sqlite3mc_config('cipher', 'sqlcipher');
SELECT sqlite3mc_config('sqlcipher', 'legacy', 1);
-- Activate cipher scheme
PRAGMA key='<passphrase>';

-- Select legacy SQLCipher version 1 encryption scheme
SELECT sqlite3mc_config('cipher', 'sqlcipher');
SELECT sqlite3mc_config('sqlcipher', 'legacy', 1);
-- Overwrite default settings for some or all cipher parameters
SELECT sqlite3mc_config('sqlcipher', 'legacy_page_size', 1024);
SELECT sqlite3mc_config('sqlcipher', 'kdf_iter', 4000);
SELECT sqlite3mc_config('sqlcipher', 'fast_kdf_iter', 2);
SELECT sqlite3mc_config('sqlcipher', 'hmac_use', 0);
-- Activate cipher scheme
PRAGMA key='<passphrase>';

Function sqlite3mc_codec_data

The function sqlite3mc_codec_data retrieves the value of encryption parameters after an encrypted database has been opened. The parameter schemaName optionally specifies the schema name of an attached database; for the main database the parameter schemaName can be omitted. The parameter paramName specifies the parameter to be queried.

The following parameter names are currently supported for paramName:

Notes

• A NULL value is returned if the database is not encrypted or if the encryption scheme doesn’t use a cipher salt.
• Some cipher schemes use a random cipher salt on database creation. If the database header gets corrupted for some reason, it is almost impossible to recover the database without knowing the cipher salt. For critical applications it is therefore recommended to retrieve the cipher salt after the initial creation of a database and keep it in a safe place.

Example

-- Get the random key salt as a hexadecimal encoded string (if database is encrypted and uses key salt)
SELECT sqlite3mc_codec_data('cipher_salt');