[odb-users] Encrypting the Sqlite DB

[Lavi Kwiatkowsky]

Hi,

First of all, Boris - thanks for the prompt reply.

Here are my conclusions from researching this subject.

When discussing encryption, we have two basic options:
1.	Application level Encryption: use any existing library and encrypt the information prior to being sent to the DB. 
	Good: 
	•	This option offers a lot of control over the information.
	•	Existing OpenSSL Library can be reused.
	Bad:
	•	A lot of programming would have to take place in order to support this throughout the DAL, entities, repositories etc.
	•	We won’t be able to query or sort the DB where there are encrypted columns.
	•	Bad performance for encrypting strings one at a time as opposed to encrypting the whole DB and decrypting it as we read pages. 
	•	Finally, if any information is left unencrypted – will be used by potential hackers to decrypt the rest. This is how you get your neighbors WIFI (even if its WPA2 PSK with 128bit AES). 

Possible solutions:
	•	http://www.openssl.org/
	•	http://www.cryptopp.com/
	•	http://libtom.org/?page=features – free, lightweight library.

2.	Infrastructure level Encryption: use a mechanism which wraps the sqlite3 db and provides the encryption automatically and on a File\Table space\column level. 
	
	Good: 
	•	Easiest solution to implement, just add a library and pass the encryption key.
	•	All DB information is encrypted, no flaws.
	•	Great performance - Since we encrypt whole blocks of data, the process is more efficient, especially with AES. A small overhead of 5-15% compared to unencrypted sqlite.
	•	Queries can be performed as usual. 
	Bad:
	•	Most solutions cost money between 100 and 2000$.
	•	We need to make sure this plays along well with the ODB Framework.

Possible solutions:
	http://www.hwaci.com/sw/sqlite/see.html? – The official SQLITE3 developer’s solution. It’s the best solution but also expensive at 2000$ for our commercial needs. It can be seamlessly 	integrated along with ODB and no special handling is needed, I will describe later on how. 

	http://sqlcipher.net/documentation/ - Has a community edition for free: https://github.com/sqlcipher/sqlcipher 
	Has good reputation and recommendations. Uses pragma language to pass the key (more on this later). Based on OpenSSL.
	Since they encrypt the whole DB, they have weird workarounds such as changing the page size to add information.

	http://sqlite-crypt.com/index.htm - Offers 256 AES but not too many features, they offer a free windows trial edition (key kept as cleartext in this edition).

	https://github.com/OlivierJG/botansqlite3#readme Open source library based on Botan encryption lib, requires a lot of messing around to get it to work in my opinion.


To work with the first option, we all know what to do – introduce an encryption class, and wrap entities with encryption entities.

To work with the second option was tricky to integrate with ODB but I made it happen:

1.	All you have to do is go to the RepositoryFactory.cpp, in the method createDB where we hold the DB object, we can get the sqlite3 handle. 

>From ODB Documentation:
The begin_immediate() and begin_exclusive() functions allow us to start an immediate and an exclusive SQLite transaction on the connection, respectively. Their semantics are equivalent to the corresponding functions defined in thesqlite::database class (Section 16.2, "SQLite Database Class"). The handle()accessor returns the SQLite handle corresponding to the connection.
	
What to do in the code:
o	Change shared_ptr<odb::database> to shared_ptr<odb::sqlite::database>
o	Once it’s an sqlite3, we can perform db->connection()->handle() which retrieves the actual pointer.

2.	Using this handle we can use SEE (official sqlite solution) method to pass the encryption key, or run pragma commands for the DB which are just like insert or update, but have been added to handle encryption. 
 
Example for pragma command:
If (sqlite3_prepare_v2(db, "PRAGMA user_version;", -1, &stmt_version, NULL) == SQLITE_OK) 
{
        while(sqlite3_step(stmt_version) == SQLITE_ROW) 
	{
            databaseVersion = sqlite3_column_int(stmt_version, 0);

For SEE we also have a method:
int sqlite3_key(
   sqlite3 *db,        /* The connection from sqlite3_open() */
   const void *pKey,   /* The key */
   int nKey            /* Number of bytes in the key */
);

int sqlite3_rekey(
   sqlite *db,                    /* Database to be rekeyed */
   const void *pKey, int nKey     /* The new key */
);

Best Regards,
Lavi
-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: Tuesday, March 5, 2013 5:16 PM
To: Lavi Kwiatkowsky
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] Encrypting the Sqlite DB

Hi Lavi,

Lavi Kwiatkowsky <lavik at codevalue.net> writes:

> What is the best way to integrate an encryption mechanism ?

While I don't have any direct experience, I did some Google searches and found these two pages that are quite informative:

http://stackoverflow.com/questions/5669905/sqlite-with-encryption-password-protection

http://stackoverflow.com/questions/8384789/encrypting-sqlite

All of the approaches discussed work transparently at the SQLite level so they should work out of the box with ODB.

Boris