Sqlite encyption

[gpb01]

I've heard nothing more about iOS encryption efforts from RunRev, but I've been testing a 3rd party encryption external that's in development and works well. I'm not sure what the release timeline is, but I'll contact the developer to let him know that there's continued interest.

It is not necessary ... I'm here

Anyone who needs it, please contact me on : guglielmo (at) braguglia.ch

Guglielmo

[phaworth]

SEE encryption looks really nice. If I understand the write up correctly, it completely replaces the regular sqlite library which could be an issue since the library is normally compiled into programs that access sqlite (including Livecode), not loaded from an external library at run time. I guess there would have to be two versions of Livecode to implement SEE, one with the regular library and one with the SEE library. It would also mean that any other programs that access the encrypted database would somehow have to be able too use the SEE library.

To be honest, given that the version of the sqlite library embedded in the current version of Livecode is already 18 months and 7 releases out of date, I wouldn't hold my breath for SEE making it.

Pete

[CALL-151]

Just to clarify, Guglielmo's external provides AES256 encryption for any data or filetype. When the Scottish provide an internal solution, I suspect that it will also be similar to the desktop rather than something like SEE. Regardless, if you need AES256 on mobile, Guglielmo's library is the only one available that I know of.

[marksmithhfx]

Further to this whole discussion hardware based encryption is now supported in the latest release of LC (5.5.2). See the iOS specific documentation for the command iphoneSetFileDataProtection filename, dataProtection. By setting the dataProtection attribute to "complete" the files will be encrypted (aes256 bit) when the device is locked, and unencrypted when the user enters their device pw. My understanding is that the file will remain encrypted even if the device is jail broken (since the users pw is not available to decrypt the data, and it is the users pw that forms part of the encryption key).

I have not tested what state the file is in once backed up onto iTunes. If the device is locked during backup is the backed up file encrypted, and if unlocked un-encrypted? I don't know. If anyone wants to test this and post the results here that would be appreciated. In any event I should get around to testing it myself in the next week or so and will let you know.

BTW, I think this is a HUGE step forward for encryption enthusiasts on iOS platforms using LC. Thank you RunRev!

