Sqlite encyption

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

James Little
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 47
Joined: Thu Apr 27, 2006 11:19 pm
Location: Seattle

Re: Sqlite encyption

Post by James Little » Wed Dec 12, 2012 5:50 am

Hi Mark,

Thanks for these detailed tests with iExplorer and for the prior email that helped us see how to achieve at least partial protection.

Regards,

Todd and Jim

James Little
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 47
Joined: Thu Apr 27, 2006 11:19 pm
Location: Seattle

Re: Sqlite encyption

Post by James Little » Wed Dec 12, 2012 6:19 pm

Hey Mark,
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.
Quick update:
I first encrypted the file on desktop, but when I try to copy the file to the documents: the copy is blank. In the past, same database same code, I was able to copy it to the documents perfectly.

This is the copy script.

Code: Select all

  put "secret.sqlite" into tFileName
   set the defaultfolder to specialFolderPath("engine")
   put specialFolderPath("engine") &"/" & tFileName into OldFilePath ## the engine folder and file path
   put specialFolderPath("Documents") & "/" & tFileName into NewFilePath ## the new path to the folder n documents folder
   if there is not a file NewFilePath then ## The file doesnt exist where you want it so make the copy
      put URL ("binfile:/" & OldFilePath) into URL ("binfile:/" & NewFilePath)
      if there is not a file NewFilePath then ## checks to see if the new file got created where you want it else sad msg.
      answer "Sorry couldn't build...." 
   end if
end if
Any thoughts on what I'm doing wrong?

-Todd

CALL-151
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 206
Joined: Wed Oct 20, 2010 11:00 am

Re: Sqlite encyption

Post by CALL-151 » Wed Dec 12, 2012 8:45 pm

Forgive me for jumping back in here without taking time to read all of the recent posts, but isn't the iOS encryption model designed to prevent someone using their computer from reading files on your device? When you connect your device to your computer iTunes (and I suppose other apps) can access certain files because the device and computer are linked to the same account. What happens if you repeat your tests with iExplorer running on someone else's computer and try to copy files from your device, simulating what might happen when a device is lost or stolen?

James Little
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 47
Joined: Thu Apr 27, 2006 11:19 pm
Location: Seattle

Re: Sqlite encyption

Post by James Little » Wed Dec 12, 2012 10:02 pm

marksmithhfx wrote:
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.
hey CALL-151,
Mark posted this earlier. If you can copy files from other people's app folders, then I'm sure others can open your app folder and extract files to their computer with iExplorer.
-Todd

CALL-151
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 206
Joined: Wed Oct 20, 2010 11:00 am

Re: Sqlite encyption

Post by CALL-151 » Thu Dec 13, 2012 3:03 am

Not necessarily. Some files from apps on your device are regarded as yours (e.g. saved data that belongs to you- high scores, text files you create,etc). Some files the developer may not care to protect. You can open these files when the device is connected to a computer on which you have a matching Apple ID, but that doesn't mean they're not encrypted 10 sec after the device is locked. Try opening them via someone else's computer.

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Thu Dec 13, 2012 4:17 am

CALL-151 wrote:Forgive me for jumping back in here without taking time to read all of the recent posts, but isn't the iOS encryption model designed to prevent someone using their computer from reading files on your device? When you connect your device to your computer iTunes (and I suppose other apps) can access certain files because the device and computer are linked to the same account. What happens if you repeat your tests with iExplorer running on someone else's computer and try to copy files from your device, simulating what might happen when a device is lost or stolen?
You make a good point. I wish I had another machine to test from. Does anyone have that kind of setup they can try iExplorer with? If not, I'll have a chance to try it over the holidays with my son's pc.

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Thu Dec 13, 2012 5:27 am

James Little wrote:Hey Mark,

first encrypted the file on desktop, but when I try to copy the file to the documents: the copy is blank. In the past, same database same code, I was able to copy it to the documents perfectly.
Any thoughts on what I'm doing wrong?
-Todd
Hi Todd,

This code tested and works

Code: Select all

