Encrypted SQLite

[mako]

Hi,
Poco is fantastic library. The one thing that is missing would be encrypted SQLite.
I have tried to do it on my own, I used modified SQLite available here http://www.zetetic.net/software/sqlcipher so that was pretty easy to compile. But I haven't tested it yet.

Do you think that feature could be included in Poco?

[aderouineau]

Personally, encrypted sqlite shouldn't have a higher priority than native pgsql and mssql connectivity...

[marshall]

It looks like SQLCipher is at least source compatible with SQLite.. for unofficial purposes it looks like it might be as easy as statically building/linking SQLCipher and dropping it in place into the Poco build.

[alex]

Do you think that feature could be included in Poco?

Sure. Once you are ready, let us know your sourceforge username and we'll give you write permission so you can submit the code in the sandbox.

[raulgd]

Hi guys, I know it's an old post and sorry, but does anyone know what happened to this? was SQLCipher commited? is it included in POCO 1.4.1?

Thanks!

[raulgd]

Ok, I managed to replace sqlite.c in Data with SQLCipher and had to do some config changes but it built.
Now I just need to run some tests to see that the encryption features work, after that, I'll post how to build it if anyone needs it.
Also, I currently work on windows only, so the changes where to the solution file for the Data/SQLite module, perhaps someone will help me to configure and build on other platforms to see if it works as well?
I'll post my changes ASAP, I just need a couple of hours to leave work and get home.

[raulgd]

Ok, It's a mess to be able to integrate SQLCipher, mostly because of the way the SQLite code is modified.
I think I'll analyze how the SQLCipher code works, and then, instead of using directly openSSL and having to do a mess for building it, I'll use POCO crypto, so when someone wants to build POCO with crypto, you'll get the crypto options for SQLite.

Also another problem for using SQLCipher, is that because most changes in SQLite are spread throughout the code, it's harder for SQLCipher developers to upgrade SQLite everytime a new release is ready, so for example, right now, SQLite is on version 3.7.5, whereas the latest release of SQLCipher is based on 3.7.2.

I'll see what I can do, if anyone else is willing to help me, just message me, as this is also gonna be a learning experience, as I haven't used POCO crypto before.

[raulgd]

Ok, I managed to build SQLCipher as an SQLite amalgamation on windows using cygwin, after that, I replaced the Data/SQLite/src sqlite3.h and sqlite3.c files with the SQLCipher amalgamated files.

Then added these two preprocessor directives for building: SQLITE_HAS_CODEC and SQLITE_TEMP_STORE=2 to the MSVC solution.
After that, I added in the include paths, the path to my openssl include folder.
Then I added in the lib paths, the path to my openssl lib folder.
For the shared builds, I added in the Linker -> Input -> Additional Dependencies: libeay32.lib
For the static builds, I added to Librarian -> Additional Dependencies: libeay32.lib

After that, just build, and for testing, I used the Tuple example for SQLite, first, I ran the example as it comes out of the box, and it generates a sample.db file, I opened the file with a Hex Editor, and I was able to read the data in clear text, and saw the Lisa and Bart Simpson text.
Now, for testing the encryption, I added the following code right after the: session << "CREATE TABLE Person ... line:

Code: Select all

//add on line 62: encrypt and use password: passphrase
session << "PRAGMA key = 'passphrase'", now;


After that, it was able to print out the data to the screen, so it means it was able to read it correctly, and when I opened the sample.db file with the hex editor again, I saw garbage instead of clear text, which means the encryption was successful.

Can somebody please help me test this on other platforms to see that it works and see how can we integrate this to the POCO source?

I'm trying to attach the sqlite3 amalgamation files I created, but I guess the forum has issues with the 1MB zip file.

[surendrank]

With the help of sqlcipher forums i managed to build sqlcipher in windows with sqlcipher.dll.
I'm not clear with few things:
1.) The files u mentioned "SQLCipher amalgamated files" they are the files dropped out during sqlcipher build isn't it?
2.) After replacing "sqlite3.h and sqlite3.c " did you rebuilt poco?

Things which i followed and got hooked with unencrypted DB:
1.)I built sqlcipher using mysys.
2.)Replaced Data/SQLite/src "sqlite3.h and sqlite3.c" with files dropped out in sqlcipher build directory during sqlcipher build.
3.)Rebuilt poco in windows. Got some undefined reference in sqlite3.c Fixed it by manually defining preprocessor (SQLITE_TEMP_STORE=2, SQLITE_HAS_CODEC) in SQLite_vs90.sln. so finally built with no errors.
4.)I tried the Tuples examples which you mentioned above. Ended up with unencrypted DB.

can you plz tell me where i went wrong........

Ok, I managed to build SQLCipher as an SQLite amalgamation on windows using cygwin, after that, I replaced the Data/SQLite/src sqlite3.h and sqlite3.c files with the SQLCipher amalgamated files.

Then added these two preprocessor directives for building: SQLITE_HAS_CODEC and SQLITE_TEMP_STORE=2 to the MSVC solution.
After that, I added in the include paths, the path to my openssl include folder.
Then I added in the lib paths, the path to my openssl lib folder.
For the shared builds, I added in the Linker -> Input -> Additional Dependencies: libeay32.lib
For the static builds, I added to Librarian -> Additional Dependencies: libeay32.lib

After that, just build, and for testing, I used the Tuple example for SQLite, first, I ran the example as it comes out of the box, and it generates a sample.db file, I opened the file with a Hex Editor, and I was able to read the data in clear text, and saw the Lisa and Bart Simpson text.
Now, for testing the encryption, I added the following code right after the: session << "CREATE TABLE Person ... line:

Code: Select all

//add on line 62: encrypt and use password: passphrase
session << "PRAGMA key = 'passphrase'", now;


After that, it was able to print out the data to the screen, so it means it was able to read it correctly, and when I opened the sample.db file with the hex editor again, I saw garbage instead of clear text, which means the encryption was successful.

Can somebody please help me test this on other platforms to see that it works and see how can we integrate this to the POCO source?

I'm trying to attach the sqlite3 amalgamation files I created, but I guess the forum has issues with the 1MB zip file.

[raulgd]

No, the amalgamated files is not what the build drops out.
The problem is that you built the DLL instead of the amalgamation.
In SQLite, an amalgamation is that when you build the project, instead of getting a library, you get as the output a sqlite3.h and sqlite3.c files, where all of the original source files are placed together inside them, so what you do is, build the amalgamation files instead of the DLL, replace the POCO SQLite amalgamation with the ones you've built from sqlcipher, then on the VS projects, add the preprocessor definitions and the OpenSSL includes and libeay library to your project properties, and then build poco.

So the result would be that you will use poco with Data/SQLite, but because you replaced it with sqlcipher, you'll be able to use the PRAGMA directive as well for crypting your SQLite database.