SQLite Encryption Extension

Documentation
Login

How To Compile And Use SEE

1.0 Introduction

This file describes the SQLite Encryption Extension (SEE) for SQLite. The SEE allows SQLite to read and write encrypted database files. All database content, including the metadata, is encrypted so that to an outside observer the database appears to be white noise.

A version of SQLite that includes SEE is also able to read and write normal database files created with a public domain version of SQLite. But the public version of SQLite will not be able to read or write an encrypted database file. Indeed, no version of any known software will be able to access an encrypted database file without knowing the encryption key.

The SEE is actually a set of extensions employing various encryption algorithms. The following encryption algorithms are currently supported:

2.0 License

The core SQLite library is in the public domain. However, the extensions needed to read and write an encrypted database file are licensed software. You should only be able to see this software if you have a license.

Your license is perpetual. You have paid a one-time fee that allows you to use and modify the software forever. You can ship as many copies of the software to your customers as you want so long as you ensure that only compiled binaries are shipped (you cannot distribute source code) and that your customers cannot make additional copies of the software to use for other purposes.

You can create multiple products that use this software as long as all products are developed and maintained by the same team. For the purposes of this paragraph, a "team" is a work unit where everybody knows each others names. If you are in a large company where this product is used by multiple teams, then each team should acquire their own separate license, or an enterprise license.

3.0 How To Compile

Your application sees SEE as a single large file of C-code that is a drop-in replacement for the SQLite amalgamation. The SEE source-code file works and compiles just like the public-domain "sqlite3.c" amalgamation. If you already build your application using the public-domain "sqlite3.c" file, then to build using SEE you merely replace the public-domain "sqlite3.c" with an SEE-enabled "sqlite3.c" file and recompile.

There are ten different SEE-enabled "sqlite3.c" files to choose from:

  1. sqlite3-see-aes256-openssl.c
  2. sqlite3-see-aes256-cryptoapi.c
  3. sqlite3-see-cccrypt.c
  4. sqlite3-see-aes256-ofb.c
  5. sqlite3-see-bcrypt.c
  6. sqlite3-see.c
  7. sqlite3-see-aes128-ofb.c
  8. sqlite3-see-aes128-ccm.c
  9. sqlite3-rc4.c
  10. sqlite3-xor.c

The recommended procedure for adding SEE into your application is to copy one of these files into your application source tree, renaming it as "sqlite3.c" and overwriting the public-domain "sqlite3.c" source file, then recompile. You will need to copy the "sqlite3.h" from this website as well. After recompiling, your application should continue working exactly as it did before, reading and writing ordinary unencrypted SQLite databases. Once you have recompiled and verified that everything still works, then go back in and add a PRAGMA (described below) that activates encryption to your application code, and you are done.

As a general rule, SEE variants that are higher on the list above are preferred over variants that are lower on the list. They all works, so you can use whichever variant works best for your situation. But if you are struggling to choose between some subset of the implementations, and all else being equal, pick the one that is higher on the list.

SEE variants that use the same encryption algorithm are all compatible with one another in the sense that they will read and writes each others database files. See the algorithm cross-reference table below to see which SEE variants support which algorithms.

3.1 Source Code Files In The SEE Distribution

The following are the source-code files used to implement the SQLite Encryption Extension:

sqlite3-see-aes256-openssl.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file, adding support for encryption using the AES-256 in OFB mode by linking against the external OpenSSL library.

sqlite3-see-cryptoapi.c

This file is a drop-in replacement for the public-domain "sqlite3.c" source file, adding encryption capabilities using the AES256 in OFB mode using the CryptoAPI native interface on Windows.

sqlite3-see-cccrypt.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file, adding support for the AES-128 and AES-256 encryption algorithms, in OFB mode, using the external CCCrypt encryption. CCCrypt is the default encryption library on MacOS and iOS, and so this implementation of SEE is recommended for those platforms.

sqlite3-see-aes256-ofb.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file, adding support for encryption using the AES-256 in OFB mode using a built-in copy of the Rijndaal reference implementation.

sqlite3-see-bcrypt.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file, adding support for encryption using the AES-256-GCM using the "Bcrypt" interfaces of Windows. This module only works on Windows. A generic version of AES-256-GCM that works on any platform is pending.