Finally, Monte Golding now has a tool (mergeAES) that supports encrypting and decrypting files on iOS under software control using the AES 256 bit algorithm using your own password. The advantage to this is you could keep the file entirely encrypted during its life while not in use by your program, and then decrypt it on the desktop using a livecode program and the LC decrypt command (which is compatible with Monte's).

Cheers,

-- Mark

[phaworth]

Hi Mark,
That's great for the mobile developers but, as usual, us desktopers are left wondering where we fit in RunRev's plans.
Pete

[marksmithhfx]

Hi Mark,
That's great for the mobile developers but, as usual, us desktopers are left wondering where we fit in RunRev's plans.
Pete

Hi Pete, have a look at the dictionary entries for encrypt/decrypt. That should address any needs you have for encryption on the desktop (I think?)

Cheers,

-- Mark

[phaworth]

Hi Mark,
Yes, those are useful but I was talking in the context of this thread regarding encrypted sqlite databases. SQLite includes a way to load external libraries which would allow the SEE library to be used but LC doesn't provide a way to access to that feature.
Pete

[marksmithhfx]

Hi Mark,
Yes, those are useful but I was talking in the context of this thread regarding encrypted sqlite databases. SQLite includes a way to load external libraries which would allow the SEE library to be used but LC doesn't provide a way to access to that feature.
Pete

He Pete,

Gotcha. Since I'm not aware of the differences, what advantage does SEE provide that AES 256 does not?

Thanks

-- Mark

[phaworth]

Hi Mark,
Yes, those are useful but I was talking in the context of this thread regarding encrypted sqlite databases. SQLite includes a way to load external libraries which would allow the SEE library to be used but LC doesn't provide a way to access to that feature.
Pete

He Pete,

Gotcha. Since I'm not aware of the differences, what advantage does SEE provide that AES 256 does not?

Thanks

-- Mark

Sorry Mark, haven;t checked the forum for a while.

Probably the biggest one is that it encrypts the whole database file, data and metadata, not just the data. In that respect, it's kinda like the hardware encryption mentioned earlier. Plus you can use standard SQLite calls (plus a couple of supply the encryption key) and the data is automatically encrypted/decrypted as necessary

[phaworth]

Hi Mark,
Yes, those are useful but I was talking in the context of this thread regarding encrypted sqlite databases. SQLite includes a way to load external libraries which would allow the SEE library to be used but LC doesn't provide a way to access to that feature.
Pete

He Pete,

Gotcha. Since I'm not aware of the differences, what advantage does SEE provide that AES 256 does not?

Thanks

-- Mark

Also, offers a a choice of encryption algorithms:
RC4
AES-128 in OFB mode
AES-128 in CCM mode
AES-256 in OFB mode

Pete

[marksmithhfx]

Hi Mark,
Also, offers a a choice of encryption algorithms:
RC4
AES-128 in OFB mode
AES-128 in CCM mode
AES-256 in OFB mode

Pete

Thanks Pete. Do you know if SEE encrypts the whole file, or does it encrypt fields individually? The reason I ask is that it if encrypts the whole file it is probably unencrypted while it is open. If it encrypts the contents of fields that could be done for each entry... a minor difference perhaps as sandboxed iOS applications are not exposed to much external threat, as far as I know.

Thanks for the info,

-- Mark

[marksmithhfx]

Further to this whole discussion hardware based encryption is now supported in the latest release of LC (5.5.2). See the iOS specific documentation for the command iphoneSetFileDataProtection filename, dataProtection. By setting the dataProtection attribute to "complete" the files will be encrypted (aes256 bit) when the device is locked, and unencrypted when the user enters their device pw.

For the technically inclined (I guess thats most of us) I ran across a new Apple document on iOS security here: http://images.apple.com/ipad/business/d ... _May12.pdf

In this document it says: "When a file is opened, its metadata is decrypted with the file system key, revealing the wrapped per-file key and a notation on which class protects it. The per-file key is unwrapped with the class key, then supplied to the hardware AES engine, which decrypts the file as it is read from flash memory."

and later it says: "(NSFileProtectionComplete): The class key is protected with a key derived from the user passcode and the device UID. Shortly after the user locks a device (10 seconds, if the Require Password setting is Immediately), the decrypted class key is discarded, rendering all data in this class inaccessible until the user enters the passcode again."

So how does this play out in the real world? To find out I did a couple of tests.

Test #1: I have an sqlite file with iphoneSetFileDataProtection set to "complete".

Code: Select all

  if the environment is "mobile" then
      iphoneSetFileDataProtection t_db_path, "Complete"
   end if

and later, just to be sure it is properly set I perform this test on the file when I open it

Code: Select all

     if the environment is "mobile" then
         put iphoneFileDataProtection(p_db_path) into field "protection" of card "home"
      end if

which always returns "complete" as the result. Now, according to the above, 10 secs after I lock the device the class key should be thrown away and all data in the NSFileProtectionComplete class should be rendered inaccessible until the user re-enters the passcode.

HOWEVER, if I now back up the ipad using iTunes, and keep it turned off during the process, I get a readable file, not an encrypted one. Now, it is possible that Apple considers a backup by an authorized user THE SAME AS typing in the passcode. But that means they have kept a copy of the passcode hidden somewhere that they can call upon to un-encrypt the class key, and hence reveal the key for un-encrypting the data (this may be stored on my Mac and not on the iPad, I'm not sure). If I had to guess I would say that is what is going on ie. a legitimate backup is the same as typing in the user passcode.

Scenario #2: same as above, only this time I am going to copy the file off the iPad using Xcode, not iTunes. And the result this time is an unreadable file (its actually a blank, empty file). So in this scenario Apple does not have access to, or make use of a copy of the passcode to decrypt the file. HOWEVER, as soon as I re-enter the passcode the file, when copied off by Xcode, is readable. Does that mean that Xcode is a legitimate user of the data and Apple decrypts the file when accessing it for download? Again, that is what I would guess.

So, what have we learned. #1 iphoneSetFileDataProtection complete appears to work to encrypt the file when it is not being accessed. It is likely that the file is adequately protected until an authorized user (either through iTunes, Xcode or the application itself) requests the file. However #2, we have also learned that it is very tricky to demonstrate this level of file protection. All we have been able to show with any certainty is that the file is encrypted when the device is locked and the passcode has been thrown away.

However, if even having it backed up to an iTunes backup in readable form makes you feel uncomfortable you have 4 options: (1) set the never backup flag to true or (2) put the file in a location that is not backed up by iTunes or (3) enable encrypted backups in iTunes or (4) encrypt the file yourself. Option 4 is particularly attractive if you want to subsequently move the file somewhere else and keep it encrypted while the file is being transferred. Options 1-3 don't support that. If you do go for option 4 you'll need to use a 3rd party encryption tool like Monte Goldings mergAES (which thankfully is compatible with LC's encrypt/decrypt routines allowing you to write a standalone utility to decrypt the file later). Also, if you want this encryption to take place not just when you quit your application, but also if the user turns off the device (or it times out), then you'll need to use another 3rd party external to detect that since LC does not currently detect a power down request. Again Monte comes to the rescue with mergNOTIFY and there may be other options as well.

If you go the route of encrypting the file yourself then the results of the above tests change as follows:

1. Backing up to iTunes: the file is encrypted
2. Copying from the device using Xcode, even when the passcode is entered: the file is encrypted.

In fact, by combining both forms of encryption you can be pretty confident the file is encrypted at all times except when your application requires access to it. And for those interested in doing there own encryption/decryption here are example routines:

Code: Select all

   if there is  a file p_pathname then
      put URL ("binfile:" & p_pathname) into tData -- copy the sqlite file into a variable
      -- check to see if the first word is 'SQLite'
      set itemdelimiter to space
      if item 1 of tData = "SQLite" then
         put "something" into encryption_key
         put mergAESOpenSSLEncrypt(tData,encryption_key) into temp
         put temp into URL ("binfile:" & p_pathname) -- copy the encrytped file back over the orginal
      end if
   end if

and to decrypt the file:

Code: Select all

   if there is  a file p_pathname then
      put URL ("binfile:" & p_pathname) into tData -- copy the encrypted  file into a variable
      -- check to see if the first word is 'Salted'
      set the itemdelimiter to "d"
      if item 1 of tData = "Salte" then
         put "something" into encryption_key
         put mergAESOpenSSLDecrypt(tData,encryption_key) into temp
         put temp into URL ("binfile:" & p_pathname) -- copy the decrytped file back over the orginal
      end if
   end if

[James Little]

hey Mark,

I've been trying to use iphoneSetFileDataProtection on an sqlite file in the engine folder to "complete" and it is always says "none."
When I copy the file to the "Documents" folder and use iphoneSetFileDataProtection on the sqlite file in the Documents folder, it says "complete."

Unfortunately with iExplorer even with encryption, I can still export the engine-folder sqlite file when the device is locked.

Any thoughts on preventing people from exporting like this?

-Todd Little

[marksmithhfx]

hey Mark,

Unfortunately with iExplorer even with encryption, I can still export the engine-folder sqlite file when the device is locked.

Any thoughts on preventing people from exporting like this?

-Todd Little

Hi Todd, I'll have to look into iExplorer (have not tried it) but if what you say is true, it sounds bizarre. When the device is locked (do you have a passcode set on the device??) after 10 seconds the entire device should be encrypted... you don't have to iphoneSetFileDataProtection on the file, everything on the device should be encrypted with a passcode set until you re-enter it [Note: I was wrong, see my next message]. With iphoneSetFileDataProtection you are just adding additional protection: in this case (when you use complete) the file should be kept encrypted until its read.

However, regardless, the solution in your case is to encrypt the file, add it to the engine when building, copy it to documents and then decrypt it when you need it. This will prevent the file from being readable from the engine. You can use the encrypt desktop function in LC to encrypt the file and Monte Goldings mergAES to decrypt the file once you've copied it to the documents folder. Unfortunately Apple prevents you from modifying anything in the engine because it is digitally signed at the time of the build.

Hope this helps. If you have any questions let me know.

-- Mark

[marksmithhfx]

hey Mark,
Unfortunately with iExplorer even with encryption, I can still export the engine-folder sqlite file when the device is locked.
-Todd Little

Hi Todd, just downloaded and installed iExplorer. Boy, that just blows the whole sandboxing protection right there. I was able to open the folders for Casey's solitaire on my iPad (the wonderful work of Jacque Landman Gay on this forum) and copy the gamedata.txt onto my Mac. It was very selective about what it would allow me to copy however. Some files it would not copy: notably the iTunes Artwork and the app called Casey's Solitaire. I had similar issues in my own Perinatal app folder. It would not copy the app, nor most of the other files. I was able to copy the encrypted database file to my Mac when the passcode was entered, but not when it wasn't.

By comparing how it responded to files in my directory versus Jacques I learned a few things. 1. The encryption that is applied to all files is defeated the moment any attempt is made to access the file if it is not in a particular protection class. This would include most files on the device. So it would appear that this level of encryption is only there to be able to erase the device (by throwing away the encryption key). 2. I found that anything I had protection complete on was protected as long as the device was locked (for more than 10 seconds). In those cases I could not copy my encrypted database file, because it had protection complete on it. When I entered the passcode I could copy the file, but because I had encrypted it myself (using mergAES) it came off encrypted. So I was happy, thats what I want to see. I need to be able to sleep at night knowing that the file, if illegitimately accessed, is encrypted.

Someone mentioned the possibility of a crash when encrypting the file, potentially leaving an unencrypted copy. So I guess there is still some risk. I actually overwrite the file when I encrypt/decrypt it, and I check the state before doing so. A crash however would be disastrous as I don't think the actual state of the file could be known in that case. It may, for example, be partially encrypted or decrypted.

The only really ideal solution for SQLite files is SEE encryption by SQLite. I'll try and figure out how I can post that as a feature request. I doubt it will be high priority.

I don;t know if any of these meanderings on my part are helpful. There does not appear to be a bullet proof way (yet, in LC) to protect data on an iDevice which is essential for certain applications. (mainly legal, medical and military).

-- Mark