Blog/Articles

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 there and look no further. This might be OK, but we are potentially missing a whole host of evidence.

• What happens if the database schema has changed? (this happens regularly)
• What do we do if our tool doesn’t support that DB? (there are lots)
• Is our tool extracting all the relevant information for our case? (very often not)
...

SQL is an extremely powerful programming language, and understanding SQL database schemas can often help immensely when creating queries on the database in question.

The particular feature that I want to discuss in this blog is foreign keys, and I intend to show their value by example, as if I have just been asked to create a report on a database (BlackBerry Messenger master.db from an IOS device) of which I have little knowledge. To help with this let's assume I have been asked to investigate file transfers between the user of this DB and his/her contacts and create a simple report to detail the transfers.

This example is, of course, a little contrived but it should, I hope, show the principles well.

First, what are Foreign Keys?

Simply put, foreign keys provide a way of ensuring that relationships between tables are valid. For example, in the case of a simple messaging database, they can ensure that for every message with a userID there is an entry for the userID in the users table.

At a recent conference while talking about SQLite forensics I found out that some people still use non forensic tools to investigate databases with WAL files and were quite happy that they would not miss anything of importance. This is something I disagree with very, very, strongly and I hope in this article to explain the very basics of how WAL files work and show why you should not use non-forensic tools to examine SQLite databases.

Most of the databases we examine are appended to as user interactions take place. Be it a web browser which contains tables related to browsing events whereby new URLs are appended to associated tables as and when the user navigates to them. Or a messaging application where each message to and from the user is appended to the end of a list of such messsages and new contacts are likewise added to the end of a list of contacts. In the example below we will consider
...

In a recent forensic case involving recovered deleted sms messages from an sms.db file on an IOS mobile device none of the mainstream mobile phone forensic software made the link between sender and recipient for the recovered records of interest.

I have been asked a few times recently about obtaining the third party of a deleted IOS SMS message that has been recovered by the Forensic Browser. The procedure is simply to create a JOIN between two (or three – there are two ways of establishing the third party) tables and if the data is in the relevant tables then a link is made. Unfortunately when messages are deleted, particularly when entire conversations are deleted, the primary keys on the all-important tables are often overwritten making these joins impossible.

When the case mentioned was looked at with the Browser neither of the JOINS described above allowed the investigators to ascertain who the third party in a deleted conversation was. So I was asked to take a look at the DB further and I managed
...

Often data held within tables in databases is stored within a BLOB (Binary Large OBject) this data is often structured data that is encoded in a particular format. XML and Binary Plists are examples of these structured storage objects. Often the data in each blob in a table is in the same format and it would be useful to query these objects and include selected data in a report.

The Structured Storage Manager does this by using a template to break down
...

The raison d'etre for a journal, be it a traditional rollback journal or the newer SQLite Write Ahead Log (WAL) file is to maintain database integrity. Simply put if an operation fails for whatever reason then the changes to the database are unwound to put the DB back to its last known good state. It might seem obvious then to state that a copy of securely deleted data would need to be kept in order to facilitate this functionality. This securely deleted data can and sometimes does exist for quite some time.

We also need to understand that SQLite groups actions together in transactions, transactions can be one database update (write, modify, delete) or it can be many thousands of such actions as determined by the user. Think of a simple messaging application whereby a message is received asking to be "friends"
...

I have posted a number of support videos since the Toolkit was released. I have added them below (most recent first).

A basic use and new feature video showing how to create two reports on the Skype database on the contacts including displaying avatar images and the messages table - showing integers as dates and using the "Alias Manager" to display meaningful text descriptions rather than numbers:

Creating a custom report and showing visual joins, filtering on deleted records, using an alias for a column name etc.:

In a recent article I discussed how I identified deleted records in a database that was using WAL journalling. In this article I want to take this a little further and show how we can see what the live records were at a specific point in time and how we can timeline the frames in a WAL and use this to determine approximately when the records were deleted.

To help with this I created a test messaging application with a few artificial messages and users to keep the demonstration simple.

The "application" I have developed has just two tables "messages" and "users" and I created a simple conversation series and added and deleted users and messages.

The Messages table when viewed with the Firefox SQLite manager plugin, i.e. the live database as the application would display it looks as follows:

The users table is even simpler:

The final step in setting the 'picture' is to show the database as it would be seen in the Forensic Browser with a simple join between the tables and the columns and the columns displayed with simple conversions so the report makes more sense:

That is how most SQLite viewers would see the database and all looks OK. So what happens when the database is opened with the Browser and the WAL journal is processed alongside the master database? The following
...

I have been working with a user who is processing an Android mmssms.db with its associated WAL journal and it became clear that another forensic tool that he was using was not identifying deleted records. My colleague wanted to create a report showing just the deleted records from the SMS table for a specific phone number.

