Support Articles
for Sanderson Forensics SQLite Software SuiteConverting the new Firefox cache2 files to an SQLite DB for investigating
A few weeks ago I was contacted by a user who asked me to have a look at the new Firefox cache2 format files and see if I could convert the metadata (along with the cached file) into an SQLite table in a similar manner to the Chrome Cache extension I did for the...
Interpreting/Formatting a date encoded as a string
I was contacted this morning by one of our users who wanted help decoding a date held in the database in the format 20140310111203345 found on an iPhone chat app "Nimbuzz" messenger. It’s easy to look at this and decode it by eye, i.e. 2014/03/10 11:12:03.345 but what...
Using group_concat to amalgamate the results of queries
Recently one of our users contacted me and asked for help creating a custom report for a Skype database after other forensic software had failed to include some very relevant data in their reports’. In a nutshell, the messages report he had produced using the other...
Dealing with records found in SQLite Rollback Journals
I was contacted recently by a police client who had identified an incriminating file name in an SQLite rollback journal who wanted some assistance getting the data into a user-friendly format. Intelligence provided was that the suspect was using Kik messenger on an...
Using Forensic Browser for SQLite to examine ANY SQLite database
We all know that SQLite has become pervasive and is common on pretty much every investigation we do and we often rely on your Swiss army knife type tools to produce reports on the supported databases found in an image. We quite often usually leave the investigation...
Using SQL as a date/time conversion tool
In a slight aside from my recent articles re using the Forensic Toolkit for SQLite I have put together a short tutorial on using SQL to convert dates. This article came about as I was using third party utilities (and in some case SQLite) to validate the date and time...
Chrome history with Recursive Common Table Expressions
Like many applications, Google Chrome uses an SQLite database (or rather a number of SQLite databases) to store information relating to pages visited. One of these databases is the history DB which uses a set of normalised tables which, amongst other things, holds a...
Using The Forensic Browser for SQLite to display maps alongside Facebook messages
I suspect that many of you have looked at Facebook SQLite databases and seen data in the messages table coordinates column of the form: Code:{"longitude":-0.19690104212,"latitude":51.085777776363003,"accuracy":5}Since release 1.1.0 the Forensic Browser for SQLite has...
Basic SQL Joins
I have written a few articles lately about designing queries using The Forensic Browser for SQLite and shown how easy it is to do this using drag and drop. But I thought it might be useful to go back to basics a little, go over joins in a little more detail and try...
Creating a Forensic Browser for SQLite extension to load Kik images from a binary plist
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...
Creating a timeline with the Forensic Browser for SQLite
Timelines are a hot topic in the forensic field and rightly so; viewing data from any source alone is only part of the picture, data when viewed in context alongside other columns often paints a very different picture and the evidence can sometimes just jump out at...
Adding the same SQLite column to a report twice – raw and converted
In this short tutorial, I am responding to a request from a user to allow both the raw column and its converted values to be displayed side by side. i.e. in a Skype messages table, we might want a report that contains the message timestamp, author and the message...
Creating a custom display for an integer data type in the Forensic Browser for SQLite
Anyone who has looked forensically at a database will have been frustrated by the use of integers to represent a state or action and the complication this adds to reading reports for both investigators and lawyers. The most common of course is Boolean values to...
SQLite Forensic Explorer – overview
Sanderson Forensics are pleased to release their new SQLite Forensic Toolkit which includes SQLite Recovery for carving files from disk and phone images and SQLite Forensic Explorer for examining SQLite database at a lower (B-Tree/page) level. In this short article, I...
SQLite Recovery
Many recent applications and even operating systems, particularly on mobile phones, have embraced the SQLite database as a standard. This means that as forensic investigators we need to be able to find and parse these databases as part of almost every case. While...
Listening to Skype Voicemail
On a few occasions, I have been asked whether it is possible to listen to Skype Voicemails using SkypeAlyzer. The answer is no, at least not at the moment, however, it is relatively straight forward to do this if a little time consuming and fiddly. These are the...
A Brief History of Time Stamps
Introduction There are various methods of recording dates and times on computers and computing devices and as a forensic investigator, it is useful to understand the main formats and also to have an understanding of why dates are stored in the way that they are. For...