SQLite Encryption Extension

[dglass]

Would be very handy if RR licensed the Encryption Extension so we could use encrypted SQLite DBs within LC.

http://www.hwaci.com/sw/sqlite/see.html

[mwalker]

That would truly be valuable. I'm new to LC, and I have an immediate application. But as an independent developer, I don't have the extra $2,000. Looking for an alternative.

[jalz]

I would also love to have this feature - especially when im thinking of developing for iOS - instresting thread about SQLite encryption on the database forum.

[bvlahos]

I agree. This would be a terrific feature.

[Tonaras]

Hello guys, do we have any update on SQLite Encryption yet? It's been over 10 years that this request was made. Having turned my back to FileMaker - for good - in favor of LC, SQlite Encryption is about one of the most crucial things I'm missing. I wouldn't mind paying the steep license fees, but I don't know if the Encryption Extension would even work with LC's implementation of SQLite. There's no documentation on how to install SQLite extensions to run in my LC standalones. Does anyone know how that can be done?

Kindest regards,
Anton

[stam]

Hello guys, do we have any update on SQLite Encryption yet? It's been over 10 years that this request was made. Having turned my back to FileMaker - for good - in favor of LC, SQlite Encryption is about one of the most crucial things I'm missing. I wouldn't mind paying the steep license fees, but I don't know if the Encryption Extension would even work with LC's implementation of SQLite. There's no documentation on how to install SQLite extensions to run in my LC standalones. Does anyone know how that can be done?

Kindest regards,
Anton

Hi Anton,

Why not encrypt the data *before* storing it to SQLite db, rather than relying on SQLite itself?
Should be quite straightforward with LC's handlers for this, but of course will be more work to run queries against the data, as would have to factor in the encryption of the data.

