Wednesday, June 19, 2013

SQLite on the Case

It is very common in SQLite databases for integers to represent a deeper meaning than their numeric value. We usually refer to this as a flag. Take the iOS call_history.db for example: the call table has a column literally called flags and the integers in that column represent the type of call.

Integer Interpretation

4

Incoming

5

Outgoing

8

Blocked

16

Facetime

Note More values are possible than those in the table above. The data above is accurate for iOS 6, but not complete. Other values integers are possible that represent a network error of some time, and while it is possible to determine whether the call was incoming, Facetime, etc. when the error occurred, this exceeds the scope of this post.

Just as common as integer flags is the utter lack of an explanation for them in the database itself. Take a look at the table schemas:

CREATE TABLE _SqliteDatabaseProperties (key TEXT, value TEXT, UNIQUE(key));
CREATE TABLE call (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, duration INTEGER, flags INTEGER, id INTEGER, name TEXT, country_code TEXT, network_code TEXT, read INTEGER, assisted INTEGER, face_time_data BLOB, originalAddress TEXT);
CREATE TABLE data (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, pdp_ip INTEGER, bytes_rcvd REAL, bytes_sent REAL, bytes_last_rcvd REAL, bytes_last_sent REAL, bytes_lifetime_rcvd REAL, bytes_lifetime_sent REAL);

The tables are essentially stand alone—the data table is not joined with the call table to give meaning to the flags column integer. So, how do we determine the meaning? Why, by testing, of course. If you thought use another tool and see what it says then go outside and drag your knuckles on the concrete for a bit. It’s okay, I’ll wait….

The best resource for checking flag values is the device’s user interface: get a test phone, export the database, and compare the records shown through the device interface to the values in the table. Yes, you have to do some work, but that is as it should be. At a minimum, you need to check if your tool or manual query is corret, right? By the way, you might be wondering how the UI knows the interpretation: its in the application programming, but you don’t get to see that because its in binary form.

So, let’s go forward with the assumption we have checked the UI for this database or a same generation database and are confident of the interpretation. Can we craft a SQL statement that can perform the interpretation for us? You bet!

The Case Expression

The SQLite CASE expression is the IF/THEN of SQL queries. The basic form of the expression is as follows (credit: sqlite.org):

  • CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

  • CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

Think of the x value in the expressions above as the column name. Rather than try to explain further with hypothetical values, lets turn back to the call_history.db flags. We could use the following excerpt in our query:

case flags
    when 4 then "Incoming"
    when 5 then "Outgoing"
    when 8 then "Blocked"
    when 16 then "Facetime"
    else "Dropped"
end as flags
Note We use the as flags alias at the end of the case statement above to name the column "flags." Without the alias, the column would take the name of the entire case statement.

The case statement above essentially reads: for the flags column, interpret 4 as Incoming, 5 as Outgoing, 8 as Blocked, 16 as Facetime, and anything else as Dropped. If we were interested in the producing output from the call_history.db that included the rowid, phone number, date, call duration, and call type, we could use the following query:

select
        rowid,
        address,
        datetime(date,'unixepoch','localtime') as date,
        duration,
        case flags
                when 4 then "Incoming"
                when 5 then "Outgoing"
                when 8 then "Blocked"
                when 16 then "Facetime"
                else "Dropped"
        end as flags
from call

Our output would look like the following:

ROWID address date duration flags

1948

8885551212

2013-02-30 19:46:41

15

Outgoing

1949

8881215555

2013-02-30 21:05:11

67

Incoming

1950

8881215555

2013-02-01 00:17:05

113

Incoming

1951

8885551212

2013-02-01 10:32:32

54

Outgoing

1952

8885551212

2013-02-01 10:37:25

34

Outgoing

But if you act now, there’s more…

It’s very nice that we can interpret these flags using a SQL statement. This means we don’t have to write a program, use an in line editor, or write a spreadsheet rule to do it. One stop shopping, just what every busy forensicator wants! But, I like to see the original value, too, just in case it turns out my interpretation was wrrr…, uh, less correct than I would like.

Enter SQL operators. Operators are syntax (usually symbols, but sometimes words) that cause the SQL engine to perform a specific task on the data such as concatenation or filtering (e.g., equals, greater than, less than). The double pipes || operator, which is the focus of this section, concatenates values. With it we can make it clear that the duration is in seconds, and we can append the append the flag integer to its English equivalent.

select
        rowid,
        address,
        datetime(date,'unixepoch','localtime') as date,
        duration || " sec" as duration,
        case flags
                when 4 then "Incoming"
                when 5 then "Outgoing"
                when 8 then "blocked"
                when 16 then "Facetime"
                else "Dropped"
        end || " (" || flags || ")" as flags
from call

Now our output could would look like the following:

ROWID address date duration flags

1948

8885551212

2013-02-30 19:46:41

15 sec

Outgoing (5)

1949

8881215555

2013-02-30 21:05:11

67 sec

Incoming (4)

1950

8881215555

2013-02-01 00:17:05

113 sec

Incoming (4)

1951

8885551212

2013-02-01 10:32:32

54 sec

Outgoing (5)

1952

8885551212

2013-02-01 10:37:25

34 sec

Outgoing (5)

Note It’s very true that we could have just labeled our duration column Duration(sec), but I wanted to illustrate another variation of the use concatenation in a query.

The second-to-last line in the query, end || " (" || flags || ")" as flags, adds the integer value of flags, encapsulated in parenthesis, to the end of the interpreted value. Thus, the complete case statement can be read _print the translation of the flag, followed by a space, open parenthesis, the flag integer, and finally a closed parenthesis.

I hope this gives you some ideas on how to produce output with more clarity and analytical value.