yaourt/sqleet

SQLite3 encryption that sucks less

sqleet is a public domain encryption extension for SQLite3.

• Compiling
• Cryptography buzzwords
• Example
• SQLite3 encryption API
• Android support
• Remarks
• Versioning scheme
• License

Compiling

SQLite3 shell with sqleet encryption support can be compiled as follows:

    % # UNIX
    % gcc sqleet.c shell.c -o sqleet -lpthread -ldl

    % # Windows
    % gcc sqleet.c shell.c -o sqleet

Example illustrates sqleet encryption using the compiled shell.

To use sqleet as a library, the recommended way is to download a preconfigured release package instead of cloning the master. The contained sqleet.c and sqleet.h files are drop-in replacements for the official sqlite3.c amalgamation and sqlite3.h header. The C interface of the sqleet encryption extension is described in section SQLite3 encryption API.

Run ./script/amalgamate.sh <sqleet.c >amalgamation.c to produce amalgamation of custom SQLite3/sqleet version with encryption support.

Cryptography buzzwords

• PBKDF2-HMAC-SHA256 key derivation algorithm with a 16-byte random salt and 12345 iterations.
• ChaCha20 stream cipher with one-time keys.
• Poly1305 authentication tags.

A low-level description of the encryption scheme is available in sqleet.c:144.

Example

Encrypting a database with a password "swordfish".

[sqleet]% hexdump -C hello.db 
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  10 00 01 01 00 40 20 20  00 00 00 02 00 00 00 02  |.....@  ........|
*
00000fd0  00 00 00 2b 01 06 17 17  17 01 37 74 61 62 6c 65  |...+......7table|
00000fe0  68 65 6c 6c 6f 68 65 6c  6c 6f 02 43 52 45 41 54  |hellohello.CREAT|
*
00001fe0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 0f  |................|
00001ff0  01 02 27 48 65 6c 6c 6f  2c 20 77 6f 72 6c 64 21  |..'Hello, world!|
[sqleet]% ./sqleet hello.db
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
sqlite> PRAGMA rekey='swordfish';
sqlite> .quit
[sqleet]% hexdump -C hello.db  
00000000  f5 85 5b cf b4 91 d1 28  f8 5c 0e da ee 7f 66 d1  |..[....(.\....f.|
00000010  55 4e 9f 71 a8 e0 8d f0  52 d8 5c 17 63 9f cc 71  |UN.q....R.\.c..q|
00000020  b3 69 9d c0 ef d1 31 5c  52 fa a3 64 47 be 65 98  |.i....1\R..dG.e.|
00000030  58 53 9c 2e db 3a ce 66  a4 d1 22 bd d2 c8 13 1b  |XS...:.f..".....|
*

The database can only be read with the correct password.

[sqleet]% ./sqleet hello.db 
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
sqlite> .dump       
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (26) file is not a database *****/
ROLLBACK; -- due to errors
sqlite> PRAGMA key='swordfish';
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE hello(x);
INSERT INTO hello VALUES('Hello, world!');
COMMIT;

If the target database has a non-default page size (i.e., other than 4096), then page_size must be initialized accordingly with PRAGMA before setting the encryption key. See Remarks for more information.

SQLite3 encryption API

sqleet defines SQLITE_HAS_CODEC at the compile time to expose SQLite3's sqlite3_key() and sqlite3_rekey() functions for managing encryption keys.

SQLITE_API int sqlite3_key(      /* Invoked by PRAGMA key='x' */
  sqlite3 *db,                   /* Database to key */
  const void *pKey, int nKey     /* Key (password) */
);

sqlite3_key() is typically called immediately after sqlite3_open() to specify an encryption key for the opened database. The function returns SQLITE_OK if the given key was correct; otherwise, a non-zero SQLite3 error code is returned and subsequent attempts to read or write the database will fail. Note that the first page of the database is read from the disk in order to validate the key.

SQLITE_API int sqlite3_rekey(    /* Invoked by PRAGMA rekey='x' */
  sqlite3 *db,                   /* Database to rekey */
  const void *pKey, int nKey     /* New key (password) */
);

sqlite3_rekey() changes the database encryption key. This includes encrypting the database the first time, decrypting the database (if nKey == 0), as well as re-encrypting it with a new key. Internally, sqlite3_rekey() performs a VACUUM to encrypt/decrypt all pages of the database. The return value is SQLITE_OK on success and a SQLite3 error code on failure.

In addition, there are sqlite3_key_v2() and sqlite3_rekey_v2() functions that accept the target database name as the second parameter. By default, the main database is used.

Raw keys

Disclaimer: The current interface is experimental and likely to change in future versions (see issue #13 for discussion). Use at your own risk!

The encryption functions pass the provided key string (password) to a key derivation algorithm (i.e., PBKDF2-HMAC-SHA256 with a 16-byte salt and 12345 iterations). Optionally, the user can bypass the key derivation by specifying a raw key in format raw:K where K is a 32-byte binary string or a 64-digit hex-encoded string. This is useful in programs that use sqleet as a library and want to handle key derivation by themselves. Additionally, the raw key string can also be followed by a 16-byte (or 32-hexdigit) salt which is stored in the beginning of the database file (otherwise a random salt is generated). Warning: In any way erroneous raw key (e.g., unsupported length or invalid hex-encoding) results in the key being handled as a normal key including the raw: prefix. Moreover, specifying a salt makes sense only when creating a new database or re-encrypting an existing database, because otherwise the specified salt is overridden by the salt stored in the database file.

Android support

sqleet does not have an out-of-the-box support for Android. However, SQLite Android Bindings project provides an easy way to bundle a custom SQLite3 version (such as sqleet) into an Android application with the standard Android interface android.database.sqlite. In particular, see Using The SQLite Encryption Extension page for build & usage instructions.

Remarks

The contents of an encrypted database file are indistinguishable from random data of the same length. This is a conscious design decision made in sqleet, but as a drawback, database settings cannot be read directly from the database file. Thus, it is the user's responsibility to guarantee that the settings are initialized properly before accessing the database. Most importantly, if the database page size differs from the default value of 4096, then the user must explicitly set page_size to the actual value (using, e.g., PRAGMA command) or otherwise opening the database will fail regardless of correct key.

In contrast, the official SQLite Encryption Extension (SEE) leaves the bytes 16 through 23 of the database header unencrypted so that specific information, including the page size, can be read from encrypted databases - with the obvious cost of making database files distinguishable from random. sqleet can optionally be compiled with the same behavior by giving -DSKIP_HEADER_BYTES=24 flag at compile time (the value 24 only skips the encryption of the bytes 16 through to 23 because the first 16 bytes contain a plaintext salt anyway).

Versioning scheme

sqleet releases follow a perverse form of semantic versioning which requires some explanation. Major version number increments indicate compatibility breaks as usual, but the minor & patch version numbers match the targeted SQLite3 version. For example, sqleet v0.25.1 corresponds to SQLite v3.25.1. Although the target SQLite3 version is the primarily supported, sqleet is typically forward and backward compatible across different SQLite3 versions without any changes to the source code.

License

Like SQLite3, sqleet has been released in the public domain (specifically, under the UNLICENSE license). In other words, feel free to do whatever the fuck you want to with the code. In the unlikely case that your country's legal system is broken with respect to public domain software, contact [email protected] for a custom-licensed version.