iOS forensics with SQLite data recovery: deep dive and comparison

There are many techniques we use when it comes to helping people recover their data with our products, and a lot of the cleverness we’ve built over the last decade lies below the surface. Today, we’re going to take a dive into one of the approaches we use for recovering deleted messages: forensic recovery of SQLite data.

How do iPhones store data, anyway?

Simply put, many apps store their data in databases, and most of them use the SQLite format. SQLite is a neat little server-less database format and fits well for what Apple and app vendors need. As SQLite runs without a separate database server there are a few ways in which it differs from other databases. In particular, in order for access to be fast it can’t regularly maintain itself when it is being used. Users don’t want to have an app slow down whilst the database performs routine maintenance such as cleaning up indexes, for example.

It is possible for these cleanup or optimisation operations to occur with SQLite, but they tend to be run infrequently. For users, that’s the right balance: your iPhone will be reasonably quick, and your data will be robust. And, if the need arises, you’ll have better than average odds at recovering your deleted iOS data because of the infrequent nature of this maintenance process.

Without getting too technical, it’s pretty simple how the process works. Think of these databases like big filing cabinets:

• When it’s time to add information to them, you’d add a file with a neat label on it, explaining what’s in the file, and that’s how your phone works.
• But when you remove data, rather than taking the file out of the filing cabinet, the phone simply pulls the label off. So the old data isn’t removed, it’s just left unlabelled (or “orphaned”, we as might technically call it.) This means that when you delete data, your phone doesn’t need to go through a relatively slow process of freeing up space in the cabinet. It just says “huh, forget about this”, and moves on.
• When it comes to adding more data, if there’s space your phone will add extra files. If there’s no space to add more data, it’ll see if there are any of those unlabelled files hanging around, and if there are, it’ll chuck out whatever parts of them it needs to to make space.

What you might take from this is that it’s fast to delete data on your phone, and pretty fast to add it, assuming there’s space. And -- unintuitively — it’s the process of adding newer data that really leads to older information being removed.

Recovering deleted information from SQLite on iOS

Say you’ve been using your phone a couple of months. The databases on it are like the filing cabinets we described earlier. They’ve got a bunch of files in them, and where you’ve deleted data, those files will still be there, but with two caveats: they may have been partially or fully overwritten, and they’ll be unlabelled.

The recovery technique then is pretty much what you expect: it’s about finding these orphaned files — complete or otherwise — and trying to find where they fit, and whether there’s enough data remaining for them to make sense. As you’d expect, it’s all a bit fiddly, and there are a number of other technicalities that can emerge to make things difficult. In particular, databases more than a few megabytes in size, and databases which have binary data in them can make the process a lot more difficult.

That said, it’s not too hard to get some data back, which is why you see a good number of tools on the market claiming to be able to do this. In general, you can divide these tools into three categories:

• The good. They use many different techniques that have been built and tested against thousands of different examples of deletion or corruption.
• The bad. They only recover the remaining content which is perfectly formed and includes a number of hints as to where it belongs.
• The ugly. These scan for orphaned content and flag up any old junk that looks like it might fit the context. If you look at it whilst squinting. “NSMutableArray” — could that be the name of your missing contact record? Hmmm.

SQLite undeletion and recovery from iOS in the real-world

Now that we’ve talked a little about how this works, let’s dive into seeing how effective it is in the real world. After all, we want to empower ordinary users to get their data back without having to be experts. Do we manage that? We’ll benchmark the four leading SQLite recovery tools — against iPhone Backup Extractor. At the time of writing, the latest version of those tools are:

To set this test up, we’re going to use a nice big database, full of real-world data. In this instance, it’s a 169 MB “Messages” database, taken from an iPhone running iOS 11. Let’s take a look at it: the following commands show its file-size, and the results of a query to count how many messages it contains.

$ ls -l sms.db
-rw------- 1 afitzpatrick 177565696 Oct  2  2017 sms.db

$ sqlite3 sms.db
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> SELECT COUNT(*) FROM message;
220261

So, there are over 220,000 messages. They're the ones which haven’t been deleted. But how many of the deleted messages can the recovery tools find?

SQLite data recovery from iOS: the results are in

We ran all of the tools shown above on the same file, and noted the results in the table below. We’d have loved to have used a Mac, but because not all of the tools support macOS we used a PC with 16 GB of RAM, running Windows 10 Pro (version 1803, build 17134.48).

OK. So that was a bit of a surprise, as we were expecting they’d all at least recover some data. 🧐 It turns out our humble tool leads the market in SQLite data recovery, despite not being a dedicated SQLite data recovery tool. (If you’ve wondered, this is why we also license our technology to other companies. This stuff is hard.)

SQLite data recovery on different version of iOS

With every new release of iOS, Apple has the opportunity to change how the operating system handles SQLite databases. This includes optimisations in how often cleanup and other maintenance operations take place and when they are triggered.

As devices get more powerful, these operations can be used more freely as their impact on the user experience becomes smaller. Therefore, with each new generation, a SQLite database is likely to be cleaned more frequently which has the benefit of making the database faster to use for normal operations. This aligns with Apple’s goal of increasing user data protection and privacy, as no unwanted data is left on the device. However, this has the caveat of making data recovery impossible via the SQLite method.

In iOS 11, we started seeing an increase in database cleanup activity for SMS and iMessages. Recovery is still possible, and more likely for more recent messages that were subsequently deleted, but not guaranteed.

In iOS 12, there has been a large increase in database cleanup frequency for the SMS and iMessages database. As this release was touted as having a heavy focus on maintenance and speed improvements, this change makes sense as it will likely lead to smoother performance for more common operations.

What do I need to do to take advantage of this when recovering iOS data?

Perhaps that's the best bit: this technology is integrated with Reincubate iPhone Backup Extractor, and has been since the early days. As you use the app, it'll apply this technology when previewing or exporting your messages and a number of other data types. Depending on the data you're viewing, and the way you're viewing it, the app will indicate which pieces of data were undeleted and which weren't. It's all included.