sqlite3-see.c

This file is a drop-in replacement for the public-domain "sqlite3.c" source file, adding support for encryption using any of the RC4, AES128-OFB, AES258-OFB, and AES256-GCM algorithms.

sqlite3-see-aes128-ofb.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file. This replacement adds support for the AES-128 encryption algorithm in OFB mode using the Rijndaal reference implementation.

sqlite3-see-aes128-ccm.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file. This replacement adds support for the AES-128 encryption algorithm in CCM mode. CCM mode includes a message authentication code which provides authentication in addition to confidentiality. This uses the Rijndaal reference implementation for AES.

sqlite3-see-rc4.c

This file is a drop-in replacement for the public-domain "sqlite3.c" file, adding support for encryption using the RC4 algorithm. RC4 is no longer considered secure. You should not use this implementation of SEE. It is provided for historical compatibility only.

sqlite3-see-xor.c

This file is a drop-in replacement for the public-domain "sqlite3.c" source file, adding pseudo-encryption which does nothing more than XOR the database against a repeated copy of the encryption key. This variant of SEE does not provide true encryption. It is for demonstration use only, or for use in cases where it is desirable to obfuscate a database file without actually encrypting it, perhaps due to legal constraints.

sqlite3.c

A copy of ordinary, unencrypted SQLite that contains additional hooks needed to add encryption. The other encrypted SQLite modules above are all copies of this file with additional code prepended and appended to do the encryption work. This file is provided for reference only and is probably not useful for development.

sqlite3.h

This file contains the interface definitions for SQLite. Other programs that link against SQLite will need this file, and you will need this file in order to compile the CLI, but you do not need this file to compile SQLite itself.

shell.c

This file contains source code for the "CLI", the Command Line Interface program named "sqlite3.exe" that you can use to access and control SQLite database files. This file is different from the "shell.c" file that comes with the public-domain version of SQLite. This shell.c has been enhanced to make use of the encryption extension.

3.2 Building And Compiling The SEE Code

To compile SEE into a static library, select an appropriate "sqlite3-see-*.c" source file (containing the algorithm and implementation you desire), then compile that file just like you would compile an ordinary public-domain "sqlite3.c" source file. On unix systems, the command sequence would be something like this: 

    gcc -c sqlite3-see-aes256-ofb.c
    ar a sqlite3-see-aes256-ofb.a sqlite3-see-aes256-ofb.o

On windows, the commands are more like this: 

    cl -c sqlite3-see-aes256-ofb.c
    lib /out:libsee.lib sqlite3-see-aes256-ofb.obj

3.3 Building A Shared-Library Or DLL

We encourage you to statically link SQLite against your application. However, if you must use SQLite as a separate DLL or shared library, you can compile as follows on Linux: 

    gcc -fPIC -shared -o libsee.so sqlite3-see-aes256-ofb.c

Or on Windows: 

    cl -DSQLITE_API=__declspec(dllexport) sqlite3-see-aes256-ofb.c /link /dll /out:libsee.dll

3.4 Building The Command-Line Shell Program

To compile the CLI, just hand the shell.c source file to your C compiler together with either the static library prepared above, or the original source code files. A typical command on Linux is: 

    gcc -o sqlite3 shell.c sqlite3-see-aes256-ofb.c -lpthread -ldl

On a Mac: 

    gcc -o sqlite3 shell.c sqlite3-see-aes256-ofb.c -ldl

On Windows with MSVC: 

    cl /Fesqlite3.exe shell.c sqlite3-see-aes256-ofb.c

For an added performance boost when building the CLI, consider adding the -DSQLITE_THREADSAFE=0 option. The CLI is single threaded and SQLite runs faster if it doesn't have to use its mutexes.

SEE can also be built for Windows Phone 8, UWP 10, and Android.

4.0 Command-Line Usage

The CLI is the same CLI used by public-domain SQLite though with enhancements to support encryption. There are new command-line options ("-key", "-hexkey", and "-textkey") for specifying the encryption key. Examples: 

    sqlite3 -key secret database.db
    sqlite3 -hexkey 736563726574 database.db
    sqlite3 -textkey secret2 database.db

