It is worth noting here that most forensic software that creates a nice ‘canned’ report on an application only displays those tables and columns that the developer deems important. For instance the Skype contacts table at the last count contained 97 columns and the messages table has 36 columns. While these reports usually contain all of the relevant data there can often be additional very useful and relevant data held in columns that do not form part of the generic report.
Additionally, database developers are prone to changing the schema of a database without notifying anyone; this may break your forensic application or may introduce relevant data in a new column. Database schemas also often vary between platforms, with a different schema for, say, Kik on Android than on IOS and different schemas might mean the best report on one platform differs from another.
The areas I will cover, with examples and screenshots, are:
- Handling live, deleted and partial records in databases and journals
- Creating custom reports showing a subset of users within a specific timeframe
- Displaying pictures (from blobs, or external to a database) within your report
|For corporate or law enforcement investigators a fully functional licence for the Forensic Toolkit for SQLite can be obtained by clicking here and providing your official email address, full name and position within your organisation|
Handling live, deleted and partial records in databases and journals
Many SQLite applications allow the user to delete records as part of their operation and databases by their nature are often dynamic with new records being added and pages of B-Trees being moved to maintain a valid B-Tree structure. Pages (possibly containing live and deleted records) are often copied to rollback journals or in the case of the newer Write Ahead Logging journal, the new pages are written to the journal and the old page containing redundant data is left in the database.
All this means that if records have been deleted and/or a journal is present then the deleted records need to be found and the journal processed so that we can see and report on both the live and any deleted data.
Extraction of records that may have been deleted and partial records (see the article on my website that covers this in more detail) is straight forward with the Forensic Browser, as is processing any associated journals (both the old rollback journal and the newer WAL journal). You just need to choose your source database and when prompted select the options that you want.
If you choose to recovery records then a copy of the source database will be made and all of the live records and recovered records (as well as any from journals) will be added to this working database.
Creating a query to show the content of table can be done by just dragging a table to the visual query designer window and check marking which fields you want in your report. The SQL is generated automatically for you. Drag the mouse between columns in different tables to create simpleor complex joins - all visually:
Filtering to show just a subset of the recovered records is straight forward, in the animation below I have clicked on the filter icon in the column header and I am choosing to create a report containing just seelcted recovered records from the live and journal file asscociated with this Skype database:
Creating custom reports showing a subset of users within a specific timeframe
After selecting just the records we want, from the source we want a common requirement is to restrict the report to one that contains messages from specified users and just within a given timeframe.
Again this is straight forward and in the same manner as we selected the records from the journal we can add a further filter on the from_dispname column and just choose selected users from the Skype database:
Then by right clicking on a numerical timestamp column we can apply one of the built in date and time conversions, selecting the appropriate display format for the column and any timezone offset we need:
Filtering records to just those within specified dates can be done by clicking the filter icon in the column header and entering the dates in the filter dialog:
Building up filters in this granular manner is very intuitive and using the date conversion routines allows us to work with dates in formats we understand rather than the underlying (in this case) raw Unix date.
Displaying pictures (from blobs, or external to a database) within your report
Many databases maintain pictures such as avatar pictures (Skype) and message attachments (WhatsApp) some forensic applications will display these pictures alongside the appropriate data but most SQLite browsers are not designed for this. Many applications however store pictures outside of the database, Blackberry messenger stores attachments as individual jpgs in the devices file system, some versions of Kik messenger store the attachments embedded within individual binary plists stored on the devices file system.
Irrespective of the method used the Forensic Browser is able to display these pictures alongside the message to which they relate. Displaying a blob as an image is trivial in the Forensic Browser, either choose to display all blobs as pictures or right click on a column and choose to display just that column as a picture:
The screenshot below shows a HTML report containing various fields including the avatar images from a test Skype contacts table:
Importing images that reside in the normal file system is also straight forward. In blackberry messenger the file transfers table contains a column FileTransferId that has a number that is the name of a jpg located in the devices file system. The Forensic Browser provides a simple import dialog that allows the user to specify where the pictures are located so that they can be imported into the case file.
It is then very straight forward to create a simple query that displays the images within the table.
For more complex import scenarios such as Kik messenger where the pictures are stored external to the database in binary plists, Browser extensions can be written to perform the import task. See the article re Kik messenger pictures on my web site.