SQLite3 with encryption
- SQLite3 with a key based transparent encryption layer (128⁄256*-bit AES), which encrypts everything including metadata
- you may for example encrypt your current databases, use them as long as you wish, then decide to decrypt them back to plain text and use them from the standard SQLite3 library, also you may use this library just as a standard SQLite3 library
- no external dependencies like OpenSSL, Microsoft Visual C++ Redistributable Packages, Microsoft .NET Framework, …
*Support for 256 bit AES encryption is still experimental
Build script currently generates only solution (*.sln) files for Microsoft Visual Studio IDE, but as SQLite3 and wxSQLite3 are cross-platform, you may try to download the original wxSQLite3 source code and built it yourself for your platform.
- Windows with MS Visual Studio 2012+ (2010 not tested but should work too)
- Download snapshot of this repository, unzip and open it
- The script should generate a solution file (.sln) in the project root dir, open it in VS as usual and upgrade the solution when needed, eg. if you have VS2013 and the script created VS2012 solution (automatic prompt or
Project -> Upgrade Solution)
Build -> Configuration Managerand choose configurations and platforms you want to build
- And here we go
Build -> Build Solution, which should produce binaries in the
Following these steps and building all binaries in their Release versions took me ~2 minutes on my laptop.
Download prebuilt binaries
Try to look for them here
Update to the latest version of SQLite
Because developers of the wxSQLite extension needs to incorporate changes with every new version of SQLite, there is a time lag between a new version of SQLite and wxSQLite. If you want to update to the latest version of wxSQLite, you can do so in two ways:
- Download the source code of the latest release of wxsqlite3
- Extract the
wxsqlite3-*/sqlite3/secure/srcdir from the archive to
srcdir in the project root dir.
VERSIONSfile in the repo root dir keeps an overview of versions of individual components provided in the repo
There are more ways how to add a native on-the-fly encryption layer to your SQLite3 DBs. Namely:
- SQLite Encryption Extension - from authors of SQLite, commercial, $2000
- SQLiteCrypt - commercial, $128
- SQLCipher - partially opensource (I didn’t manage to get it working on Windows though), depends on OpenSSL
So after a few hours spent trying to build SQLCipher, I dived more deeply into the internet and found wxSQLite3, did some scripting to ease the build and this is the result.
SQLite3 Encryption API
#define SQLITE_HAS_CODEC #include <sqlite3.h>
Set the key for use with the database
- This routine should be called right after
sqlite3_key_v2call performs the same way as
sqlite3_key, but sets the encryption key on a named database instead of the main database.
int sqlite3_key( sqlite3 *db, /* Database to be rekeyed */ const void *pKey, int nKey /* The key, and the length of the key in bytes */ ); int sqlite3_key_v2( sqlite3 *db, /* Database to be rekeyed */ const char *zDbName, /* Name of the database */ const void *pKey, int nKey /* The key, and the length of the key in bytes */ );
Testing the key
When opening an existing database,
sqlite3_key will not immediately throw an error if the key provided is incorrect. To test that the database can be successfully opened with the provided key, it is necessary to perform some operation on the database (i.e. read from it) and confirm it is success.
The easiest way to do this is select off the
sqlite_master table, which will attempt to read the first page of the database and will parse the schema.
SELECT count(*) FROM sqlite_master; -- if this throws an error, the key was incorrect. If it succeeds and returns a numeric value, the key is correct;
Change the encryption key for a database
- If the current database is not encrypted, this routine will encrypt it.
nKey==0, the database is decrypted.
sqlite3_rekey_v2call performs the same way as
sqlite3_rekey, but sets the encryption key on a named database instead of the main database.
int sqlite3_rekey( sqlite3 *db, /* Database to be rekeyed */ const void *pKey, int nKey /* The new key, and the length of the key in bytes */ ); int sqlite3_rekey_v2( sqlite3 *db, /* Database to be rekeyed */ const char *zDbName, /* Name of the database */ const void *pKey, int nKey /* The new key, and the length of the key in bytes */ );
- it’s wrapper around sqlite3_key_v2
- example usage:
- it’s wrapper around sqlite3_rekey_v2
- example usage:
- example of decrypting:
Encrypting a new db
open // <-- db is still plain text key // <-- db is now fully encrypted use as usual
Opening an encrypted DB
open // <-- db is fully encrypted key // <-- db is still fully encrypted use as usual // <-- read/written pages are fully encrypted and only decrypted in-memory
Changing the key
open // <-- db is fully encrypted key // <-- db is still fully encrypted rekey // <-- db is still fully encrypted use as usual
open // <-- db is fully encrypted key // <-- db is still fully encrypted rekey with null // <-- db is now fully decrypted to plain text use as usual