If the key is omitted or is an empty string no encryption is performed.

There are three different key formats. The first format (-key) takes the key string and repeats it over and over until it equals or exceeds the number of bytes in the key of the underlying algorithm (16 bytes for AES128, 32 bytes for AES256, or 256 bytes for RC4). That approach limits the key space since it does not allow 0x00 bytes in the key. The second format (-hexkey) accepts the key as hexadecimal, so any key can be represented. The third format (-textkey) computes a hash on the input key material to extract as much entropy from the input key as possible and uses the resulting hash as the key of encryption algorithm.

Most of the SEE variants are forgiving of the key format. If a key is too short, then it is lengthened by duplication. If the key is too long, it is truncated to the correct length. The sqlite3-see-bcrypt.c SEE variant is an exception to this rule, however. With sqlite3-see-bcrypt.c, the key must be exactly 32 bytes and the -textkey format is not supported. The usual way to supply a key to sqlite3-see-bcrypt.c is to use the -hexkey format with a 64-digit hexadecimal key. The limitations on the key format and length in sqlite3-see-bcrypt.c are by request of the launch customer for that variant.

4.2 Specifying an alternative encryption algorithm

Some SEE variants support multiple encryption algorithms. Use the "--alg ALGORITHM-NAME" command-line option to specify which encryption algorithm to use. For example, with the "sqlite3-see.c" variant, to use the AES256-GCM algorithm, the command-line would be something like this: 

    sqlite3 -textkey secret --alg aes256gcm database.db

4.3 Using URI pathnames with query parameters

All SEE variants support URI pathnames for the database file. URI pathnames begin with "file:" and may optionally be followed by query parameters. The "key=", "hexkey=", and "textkey=" query parameters can be used to specify the encryption key and the "alg=" query parameter can be used to specify the encryption algorithm.

For example, the following two command-lines have the same outcome: 

    sqlite3 -textkey secret --alg rc4 database.db
    sqlite3 "file:database.db?textkey=secret&alg=rc4"

The advantage of the URI filename technique is that it allows database names that include the encryption key and algorithm to be passed to SQL commands like "VACUUM INTO" or "ATTACH", or into C-language APIs like sqlite3_open(). The disadvantage of using URI filenames is that it combines the name of the database file and the encryption key into a single string, making it easier to accidentally expose the encryption key and compromise security. System designers should carefully consider the advantages and disadvantages of URI filenames and choose the technique that works best for their specific implementations. It is ok to use both techniques in the same system, switching between them as necessary to maximize security and utility.

4.4 Changing the encryption key

The SEE-enabled CLI also includes new dot-commands ".rekey", ".hex-rekey", and ".text-rekey" for changing the encryption key: 

   .rekey OLD NEW NEW
   .hex-rekey OLD NEW NEW
   .text-rekey OLD NEW NEW

The first argument is always the old password, in exactly the format as it was supplied to the "-key", "-hexkey", or "-textkey" options when the command-line tool was started. If the the database was previously unencrypted, use an empty string "" as the key. The 2nd and 3rd arguments are the new encryption key. You must enter the new key twice to check for typos - the rekey will not occur unless both instances of the new key are the same. To encrypt a previously unencrypted database, do this: 

   .rekey "" new-key new-key
   VACUUM

The VACUUM step is not required to enable encryption but it is highly recommended. The VACUUM command ensures that every page of the database file has a secure nonce. The VACUUM is only needed when an existing, non-empty database file is encrypted for the first time.

To decrypt a database do this: 

   .rekey old-key "" ""

The .rekey command only works with text keys. To rekey a database that contains a binary key use the ".hex-rekey" command instead. The .hex-rekey command works just like .rekey except the new key is entered as hexadecimal instead of text. The ".text-rekey" command computes a hash of the NEW argument and uses that hash as the encryption key.

4.5 Changing the encryption algorithm

If you need to change the encryption algorithm for a database, the best way to do so is using the "VACUUM INTO" SQL command with a filename argument that is a URI pathname that includes the new encryption key and algorithm as query parameters. This creates a new database file using the new key and algorithm which you can then substitute in place of the old database file.

