Sqlite encyption

[marksmithhfx]

What RunRev needs to do is license the encryption extension. Real Software did it for Real Studio/Real BASIC so licensing for an IDE, and its users, by the developer of the IDE is not unprecedented.

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

Agreed. Or is that Bump!

I would definitely put down some cash to see this happen. Maybe we need a poll to find out how many developers are interested in having encrypted SQlite files on iOS devices and pass that long to support?

-- Mark

[marksmithhfx]

Encryption is critical for mobile devices (not just phones & tablets but laptops and netbooks too). This is a nice selling point for a mobile app. If you add simple password protection the user can feel reasonably secure even if the device is lost or stolen. In addition to protecting the data for customers that use the software it would also protect your schema from prying eyes as well.

I want something that is read/write encryption and something I can just initiate right at startup of the app and forget about until the app shuts down.

Cheers

Ditto

[Bernard]

Yesterday I came up with a way in which Runrev can provide encrypted sqlite dbs, across all supported platforms, and at no external cost to the company.

I forwarded my suggestions to the head honcho for his consideration.

They have the skillset needed to do it, and most the work is laid out in front of them. We just have to see if they come up with some reason why my suggestion is not feasible.

Until then, I'll just have to continue using Valentina.

[marksmithhfx]

Yesterday I came up with a way in which Runrev can provide encrypted sqlite dbs, across all supported platforms, and at no external cost to the company.

I forwarded my suggestions to the head honcho for his consideration.

Bernard, is there anyway we can simulate this using LC while we wait for them to develop a native code solution?

Just curious as I am playing around with a medical app for iPad and we need to solve this issue or move off iPad onto some other platform we can secure. BTW, for this particular application sending data via wi-fi to a secure server would work as well (or better) but the hospital environment we are working in has no wi-fi access.

-- Mark

[Bernard]

You can encrypt data using the Livecode "encrypt" commands. However, what that means is that you cannot do partial word searches using sql (at least, not that I could discover - I could get full word searches to work). If you used the in-built encrypt command on some text then inserted that text into the database, when your app closes, the data could not be (easily) read. The structure of the database will not be encrypted, but as encrypted data, it is (more or less) opaque to sql searches too.

http://docs.runrev.com/Command/encrypt

It seems amazing that encrypted sqlite data has been an afterthought to Apple and Google. I do not think that the native SDKs provide encrypted data storage. This is all staggering to me as someone who spent 10 years as a Lotus Notes developer. Notes from about 1990 was providing encrypted data, encrypted comms, and replicating, NoSql storage.

[marksmithhfx]

Exploring this a bit more I decided to take a look at an iTunes backup of the application data. On a Mac you can find it at ~/Library/Application Support/MobileSync/Backup/ and the directory looks something like the following:

Not a very transparent directory listing. There are 4 filenames that are in readable english and they are:

Info.plist
Manifest.mbdb
Manifest.plist
Status.plist

Info.plist and Status.plist contain no useful file or application information. Manifest.plist gives an entry for each application on the iPhone. In my case:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>CFBundleVersion</key>
<string>1.0.0</string>
<key>Path</key>
<string>/private/var/mobile/Applications/44913BC2-3C81-4B65-A927-7DD556ADEE0C/LargeCalendar.app</string>
<key>CFBundleIdentifier</key>
<string>com.marksmith.largecalendar</string>
</dict>
</plist>

Is the entry for an application I wrote (actually Sarah Reichelt wrote and I am modifying) that generates an SQlite database file. Note the file is not mentioned here. Manifest.mbdb is a database (fairly unreadable but does contain a lot searchable text strings) that lists all of the files on the iPhone. Searching for my sqlite file was fairly trivial and resulted in this entry:

com.marksmith.largecalendarDocuments/perinatal.sqliteˇˇˇˇˇˇÅ§®‰ııNÎ∫QNÎ∫QNÎê1

Now, I am not sure what all of the unreadable text is at the end of that line, but I am assuming it relates back to a file in the backup directory ie. one of the many "numbered" files that are listed there. But I am not sure which one? So, in terms of finding my sqlite file and looking at its contents I have reached a dead end. This is not an encrypted backup, just a regular one. Does anyone know what the next step in tracking down my data is (short of opening all 750 files and looking inside each one)? a laborious task to say the least. I guess the real trick would be to decipher the garbage text for the perinatal.sqlite entry in Manifest.mbdb but I don't know how to do that.

At one point I had thought that I might use desktop access to the backup file as one way to get the data off of the iPad (I am assuming of course that iPhones and iPads are backed up the same way), but at the moment that scenario is looking pretty bleak if I can't determine which of the many numbered files is my perinatal.sqlite db. Any suggestions?

Thanks
-- Mark

[marksmithhfx]

Problem solved. Someone has brilliantly written an application to do all of the work for you http://supercrazyawesome.com/ So yes, it is easy to extract unencrypted SQlite data from an iTunes backup of an iphone. I presume the same is true for an iPad but have not tested it.

We do need a way to secure our data. My next test will be to try and use encrypt() and decrypt() to modify the entire .sqlite file and then use these tools to try and read the data. I'll let you know how it goes.

