Value Level Encryption (VLE)

Table of contents

1. Overview
2. Value Level Encryption API
   1. sqlite3mc_vle_key()
   2. sqlite3mc_vle_encrypt()
   3. sqlite3mc_vle_decrypt()
   4. Example Usage

Overview

Although SQLite3 Multiple Ciphers is primarily intended for the complete, transparent encryption of SQLite databases, there are occasionally situations in which full encryption is unnecessary and it is sufficient to encrypt individual values holding confidential information. The feature Value Level Encryption (VLE) was introduced in version 2.4.0 to allow partial encryption of data in such situations, where only a subset of the data needs to be kept confidential.

The VLE feature uses the same cryptographic functions as used by SQLite3 Multiple Ciphers for full database encryption. VLE provides a clean API via a small set of SQL functions. The VLE functions serve the following purposes:

• Set up a main encryption key
• Encrypt a single table column in a non-encrypted database on inserting or updating a table row
• Decrypt a single table column in a non-encrypted database on selecting a table row

Encrypted payloads have the type BLOB which consists of a small header (specifying the algorithm used), nonce, ciphertext, and verifiable tag. This incurs an overhead of 36 bytes per encrypted value.

Currently, the VLE feature supports only stream cipher algorithms, namely chacha20 and ascon128, since block cipher algorithms would significantly increase the overhead because blocks would have to be added or padded on a regular basis.

Value Level Encryption API

All VLE functions take and return standard SQLite values. An application can pass into or bind arbitrary values as appropriate.

sqlite3mc_vle_key()

This function is used to establish a key for use within a SQLite connection. It must be called before any of the other VLE functions can be used.

sqlite3mc_vle_key(password [, salt [, algorithm [, options] ] ]);

Parameters:

After the key has been derived it is stored in the function context for future use via the VLE functions. The function returns a string beginning with Ok followed by the used option set, or an error message if something went wrong.

Notes

• sqlite3mc_vle_key() does not enable full database encryption. The key derived by this function is completely separate from the key used for full database encryption.
• If no salt is specified a default salt value will be used. The given salt will always be passed through a hash function to generate a final salt with a fixed length as required by the used algorithm (typically 16 bytes).
• Currently, the only algorithms supported are chacha20 and ascon128.

Examples

-- Specify just a passphrase for the key
SELECT sqlite3mc_vle_key('passphrase');

-- Specify a passphrase and a salt for the key
SELECT sqlite3mc_vle_key('passphrase', 'This is my own salt');

-- Select a non-default algorithm (using default salt)
SELECT sqlite3mc_vle_key('passphrase', NULL, 'ascon128');

-- Change the default key derivation option
SELECT sqlite3mc_vle_key('passphrase', NULL, 'ascon128', 'kdf_iter=12345');

sqlite3mc_vle_encrypt()

This function is used to encrypt an input value and returns the encrypted ciphertext as a BLOB result.

sqlite3mc_vle_encrypt(input [, scope]);

Parameters:

The encrypted value is returned as a BLOB. SQLite type information is preserved through the encryption and decryption process.

Notes

• The SQLite type of the input value is preserved.
• The scope value must have the SQLite type TEXT or BLOB.
• If no scope is specified a default scope value will be used. The given scope will always be passed through a hash function to generate a final scope value with a fixed length as required by the used algorithm.

sqlite3mc_vle_decrypt()

This function is used to decrypt an input value and returns the decrypted value as the original SQLite type.

sqlite3mc_vle_decrypt(input [, scope]);

Parameters:

Notes

• The input value must be of type BLOB.
• The scope value must have the SQLite type TEXT or BLOB.
• If no scope is specified a default scope value will be used. The given scope will always be passed through a hash function to generate a final scope value with a fixed length as required by the used algorithm.

Example Usage

Following is a simple example demonstrating the use of the VLE functions to derive and establish a key, encrypting and then decrypting a table column value.

SELECT sqlite3mc_vle_key('passphrase');
CREATE TABLE user_tokens(id, name, token);
INSERT INTO user_tokens(id, name, token) values (1, 'anton', sqlite3mc_vle_encrypt('Token of Anton'));
-- show the raw storage
SELECT id, name, hex(token) FROM user_tokens;
-- decrypt the value
SELECT id, name, sqlite3mc_vle_decrypt(token) AS token FROM user_tokens;

The above SQL statements will produce the following output: