• Blog/Articles

    Published on 12th January 2015 15:26  Number of Views: 9414 
    Article Preview

    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)
    ...
    by Published on 23rd March 2017 15:48  Number of Views: 988 
    Article Preview

    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.

    e.g.:

    Code:
    CREATE TABLE messages(
      time INTEGER PRIMARY KEY, 
      message TEXT,
      userID
    ...
    by Published on 14th November 2016 17:34  Number of Views: 3992 
    Article Preview

    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 ...
    by Published on 2nd November 2016 10:34  Number of Views: 3747 
    Article Preview

    I have seen a number of posts on bulletin boards recently that refer to some of the main stream software failing to be able ...
    Published on 13th September 2016 12:27  Number of Views: 5068 
    Article Preview

    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 ...
    Published on 1st September 2016 09:08  Number of Views: 3487 
    Article Preview

    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 ...
    Published on 25th May 2016 21:24  Number of Views: 4243 
    Article Preview

    A. When a journal is in use (potentially).

    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" ...
    Published on 21st July 2015 10:36  Number of Views: 6971 

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




    Creating a report on a Kik database ...
    by Published on 24th March 2016 13:30  Number of Views: 6527 
    Article Preview

    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 journal 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 ...
    by Published on 23rd March 2016 19:42  Number of Views: 4247 
    Article Preview

    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 ...
    by Published on 29th June 2016 10:00  Number of Views: 2693 
    Article Preview

    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:

    1. Run exiftool and export the relevant results as a csv
    2. Import the csv into an SQLite database
    3. Use the Browser to create a query displaying the lat and long as two fields
    4. Create a VIEW to represent this query
    5. Use the Browser to display a map for each row showing the location defined by the lat and longs
    6. 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"


    1. Run exiftool and export the relevant results as a csv
    ...
    by Published on 8th March 2016 23:09  Number of Views: 4317 
    Article Preview

    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 ...
    Published on 26th September 2015 16:15  Number of Views: 9743 
    Article Preview

    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 ...
    Published on 29th June 2015 15:58  Number of Views: 8758 
    Article Preview

    Creating a custom report on the Kik messenger database

    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 ...
    Published on 31st March 2015 11:26  Number of Views: 32134 
    Article Preview

    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 ...
    Published on 20th May 2015 11:40  Number of Views: 8577 
    Article Preview

    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 ...
    Published on 17th March 2015 09:06  Number of Views: 18109 
    Article Preview

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

    More information here: https://support.skype.com/en/faq/FA1...t-is-the-cloud

    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 ...
    Published on 27th February 2015 15:19  Number of Views: 11167 
    Article Preview

    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 have therefore written ...
    Published on 18th February 2015 14:55     Number of Views: 13601 
    Article Preview

    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 ...
    Published on 16th February 2015 11:43  Number of Views: 9242 
    Article Preview

    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.

    Before I discuss the ...
    Published on 13th February 2015 11:02  Number of Views: 7315 
    Article Preview

    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 ...
    Published on 9th February 2015 21:02  Number of Views: 6362 
    Article Preview

    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 ...
    Published on 26th January 2015 10:20  Number of Views: 12338 
    Article Preview

    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 ...
    Published on 3rd December 2014 10:00  Number of Views: 8637 
    Article Preview

    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:

    Code:
    WITH RECURSIVE cte(x) AS (
        SELECT 1
      UNION ALL
        SELECT cte.x + 1
        FROM cte
        LIMIT 10)
    SELECT *
    FROM cte
    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 ...
    Published on 25th November 2014 10:19  Number of Views: 6722 
    Article Preview

    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 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 ...
    Published on 7th January 2015 18:05  Number of Views: 9033 
    Article Preview

    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 ...
    Published on 29th October 2014 14:14  Number of Views: 7689 
    Article Preview

    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 ...
    Published on 20th October 2014 20:04  Number of Views: 9781 
    Article Preview

    This short article will show how a user (you) can write code to extend the functionality of The Forensic Browser for SQLite ...
    Published on 13th October 2014 17:43  Number of Views: 7159 
    Article Preview

    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 ...
    Published on 9th October 2014 09:29  Number of Views: 6418 
    Article Preview

    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 ...
    Published on 8th October 2014 10:31  Number of Views: 6546 
    Article Preview

    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 ...
    Published on 14th August 2014 13:48  Number of Views: 6818 
    Article Preview

    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.

    ...
    Published on 3rd June 2014 19:00  Number of Views: 5969 

    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 ...
    by Published on 7th March 2012 21:54  Number of Views: 17589 
    1. Categories:
    2. SkypeAlyzer
    Article Preview

    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 requirements:

    • an installation of Skype
    • an active Skype account
    • To record the message
    ...
    by Published on 12th August 2013 19:02
    Article Preview

    I am pleased to announce a new feature to reconnoitre and that is the ability to import Encase bookmarks created and exported by C4P and to identify the files within shadow copies that relate to the bookmarks.

    For those of you who ...
    by Published on 22nd June 2010 15:49  Number of Views: 46619 
    1. Categories:
    2. LinkAlyzer,
    3. SkypeAlyzer,
    4. RevEnge,
    5. PmExplorer,
    6. Free Software
    Article Preview

    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. ...
    by Published on 7th February 2011 14:43  Number of Views: 49551 
    Article Preview

    Scenario

    You have a document that you need to know the provenance of, or in my case you need to find an earlier version. There are no obvious backups and you have checked the existing shadow copies (using vssadmin) and there is nothing of interest there –however you have good intel to show that the file had been modified, but unfortunately the file was binary and it is not easy or possible ...
    by Published on 21st August 2010 12:05  Number of Views: 9592 

    Sanderson Forensics are committed to providing first class support but in order for us to best help you we need to ask you to help us.

    The most difficult bug to fix is one that we cannot reproduce in order for us to quickly track down a bug and fix it we need the ...
    by Published on 8th August 2012 19:50  Number of Views: 11932 
    Article Preview

    On occasion I have looked at the fragmentation of a file to try and draw some conclusions as to how the file has been "built" on the disk, i.e. is the file contiguous or is the file fragmented. On one occasion this was used to prove that a file hadn't been part of just a mass copy exercise (where all the other files were contiguous and one after the other), all pretty basic stuff.

    However today when playing with MFT ...
    by Published on 29th April 2010 08:38  Number of Views: 21266 
    1. Categories:
    2. LinkAlyzer
    Article Preview

    How do I determine whether a file has been moved from one volume to another

    One of the structures found in some link files is the ObjectID, these structures relate to Distributed Link Tracking (see further reading below). Microsoft has in certain circumstances chosen to embed data within link files for use with its this Distributed Link Tracker Service. This information includes data relating to the original machine on which the target was created – the birth machine and the current machine. There are four relevant structures:


    • NewVolID
    • NewFileOID
    • BirthVolID
    • BirthFileOID
    ...