-- Mark

[Bernard]

My next test will be to try and use encrypt() and decrypt() to modify the entire .sqlite file and then use these tools to try and read the data. I'll let you know how it goes.

I don't forsee any problems in principle with being able to encrypt a small database that way. The problem comes when the database size gets larger. The device might simply not have the room to store the encrypted copy and to decrypt it. The other problem is a device getting into the wrong hands, where your application did not encrypt the database on closing.

There will also be a delay in the strartup/shutdown of your app as it encrypts the data.

[marksmithhfx]

I don't forsee any problems in principle with being able to encrypt a small database that way.

Does anyone have an example of using encrypt/decrypt on a file?

Thanks

-- Mark

[Bernard]

I don't have LC installed here, but it would go something like this:

put "/path/to/file.db" into tDataURL
put "binfile:" before tDataURL
put URL tDataURL into tData
encrypt tData using "aes-128-cbc" with password "blahblah"

put tDataURL into tEncryptedURL
set the itemDel to slash
put "secret.db" into item -1 of tEncryptedURL

put tData into URL tEncryptedURL -- should now be a new, file called "secret.db" at path "/path/to". containing encrypted database

[marksmithhfx]

Thanks Bernard, so far I've been able to get it to work on the desktop

Code: Select all

   -- DECRYPT THE FILE HERE
   put the c_db_path of this stack into tstring
   put URL ("binfile:" & tstring) into tData -- copy the encrypted sqlite file into a variable
   decrypt tData using "aes-128-cbc" with password "blahblah" -- puts decrypted data into "it"
   put return & the result after msg -- this will display any error message in the msg box
   put it into URL ("binfile:" & tstring) -- copy the decrytped file back over the orginal
end read_custom_properties

on write_custom_properties
   put return & "write custom properties to file" after msg
   put the c_db_path of this stack into tstring
   put specialfolderpath("documents") & "/testencrypt.txt" into tfilepath
   put tstring into url("file:" & tfilepath)
   --ENCRYPT THE FILE HERE
   put URL ("binfile:" & tstring) into tData -- copy the sqlite file into a variable
   encrypt tData using "aes-128-cbc" with password "blahblah" -- puts encrypted data into "it"
   put return & the result after msg -- this will display any error message in the msg box
   put it into URL ("binfile:" & tstring) -- copy the encrytped file back over the orginal
end write_custom_properties

but so far no luck on the iPhone or simulator, but I think it is related to other problems in my example. It may be a few days before I get back to it.

-- M

[n00b3]

Yes, I own Valentina for LiveCode (and RB/RealStudio as well). Valentina does not have versions for iOS or Android . Talked with Ruslan on Valentina mailing list a few months back and it looks like they have no plans for mobile in near future.

I got a reply from LiveCode regarding how much it would cost to build SQLite version with encryption, they are not doing custom libraries at this time.

So if you want a really portable App (OSX/Win/Linux/iOS/Android) with database encryption, neither LiveCode or RealBasic are the easy answer (for those not familiar with RB they don't do iOS/Android apps at the moment and probably won't ever).

Cheers

[n00b3]

Wow, after posting I went to preview and saw the other posts (I though I had scrolled to last one but obviously not).

This is great news indeed.

It would be real great Christmas present if the elves at LiveCode could get this to work on mobile!
I would run down to the local Mall and ask Santa if I thought it would help

Cheers

[marksmithhfx]

Ok, I've been fooling around with using encrypt and decrypt using Bernards example and I think I have come up with a reasonably simple example that demonstrates this will not work, unfortunately, on mobile. There are 4 buttons on the card: the first lets you create an SQlite database, the 2nd lets you encrypt it, the 3rd lets you decrypt it and the 4th lets you read it (I would not try reading it before creating it since I did not bother with adding the necessary code to handle that situation). Once created, try encrypting/decrypting and reading the file. On a desktop (Mac) the encrypt/decrypt seems to work just fine. The read works appropriately (can't read the encrypted but can read the decrypted file). Not so on mobile, or the simulator. Once the encrypt routine touches it the file is no longer readable.

Now, the documentation does say that encrypt/decrypt is only supported for Mac/Windows/Linux (no mention of mobile) but I had to see if it might work anyway. I'll also post a question to tech support to verify the encrypt/decrypt do not work on mobile (because I seem to recall someone suggesting that this had been offered as one possible solution from runrev).

So in my estimation, unless I screwed up on the example somewhere (alway a distinct possibility), I don't think there is any protection for mobile data coming from LC anytime soon.

Cheers, (and here's hoping I am wrong and encrypt/decrypt does work on mobile)

-- Mark

[Jason1234]

I am following this with keen interest as are many others.

My requirement is to send an encrypted file with the mobile application which then de-crypts it then just reads the data. I don't always need to post new data just view the file.

Getting the example provided by marksmithhfx to work in the mobile environment Android / Ios may be the best solution for now and clearly this is an important requirment for anyone deploying databases that have taken a lot of work to produce and don't want the schema copied... or is of a sensitive nature that should not be read outside of the application.

I hope that RunRev see this as a priority and provides a solution soon.