SQLite Forensics Book, now available on Amazon

More information here


  • Interpreting/Formatting a date encoded as a string

    I was contacted this morning by one of our users who wanted help decoding a date held in the database in the format 20140310111203345 found on an iPhone chat app "Nimbuzz" messenger. It’s easy to look at this and decode it by eye, i.e. 2014/03/10 11:12:03.345 but what was wanted was a way of decoding all of the dates in this form that are in a particular table.

    This article will show how to format the date in a manner of our choosing for example as above or formatted in the American MM/dd/yyyy HH:mm:ss.

    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

    The process is straight forward and requires two ‘features' of SQLite:

    The first is substr a function that allows us to extract specified characters from a string (or each row in a column). The SQLite definition is:

    The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

    By way of example SubStr(date, 5, 2) extracts 2 characters from the date column at position 5 onwards and returns it as a new string.

    The second is the SQLite operator Concatenate, the SQLite definition is:
    The || operator is "concatenate" - it joins together the two strings of its operands.

    So on to the solution. The test table we will use for this example just contains two rows as follows:

    Our task is to create a second column with the formatted date as above, i.e. 2014/03/10 11:12:03.345.

    The first query we will use will just extract the first four characters (i.e. 4 characters starting at character 1), the year, and created a new ‘virtual’ column that we will call ‘FormattedDate’. The SQL and output table is below:

    Working through the complete string and also formatting the time characters gives us the final query as below:

    Remember once you have created this query you can save it to the Query manager fpor use in a future case:

    Finally, of course we don’t have to extract the date in the order that the characters are displayed; if we wanted to use the American MM/dd/yyyy HH:mm:ss format then we can simply re arrange our query as follows:

    The final display in the Forensic Browser for SQLite looks as follows: