SQLite Forensic Explorer – overview

by | Aug 14, 2014 | SQLite Forensic Explorer

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.

First a brief overview of the SQLite Forensic Explorer display which is divided into 5 main areas, each of which is explained below:
 

  1. Tables
  2. Pages
  3. Records
  4. Decoded page
  5. Raw hex

Tables

This shows the main tables in the database, both as defined in the sqlite_master table and in “normal” grid-like form in which you can view all of the rows in each table. Selecting a table (or index) from the sqlite_master table allows SQLite Forensic Explorer to identify the root page of the B-Tree that forms that table. We can see from the second screenshot below that the messages table has its root on page 14.[*]

(Raw table view – shows individual records)

(sqlite_master table – identifies start page of B-Tree for each table)

2. Pages

The B-Tree itself is shown here, but for ease of viewing it is shown on its side (with the root at the left rather than at the top). We can see that the root at page 14 is for the messages table and that this table has 6 leaf nodes (at pages 92, 99, 121, 96, 125 and 129). Selecting an interior or leaf node here causes the remaining three displays to be populated.

3. Records

Shows the decoded data for each record in the node in comma separated form. Clicking on a record here causes the decode page data to highlight the complete record in both the decoded page and raw hex displays.

4. Decoded Page

This shows every byte decoded as per the SQLite page format. Selecting a field from the record in the Decoded page display causes those bytes to be highlighted in the raw hex display. You can select either a complete object such as the payload data to see all of this data highlighted, or just a cell within the payload to see this discrete item highlighted.

5. Raw Hex

Selecting the start of a record in the raw hex display causes SQLite Forensic Explorer to try and decode the bytes from that location and find a match for one of the tables from the schema. More on this later when we discuss deleted data.

Recovering Deleted Data.

When records are added, modified and removed from SQLite databases, complex logic is utilized to ensure that the B-Tree structure complies with certain rules. This means that the B-Tree structure is regularly modified with records being moved from node to node and new nodes being added and deleted. Although an empty node (i.e. a node with no records) is wiped by SQLite, nodes can and do contain unallocated space and this space can and often does contain deleted or modified data (or a copy of data that has been moved elsewhere).

In the screenshot below “unused space” in the decoded page data has been selected and SQLite Forensic Explorer has highlighted these unused bytes in the raw hex display. It is quite easy to see there is a lot of data in this unallocated space.

As SQLite Forensic Explorer has previously parsed the table schemas, moving the cursor through the hex display allows the program to try and decode the data from the cursor position and see if it matches any of the tables. When a match is found a dialog is popped up as shown below:
Hitting the “Insert into database” button will copy this record into a new database – On the first time this option is called SQLite Forensic Explorer will prompt the user to select either a complete copy database (i.e. all records and indexes) or a blank copy database (just the table structure with no records).

SQLite Forensic Explorer and SQLite indexes

Indexes are an often overlooked source of forensic information, but a complex index such as those found in Skype and Firefox can provide a great source of intelligence.

For instance, Skype has an index on the messages table, with the schema below:

CREATE INDEX IX_Messages_timestamp_chatname ON Messages (timestamp, chatname)

An SQLIte index B-Tree is very similar to a table B-Tree with difference being that tables only have data in their leaf nodes, whereas indexes have data in both interior and leaf nodes. SQLite Forensic Explorer allows the user to navigate through the nodes of an index B-Tree exactly as they would through a table B-Tree.

SQLite Forensic explorer is unique in its abilities to show the content of an index and in the example below we can see the Unix timestamp of a series of conversations between r3scue193 and sandersonforensics-lappy. Clearly this could be useful in any forensic examination especially if this is deleted information or changed information in a WAL file (this will be the subject of another article).

As indexes also grow when tables grow it is not unusual to see deleted data from indexes as in this example below where the root node of the IX_Messages_timestamp_chatname index contains deleted data in the unallocated space.

SQLite Recovery

by | Jun 3, 2014 | 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 there are tools that can examine specific SQLite databases such as SkypeAlyzer and NetAnalysis and these tools provide the 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 and to identify deleted tables and records that no longer form part of the file system.

SQLite Recovery is designed to make this easier.

SQLite Recovery is:

  • Simple to use
  • Template based
  • Carves deleted journal and WAL files
  • Carves unknown databases (including those in unallocated space)
  • Extracts to sqlite databases to investigate with ‘other’ forensic software
  • Export a recovered table to XLS
  • Parse time filtering to improve the quality of recovered data
  • Optionally display numeric columns as formatted date
  • Advanced filters to clean up data post-parse and aid investigations
  • Automatically identify and delete duplicate rows
  • Supports parsing from individual files (DD/Unallocated), logical and physical devices, EWF images.