on startup
   -- check to see if encrypted.sqlite exists in the engine, if it does copy to the documents directory
   if the environment is "mobile" then
      put specialfolderpath("engine") & "/encrypted.sqlite" into t_db_path
      if there is a file t_db_path then
         put "copying..." into msg -- check the device console for this msg
         put specialfolderpath("documents") & "/encrypted.sqlite" into next_db_path
         put URL("binfile:" & t_db_path) into URL("binfile:" & next_db_path)
      end if
   end if
end startup
PS this is probably not what you want (it re-copies the file each time you startup) but I just wanted to make sure I could copy an encrypted file from the engine to documents. I subsequently copied it to the desktop with Xcode just to make sure it was all there.

Best,

-- Mark
Last edited by marksmithhfx on Mon Dec 17, 2012 3:31 am, edited 1 time in total.
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Thu Dec 13, 2012 5:37 am

James Little wrote:Hey Mark,
This is the copy script.

Code: Select all

  put "secret.sqlite" into tFileName
   set the defaultfolder to specialFolderPath("engine")
   put specialFolderPath("engine") &"/" & tFileName into OldFilePath ## the engine folder and file path
   put specialFolderPath("Documents") & "/" & tFileName into NewFilePath ## the new path to the folder n documents folder
   if there is not a file NewFilePath then ## The file doesnt exist where you want it so make the copy
      put URL ("binfile:/" & OldFilePath) into URL ("binfile:/" & NewFilePath)
      if there is not a file NewFilePath then ## checks to see if the new file got created where you want it else sad msg.
      answer "Sorry couldn't build...." 
   end if
end if
Any thoughts on what I'm doing wrong?
-Todd
Hi Todd,
When I compare to my code I see 2 things: I think there are too many "/"'s and I used file: instead of binfile:
Cheers,

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

James Little
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 47
Joined: Thu Apr 27, 2006 11:19 pm
Location: Seattle

Re: Sqlite encyption

Post by James Little » Thu Dec 13, 2012 7:01 am

Mark,

That was the ticket for copying! I'm following your course of action:
1) encrypt sqlite file using a "aes-256-cbc" cipher on Livecode Desktop
2) copying sqlite file from "engine" to "documents"
3) decrypting sqlite file using mergAESOpenSSLDecrypt(pData,pPassword)
4) iphoneSetFileDataProtection on the sqlite file

So now, I'm on the 3rd step. I try to read from the database after I try to decrypt and get this warning: "Database Error: The database disk image is malformed." This is great because I know at least it is encrypted, but the decryption seems to not be working. Am I using the wrong cipher?

-Todd

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Thu Dec 13, 2012 12:56 pm

James Little wrote:Mark,
So now, I'm on the 3rd step. I try to read from the database after I try to decrypt and get this warning: "Database Error: The database disk image is malformed." This is great because I know at least it is encrypted, but the decryption seems to not be working. Am I using the wrong cipher?

-Todd
Hi Todd, it should be working. I've only done the opposite (encrypt on iOS and decrypt on the desktop using the 256-cbc cypher) and it worked fine. I can't get to this until Friday night but if you don't get it working by then I'll give it a go and post some code if I get it working.

-- Mark

PS is the first item of your encrypted file "Salted"? (set itemdelimiter to "d" and check that the first item is "Salte")
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Thu Dec 13, 2012 1:55 pm

CALL-151 wrote:When you connect your device to your computer iTunes (and I suppose other apps) can access certain files because the device and computer are linked to the same account. What happens if you repeat your tests with iExplorer running on someone else's computer and try to copy files from your device, simulating what might happen when a device is lost or stolen?
Hi Call-151,

Still haven't done the test you suggest yet, but wanted to jump in with another thought. I recently had to upgrade my wife's iPhone to iOS 6 and decided to do it with my laptop not hers. My recollection is that all I needed to do to sync her phone with my computer was enter her device password on her iPhone. The rest happened automatically and there is even a backup of her phone on my laptop (apparently she has nothing to hide) :D

