Encryption type on Sqlite Database

Hello,

I am trying to open an encrypted SQLite database that has been encrypted with Xojo 2020 r2.1.

From the website: SQLite Encryption Extension: Documentation, it seems the three most common types are:
AES-256 in OFB
AES-128 in OFB
AES-128 in CCM

ScreenGrab

What other parameters would open an encrypted SQLite Xojo database?

Thank you

You can look for SQLDatabaseMBS class and the SQLiteEncryptionKey key there.

While our plugin can do AES 256, Xojo just uses AES 128.

Hi Christian,

I am glad that you can open AES 128, or AES 256, and so can Xojo.

What are the settings to open a Xojo SQLite encrypted database?
Just to help you with the information that I am exploring, here are some possible settings to be considered with your response for the Cipher setting:
aes-128-cbc
aes-128-cfb
aes-128-cfb1
aes-128-cfb8
aes-128-cctr
aes-128-ecb
aes-128-gcm
aes-128-ofb
aes-128-xts
aes-128-cbc
aes-128-cfb
aes-128-cfb1
aes-128-cfb8
aes-128-ctr
aes-128-ecb
aes-128-gcm
aes-128-ofb
aes-128-xts
aes128
camellia-128-cbc
camellia-128-cfb
camellia-128-cfb1
camellia-128-cfb8
camellia-128-ecb
camellia-128-ofb

Thank you.

Are you using a sqlite based product that has SEE built in to try & open the DB ?
If not I dont think you will succeed in reading it

If you are then one of the AES-128 versions should work
aes-128-ofb very likely
for the ccm support I’m not sure which of those above is correct

1 Like

Well, MBS Xojo SQL Plugin ships with built-in SQLite library with SEE included.
So our plugin can do the same SQLite encryption as Xojo itself. Xojo uses AES 128 by default.

It’s AES128-OFB.

1 Like

I am trying SQLite Studio to try and open a simple encrypted database. As an example I created a simple table with a few rows of data and created the encrypted SQLite database and saved the file as PasswordEncrypted.sqlite. The password for this test is: MyPassword, and I attempted to open it with aes-128-ofb, and below is a screen grab.

ScreenGrab1

The reason I am asking is that there are many versions of encryption, types, iterations, and page sizes available, and I can’t seem to find anything on the Xojo website. The good news is that I can open the encrypted database with Xojo, but I am unable to open the database with any other editor, I have tried 5 so far.

I don’t believe that Xojo attempted to make a proprietary encryption type, so I am trying to figure out:

  1. How to open a Xojo encrypted SQLite database in a third party SQLite editor
  2. or create Xojo programming to create an encrypted database that is compatible with other programs, so that Xojo is not perceived to have proprietary encryption. Proprietary encryption is not welcomed by businesses.

The attached screen grab attempted AES-128-OFB, and no joy.

Thanks for your comment :slight_smile:

The third party tool must have the SEE built into it’s SQLite libraries. I’m not sure how common this is, so it’s probably easiest to use a SQLite app built in Xojo like SQLite Manager

I’m pretty sure SEE is a proprietary format.

https://sqlite.org/purchase/see

1 Like

The two encryption systems/methods are not compatible.
SQLCipher is an open-source extension/modification to SQLite.
SEE (used by Xojo) is a proprietary extension developed by the same group that created SQLite.
https://www.sqlite.org/see/doc/release/www/index.wiki

Thank you Tim. The SQLite manager program was successful at opening the encrypted database! This is great!

In my humble opinion, this is a poor decision for SQLite. When encryption is proprietary (albeit it is available for a cost) then it is not commonly implemented. This is shown by SQLite Manager appears to be one of the very few programs which implements it.

Thank you, you made my day!

Thanks @jjr, I found this before and although it is helpful when programming a new editor or program, the low number of SQLite editors that have implemented SEE seems that this is not a common practice. Maybe SQLite is hoping that it will be a standard in the future? Not sure what to think. Is implementing SEE just a $2,000 money grab for SQLite? I doubt it, but it was just a thought that crossed my mind when I found the purchasing page here: SQLite Encryption Extension

What I find interesting is the Swift/ObjC which use an Apple supplied SQLIte framework (import SQLite3) doesn’t offer any encyption methods at all. In order to do so, you must bypass their framework and implement some 3rd party one (at the risk of imcompatibily at a later date)

Thats interesting information Dave. My question is why would Apple implement a counter-intuitive way to work with SQLite? This seems to force you to work-around the framework. shrug

Not sure what you mean by “counter-intuitive”… the included framework is super easy to use, just lacks encryption… which may or may not be considered important, since you have the ability to password secure the entire computer

I am minsunderstanding Dave. Does Swift/Obj C have the ability to natively read/write AES-128-OFB encryption? If the AES-128-OFB algorithm is built-in to Swift/Obj C then it is easy to implement.

If AES-128-OFB is just a library to be imported into Swift/Obj C then this is also easy to implement.
If there are no external libraries to implement AES-128-OFB then this is much more work to write the algorithm and test to make sure it is correct.

I am sure I misunderstood. Thanks Dave :slight_smile:

No… Swift/ObjC has NO Sqlite encryption methods… it would require the use of SQLCypher or something similar, and this would require either a totally new framework (replacing SQLite3 framework as supplied by Apple, and this is a Swift FRAMEWORK, not just the SQLite installed with macOS or iOS)… or it would require writing a library to use the two frameworks together. I don’t know which of those two is the easiest or most practical.

At this time I have a library based on the supplied Apple Framework that allows the use of SQLite in Swift using syntax almost 100% identical to what Xojo (API1) used

1 Like

Ok, I understand it now. Thanks Dave. :slight_smile:

the upside to the way sqlite does it is that data does NOT need to be decrypted to use normal sql command etc
that isnt true for most other schemes

and the sqlite code is very robust - it has 100% test case coverage last time I checked

I once paid that for MBS Xojo SQL Plugin.