SQL Cipher works with iCloud syncing?

#1

Hello,

We are creating an iOS 8 application for iPhones and iPads. We need to implement AES256 encryption, so I am interested in using SQL Cipher. Are there any issues with syncing using iCloud to multiple devices? My understanding is that when the application is installed on each device, the database is protected by its own encryption password, but the updates from iCloud are synced properly. If the user deletes our application and reinstalls it, the data from iCloud will be downloaded and populated in the encrypted SQLite database on the device. In other words, it just works. :slight_smile:

Is this true, or are there any issues that we need to be aware of?

It is possible that we will create an OSX version of our application in the future, and need it to sync with iCloud, too. Would this be an issue?

Thank you for your time and any assistance that you can provide.

Mike

#2

There’s nothing built-in to SQLCipher which would provide iCloud sync support for you. You have to build that yourself. I use iCloud sync with SQLCipher and it has been very difficult. But that may just be due to my implementation. If you’re using CoreData then you may have an easier time of it. Take a look at the Ensembles project to see if that would work for you. It uses CoreData: https://github.com/drewmccormack/ensembles

#3

I’m sort of embarrassed to say this, but since we don’t use the iCloud sync APIs in our own software I don’t know much about what’s involved. I can say that it’s a problem distinct from encrypting data stored locally on the device by your app. From what I recall, the data model is encapsulated by a UIDocument subclass, and that subclass implements certain hooks for iCloud to pass any changes and increment a change sequence number. That’s no small amount of work and you’re still faced with passing the user’s data unencrypted to iCloud.

So there you have a big implementation hurdle—working out some kind of public key exchange between instances of the app running on different devices for that user, so that the user’s other devices can decrypt the data from the server, but the server can’t.

It’s a tricky problem, but we like tricky problems :wink:

@BrendanD Would love to hear about your implementation if you have the time, I’m sure others would, too, even if you think, “man, there’s got to be a better way to do this.”

#4

Hi BrendanD & Wgray,

Thank you for your responses. Hmmm… It seems to me that it wouldn’t matter whether iCloud was involved or not…

App writes data to SQLite through Core Data… iCloud intercepts the writes and pushes them out to the iCloud servers… The data is saved in the SQL Cipher encrypted data on the original device. Other devices are fed the update from iCloud. The update is not encrypted, but is passed through an encrypted connection to the other devices. iCloud handlers in the other devices take the change and insert it into their SQL Cipher encrypted databases. The data on the device is stored in an encrypted format. True, the data changes being saved on iCloud’s servers is not encrypted, but Apple does have a good reputation for data security, and the application can always do encryption on PII.

So, it seems to me, on paper, that things would work. This is why I was asking whether anyone had actually tested this process. It seems that a lot of folks would want to be able to sync with an encrypted database on the device, but it sounds like no one has tried it… If that is true, I will set aside some time within the next few weeks and give it a whirl with a simple test application.

Thanks for your comments and have a great evening!
Mike

#5

In my app (Tap Forms Organizer), I store database changes in a separate encrypted SQCipher database file and upload that to iCloud. So the data going to iCloud is encrypted. Each file I upload has a sequence number in the file name so that Tap Forms can keep track of the correct order of operations and which file has already been processed. I store that information in a device registration file which I also sync. Basically it’s just a plist file that contains the device name, icon, and some counters so each device can keep track of which sync file it has already processed from each other device.

The trouble with my implementation is that there’s no baseline file that I can roll up all the transactions into so if you have 3 devices that you’ve restored the same data to and then synced that, Tap Forms will upload each database to iCloud. So essentially it would take up space for 3 copies of the data, one from each device. I haven’t figured out a good way to amalgamate all the sync files into a single baseline to reduce the amount of iCloud storage required. I just can’t see an iPhone having to process a bunch of multi-megabyte files to combine them into a single file.

This can also cause the number of sync files to just grow and grow forever consuming space. So I’ve provided a Reset iCloud Sync button the user can press which will clear out all these sync files. Then the next time they upload to iCloud, a new sync file with all the data will be uploaded to iCloud.

So ya… “man, there’s got to be a better way to do this.”

I’m currently in the process of adding Dropbox sync to Tap Forms using the basic same techniques I described here. I’m currently finding it syncs MUCH faster than iCloud Drive does.

I’d love to hear how others are doing this or if you have suggestions of a better way to do this.

Thanks!

Brendan

#6

We have a library called ditto, it’s a series of custom replication functions and triggers that we build into SQLCipher using SQLite’s sqlite3_create_function interface. As tables in the data model are updated the trigger functions fire and record changes into a log that can then be replayed. That’s an awfully simplistic description of what ditto does, but the point is that in the application’s encrypted SQLCipher database we have a change log.

Our password manager STRIP uses SQLCipher for encryption, and the ditto library for replication. To exchange changes between two databases:

  1. The remote database is downloaded (or received from another client)
  2. We ATTACH it to the local database: ATTACH DATABASE ? AS remote KEY ?;
  3. We let ditto take care of the dirty details: SELECT ditto_replicate('remote');
  4. After a little cleanup the remote database is uploaded or sent back to the other client

:fire: Note the use of bind parameters, don’t use string substitution for building SQL.

Sounds great, multi-master replication, abstracted out from the applications using it. But it’s not ideal for really distributed sync and sharing, at all. Or background sync. Mostly because you need to ship the entire database on each sync! We’re working on a new system, but that’s how we do it in STRIP now.

We might open-source ditto at some point because it’s cross platform, so easy to use, and great for certain applications, but we’re definitely outgrowing it ourselves. Mostly I want to show off all the work @sjlombardo did on the gory internals.

We were thinking a pair of sheep for the logo :sheep: :sheep:

#7

William, ditto sounds pretty awesome. But ya, the shipping the entire database for each sync bit would be a problem for me. Couldn’t you just write the change logs out to a separate file for a set of transactions?

I know even that would get tricky because you probably don’t want a new file for each transaction. And determining when to write out a block of transactions could be a bit tricky too, especially if you support any kind of batch importing like I do plus regular interactive transactions.

I guess shipping the database works ok for WiFi sync, but it’s less desirable for cloud syncing. Some of my customers have a database file which is over a gigabyte. Although when I upload the base database, it contains very minimal indexes and certainly no FTS index. I rebuild the FTS index from the changes during the sync process.

Does ditto also keep track of database schema changes and apply those to the remote during a sync?

#8

Totally; there was major convenience in doing it all in the same SQLCipher database. Unfortunately, this approach isn’t the right one for data of any significant size.

Does ditto also keep track of database schema changes and apply those to the remote during a sync?

Actually, we “detach” ditto from the tables it watches (removing the triggers, basically), then we modify the schema, and then reattach the trigger functions to the table in question.