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)
These are all valid questions. New tables and fields are added to databases all the time and although a tool might produce what looks like a comprehensive report, without looking further we don’t know what we are missing!
Some tables are huge, and by that I mean contain lots of data well beyond the ability of a generic tool to display in a nicely formatted report. For instance the Skype contacts table contains (when I last counted) 98 different columns. A tool that produces a nice simple report cannot possibly extract all of the relevant data from these columns – if we don’t look at them all how do we know that we are not missing crucial evidence?
There are literally millions of apps that use SQLite as storage on both phones and desktop and as mentioned above these apps usually have a changing DB structure and contain masses of data. We really need a tool that will allow us to create a nicely formatted custom report on just those tables and columns that we want, restricting the report, if required, to just certain users/rows and on databases that we may never have seen before.
The Forensic Browser for SQLite was written to address all these issues.
In simple terms The Forensic Browser for SQLite is a visual, Drag and Drop, SQL query generator that allows a user to examine every column and row in every table in any database and produce custom compound reports across multiple tables. Once a query has been created it can be saved for future use or shared with other users. In this short article I go through a whirlwind tour of some of the features of The Browser showing the results on different databases, it briefly covers:
• Querying a table
• Displaying an integer column as a formatted date string with an appropriate timezone offset
• Displaying blobs as pictures/hex/decoded binary plist, Boolean integers as a more meaningful yes/no/true/false/on/off
• Choosing specific columns for a report
• Filtering reports on particular users or for a custom period
• Creating SQL joins using just drag and drop
• Exporting all of the above (including pictures and maps) in a report to HTML/XLS/PDF
Creating a report on selected columns from a table is simplicity itself, as this example from a Skype database shows:
1. Open the database in the normal manner from the “File” menu
2. Drag the table you want to report on from the tables list on the right-hand side into the central visual query designer and select the columns you want in your report
3. The SQL for your query is automatically generated
4. Hit “Execute SQL” to run the query
5. Examine the results and when happy
6. Hit “Create Report” to create and save your report to disk as a PDF
In the examples above the date is just displayed as an integer, so what about changing the way a column is displayed – again this is straight forward. The screenshot below shows the results of a query on just four fields from 96 in the contacts table. The data shown is the “raw” data, i.e. as is present in the database with no conversion, the blob field is shown as hex (the default for the Forensic Browser):
Right clicking on any column allows the user to change the display of the data in that column.
Any numeric date fields (Unix 10 digit in the example above) can be displayed in any text format and timezone and DST conversions can be applied. Boolean 1/0 fields can be displayed as yes/no, on/off or true/false. Blobs can be displayed as pictures, etc.
Now we have our report we may want to filter so that just certain rows are returned. We can filter on any value in a column by clicking on the ‘filter icon’ for that particular column header and just ‘check mark’ those entries that we want to see:
Or if we are interested in a particular date range all we need to do is choose the custom option from the filter menu:
And enter the range of dates we would like to filter on:
So what about more complex databases, a brief examination of the Kik messenger DB shows that the messages table records the message details, date and time etc. but the user is stored as an integer and we need to do a look up (JOIN) on the primary key of the users table.
Creating this join in The Forensic Browser is straight forward, we first drag the ZKIKMESSAGE table to the query designer window, then drag the ZKIKUSER table to the query designer window and then left click the ZUSER row in the ZKIKMESSAGE table and drag the mouse to the Z_PK row in the ZKIKUSER table.
The designer and corresponding (automatically created) SQL query looks as below:
We can now select the columns we want (by check marking them as discussed above) in our report and apply a conversion to the ZTIMESTAMP column (this time it is a mac absolute date) and our report is ready to print.
More complex reports can be made across multiple tables in the same manner.
To create a report just select the appropriate output option from the menu. Reports are an exact mirror of what you see on screen. So if you click on a column to add a sort or adjust the width of a column then this is reflected in the output report. Of course if you output your report to Exel then further adjustments can be made in that application.
There are many more features in The Forensic Browser for SQLite including:
• Decoding geolocation coordinates and displaying a map alongside a post
• Importing a picture held externally to a file to display alongside a message
• Maintaining a comprehensive query library so that when custom queries/reports have been built they can be saved (and/or shared) for future use
• Searching all tables for multiple keywords and creating complex queries against the search results
• Importing tables from multiple databases and creating queries across all tables
• Creating Forensic Browser extensions to decode, display and query complex structures held as blobs