Encrypted SQLite - A Gift

[swatts]

Hello My Lovelies,

Sorry I've been quiet, been really really busy. I hope you are all doing well.

Friday at about 4:30 Joerg raised the question of using encryption on SQLite, and it's something that has been raised a few times. So here's how we did it!

The interface to SQLite is a dll - sqlite3.dll for 32 bit or sqlite3_64.dll for 64 bit.

What I really wanted was a drop in dll, so a google search kicked up this....

https://www.zetetic.net/sqlcipher/documentation/

Splendid, but all really wanted was a dll (and I didn't want to build it myself!)

Thinking outside the box, I thought I could download something that used it and swipe the dll (if it's on an appropriate license), well ....

https://sqlitebrowser.org/about/

That was downloaded and installed and these files were available.

Note the libcrypto and libssl dlls are required dependencies

These were moved to the following directory in James toolkit.

Next you will need to modify the open vi in James toolkit to find the new dll

That's about it... So how do you use it.

Firstly I encrypted a database using the DBBrowser software I swiped the dlls from.

Then you have to use the PRAGMA key ='password' SQL statement

Then it will work just fine (albeit a bit slower).

The final piece of the puzzle was to be able to convert an unencrypted dB to an encrypted dB.

This shows how to do it.

It took about an hour to sort this and test it. Which is rather pleasing and a testament to James's design.

If I can be arsed (and there's any interest) I'll load some examples.

Lots of Love

[joerg.hampel]

As I already said on Skype: Fan-diddely-tastic!!!

Thanks for finding and sharing this 💜

[cbutcher]

Thanks for the links and the how-to. Always good to have another tool available!

[joerg.hampel]

After playing around with this some more, trying to make it as easy as possible for our customers to use this, I found this: Just supplying the path to the alternative DLL seems to do the trick alright, with no modifications needed to be made in James' SQLite library VIs:

[joerg.hampel]

...and regarding licensing, it seems to us that the SQLCipher Community Edition is free for commercial projects as per this page:

“SQLCipher Community Edition can be used it in both open source and closed source commercial software, but you must include our BSD-style license and copyright statements prominently in the application and documentation.“