Commercial & Enterprise Edition Feature
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:
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
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:
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.
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:
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.
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:
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.
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:
sqlcipher_vle_encrypt()sqlcipher_vle_key will be used.The decrypted (plaintext) value will be returned as the original SQLite type fed into sqlcipher_vle_encrypt().
The sqlcipher_vle_cipher() function is used to directly invoke low-level AES-256-CBC encryption or decryption operations without type encoding, PKCS#7 padding, or HMAC-SHA512 authentication of input. It accepts parameters configuring the mode, initialization vector BLOB, input BLOB, and an optional key BLOB. The result of the operation is returned as a BLOB.
sqlcipher_vle_cipher(mode, iv, input, <key>);
Arguments:
0 for decrypt, 1 for encryptsqlcipher_vle_key will be used.The encrypted value returned as a BLOB result.
The sqlcipher_func_hmac() function is used to create a MAC for any given input.
sqlcipher_vle_hmac(input, <key, algorithm>);
Arguments:
sqlcipher_vle_key will be usedThe 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:
This function returns the random data as a BLOB result.