SQLite Database Encryption Issue

While I know that this has been discussed before, I would like to see if there is any new information.

I am using a SQLite DB and would like to be able to encrypt it and read/write both in Xojo and Navicat. I know that Bob (Keeney/Guru/I am not worthy!) has had luck with Navicat opening encrypted Xojo SQLite DB’s, but I’m afraid I haven’t.

If I initially encrypt the DB in Xojo, it won’t open in Navicat even if I put in the encryption string. The opposite is also true. If I create the DB in Navicat and encrypt it, the same encryption string will not work in Xojo.

The documentation for Xojo says that SQLite encryption is using AES-128. Is it possible that Navicat is using something else?

You are correct. I’ve never had any issues with NaviCat opening encrypted files made in Xojo. Another utility you might try is SQLite Manager http://www.sqlabs.com/sqlitemanager.php I don’t like it’s interface very much, but it also handles encrypted Sqlite databases.

Oh, and you ARE worthy. Really. I’m just a poor shmuck like everyone else here. I’ve just been doing it a long time. :slight_smile:

It’s not so much WHAT encryption algorithm is used but HOW this is incorporated into the sqlite engine
There are several encryption modules for sqlite - and they are not cross compatible as far as I know
We use the one supplied by Dr Hipp
And if another vendor uses a different one they will not be able to open the database with the encryption
And the reverse is true

Thanks, Guys. Dunno. It seems like I used to be able to go back and forth, but I’m not holding my mouth right or something. Norman, I can’t find out what encryption Navicat uses. It seemed like it worked before. I’ve got Navicat Premium up to date. I’ll keep fishing.

Unless they’re using the one from Dr Hipp I would not expect it to work
Just ask Navicat support if they use this http://www.hwaci.com/sw/sqlite/see.html

Thanks, Norman. I have submitted a support ticket. I did notice that, per documentation, Xojo uses SQLite v: 3.8.5. When checking the version on Navicat, I get v: 3.8.2. Might that be the issue?

Between major versions SQLIte is pretty good about compatibility
So I’d expect 3.8.2 to open a db from 3.8.5 and vice versa

I have had really good luck with SQLite Manager with cubeSQL and SQLite dbs.

I have had good luck with (unencrypted) SQLIte, and MySQL dbs with NaviCat.

Probably because it was built with REAL Studio…

Got a great reply from the folks @ Navicat.

I checked a built Xojo project and in the Libs folder there was a file called SQLiteDatabase.dll. What do you think, Norman. Would it be possible to put that into the C:\…Navicat directory, rename their old sqlite3.dll, and then rename the SQLiteDatabase.dll from Xojo to sqlite3.dll?

No
That would be the plugin which Navicat wont have a clue about

Thanks, Norman. So does anyone know where I could find a sqlite3.dll that uses Dr. Hipps’ AES-128 encryption?

You probably won’t since that DLL would be commercial
Dr Hipps encryption is a paid for addition to SQLite

OK, Norman. Thanks.

Am I’m the only one who thinks this is insane? With the same encryption method and given the same password it should be accessible. If it isn’t then that seemsi like a bug in the encryption to me.

Different salts?

Salting should be up to the programmer, not the plugin. Using a salt in the plugin has only one effect: make it incompatible.

This has more to do with HOW the encryption is incorporated to the engine than whether its using AES-128 / 256 etc.
They each do it slightly differently which is what makes them incompatible with each other.

AES = Advanced Encryption Standard

The implementation should not effect the outcome. If it does then that would violate the standard and would be a bug, wouldn’t it?

[quote=156385:@Norman Palardy]This has more to do with HOW the encryption is incorporated to the engine than whether its using AES-128 / 256 etc.
They each do it slightly differently which is what makes them incompatible with each other.[/quote]