For example, suppose you have an existing database named "old.db" that is encrypted using the RC4 algorithm with a key of "oldkey" and you want to upgrade that database to use a more modern and secure algorithm, such as AES256OFB. To do that, you can run an SQL command like this: 

    VACUUM INTO 'file:new.db?textkey=newkey&alg=aes256ofb';

That command creates a new database file named "new.db" that contains all the same content as "old.db" but is now encrypted using the AES256OFB algorithm with the text key "newkey". (You should be using more secure keys than the ones shown in this example, of course.)

5.0 C Interface

If you deploy the SQLite encryption extension as a DLL or shared library then you must first activate the library by invoking: 

   sqlite3_activate_see("7bb07b8d471d642e");

The argument is your product activation key. The activation key is available as plain-text in the source code so you can clearly see what it is. The purpose of the activation key is to prevent one of your customers from extracting the SQLite library and using it separately from your application. Without knowledge of the activation key, which only you should know, your users will be unable to access the encryption features.

If you are unable to invoke the C-interface to sqlite3_activate_see() (perhaps because you are accessing SQLite through a wrapper layer) then you can also alternatively activate the encryption features using a PRAGMA: 

  PRAGMA activate_extensions='see-7bb07b8d471d642e';

Use the sqlite3_open() API to open an encrypted database or any database that you want to rekey. Immediately after opening, specify the key using sqlite3_key_v3(): 

   int sqlite3_key_v3(
      sqlite3 *db,         /* The connection from sqlite3_open() */
      const char *zDbName, /* Which ATTACHed database to key */
      const void *pKey,    /* The key */
      int nKey,            /* Number of bytes in the key */
      const char *zCodec   /* Encryption algorithm */
   );

The recommended keying format for pKey to point to a UTF-8 encoded, zero-terminated string that is the encyption key and for nKey to be -1. This format uses the --textkey keying method described above. The string key that pKey points to is hashed using a cryptographic hash algorithm and the hash value becomes the actual encryption key.

Alternatively, pKey can be a pointer to directly at the encryption key itself. This is equivalent to the --key or --hexkey keying methods described above. In this case, the nKey parameter must be the length, in bytes, of the encryption key.

The zCodec parameter is usually NULL. However, in variants of SEE that support multiple encryption algorithms (for example sqlite3-see.c that support AES-128-OFB, AES-256-OFB, and RC4) the zCode parameter can optionally be a string which is the name of the encryption algorithm to use. The encryption algorithm name is all lower-case, without spaces or punctuation. The following is a complete list of encryption algorithm names supported by SEE across all variants:

Each SEE variant will typically only support a subset of the algorithms listed above. For example, with sqlite3-see-aes128ccm.c, only the "aes128ccm" algorithm is supported and so for that variant the zCoded argument must either be NULL or the string "aes128ccm". If an unknown algorithm is specified, sqlite3_key_v3() return SQLITE_ERROR_KEY.

The following table shows which SEE variants support which algorithms:

SEE Variant Algorithm
 aes128ccm   aes128ofb   aes256gcm   aes256ofb   rc4 
 sqlite3-see-aes256-openssl.c        ✓  
 sqlite3-see-aes256-cryptoapi.c        ✓  
 sqlite3-see-cccrypt.c    ✓   ✓  
 sqlite3-see-aes256-ofb.c        ✓  
 sqlite3-see-bcrypt.c      ✓    
 sqlite3-see.c    ✓ ✓ ✓ ✓
 sqlite3-see-aes128-ofb.c    ✓      
 sqlite3-see-aes128-ccm.c  ✓        
 sqlite3-rc4.c          ✓

The see-ccrypt.c module uses "aes128ofb" encryption by default. However, if see-ccrypt.c is compiled with -DCCCRYPT256 and if the sqlite3_key_v3() interface is called with nKey==32 and with zCodec==NULL, then "aes256ofb" encryption is used instead. This is to support legacy behavior. New application are advised to actually specify "aes128ofb" or "aes256ofb" for the zCodec parameter, rather than depending on the key length.

If you specify an incorrect key, you will not get an error message right away. But the first time you try to access the database you will get an SQLITE_NOTADB error with a message of "file is encrypted or is not a database".

