TL;DR: Data hidden in SQLite may not be human-readable and SQLite Miner will help you find the hidden gems inside those databases. You can use either the latest stable release or deal directly with the master repository.
The Problem
Mobile applications can store whatever they want in databases in formats that may or may not be obvious to the naked eye. For example, a column may contain compressed data that obscures the plaintext from the forensic examiner’s view, such as Apple iCloud Notes using GZIP for the Note data. Alternately, the file format itself may not be obvious to everyone, such as a JPEG picture without EXIF which starts with the obscure 0xFF 0xD8 0xFF 0xD8. To effectively consider everything that may be in the database of an unknown application, the forensics examiner needs to identify all of these common file formats and do something to make the information readable.
Possible Solutions
For well known applications, relying on your forensic tool of choice may be the right way to go. The downside to this is if the tool does not fully support that database, you may not even know if there is missing information, or if that missing information is garbage, encrypted, or what.
Another possible solution if the tool you prefer is not parsing the database would be to export the SQLite file and use hexdump to simply eyeball the entire file. While this would allow for rudimentary searches for known magic numbers, it wouldn’t put those entries in the context of their columns to know how to interpret the information. Nor will you want to do this for a file of any significant size.
Another solution could be to export the SQLite file out and open it in some CLI or graphical browser. Then the examiner can eyeball the data and run some specific queries to try to identify these files in the context of their columns. One problem with this approach is that those queries require the forensics examiner to understand the structure of the database (i.e. know which columns to search). Another problem is the sheer number of queries that need to be run to chew through a good number of the file types that could have data stored in the SQLite database.
All of these approaches get exhausting, even if you limit your searches to known test file types you put into the application. This may lead to a forensics examiner trusting their tools or just accepting the existing push-button approach, to the detriment of their investigation.
A Better Solution: SQLite Miner
As always, automation is a better answer than running dozens of SQL queries yourself. I was unaware of any tools that dive into the database and report what it finds back to the forensic examiner so I wrote one called SQLite Miner. The name stems from the fact that I want it to find hidden gems for me that I may miss by looking at the file blindly. I wrote this while working on the iCloud Notes problem, wanting to find some more data that I was sure was in the database file somewhere and really not wanting to click on everything to find where, I wanted the database to tell me itself.
SQLite Miner doesn’t assume it knows where anything is stored, it talks directly to the source SQLite file and asks nicely for those hidden gems. It does this by checking the schema for all the tables and then querying each of those to identify which columns contain blobs. All of those blobs are then checked for specific magic numbers that identify various file formats. While SQLite Miner comes with a number of file formats baked in, the file formats themselves are editable in the included Fun Stuff© config file to add any specific file types that are relevant to your investigations or simply to improve SQLite Miner for others. Check out the README for more information.
What SQLite Miner Is and Is Not
SQLite Miner is not an automated analysis of the entire SQLite file. It will not programmatically tell you how tables join together or produce a report about the app you are investigating. SQLite is also not aimed at any one application and doesn’t hard code any knowledge of popular databases. It is aimed at identifying files within databases and hard codes those magic numbers to find, not where to look for them.
However, SQLite Miner is one tool that could be very beneficial for the forensic examiner to inform that manual analysis of a new application, find relevant data within a SQLite database of interest, or search across a larger set of backup folders to automatically export all embedded files for easier searching.
Examples
Finding Unknown, But Interesting, Apps
One of my favorite places to live is the “unknown unknown” space. I get very focused on the thing I’m looking for when I know what I’m looking for which inevitably leads to tunnel vision and proof that I didn’t actually know what I needed. By branching out and considering a wider amount of information, often interesting things pop up. Running SQLite Miner across an entire (or multiple) device backup(s) quickly rips what could be a large backup down to those apps that are hiding data which might not be readily apparent.
For example, this afternoon I ripped my wife’s tablet that she’s used for a while (grabbing APKs and shared space), resulting in a ~1GB backup.ab. After extracting that, I pointed SQLite Miner at the directory and just over a minute later had narrowed down all the apps to five that appeared to have hidden gems in them. I also, for funsies, aimed SQLite Miner at the entire FOR585-Labs folder from my SANS FOR585 workstation and in ~518 seconds (slower than it should be as I was running the VM in a not-quite-powerful environment while also decompressing and exporting all objects) identified 43,117 blobs that were known file types, the vast majority being pictures.
From here, each of the indicated files can be opened to check the particular tables and columns indicated. Reading the results.csv that was generated would give the specific indexes to check in the database for each of these returns.
Investigating an App of Interest
After looking at the outpu from my wife’s table, I noticed that “nidus.db” had a lot of hidden goodness. Having never looked at Nidus before, I opened up the database file to see what was in all those GZIP’d blobs. It appears Acer is maintaining a very, very granular view of each and every process and app that runs on the tablet. A granular enough level that I believe you can tell when apps are minimized and changed to the millisecond and it stores the data per day going back possibly as far as the last time you cleared the app’s cache (or all caches). I’d expect this to be a boon on any Acer devices that are being examined, but only if you remember to gunzip the blobs first! If you don’t have a tool that is doing that, I would guess many examiners would miss some of the goodness in this database since their tool doesn’t tell them they have more information available.
In the com.acer.android.acernidus/db/nidus.db SQLite file, check out the pkgUsage table to see the (possibly) last day a particular app was used, pkgData to see when packages were added and deleted (even for deleted apps), procStats table to see every process started on the device and the usageStats table to see everything that was done on that day.
I would not have thought to check out that app if I didn’t see a large amount of hidden data and I would not have been able to so quickly figure out what it was doing if all that data remained compressed. (Side note, I’m definitely not buying an Acer again, no one app should have this much knowledge of our use)
Dealing With Content of Interest From A Known App
Perhaps more mundanely, my wife’s tablet also had Gmail synced to it. While every tool in the world is (hopefully) going to deal with this nicely, I still wanted a quick and easy way to get emails out and work with them. SQLite Miner took all 420 emails that were compressed with ZLIB and left the ZLIB object in my exports directory. It also decompressed the object and (in a copy of the database) put it back on top of the original compressed object. This meant that I could do my usual queries on the SQLite database to pull out the “From” and “To” for the messages, while reading the plaintext of the message itself in the same row, even without loading the SQLite file into a tool that “understands” Gmail (or to check a tool that purports to “understand” Gmail).
Ripping Android Backups Directly
In order to streamline my workflow, I added in support for reading directly from an Android backup file in SQLite Miner v1.2.1. This behaves much like the directory search except it opens the Android backup file directly and exports only the SQLite files from it, removing the added step of exporting the Android backup before running SQLite Miner.
Motives
SQLite Miner is free and available under the GPL in hopes that those mobile forensics tools we pay thousands of dollars for might work this functionality into their “fuzzy search” logic. Don’t just show us what you think is a database, show us the hidden gems within those databases and provide us with the context to know what to do with them! This code should be more than enough to help kickstart that development and while I’d appreciate credit from any tools that do start implementing this, the bigger payoff would be to have this level of information available by default in all of our favorite tools.
Conclusion
If you find SQLite Miner useful, please let me know. I have a list of ways I want to improve it for my own use and would be happy to entertain other suggestions that may make it more useful for you as well.
Acknowledgements
Thanks to Heather Mahalik of Smarter Forensics for testing SQLite Miner with her SANS FOR585 class in Berlin and including it on her list of handy smartphone tools.