This brief post is about something I recently came across and felt like writing a little about despite it going against something I wrote on an earlier post.

Please bear in mind that the circumstances I'm going to describe are an exception to a rule, rather than a rule. And yet I believe as examiners we need to be aware of it.

As the title suggests, I'm calling this the "Primary Key / Date Stamp Fallacy" and it is the understanding that databases such as Call Log and Messages have a primary key that increases in the same direction as the timestamp.

The Initial Belief

As I'm sure you are aware, most database tables utilize a Primary Key (ie. an enforced unique value amongst the rows of data) to aid in record locating and referencing etc. Most tables also use an 'Auto-Increment' that automatically increases the Primary Key by one every time a new record is created. The current highest number is usually stored in a separate table.

This allows you as a forensic examiner to look at a database and instantly know how many records should be within any given table.

It also allows you to identify if records have been deleted. Fore example, if record 100 and 102 exists, then record 101 must have existed at some point and since it no longer exists, it must have been deleted.

Taking this a step further, what I explained on the 'Knowledge C and Friends' post, is taking the date and time of records 100 and 102 and suggesting that record 101 must have occurred between those two times. Makes sense right?

So armed with this information, what can be determined from this database?

Immediately, you can see that records 3 and 5 exist, but record 4 does not.

You know that because record 5 exists, record 4 must have existed at some point and that it must have been deleted.

We can also presume, that since record 4 is between record 3 and record 5, then the "date" of record 4 must be between 06:23:11 and 06:23:28.

Do you agree? Disagree?

Click the original image above to see the answer.

Whaaaaa?!?

The Fallacy

OK. Let's take a look at this a bit closer.

The first clue in this case (and this won't always be present) is that "Test Message 3" failed to send. (You can see that because column "is_delivered" is 0).

The reason it failed to deliver is because this device was in airplane mode.

This is what actually happened...

*Obviously, all messages are routed via a third party (which I am calling the 'cloud' here but it could equally be a cell provider). I've only added the cloud to the diagram where the cloud is particularly relevant.

So you can see how the message, sent earlier on in the conversation, was held in the cloud and not delivered until after other messages had already been saved.

Wrapping Up

This isn't the only scenario that can cause this kind of discrepancy. Multiple devices syncing a single account or just simply having a poor signal could also cause something similar.

Even so, I'm sure that 99+ times out of 100 this would not even be an issue, and the assumed dates/times of missing records will be correct. However, this will certainly change the amount of weight I give to these timestamps unless I have some other form of corroborative evidence (Notifications, 3rd party device or cell provider records for example).