First a little back ground regarding Write Ahead Log (WAL) journals.

When a new record is to be written to an SQLite database and WAL journalling is in operation the SQLite engine identifies which page of the database needs to be updated with the
...

I recently saw a Twitter conversation where a user wanted to see the EXIF data from some image files displayed as maps and showing a clickable URL for googlemaps. The latter part of this problem can easily be solved with the Browser - the steps are as follows:

Run exiftool and export the relevant results as a csv

Import the csv into an SQLite database

Use the Browser to create a query displaying the lat and long as two fields

Create a VIEW to represent this query

Use the Browser to display a map for each row showing the location defined by the lat and longs

Use the Browser to combine the lat and longs into a clickable URL

This example assumes that you want to display the locations of all the files in the path "E:\My Pictures"

The Forensic Browser for SQLite incorporates features such that you can right click on a numeric date column and have the Browser convert a number to one of the supported date formats, applying a timezone offset as required.

The process is simply - right click the required column and choose "View column as..."

Select the date format that you believe the column is saved as, in this case I recognise this as an IOS NSDate format:

And it's that simple...

So how can we validate the conversion done by the Forensic Browser? The method I choose is to use the SQLite built in functions within the Forensic Browser, as follows.

We can do this by simply having two copies of the column that we are converting within the same query an dthe using the SQLite DateTime fucntion
...

Along with SQLite the Microsoft Extensible Storage Engine (ESE) is becoming increasingly common on Windows mobile phones and desktop operating systems. ESE, also known as Jetblue or EDB DBs, is the technology that underpins these databases such as Windows search and Cortana. In this article I want to look briefly at the Microsoft ESE database used by Internet Explorer 10 and the new Edge browser and also a little at Cortana and highlight some of the features of The Forensic Browser for SQLite that make examining these databases easy and also highlight some interesting artifacts as we go.

Update - can now recover deleted records.

The extension can be purchased from this link, you will also need a current licence for the Forensic Toolkit for SQLite , also available at the same link.

If you are keeping up with developments with the Forensic Toolkit for SQLite you will
...

In this article I want to take you step by step through the process of creating a custom, but simple, report on a Kik messenger database. As we work through the process we will chose which columns we think will be useful in our report and modify our report by creating
...

In this article I want to discuss how we can recover deleted records from an SQLite database, or rather how we can recover all records and distinguish between those that are live in the DB and those that are found in unused areas and do not match a live record. I will also show how the first few bytes of records are regularly overwritten by SQLite structures and how these partial records can be recovered.

Before I can discuss how we do this, it’s quite straight forward with SQLite Forensic Recovery, I need to take you briefly through a slightly simplified structure of a database explaining how the database fits together and how records are stored within the ‘pages’ of the database. At that point I can give a basic overview of the algorithm used to recover the non-live records which will give you, as the investigator, a handle on how much confidence you can ascribe to one of these records.

I then show you how the deleted records and partial deleted records look when you open the database in The Forensic Browser for SQLite.

Database structure

SQLite databases are made up of a number of pages of a fixed number of bytes, either
...

In this article I want to cover a few of the areas where the Forensic Browser for SQLite provides features that are missing in other browsers or where it complements other more generic forensic software by providing features that are specific to general databases rather than specific ones. The Browser does this by providing a Visual Query Building environment (drag and drop SQL query generation) allowing the creation of very powerful and customised reports often without typing a single character.

It is worth noting here that most forensic software that creates a nice ‘canned’ report on an application
...

Skype recently introduced cloud based operation and started moving away from peer-to-peer messaging with a view, to paraphrase Skype, of improving the service that we receive.

Without going into the pros and cons of this, from a forensic point of view it is irrelevant anyway, the move has had the effect of introducing a new set of artefacts and in particular a new location for stored/cached image files (pictures).

This article deals with the SQLite tables that reference to these pictures, the locations of the pictures themselves and how to join the relevant tables, decode the data held in certain blob fields and create a report showing who sent what to whom including the pictorial evidence where possible.

At the end of the article I will have shown how the different tables fit together and will provide a Browser extension that will create the necessary tables and import the cached pictures; you will be able to run a report that shows who sent an image and when. Alongside this it
...

I had reason recently to look at Skype ChatSync files to recover the IP addresses held within and I needed to get these into a report. For those of you that aren’t aware when Skype is syncing data between two different accounts it uses ChatSync files to transfer this data. The data held within is for the most part duplicated in the main.db file (after all that is what the sync part of ChatSync refers to). However, and most interestingly for forensic purposes, usernames and IP addresses are also stored within these files.