On the other hand why even use SQLite? My current preference is to use LiveCloud, which is fully encrypted and provides both local and cloud databases (have a look at https://livecloud.io, there is a free tier that you can use for testing - and have a relatively low-cost subscription model beyond this).

The caveat is that this is not an SQL database as such and requires a little re-thinking as it has different ways of querying the data (the analogy i like to use is that it's like knowing how to drive a stick-shift car and then one day driving an automatic car - the first time you're guaranteed to cause whiplash as your muscle memory keeps looking for the clutch! But in the longer term it's much easier...)

S.

[Tonaras]

Hello Stam,

thanks for your reply! Highly appreciated!!!

Why not encrypt the data *before* storing it to SQLite db, rather than relying on SQLite itself?

I'm hessitant because - given the speed struggles that LC already has - it would become practicaly unworkable having to encrypt/decrypt every single data chunk to/from the db.

About LiveCloud, I've done some little testing months ago and it is a really good solution for inhouse requirements. In my use case it doesn't really work because I would have to sell a subscription service to my target customers along with my software solution and I don't want to turn everything into a SaaS model. Btw, I loved your "stick-shift car/automatic car" analogy

I've also experimented slighly with Valentina DB which certainly seems like a great alternative, but I had some issues with it that I couldn't sort out yet as I was (and still am) really VERY much under pressure deadline wise.

Finally, I consider SQLite such a brilliant universally useful tool, I'm totally in love with it. I believe the Mothership should definitely NOT be cheap charlies in this case and buy the SEE license for these ridiculous 2k bucks. From what I understand, their License permits distribution in commercial apps without the need of sub-licensing, but I'm not an expert in legalese so I might be wrong.

[stam]

I don't think LC can buy a single SEE licence to cover all users. They specifically mention that this licence is for small teams (where everyone knows everyone) and for larger companies they request that each project team purchase their own licence - so sounds like that would be a definite no-go for LC.

Alternatively you could use alternatives to SEE for encryption of SQLite databases:
SQLite Crypt: https://www.sqlite-crypt.com ($380)
SQL Cipher: https://www.zetetic.net/sqlcipher/ ($480)

No idea how well these would work with LC mind you... or even how SEE would for that matter...

ValentinaDB is more complicated/difficult to pick up than sqlite for sure - but they do provide an interface for LC and i'm guessing that's going to be your most cost effective solution 'out-of-the-box' solution.

Alternatively you could try experimenting with encrypting the data in LC - but you probably wouldn't have to decrypt the entire SQLite db - you'd just query for the encrypted version of your search terms and use it normally, so that may work. Or may be slow AF

Or just use an encrypted file-based system? You can create arrays, text and so on as encrypted files - or even have custom props containing arrays in an encrypted stack for example. That's probably what i'd go for if i was *really* pressed for time...

S.

[Tonaras]

I checked SEE licensing again and you're most probably right, Stam, looks dull for licensing SEE by LC.

Thanks for the hints about SQLite Crypt/ SQL Cipher! These look really hot! However, as you said, how any of these solutions could be made to work with LC remains a big mistery.

I wish - AT LAST - someone from the mothership would take a clear stance on this long standing, obscure SQLite encryption issue and shed some light on how this could be accomplished in LC.

or even have custom props containing arrays in an encrypted stack

From what I know, custom props remain unencrypted in a password protected stack.

Or just use an encrypted file-based system?

I've thought about that many times. You know what would be really awesome? To implement a functionality in the LC Engine to have some sort of a RAM based data partition like a RAM disk in which you could write/read as if it was a physical disk. With the abundance of RAM these days, a lot of such things like encryped data handling etc could be accomplished in a snap. I'm actually going to propose this as a feature request.

[stam]

I've thought about that many times. You know what would be really awesome? To implement a functionality in the LC Engine to have some sort of a RAM based data partition like a RAM disk in which you could write/read as if it was a physical disk. With the abundance of RAM these days, a lot of such things like encryped data handling etc could be accomplished in a snap. I'm actually going to propose this as a feature request.

Well, you can store arrays as binary files which can be encrypted. Or perhaps even better, you can encrypt the whole sql file - then decrypt it and load it as a RAM based database - SQLite doesn’t need a file and can run wholly in memory. Then you’d need to flush it to disk and replace the old SQLite file. Or some such

[Tonaras]

Well, you can store arrays as binary files which can be encrypted.

I'm doing that for quite some time now and it is true, it works like a charm for use cases where this is appropriate. For large data sets however, arrays ain't the way to go, especially when I need to match and update related data in different tables. With SQL it's just ONE query, with arrays it's usually a damn repeat loop or - in rare occasions - you can get away with a filter + regex or the like.

Or perhaps even better, you can encrypt the whole sql file - then decrypt it and load it as a RAM based database - SQLite doesn’t need a file and can run wholly in memory. Then you’d need to flush it to disk and replace the old SQLite file. Or some such

I'm already using RAM based SQLite dbs quite a lot, but I create the *memory* based db via [revOpenDatabase("sqlite",":memory:","binary")] which gives us the ID of an EMPTY!!! database and then the tables on the fly via SQL queries. I have not the slightest idea how I would have access to a db that was encrypted on disk, then loaded into RAM, decrypted in RAM and then... what? How would I get a database id to that decrypted db file that now is in memory???? I mean, we obbiously don't want to save the decrypted db from RAM to a decrypted file again for accessing it.

As we need to open the db and get an id with...

Code: Select all

revOpenDatabase("sqlite",filePath,[sqliteOptions])

...what 'filePath' would we need to point at once the db file is decrypted in RAM??? If I specify ':memory:' the db will be freshly created in RAM and it will be EMPTY! If you can give me a recipe on this one you'll earn my all time LiveCode Nobel Prize of the Decade!!!

[mtalluto]

Just a quick FYI on LiveCloud. Local storage is free with no limitations. Just create a free Mist account in LiveCloud Manager. You can use all the APIs with "local" as the target parameter.

https://livecloud.io/frequently-asked-questions/

[Tonaras]

@ mtalluto

Just a quick FYI on LiveCloud. Local storage is free with no limitations. Just create a free Mist account in LiveCloud Manager. You can use all the APIs with "local" as the target parameter.

Hello Mark, and thanks for the reminder. I discovered that from the last time I tried LiveCloud, a lot has been pushed forward and to the right direction. It's some quite amazing work that's been done!

My 3 main problems with the LiveCloud functionality are
1) Low speed
2) Lack of relational functionality for inter-table CRUD opperations
3) Reliance on LCS repeat loops for batch processing data in tables

From a quick search in the LiveCloud forum, I saw that others seem to have the same issues. It will be interesting to see how the upcoming LC Compiler will help to improve points 1 and 3. About point 2, I'm really not sure how you could tackle this since the entire thing is basically array based, so creating "queries" that offer similar functionality as inner/outer/left/right joins in SQL seem to me rather complicated to be implemented in that environment.

I'm certainly going to invest more time to dig deeper into this amazing LiveCloud ecosystem once I'm done with my current project.
Warmest regards,
Anton

[stam]

I use LiveCloud a fair amount.
I had to implement my own system for joins, which works fine. More recently I’ve moved to using an array-based middle layer that holds the data I’m working with and usually array operations are very fast in LC. I have a system for marking records that are new, that have been modified or deleted and selectively sync these changes to the cloud at opportune moments (syncing entire tables blindly is possible but slower).

The other reason for moving to an array-based middle layer is that it affords more flex if I do at some point decide to change backends. All I’ll need to do is create a set of handlers that reads/writes/deleted records (since all queries happen in the array layer), which is a much simpler task from the point of view of changing my code.

[Tonaras]

Hi Stam,
all this sounds highly intriguing!!

I had to implement my own system for joins, which works fine.

Maybe you could share your "join" solution with Mark and his team so they can implement something like that faster into the LiveCloud API? That would be insanely generous!!

I can confirm, that array processing is quite fast in LC and the main bottleneck seems to be updating the records of a db table from the data in an array (talking SQLite here). So maybe it ain't that mad afterall to keep the entire data thing array based. Hmmmmmm.