SQLite - Creating encrypted databases. How the...?

Question

How can I create an encrypted SQLite database that actually stays encrypted or that I can open afterwards.

I used SQLite2009 Pro Enterprise Manager that "can" create encrypted databases, but after typing in the encryption key they are no longer openable.

I used SQLiteManager from SQLabs to create an encrypted database and while this one can be opened afterwards, this can be done from any SQLite management tool or code without requiring the key I entered.

So, how the heck can you create an encrypted SQLite database please?

I plan to use the database in an Adobe Flex aplication.

Thank you.

Answer 1

Have a look :http://elromdesign.com/blog/2009/04/16/adobe-air-sqlite-manager-api-for-adobe-air-15-with-password-encryption/

It may help.

Answer 2

Re: encrypting an existing database

"Out of curiosity, can a database that was created not encrypted be made so afterwards, when it has all sorts of data in it already?"

You can't encrypt an unencrypted database directly. You can change a database's encryption key using the SQLConnection.reencrypt() method, but it only works on already-encrypted databases:

http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLConnection.html#reencrypt()

However, as a workaround you can open multiple databases within a single SQLConnection and you can copy data and structure from one to the other. I've actually written a utility class that uses that technique to allow you to encrypt an unencrypted db:

https://github.com/probertson/air-sqlite/blob/DB-copy-dev/src/com/probertson/data/DBCopier.as

Answer 3

SQLite supports integrating encryption but the default open-source SQLite distribution does not support encryption. Encrypted versions of SQLite are only going to be portable across different clients or tools if they all use the same encryption extension.

The SQLite developers themselves distribute a commercial version of SQLite that supports encryption transparently. When this module is used for encryption, the encryption works across platforms and every byte written to the file is encrypted. There is nothing in an encrypted SQLite file stored on disk to even indicate it's a SQLite database. Many tools support this implementation by allowing you to drop in the commercial sqlite.dll file in place of the open-source one.

There are also third party extensions that support encryption. For example, System.Data.SQLite supports encryption but utilizes .NET libraries to do so and thus an encrypted System.Data.SQLite can only be read by another client that also uses System.Data.SQLite (this is on purpose, out of deference to the core SQLite developers and their commercial product).

Adobe AIR 1.5 support encryption. I don't know for sure which mechanism is used though, I searched and couldn't find the answer. It's possible that an AIR encrypted database can only be read with AIR. I don't know definitively either way. However, here is a good starting point for learning about working with encrypted databases is AIR:

http://probertson.com/articles/2008/11/18/air-1_5-encrypted-sqlite-database-how-to/