SQLCipher Design - Security Approach and Features (original) (raw)

SQLCipher is a specialized build of the excellent SQLite database that performs transparent and on-the-fly encryption. Using SQLCipher, an application uses the standard SQLite API to manipulate tables using SQL.

Behind the scenes, the library silently manages the security aspects, making sure that individual data pages are encrypted and decrypted as they are written to and read from storage.

The adjacent graphic provides a representative view of the file structure of a SQLCipher database

SQLCipher Page Structure

Security Design

The following is a brief overview of SQLCipher's security design.

  1. The encryption algorithm is 256-bit AES in CBC mode
  2. Each database page is encrypted and decrypted individually. The default page size is 4096 bytes but this can be adjusted at runtime to improve performance for certain query types.
  3. Each page has it’s own random initialization vector. The IV is generated by a cryptographically secure random number generator (e.g. OpenSSL’s RAND_bytes, CommonCrypto's SecRandom, LibTomCrypt Fortuna), and is stored at the end of the page. IVs are regenerated on write so that the same IV is not reused on subsequent writes of the same page data.
  4. Every page write includes a Message Authetication Code (HMAC-SHA512) of the ciphertext and the initialization vector at the end of the page. The MAC is checked when the page is read back from disk. If the ciphertext or IV have been tampered with or corrupted the HMAC check will cause SQLCipher to report a problem with the database.
  5. When initialized with a passphrase SQLCipher derives the key data using PBKDF2-HMAC-SHA512. Each database is initialized with a unique random salt in the first 16 bytes of the file. This salt is used for key derivation and it ensures that even if two databases are created using the same password, they will not have the same encryption key. The default configuration uses 256,000 iterations for key derivation (this can be changed at runtime using PRAGMA kdf_iter).
  6. The key used to calculate page HMACs is different that the encryption key. It is derived from the encryption key and using PBKDF2 with 2 iterations and a variation of the random database salt.
  7. If use of a passphrase is undesirable, an application may provide raw binary key data (for instance to support vaulted keys, or the use of PKI based key exchange).
  8. When encrypted, the entire database file appears to contain random data.
  9. Memory allocated by SQLCipher is locked when possible (via mlock/VirtualLock) and wiped before it is freed
  10. SQLCipher does not implement its own encryption. Instead it uses the widely available encryption libraries like OpenSSL libcrypto, LibTomCrypt, and CommonCrypto for all cryptographic functions. The cryptography provider used depends on the platform and configuration options.

Database Encryption and Temporary Files

All data in the main database file is encrypted. In addtion, SQLCipher encrypt data pages in journal files. Provided that you taken the important step of disabling file base temporary stores (i.e. --enable-tempstore=yes during configuration and define SQLITE_TEMP_STORE=2 during build), we are primarily concerned with the following:

Other transient files are not encrypted, so you must disable file based temporary storage if your application will use temp space, as noted above.

An Illustrative Terminal Listing

~ sjlombardo$ hexdump -C sqlite.db
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
...
000003c0  65 74 32 74 32 03 43 52  45 41 54 45 20 54 41 42  |et2t2.CREATE TAB|
000003d0  4c 45 20 74 32 28 61 2c  62 29 24 01 06 17 11 11  |LE t2(a,b)$.....|
...
000007e0  20 74 68 65 20 73 68 6f  77 15 01 03 01 2f 01 6f  | the show..../.o|
000007f0  6e 65 20 66 6f 72 20 74  68 65 20 6d 6f 6e 65 79  |ne for the money|

~ $ sqlite3 sqlcipher.db
sqlite> PRAGMA KEY='test123';
sqlite> CREATE TABLE t1(a,b);
sqlite> INSERT INTO t1(a,b) VALUES ('one for the money', 'two for the show');
sqlite> .quit

~ $ hexdump -C sqlcipher.db
00000000  84 d1 36 18 eb b5 82 90  c4 70 0d ee 43 cb 61 87  |.?6.?..?p.?C?a.|
00000010  91 42 3c cd 55 24 ab c6  c4 1d c6 67 b4 e3 96 bb  |.B?..?|
00000bf0  8e 99 ee 28 23 43 ab a4  97 cd 63 42 8a 8e 7c c6  |..?(#C??.?cB..|?|

~ $ sqlite3 sqlcipher.db
sqlite> SELECT * FROM t1;
Error: file is encrypted or is not a database

Packaging

SQLCipher is an extension to SQLite, but it does not function as a loadable plugin for many reasons. Instead, SQLCipher modifies SQLite itself, and is maintained as a separate version of the source tree. SQLCipher releases are baselined against a specific source version of SQLite. However, the project minimizes alterations to core SQLite code to reduce the risk of breaking changes during upstream SQLite merges.

The reasons that SQLCipher is packaged this way, as opposed to a "plugin" or extension to the SQLite amalgamation, follow: