Creating a custom display for an integer data type in the 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.