Creating a Forensic Browser for SQLite extension to load Kik images from a binary plist

by | Oct 20, 2014 | Forensic Browser for SQLite

This short article will show how a user (you) can write code to extend the functionality of The Forensic Browser for SQLite by either decoding a binary structure within an SQLite database or decoding such a structure in a file external to the database.

For this example, we will consider attachments to messages in the Kik messaging application.

In order to create a meaningful report including an attachment on a Kik database, we need to take selected fields from three tables ZKIKMESSAGE, ZKIKUSER and ZKIKATTACHMENT. Investigation has shown that the ZKIKATTACHMENT table has a field ZMESSAGE which relates to the ZKIKMESSAGE table primary key (Z_PK) and a further field ZCONTENT which relates to the actual message which is stored externally (more shortly).

So our first task is to create a query that has every row in the ZKIKMESSAGE table with the ZKIKATTACHMENT TABLE on the fields discussed. The screenshot below shows our join with a few meaningful additional columns included. I have also included the ZKIKUSER table so our report can contain the user name:

If we execute this query we can see that the ZCONTENT column from the attachments table contains what looks like a GUID and if we look in the folder structure for Kik the attachments folder contains files with a similar naming convention:

Further investigation shows that these files do indeed have a name matching the database entry and that the files are in fact binary Plists. When these Plists are decoded we can see that there are binary structures stored within this Plist and anyone familiar with looking at hex will recognise that the icon and preview data are a PNG and a JPG respectively.

So we now know there are pictures in the Kik Binary Plists and we could easily carve them out using an appropriate tool. But what we really want to do is access them through The Forensic Browser for SQLite and create a report including any pictures as and where appropriate. The remainder of this article shows you how to do this.
The process is simply to create a script that gets the data you want and write the data to an SQLite database that can then be “attached” to the SQLite database you are examining. The tables from the new database can then be queried by using a fully qualified table name i.e. <database>.<table>.<field> (if there is any conflict).
The pseudo code to create this database is:

  • Create databases and tables
  • Open each file in turn
    • Get handle to Plist images dictionary
    • Extract images and write to the new database
  • Flush changes to the database

When the script runs you are prompted for the location of the Kik attachments (usually a folder structure ending in com.kik.chat\documents\attachments. On completion, there will be a database in the same folder as Kik.py with the file name kik_attachments.db.

The Python code for the above is as follows :

import sqlite3
import SFBPList
import os

sqlite3.paramstyle = 'qmark'

outDB = sqlite3.connect('kik_attachment.db')

outDB.execute("create table if not exists decoded_preview (msg_id text, preview blob)")
outDB.execute("create table if not exists decoded_icon (msg_id text, icon blob)")

#indvidual tables can make any subsequent logic for joins simpler
outDB.execute("delete from decoded_preview")
outDB.execute("delete from decoded_icon")

filepath = input("Enter the file path to the folder containing the plists\n: ")

filenames = next(os.walk(filepath))[2]

# for each file
for filename in filenames:

try:
plist = SFBPList.ProcessPlist(filepath + "\\" + filename)
finally:
#parse the image dictionary from the plist
image = plist["image"]
#loop through every item
for i in image:
#determine whether picture is a preview or icon
if i['name'] == 'preview':
outDB.execute("insert into decoded_preview(msg_id, preview) values(?, ?)", (filename, sqlite3.Binary(i['value'])))
else:
outDB.execute("insert into decoded_icon(msg_id, icon) values(?, ?)", (filename, sqlite3.Binary(i['value'])))
outDB.commit();

Now we have our table the rest is quite straightforward. As of version 1.0.5 of the Forensic Browser for SQLite, you can attach a second (or subsequent) database to an open database.

Select “Attach DB” from the file menu and choose the Kik_attachment.db created with our python script. You will then be prompted for a name for this database (a default name is supplied).

The database is now attached and you can use the tables exactly as you would any other within The Browser.

All that remains now is to complete our query and report. Add the decoded_preview table to the report created earlier and join the new table with a left join on decoded_preview and select view blob as pictures from the results grid context menu.

The final query design and the table look like this: