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:
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 :
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))
# for each file
for filename in filenames:
plist = SFBPList.ProcessPlist(filepath + "\\" + filename)
#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'])))
outDB.execute("insert into decoded_icon(msg_id, icon) values(?, ?)", (filename, sqlite3.Binary(i['value'])))
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).
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.