SQLite Database Encryption Issue

Databases
#1

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?

#2

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:

#3

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

#4

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.

#5

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

#6

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?

#7

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

#8

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.

#9

Probably because it was built with REAL Studio…

#10

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?

#11

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

#12

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

#13

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

#14

OK, Norman. Thanks.

#15

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.

#16

Different salts?

#17

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

#18

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.

#19

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?

#20

[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]