Mining Hidden Gems WIth SQLite Miner

Read More

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.

Screenshot of how Oxygen Forensic shows a SQLite file that has compressed information within it.

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

Continue reading “Mining Hidden Gems WIth SQLite Miner”

There’s Gold In Them There Blobs!

Read More

TL;DR: Apple iCLoud Notes are GZIP’d when stored and this script will decompress them for you.


To set the stage for this post and overall blog, I recently took the SANS advanced smartphone forensics course, FOR585. It was a great opportunity and this flip-phone using Luddite took away a ton of good information. One takeaway was a desire to contribute more to the forensic community and publishing code that I use myself which would benefit others, hence this blog. Another takeaway was that iCloud Notes needs to be better understood, hence this post.

Apple Notes

Apple Notes itself appears to be decently parsed by the major tools so I won’t spend much time on it. The relevant SQLite database (notes.sqlite) stores data fairly simply. Even without any tools beyond hexdump, you can clearly find test notes saved in the database. The below image shows a note containing the text “I moved this note.”

iCloud Notes

iCloud Notes, on the other hand, is not parsed nicely (yet) by anything but Cellebrite’s Physical Analyzer. This is likely because the messages are not stored in plain text. For example, the same note above, when moved to iCloud Notes, looks like this:

Continue reading “There’s Gold In Them There Blobs!”