sqlite deployment

[jalz]

Hi Guys,

Need some advice please and I'm probably over thinking this. I've got a basic productivity database application for both Mac and Windows desktops ready to put on app store. The database I am using is SQLite with my application, before I release it is there a way to protect the database so it can't be 'easily' interrogated, structure and data from unauthorised users? I'm really nervous having tables easily accessible in the documents folder that contain data that should really be viewable through my application.

Main concern is someone copying the sqlite database as db is accessible on a Mac or Windows documents folder and them using a tool like a gui sqlite editor to export data and schema. If it's the owner, not too bothered as I would say they owned the data but I know I can't differentiate between owner and unauthorised user who has just got access to that computer and copied a load of files. Anyone know of a way to protect as best as I can (I know nothing is foolproof) from this type of threat?

Is there some legal framework (EU/US) I need to follow when it comes to storing data in databases.

Many thanks

Jalz

[Mikey]

There are a couple of things you can do, but understand that no method is going to stop someone who is determined:
1) If you don't have tens-of-thousands of records, don't keep the data in a database at all, keep it right in your stack, and require a password to access it. To make it even more challenging, encrypt the data in the stack, and decrypt it with the user's password.
2) Encrypt the database. This makes things much more complicated for you, but it is an option. There are a variety of things you can do from here but do your homework, first.
3) Don't keep the data on the machine, keep it in the cloud, also encrypted. When the user wants to run the app, download the data, decrypt it in memory only, and have all changes saved back to the cloud. This is more complicated still, but it also adds a layer of data security for your users (if their machine crashes or they move to a new one, retrieving the data is straightforward).

[jalz]

Hi Mikey,

Thanks for the response. I think both option 1 and 2 are both feasible. I'm looking at encryption options, found the SQLite SEE encryption but its quite expensive and I'm not sure I can compile it properly (or have the time to). I've also come across an article which used mergAES to encrypt/descrypt data+structure on application launching and closing. Valentino also seems like an alternative db which has encryption built in, but don't know how well it works with mobile devices….

Jalz

[Mikey]

I thought you were looking for mac and windows, not mobile. Mobile is a different animal - on ios, protecting the data is far simpler. MergAES does not work on desktops, I do not believe. Again, if you don't have thousands of records, consider just storing the data in your stack for each user, and using a pasword for access, which will then decrypt the data in the stack.

There are other ways to achieve 2, such as: keeping a dump of each table on disk, encrypted, then when the app is running, load the file into memory, decrypt it, generate an sqlite db in memory, populate the db, and then operate on the DB. When the user wants to quit, dump back to disk and encrypt. I repeat, there are not simple ways to achieve 2, but there are ways to do it.

[jalz]

Well, looking at launching it on Mac and Windows initially, if successful I want one codebase for iOS and Android…. which will be in the future.
My relational sqlite db isn't very big with 12 tables which have relatively small number of columns, but the rows in 6 of the tables could grow up to 5000 each depending on how active the user is. Would storing data in a stack be suitable for something like this? If it is, would I structure the table in the same way as a db, separated by a tab or something in a field - I'll see if I can find some resources for this?

[Mikey]

With that many records, a DB is more appropriate, IMHO, especially because you will probably be indexing the tables, doing joins between them, etc.

[AxWald]

Hi,

it wouldn't help to encrypt only the relevant fields in the records?
It slows down quite a bit, but as long as you don't have heavy db usage this shouldn't matter.

Often it's enough to use non-describing table names, and encrypt only 1 or 2 fields ...

Alternatively, if not too big, decrypt the whole db (as text file) at startup, and encrypt it at shutdownrequest/ closestack?

Have fun!

[jalz]

I think encrypting data in some of the fields maybe just fine for this app and am exploring this option. Although the structure would be available, as long as the key data is hashed I think I would be more comfortable. Your idea of doing some fields might be worth investigating as its a small app so I don't think performance will be too much of an issue (he says....).

The encrypting/decrypting the file on startup/shutdown intrigues me, but I suspect I need an external to do this which is beyond me unless you were talking about native LC encrypt feature?

[Mikey]

Encrypting the entire db can be done with the LC encrypt command (as can only encrypting certain fields - but if they are search fields then you are opening another can of worms).

[AxWald]

Hi,

The encrypting/decrypting the file on startup/shutdown intrigues me, but I suspect I need an external to do this which is beyond me unless you were talking about native LC encrypt feature?

Encrypting the whole db should work like this:

Code: Select all

put char 1 to 256 of the script of this stack into MyPW
   answer file "Which one to encrypt?"
   if it is not empty then
      put it into MyFile
      open file MyFile
      read from file MyFile until eof
      close file MyFile
      -- Hint: SQLite files begin with "SQlite" ...
      encrypt it using "aes256" with password MyPW
      get binarydecode("H*",it,MyVar)
      open file MyFile for write
      write MyVar to file MyFile
      close file MyFile
   end if

I played a bit with it, and it works nice - just I get some unwanted minor changes when decrypting (in the same way). Assume the .sqlite needs some special encoding before encrypting - this is left as an exercise ;-)
Btw., 1.6MB .sqlite encrypts/ decrypts in the blink of an eye.

Have fun!

[jalz]

Thanks both of you for your advice, I'm going to play around with some of the ideas posted here see if I can get it work with my app