The zDbName parameter specifies which ATTACH-ed database should get the key. Usually this is "main". You can pass in a NULL pointer as an alias for "main". Unless you have a good reason to do otherwise, it is best to pass in a NULL pointer for the zDbName parameter.

You can change the key on a database using the sqlite3_rekey() routine: 

   int sqlite3_rekey_v3(
      sqlite *db,                    /* Database to be rekeyed */
      const char *zDbName,           /* Which ATTACHed database to rekey */
      const void *pKey, int nKey,    /* The new key */
      const char *zCodec             /* Encryption algorithm */
   );

A NULL key decrypts the database.

Rekeying requires that every page of the database file be read, decrypted, reencrypted with the new key, then written out again. Consequently, rekeying can take a long time on a larger database.

Most SEE variants allow you to encrypt an existing database that was created using the public domain version of SQLite. This is not possible when using the authenticating version of the encryption extension in see-aes128-ccm.c. If you do encrypt a database that was created with the public domain version of SQLite, no nonce will be used and the file will be vulnerable to a chosen-plaintext attack. If you call sqlite3_key_v2() immediately after sqlite3_open() when you are first creating the database, space will be reserved in the database for a nonce and the encryption will be much stronger. If you do not want to encrypt right away, call sqlite3_key_v2() anyway, with a NULL key, and the space for the nonce will be reserved in the database even though no encryption is done initially.

A public domain version of the SQLite library can read and write an encrypted database with a NULL key. You only need the encryption extension if the key is non-NULL.

6.0 Using the "key" PRAGMA and URI Parameter

As an alternative to calling sqlite3_key_v2() to set the decryption key for a database, you can invoke a pragma: 

    PRAGMA textkey='your-secret-key';

Or: 

    sqlite3_open("file:///...?textkey=...", ...);

Use the textrekey pragma to change the encryption key: 

    PRAGMA textrekey='new-passphrase';

For these latter three techniques, the provided key or passphrase is hashed using a cryptographic hash function and the resulting hash becomes the actual encryption key. In other words, these techniques work like the --textkey argument to the command-line interface, which is the recommended keying procedure.

Similar pragmas and filename query parameters exist for --key and --hexkey keying methods: 

    PRAGMA key='secret-key';
    PRAGMA hexkey='7365737265742d6b6579';

    sqlite3_open("file:///...?key=secret-key", ...);
    sqlite3_open("file:///...?hexkey=7365737265742d6b6579", ...);

    PRAGMA rekey='new-key';
    PRAGMA hexrekey='6e65772d6b6579';

When using a pragma to set the encryption key, the pragma must be invoked before trying to do any other interaction with the database. When using a URI parameter, any unusual characters, in particular, ?, #, and &, as well as any non-ASCII bytes, must be hex-encoded in the form %XX.

Through the use of these pragmas, it is never necessary to directly invoke the sqlite3_key_v3() or sqlite3_rekey_v3() interfaces. This means that SEE can be used with language wrappers that do not know about those interfaces.

The "key", "hexkey", and "textkey" PRAGMA statements expect the same key strings as the "-key", "-hexkey", and "-textkey" arguments to the command-line shell, respectively.

The key PRAGMAs will return a string "ok" if they successfully load an encryption key into SEE. If you invoke one of these pragmas on a system that does not support encryption, or if the key loading operation fails for any reason, then nothing is returned. Note that the "ok" string is returned when any key is loaded, not necessarily the correct key. The only way to determine if the key is correct is to try to read from the database file. An incorrect key will result in a read error.

7.0 Using The ATTACH Command

The key for an attached database can be specified using a URI filename and the textkey=, key=, or hexkey= query parameters on that filename. Like this: 

    ATTACH DATABASE 'file:file2.db?textkey=zyzzy';

If no KEY is specified, the same key is used that is currently in use by the main database. If the attached database is not encrypted, specify an empty string as the key.

8.0 Key Material

The amount of key material actually used by the encryption extension depends on which variant of SEE you are using. With "rc4", the first 256 bytes of key are used. With the "aes128ofb" and and "aes128ccm" variants, the first 16 bytes of the key are used. With "aes256ofb" and "aes256gcm", the first 32 bytes of key are used.

