QuickLook Python Parser - all your BLOBs belong to us

I've always mentioned in my presentations and blog posts that if anyone needs any help parsing an artifact, to hit me up - I love working on these types of projects in my spare time. Matthew Feilen (@mattevps) did just that. The artifact in need of parsing was the index.sqlite file which is part of the

OS X QuickLook feature. While an SQL query can pull most of the data, there is a plist file stored as a BLOB (Binary Large Object) that needs to be parsed. This BLOB has additional data that can be useful to an examiner. Read on for more details.

QuickLook Background

The QuickLook database stores information about thumbnails that have been generated on a Mac.
This information includes things like the file path to the original file, a hit count, the last date and time the thumbnail was accessed, the original file size and last modified date of the original file.

The cool thing is that this database can contain entries after a file has been deleted as well as entries for externally mounted volumes, like thumb drives. This database can also persist after a user account has been deleted since it's not located in a user directory. Sara Newcomer wrote an excellent white paper that details this artifact. I suggest reading her white paper for the finer points since my focus will be mainly on parsing the data out.

There is an index.sqlite file for each user on the system. These files are located under /private/var/folders/<random>/<random>/C/com.apple.QuickLook.thumbnailcache. The <random><random> will be different for each user. Since this database is not stored under a user's folder, you will need to tie the index.sqlite to a user by checking the permissions on the file. If you're on a live system, it's pretty easy to do with the ls -l command. However, if you have an image it may be a little more involved. One way I found to do this is to check the owner properties on the file, than cross reference this to the user's plist file. In the example below, I've used FTK Imager to view the UID of the index.sqlite file, which is 501:

UID of the index.sqlite file

Next, I exported out the user's plist file located under /private/var/db/dslocal/node/Default/users and used plist editor to locate the UID for that user:

UID in user plist file

Getting the Data Out

There are two tables of interest in the QuickLook index.sqlite file, the "files" table and the "thumbnails" table. Most of the information contained in these two tables can be pulled with an SQL query. In fact, a blog post by a "Dave" details how to write an SQL query to join these two tables and decode the Mac absolute timestamps. However, the "files" table contains a field named "version" that contains a BLOB. This BLOB, aka binary data, is a plist file:

This embedded plist file contains the last modified date of original file in Mac absolute time, the original file size, and the plugin used to generate the thumbnail:

While the SQL statement works on most of the data, an additional step is needed to parse the embedded plist file from the BLOB. The data in this plist file could be helpful to an examiner, especially if it contains information about a file that no longer exists on a system.

Python to the Rescue

There is a python library called biplist that does a great job of parsing binary plists. Using this library and the SQL syntax provided in the blog post by Dave, I was able to create a python parser in pretty short order for this artifact.


The syntax is pretty simple, just point it to the index.sqlite file:

quicklook.py -f index.sqlite >> output.tsv

If you don't already have biplist installed, it can be installed by running:

sudo easy_install biplist

I've also included a compiled executable for Windows on my github

The output looks like this with the parsed BLOB information in the Version column. As you can see in the example below, there is information for files on my system, as well as for files on a Lexar thumbdrive:

A huge thanks to Matt for contacting me about this artifact and supplying me with several test index.sqlite files. The Quicklook index.sqlite parser (both python and executable) can be downloaded from my github.


Resources:

Sara Newcomer's detailed article on the artifact: http://iacis.org/iis/2014/10_iis_2014_421-430.pdf
Dave's blog post covering the SQL query: http://www.easymetadata.com/2015/01/sqlite-analysing-the-quicklook-database-in-macos/