Adding the same SQLite column to a report twice – raw and converted

by | Oct 9, 2014 | Skype, Forensic Browser for SQLite

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 the three required columns checked:
We now right click on the timestamp column and access the “View column as..” menu option so we can customise the display of this column.
The subsequent dialog (shown below) gives us a number of choices, for a Skype date we need to select the “Unix 10 digit timestamp” option. For the purposes of this demo, we will leave the timezone alone.
The converted column now looks as follows:
Now we get to the crux of the tutorial – clearly there is only one check box in the query designer for the timestamp, so how do we add the timestamp column to the report a second time?

With the Forensic Browser for SQLite this is straight forward. In empty row in the columns display click on the down arrow (this will become visible when the row is selected) and choose the messages.timestamp column again.

The Browser will automatically add an alias for this column to avoid duplicate names.
Alternatively, we can rely on the SQL query language itself and enter a new line manually so that the code looks as below (the columns display will be automatically updated if you modify the SQL in this way).
Then Execute the query.

The example shown chooses the timestamp column again and provides an alias for it (as mentioned SQL won’t allow the same column name in the output twice – if you don’t add an alias when you manually type a query you will be prompted with a “duplicate column name” error).

As we haven’t provided a conversion for the column the displayed date is displayed in its raw form.

Creating a custom display for an integer data type in the Forensic Browser for SQLite

by | Oct 8, 2014 | Forensic Browser for SQLite

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 true/ false and on/off. But a numeric column in a table can represent a lot of other ‘states’. Using The Forensic Browser for SQLite it is straight forward to create an SQL query that replaces a given numeric value with a meaningful text string. The SQL expression modifier used to do this is CASE.

By way of example, we will revisit our trusty Skype database and in particular the messages table and the type column. The picture below shows a query in four of the columns from this table and we can see that the type column is pretty unhelpful. We would like to make this a bit more user-friendly to present the report in court.

And if we run a new query and group by the type column and add a count we can see what values actually occur in this column and how many times they occur.
Research has shown that the type values correspond to various types of message, as you might expect. For simplicity we will deal with just three of the 61 = ‘Text Message’, 53 = ‘Contact Blocked’ and 30 = ‘Start Voice Call’. So back to our SQL CASE expression.

The SQLite user manual shows that a case expression has the following form:

CASE expr WHEN expr THEN expr WHEN expr ELSE expr END

A real-world example will hopefully make this clearer

CASE Messages.type WHEN 53 THEN ‘Contact Blocked’ WHEN 61 THEN ‘Text Message’ WHEN 30 THEN ‘Start Voice call’ ELSE Messages.type END AS MsgType,

The example above compares the Messages.Type field to each of the three supplied values in the WHEN part of the expression and if they match then they are replaced by the value in the THEN part of the expression which in our case are quoted string values. So 53 will be replaced by ‘Contact blocked’ 61 by ‘Text Message’ etc. (*you can supply more than 3 WHEN expressions).

The final ELSE part of the expression is important as it instructs SQLite what to do if a type value is found which isn’t coded into our expression. We could use ELSE ‘Unknown’ which would display unknown for every type value that isn’t 53, 61 or 30 but this potentially hides evidence so by using table.fieldname again we can display the actual value. The table below shows this more clearly.

These more complex SQL queries go beyond the ability of the visual query designer however The Forensic Browser for SQLite allows you to enter SQL queries by hand extending the power of the toolkit beyond simple SQL queries and utilizing the additional power of SQLite expressions.