Open Encrypted Database in PHP Created In Xojo

I’m using the built-in SQLiteDatabase in a desktop app, and have need to open the database on a server running PHP. Has anyone already cracked this nut?

Thanks!

Kimball I am guessing the answer is no. Personally I try to avoid the RoR/PHP world. Not always successful.

I’ve had great success in both PHP and RoR. Just seems that the database encryption chosen by Xojo is not particularly portable.

It requires you compiling SQLite yourself with the SEE extensions that you can purchase from the SQLite team directly.

Hi @Phillip Zedalis - that’s close to accurate, but not the only way: PHP accesses sqlite3 databases through a driver of sorts, and if your driver supports encryption, you can open / read encrypted databases. The SQLite driver for PHP has hooks built in to allow developers to provide their own implementation of encryption. The SEE extension provides one such implementation, but it is not the only one.

For example, there is SQLCipher, which allows you to use any encryption algorithm supported by openssl for your SQLite database. The SEE extension provides standard encryption algorithms:

RC4
AES-128 in OFB mode
AES-128 in CCM mode
AES-256 in OFB mode

openssl provides many dozens of algorithms, including RC4, AES-128-OFB, and AES-256-OFB. It does not (by default, or easily) provide AES-128-CCM.

If the SQLite databases from Xojo are encrypted using one of the supported algorithms from the SEE, I should be able to use the SQLCipher extension to access my databases. In consulting the Xojo documentation, we find that the SQLite engine provided by Xojo says “AES-128 encryption is used.” - but they do not specify if they are using the OFB or CCM version of it in the SEE.

Yesterday I spent some time getting SQLCipher installed on one of my dev servers, and verifying it can read/write its own encrypted databases. I then tried to get it to open several different Xojo database files… none of them worked, no matter what AES-128 flavor I tried. The only one I cannot try is AES-128-CCM, which leads me to believe that must be the cipher Xojo elected to use from the SEE.

Can anyone from Xojo chime in to confirm my findings?

The reason I am looking into this is here: https://forum.xojo.com/6036-seeking-advice-on-a-new-project-php-or-we.

I’m not dead set on needing to get access to these sqlite databases in an php environment, but I’d like more input on the overall big picture described at that post.

Thanks!

[quote=42957:@Kimball Larsen]In consulting the Xojo documentation, we find that the SQLite engine provided by Xojo says “AES-128 encryption is used.” - but they do not specify if they are using the OFB or CCM version of it in the SEE.
[/quote]
We use http://www.hwaci.com/sw/sqlite/see.html as licensed directly from Dr. Hipp.

@Norman Palardy - the SEE provides 4 different ways to encrypt the databases, 2 of which can be described as AES-128. Which specific cipher is Xojo using within the SEE?

I’m not the expert on this and it does not seem to me that we select a method - rather just a key is set.

If you check that page there’s no extra API for selecting a method - just a “Usage” not that says you should set the key right away after attaching either using the pragmas or using the C api

EDIT : Further reading leads me to suspect it’s AES-128 OFB

Thanks, @Norman Palardy - Though I suspect that it is AES-128-CCM, given my above comments. I’ll try again tomorrow to connect using ofb.

Hi Kimball. Yes I know there are multiple ways to encrypt SQLite databases. It was my understanding (and assumption) it would not be possible to open a Xojo encrypted database using SEE from PHP without using SEE. The reason is you compile SQLite directly with the extensions and therefore any encryption that occurs after a SQLite connection is made would not work because they can’t connect. It happens at the driver level. In fact as I think about it you’d likely have to compile PHP with the SQLite SEE header. This is all as assumption though as I haven’t tried to do it.

I’m basing my suspicions on what I see in the C source files we compile the plugin from.
But I could easily be misreading this

However the encryption is done right low level in at the db C API level - not just encrypting data.
For the split code base you literally replace “sqlite.c” with the one that includes the SEE code and compile.
It encrypts ALL the data pages & metadata as db pages are read & written - not just the data IN the columns

I can confirm that it is AES-128 OFB after some testing with SQLiteManager.

https://forum.xojo.com/39543-sqlite-encryption