SQLCipher Value Level Encryption (VLE) Functions

SQLCipher is well know for providing Full Database Encryption for SQLite databases. The SQLCipher Value Level Encryption (VLE) features available in Commercial and Enterprise packages extend upon that foundation to allow partial encryption of data. For many classes of applications, e.g. when only a subset of information needs to be secured, multiple separate keys are required, etc. partial data encryption has numerous advantages.

SQLCipher VLE features use the same underlying cryptographic provider, but instead of applying to the entire database, VLE exposes convenient SQL functions and encrypted virtual tables that can selectively encrypt and decrypt data as needed.

SQLCipher VLE functions are extended SQL functions that can be used for a variety of purposes including encryption, decryption, HMAC, randomness, and key derivation (both persistent and ephemeral). Here are some example uses of VLE functions:

  1. Encrypt a single table column in a non-encrypted database
  2. Encrypt values in specific rows with different keys
  3. Create and verify HMAC “signatures” for important data to provide tamper evidence
  4. Generate high-entropy random data for internal utilization
  5. Add additional levels of encryption, using separate keys, for particularly sensitive data in an encrypted database
  6. Using Encrypted Virtual Tables to secure data at the table level

Example Usage

All SQLCipher VLE functions operate using standard SQLite values. An application can pass into or bind arbitrary values as appropriate. Encrypted payloads are BLOB types which encapsulate IVs, ciphertext and a verifiable HMAC of both.

Here is a simple example that demonstrates the use of sqlcipher_vle_key() to derive and establish a persistent key (valid for the entire duration of the connection), followed by encrypting with sqlcipher_vle_encrypt() and then decrypting with sqlcipher_vle_decrypt().

> SELECT sqlcipher_vle_key('key material or password');
> CREATE TABLE app_secrets(id, name, secret);
> INSERT INTO app_secrets(id, name, secret) values (1, 'launch code', sqlcipher_vle_encrypt('projectile 92730'));

> -- show the raw storage
> SELECT id, name, hex(secret) FROM app_secrets;
id          name         hex(secret)
----------  -----------  --------------------------------------------------------------------------------------------------------------------
1           launch code  A227E5AF5B19C4A68652739A039CA65F9796A0131BBE731CE9A6D86FD153E0579B3EF60C1A42B7A02C1C80B7B35F00C8FED5A3AFD4BA42682C4A
                         762B824F324F990E4957D18611329CAEF837EEFFA94190046313D92F128E729E7AB25E8107EA5271E46E5F0F9936CA1DD2ED9C320DDD

> -- decrypt the value
> SELECT id, name, sqlcipher_vle_decrypt(secret) AS secret FROM app_secrets;
id          name         secret
----------  -----------  ----------------
1           launch code  projectile 92730

Value Level Encryption API

sqlcipher_vle_key()

The sqlcipher_vle_key() function is used to establish a persistent key for use with the sqlcipher_vle functions and the virtual table interface.

sqlcipher_vle_key(password, <salt, algorithm, iterations>);

Arguments:

  • password - required input key material, text or blob
  • salt - optional salt passed to PBKDF2. If NULL or omitted, this comes from sqlcipher_master table, or is generated randomly
  • algorithm - PBKDF2_HMAC_SHA1, PBKDF2_HMAC_SHA256, PBKDF2_HMAC_SHA512. If NULL or omitted the SQLCipher default is used
  • iterations - positive integer. If NULL or omitted then the SQLCipher default is used

After the key is derived it is stored in the function context for future use through the sqlcipher_vle functions and the virtual table interface. This function does not return any value as a result set.

Note that sqlcipher_vle_key() does not enable full database encryption, and the key derived from calling this function is completely separate from the key used for full database encryption.

sqlcipher_vle_pbkdf2()

The sqlcipher_func_pbkdf2() function is used to compute key material from a passphrase or other input using the PBKDF2 algorithm. Such key material can optionally be passed explicitly to other sqlcipher_vle functions as an override, or retrieved for any other purpose.

sqlcipher_vle_pbkdf2(password, <salt, algorithm, iterations, length>);

Arguments:

  • password - required input key material, text or blob
  • salt - optional salt passed to PBKDF2. If NULL or omitted, this comes from sqlcipher_master table, or is generated randomly
  • algorithm - PBKDF2_HMAC_SHA1, PBKDF2_HMAC_SHA256, PBKDF2_HMAC_SHA512, KDF_NONE. If NULL or omitted the SQLCipher default is used
  • iterations - positive integer. If NULL or omitted then the SQLCipher default is used
  • length - positive integer for the number of bytes to output from the function. If NULL or omitted then the default 64 bytes is returned

This function returns the derived key as a BLOB result.

Unlike sqlcipher_vle_key the generated key is ephemeral and is returned as the result from the function - it is not stored in the context. The other difference is that a caller can specify the output length of the key.

sqlcipher_vle_encrypt()

The sqlcipher_vle_encrypt() function is used to encrypt an input value and returns the encrypted ciphertext as a BLOB result.

sqlcipher_vle_encrypt(input, <key>);

Arguments:

  • input - required input to be encrypted
  • key - optional explict key for encryption and HMAC operations. The key must be 64 bytes long, where the first 32 bytes will be used for the encryption key and the second 32 bytes will be used to compute an HMAC of the encrypted payload. If NULL or omitted, the keys previously established with sqlcipher_vle_key will be used.

The encrypted value returned as a BLOB result consisting of the Initialization Vector (IV), the PKCS#7 padded ciphertext, followed by the HMAC of the IV and ciphertext. SQLite type information is preserved through the encryption and decryption process.

sqlcipher_vle_decrypt()

The sqlcipher_vle_decrypt() function operates on the ciphertext BLOB produced by sqlcipher_vle_encrypt() and returns the plaintext value following HMAC verification and decryption as a result.

sqlcipher_vle_decrypt(input, <key>);

Arguments:

  • input - required input to be decrypted, which MUST be a BLOB previously returned by sqlcipher_vle_encrypt()
  • key - optional explict key for decryption and HMAC operations. The key must be 64 bytes long, where the first 32 bytes will be used for the decryption key and the second 32 bytes will be used to verify the HMAC of the encrypted payload. If NULL or omitted, the keys previously established with sqlcipher_vle_key will be used.

The decrypted (plaintext) value will be returned as the original SQLite type fed into sqlcipher_vle_encrypt().

sqlcipher_vle_hmac()

The sqlcipher_func_hmac() function is used to create a MAC for any given input.

 sqlcipher_vle_hmac(input, <key, algorithm>);

Arguments:

  • input - required input to be passed to HMAC, which will be converted to a BLOB value internally whose raw bytes will be fed to the HMAC function
  • key - optional key for the HMAC. If NULL or omitted, the HMAC key previously established with sqlcipher_vle_key will be used
  • algorithm - HMAC_SHA1, HMAC_SHA256, HMAC_SHA512. If NULL or omitted the SQLCipher default is used

sqlcipher_vle_random()

The sqlcipher_vle_random() function is used to retrieve cryptographically secure randomness from the SQLCipher cryptographic provider. This function returns the specified number of bytes as a BLOB result.

sqlcipher_vle_random(length);

Arguments:

  • length - required length of random data

This function returns the random data as a BLOB result.