If you specify a key that is shorter than the maximum key length, then the key material is repeated as many times as necessary to complete the key. If you specify a key that is larger than the maximum key length, then the excess key material is silently ignored.

8.1 Encryption algorithm selection using a key prefix (Legacy Only)

This section describes behavior that is supported for backwards compatibility in legacy applications only. New applications should make use of the zCodec parameter to the sqlite3_key_v3() interface to select encryption algorithms, not a key prefix.

For the "sqlite3-see.c" SEE variant, the key may begin with a prefix to specify which algorithm to use. The prefix must be exactly one of "rc4:", "aes128:", or "aes256:". The prefix is not used as part of the key sent into the encryption algorithm. So the real key should begin on the first byte after the prefix. Take note of the following important details:

9.0 The Importance of a Nonce

The encryption is much more secure if it has a random nonce value on each page of the database. Without a nonce, the encryption can be broken using a chosen-plaintext attack. Purists will argue (rightly) that the encryption is weak without a nonce.

The number of bytes of nonce on each page of the database is determined by byte 20 of the database file. This value is set to zero by default in databases created by the public-domain version of SQLite. You can change this byte to a positive value by running the VACUUM command using an SEE-enabled version of SQLite.

You can check the size of the nonce for a database by using the ".dbinfo" command in an ordinary sqlite3.exe command-line shell program. The output of the ".dbinfo" command will look something like this: 

database page size:  4096
write format:        1
read format:         1
reserved bytes:      12    ← Nonce size
file change counter: 3504448735
database page count: 14190
freelist page count: 0
schema cookie:       107
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3008008
number of tables:    53
number of indexes:   53
number of triggers:  0
number of views:     0
schema size:         14257

Bytes 16 through 23 of the database are unencrypted. Thus, you can always check to see how much nonce is being used, even on an encrypted database file, just by looking at byte 20. It is recommended that any product that uses encryption check this byte to make sure it is being set to 4 or 12 or 32 and not 0.

The nonce size may be increased, but not decreased, with one of the following approaches:

From the command-line shell: 

    .filectrl reserve_bytes 32
    VACUUM;

Using the C API: 

    int nNonce = 32;
    sqlite3_file_control(db, "main", SQLITE_FCNTL_RESERVE_BYTES, &nNonce);
    sqlite3_exec(db, "VACUUM;", 0, 0, 0);

10.0 Security Checklist

When using SEE in an application, it is recommended that you double-check that everything is implemented correctly, and that you are getting strong encryption, by performing the following tests, at a minimum:

  1. Use the SEE-enabled CLI to run the "sqlite3 $DATABASE .dbinfo" command (adding an appropriate -key, -hexkey, or -textkey argument) and verify that your encrypted database files contain a nonce. The nonce should be at least 12 bytes.
  1. Use the SEE-enabled CLI to read an encrypted database, but change the last character of the supplied key by a single character value. Verify that a minor change to the end of the key like this renders the database unreadable. The error message should be "file is not a database". Repeat this test with multiple variations of the key. Confirm that the database is only accessible if the key is exactly correct.
  1. Try to compress an encrypted database file and verify that the file is uncompressible. In other words, run a program like "zip" or "gzip" against the encrypted database and verify that compression does not change the size of the file more than a few bytes smaller.

Limitations

  1. TEMP tables are not encrypted.
  1. In-memory (":memory:") databases are not encrypted.
  1. Bytes 16 through 23 of the database file contain header information which is not encrypted.

11.0 How SEE Works

Each page is encrypted separately. The key to encryption is a combination of the page number, the random nonce (if any) and the database key. The data is encrypted in both the main database and in the rollback journal or WAL file but is unencrypted when held in memory. This means that if an adversary is able to view the memory used by your program, she will be able to see unencrypted data.

The nonce value is changed by a rollback.

The see-aes128-ccm.c variant uses AES in CCM mode with a 16-byte randomly chosen nonce on each page and and 16-byte message authentication code (MAC). Thus with crypto3ccm.c, 32 bytes of every database pages are taken up by encryption and authentication overhead. Consequently, database files created using crypto3ccm.c may be a little larger. Also, because the MAC is computed whenever a page is modified, and verified when a page is read, crypto3ccm.c will often be a little slower. Such is the cost of authentication.