Simple to Use

Running SQLite Recovery is as simple as following a three-page wizard:
1. Select the destination folder
2. Choose which templates to search for (usually, accept the defaults = all known and unknown)
3. Choose your source image/disk/file

Wait for SQLite Recovery to complete and examine the evidence

Template based

SQLite Recovery allows the user to specify a template defining the database(s) associated with a particular application such as FireFox or Skype. Specifying a database in such a manner allows user-defined constraints on particular table sand columns. These constraints help ensure that the data carved by SQLite Recovery is as clean as possible.

Using templates also allows you to flag a database as not relevant to an investigation and hide it, for instance the data held by the UK National Lottery application on an iPhone is unlikely to be relevant so by default the tables associated with this database are carved, but hidden (allowing the investigator to focus on that data that could be important). Of course, SQLite Recovery is a forensic application so that data is still there and can be examined if required.

Carves Deleted Journal and WAL Files

An integral part of the SQLite databases on disk is the additional temporary journal files and WAL (Write Ahead Logging) file used when databases are updated. SQLite Recovery can carve from these files and ensure that the records within are identified and placed into matching tables.

Carves Unknown Databases (including those in unallocated space)

As well as the templates that you specify SQLite recovery also searches for and builds databases on all SQLite database schemas that it can find on the disk. This means that all databases and tables will be recovered, you don’t just have to search for “known” applications.

Extracts to SQLite Databases to Investigate With Other Forensic Software

Other forensic applications simply output carved data to a csv file. The initial output of SQLite Recovery is an SQLite database. This makes sense as this is the best format to maintain the integrity of the data, you can’t really hold a blob (maybe a jpeg picture) in a csv file. Once the data has been extracted as an SQLite database then you can examine it in the forensic tool of your choice, i.e. SkypeAlyzer for a Skype main.db file.

You can, of course, do an initial examination of each table in SQLite Recover and filters are provided to allow you to ‘drill down’ into the data within a table. SQLite Recovery also allows the user to display (for example) a Unix 10 digit date as a formatted date to enable ease of investigation.

Export a recovered table to XLS

Of course, if you would like to export a particular carved table in XLS format then SQLite recovery allows the user to do this.

Parse Time Filtering to Improve the Quality of Recovered Data

Due to the format of an SQLite database on disk, it is possible to “carve” data with a guaranteed accuracy rate. A carved record is matched to all possible tables based on the format of each column. i.e. simply put if a carved record has three columns the first of which is txt and the second two integers, then this record would be matched to all tables which have a structure “txt, int, int”. SQLite recovery provides parse time constraints such that the user can define that only a record whose first column (for instance) starts with “HTTP“ will match a particular table. Using constraints in this way can reduce the number of spurious entries seen in some carving applications.
This is an important part of the parsing process as SQLite is a very flexible database engine that enforces few restraints on the database developer such that (for instance) an integer can be written to a floating point field (and stored internally as an integer). Defining just once constraint on each table can dramatically improve the quality of the recovered data.

Optionally Display Numeric Columns as a Formatted Date

It is rare that dates are stored within a database in a human-readable format, it’s too inefficient and makes sorting resource and locale intensive. Dates are normally stored as numbers, this might be a Unix date (usually a 10 digit string but sometimes 13 digits if millisecond accuracy is required) or it might be as a 64bit integer (used a lot by Microsoft Windows) or even a float point number with the whole part representing a number of seconds since a given date and the floating point part the fraction of the day.

SQLite Recovery allows you to simply display these encoded columns in a human-readable format by choosing an appropriate display format from a menu selection.

Advanced Filters to Clean Up Data Post-Parse and Aid Investigations

While SQLite Recovery is designed to recover or carve any records it identifies into SQLite tables for investigation with other forensic software it also has some features built in to enable an investigator to perform some analysis. To facilitate this SQLite Recovery has advanced SQL filters that can be used to display a subset of the data based on an SQL query. So for instance, if you are only interested in a Skype conversation that involves a certain third party you could filter Skype messages where the dialog_partner field only contains their username. Or an internet history search could be limited to specific URL’s.

Automatically Identify and Delete Duplicate Rows

Although an SQLite table may be created with a unique constraint there may be, and often are, multiple copies of a record found on a disk. SQLite recovery creates MD5 hash on each record and can utilize this has to delete exact duplicates of each record that are found in a given table.

Supports Parsing From Individual Files (DD/Unallocated), Logical & Physical Devices and EWF Images

SQLite recovery can parse a disk or disk image or the source could be, for instance, and extracted or mounted unallocated space file or SQLite Recovery can be pointed directly at an SQLite database to identify deleted records.