In all seriousness, if that is all it takes then all that would be required to browse 90+% of iDevices out there is to download a simple program that can brute force crack the password, then use iExplorer to explore the device. Now, I did notice when testing on my own iPad that not everything was readable or copyable. This note from Macroplant (makers of iExplorer), in response to a question I posed them, explains why:
If you are on iOS 6, unfortunately, Apple made some changes in iOS 6 where the .app (dot app) folder within an app directory cannot be modified or written to, and the files within that folder can't be opened or transferred. But, you can still read/write in any other app subdirectory such as Documents, same as in previous iOS versions.Our developers are looking into ways around this limitation imposed by Apple, but I don't currently have an ETA on when an update might happen. You'll find that any iOS transfer utility software will have the same limitation with iOS 6.

In order to access the real-root, you would need to jailbreak your device and install afc2add by Cydia, though you still have read/write access to the Media and Apps directories in iExplorer 3 without having to jailbreak. You can also still access a lot of other data close to the root with iExplorer 3 in the Backups Explorer section under Backups.

And for the sandbox security breaching your mentioning about, can and will happen to many apps. It all depends on how well the developers properly code against these, what Apple calls "hijackers". Sandboxes do not prevent attacks from happening to a particular app and it is up to the developers to code defensively to prevent attacks.
I thank them for their honesty and candor. It really helps to know what you are up against when trying to protect data. From these comments it appears to me that we really have to do a lot of it ourselves, and that is where this long thread and discussion is leading: use iPhoneSetFileProtection on any file you want to be protected when the device is locked. If unlocked, you are exposed and since it is relatively easy to brute force crack most passcodes, either use a complex passcode (you can enforce this on other devices using a device profile... download the iPhone Configuration Utility from Apple to set this up on a device) or add additional layers of protection like encrypting the file yourself, or do both.

Hope its helpful,

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

James Little
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 47
Joined: Thu Apr 27, 2006 11:19 pm
Location: Seattle

Re: Sqlite encyption

Post by James Little » Thu Dec 13, 2012 8:06 pm

CALL-151 wrote:When you connect your device to your computer iTunes (and I suppose other apps) can access certain files because the device and computer are linked to the same account. What happens if you repeat your tests with iExplorer running on someone else's computer and try to copy files from your device, simulating what might happen when a device is lost or stolen?
Mark and CALL-151,

We tested on another computer using iExplorer and were not able to export or open a sqlite database or other files inside of the "engine" folder on an iPad running iOS6, but were able to export on another iPad running iOS5.

We looked at the Provisioning Portal on the Apple's Developer website and noticed there is a configurable Data Protection option that we were not aware of.
So our conclusion, this works for iOS6, but not iOS5.

-Todd and Jim

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Sat Dec 15, 2012 3:20 am

James Little wrote:Mark,
So now, I'm on the 3rd step. I try to read from the database after I try to decrypt and get this warning: "Database Error: The database disk image is malformed." This is great because I know at least it is encrypted, but the decryption seems to not be working. Am I using the wrong cipher?
-Todd
Hi Todd, I can confirm it does not work here either. To reiterate where we are at: I can encrypt with Monte's program and decrypt on the desktop (LC). However, I cannot encrypt on the desktop and decrypt with Monte's program. I'll report this to Monte and see where he wants to go with it.

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Sat Dec 15, 2012 3:44 am

marksmithhfx wrote:Hi Todd, I can confirm it does not work here either. To reiterate where we are at: I can encrypt with Monte's program and decrypt on the desktop (LC). However, I cannot encrypt on the desktop and decrypt with Monte's program. I'll report this to Monte and see where he wants to go with it.
-- Mark
Hi Todd, I spoke too soon. I was trying to leverage existing code to open the copied encrypted file and it does not have a decryption routine in its path. Its a bit tricky to make sure I put that into the right location in the code execution sequence (and it requires more brainpower than I have right now) so I will take a look at this again tomorrow.

Cheers,

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: Sqlite encyption

Post by marksmithhfx » Sun Dec 16, 2012 6:54 pm

Hi Todd, the problem I reported earlier was due to my using file: instead of binfile:

Apologies if I caused any confusion.

Cheers,

-- Mark
Last edited by marksmithhfx on Mon Dec 17, 2012 3:33 am, edited 1 time in total.
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

Post Reply

Return to “Databases”