SQLCipher Encrypted Virtual Tables

Commercial & Enterprise Edition Feature

SQLCipher Encrypted Virtual Tables provide a convenient way to partially encrypt data on a per-table basis using SQLCipher Commercial and Enterprise packages. Encrypted Virtual Tables extend upon the foundation of Value Level Encryption, allowing an application to encrypt a subset of data in a database while performing standard queries.

Unlike using SQLCipher VLE directly, virtual tables do not require that statement code include SQL functions to operate. Many of the optimal use cases for partial encryption apply to these virtual tables, e.g. where only a subset of information in the database needs to be secured, use of multiple keys are required, or full database encryption introduces undesirable performance impacts.

SQLCipher Encrypted Virtual Tables use the same underlying cryptographic provider as SQLCipher, but instead of applying encryption to the entire database, the virtual table interface transparently handles encryption and decryption. Underlying encrypted data is stored in a backing “shadow” table. The encrypted virtual table implementation selectively encrypts and decrypts individual rows and column values as needed to satisfy queries.

An application declares an encrypted virtual table using the sqlcipher_vle module. There are three parts to the declaration:

  1. Virtual table and module declaration - specifies the name of the virtual table and instruct the library to encrypt it using sqlcipher_vle
  2. Shadow table specification - consists of a CREATE TABLE IF NOT EXISTS statement that defines the schema for the table using the table name with _shadow appended (SQLCipher will create the name_shadow table and store all encrypted data within it)
  3. Optional column exclusion list - a single-quoted comma separated list of numeric column indexes, starting with 1, that should be excluded from encryption (i.e. values will be stored in plaintext but in the same shadow table.

Here is a complete example which will create an Encrypted Virtual Table called app_secrets. The Virtual Table will transparently handle encrypted storage into app_secrets_shadow, and automatically decrypt data for queries. The first column in the table, id will be stored unencrypted since it is an opaque identifier that does not require protection.

CREATE VIRTUAL TABLE app_secrets USING sqlcipher_vle(CREATE TABLE IF NOT EXISTS app_secrets_shadow(id, name, secret), '1');

Before interacting with an encrypted virtual table the application must invoke sqlcipher_vle_key to establish persistent key material for VLE encryption, e.g.

SELECT sqlcipher_vle_key('key material or password');

All data is stored in the backing “shadow” table, as encrypted BLOB values containing the Initialization Vector (IV), the PKCS#7 padded ciphertext, and HMAC of the IV and ciphertext for each value. SQLite type information is preserved through the encryption and decryption process.

Here is a complete example that demonstrates the usage of the virtual table

> -- create Virtual Table
> CREATE VIRTUAL TABLE app_secrets USING sqlcipher_vle(CREATE TABLE IF NOT EXISTS app_secrets_shadow(id, name, secret), '1');
> -- establish key
> SELECT sqlcipher_vle_key('key material or password');

> -- insert through the virtual table interface
> INSERT INTO app_secrets(id, name, secret) values (1, 'launch code', 'projectile 92730');

> -- query data from the virtual table interface
> SELECT * FROM app_secrets;
id          name         secret
----------  -----------  ----------------
1           launch code  projectile 92730

> -- show that the raw storage of the data is encrypted
> SELECT id, hex(name), hex(secret) FROM app_secrets_shadow;

id   hex(name)                                                         hex(secret)
---  ----------------------------------------------------------------  ---------------------------------------------------------------- 
1    5C80C090CFA019046D79B4EF923904D2D2DCAEE2DF35B5C0115842DFE93117AA  EDCA6B3E6163D64AEC3FCBDD82165E1DB6E4A694FB6434BF3D75BE65116D70C5
     2D82272065B6023931FAE92FD6E24DBA251B2E6B2C7AB223EE91224166792E56  5B2CD3F2BD0C2E42019AE1C0F71B2AA9D62BE96E9576F355FB76B1A2AE6D0A71
     76EA536573745A0F8CF516D8B6480273295DA562C8FA6D1291B50D8B747FE6DF  2ABED9B9E15CD69B3C6E36DCC49909B4E10DA337A3076AFCEA15DD7B863D3FFB
                                                                       4BBC765BCBB19CD1AAD2A9339675EAF1