I am sure that you are aware that when an SQLite database is opened if there is an associated WAL file then the pages in this WAL are automatically written to the main database, thus overwriting records, and the WAL file is reset. You may not be aware though that the WAL can contain multiple copies of the same page (each with different data/records) and that there can also be a sort of WAL “slack” i.e. records from a previous database transaction, if you like records from previous WAL files. So by opening the database and committing the WAL you are potentially overwriting/missing valuable evidence.

This article describes how WAL files work and how to deal with them forensically – the steps are very straight forward with the Forensic Toolkit for SQLite and the article takes you through them. I will go into a little detail regarding the format and usage of a WAL file, some of the forensic implications of recovering data and present two methods for recovering
...

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 meta data (along with the cached file) into an SQLite table in a similar manner to the Chrome Cache extension I did for the Forensic Browser a few months ago. The result of this work is the free tool FirefoxCache2ToSQlite.

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 was wanted was a way of decoding all of the dates in this form
...

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 software only included the author of a given message and did not list the other person(s) who were part of that conversation. This information is
...

I was contacted recently by a police client who had identified an incriminating file name in a 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 Android device and had deleted a number of messages. The file itself, a jpeg, had been found but they needed to link it back to the message and put it in context with the other messages around it.

The process I suggested using the Forensic Toolkit for SQLite is rather straight forward and is detailed at the end of this article. But, I thought I would write up some of my observations on whether to manually ‘rollback’ the journal myself and why I chose not to do so.

SQLite maintains two sorts of journals, the older rollback journal file, that I will discuss in most of this article and the
...

Like many applications Google Chrome uses a 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 table showing the date and time of every page visited, where appropriate the previous page (the page on which the user clicked a link/referring page) and an internal ID number of an entry in the URL table that contains the text of the page itself.

The purpose of this article is to show how we can generate a list of the pages that a user has followed while browsing the internet, or more correctly showing the chain of webpages that led to a specific page. To achieve this we will use a feature of SQL known as Recursive Common Table Expressions (RCTE), don’t worry it sounds worse than it is!

But first let’s look at a simple RCTE (from the SQLite web site) and see how it all works:

So what does the above query do? Very simply it creates a simple table with one column (x) containing the value 1 and writes out the value, then adds 1 to this column and then writes out the value until it has written all the results from 1 to 10. i.e.:

The essential feature of any RCTE is that there is a UNION between two tables, the first part
...

Since release 1.1.0 the Forensic Browser for SQLite has been able to display data stored in two columns, one for the latitude and one for the longitude as a map of the location.

In this article I want to show how we can create a query using SQL that extracts the latitude and longitude (lat and long) from the single field above and create a temporary VIEW (a sort of temporary virtual table) that contains the lat and long. This VIEW can then be used to create a new permanent table (in the Forensic Browser case database) that holds three maps at different zoom levels that can be
...

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 conversion procedures in The Forensic Browser for SQLite.

First what am I trying to achieve? Basically I want a table where I can insert a date and have all of the possible valid dates in different possible formats (Unix, NSDate, Chrome, FileTime etc.) calculated and displayed alongside. Sound simple?

This is what it should look like after a valid NSDate has been added to the base column:

The first thing to do is to create a table with a column for our "base" date and then additional columns for each of the date formats
...

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 and put the data in terms that we understand in the computer forensics world.

Before we start though we need some tables to work with, so to make things relevant we will
...

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 you.

There have been many articles written about timelines and there are various programs such as log2timeline from the SANS institute that will help an investigator create timelines. Some of these programs have plugins that allow you to work with specific SQLite databases, but not all databases have plugins written for them and sometimes the table schema changes.

To compliment these programs you can use The Forensic Browser for SQLite to create a timeline on multiple tables and output the data in a comma separated format, such that the timeline can be examined with another tool of your choice.

The following tutorial shows you how to create an arbitrary timeline on two tables from a Skype database. This could easily be extended to multiple tables on almost any database. The tutorial is also a useful source of information regarding some slightly more complex SQL constructs and
...

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 body. The following screenshot shows the raw report with just
...

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 represent yes/no
...

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 want to introduce you to SQLite Forensic Explorer, which shows the structure of an SQLite database in a very visual way and show how it can be used to find deleted SQLite data (that can’t be recovered by other tools) including forensically valuable data from within SQLite indexes.

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 there are tools that can examine specific SQLite databases such as SkypeAlyzer and NetAnalysis and these tools provide functionality to parse databases to look for deleted records and carve records from unallocated space. There is still a need to examine all databases on a given system
...

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.

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 those of us who like to delve a little deeper into file formats some familiarity with how these dates ‘look’ in a hex dump can help when reverse engineering a new file format.

During this short discourse I will be presenting screenshots taken using software developed by Sanderson Forensics – RevEnge.
...