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.