Investigating a database using foreign keys

by Angie | Mar 23, 2017 | Forensic Toolkit for SQLite

SQL is an extremely powerful programming language, and understanding SQL database schemas can often help immensely when creating queries on the database in question. 

The particular feature that I want to discuss in this blog is foreign keys, and I intend to show their value by example, as if I have just been asked to create a report on a database (BlackBerry Messenger master.db from an IOS device) of which I have little knowledge. To help with this let’s assume I have been asked to investigate file transfers between the user of this DB and his/her contacts and create a simple report to detail the transfers. 

This example is, of course, a little contrived but it should, I hope, show the principles well.

First, what are Foreign Keys?

Simply put, foreign keys provide a way of ensuring that relationships between tables are valid. For example, in the case of a simple messaging database, they can ensure that for every message with a userID there is an entry for the userID in the user’s table.

Code:

CREATE TABLE messages(
  time INTEGER PRIMARY KEY, 
  message TEXT,
  userID integer,
  FOREIGN KEY (userID) REFERENCES users(userID)
)

CREATE TABLE users(
  userID INTEGER PRIMARY KEY,
  username Text
)

In the example above trying to add a new message with a userID that does not exist in the users table will fail, likewise attempting to delete a user when there is a message that refers to that userID will also fail.

When they exist, they, therefore, provide a useful clue to help us understand the relationships between tables.

Note there is much more to foreign keys than in my explanation above, but this is sufficient to understand the main points of this blog. There is a link to the SQLite website at the end of this blog where foreign keys are discussed at length.

The rest of this blog follows the basic thought processes I might go through when initially determining which tables relate to each other and creating the query for my final report.

The DB I am looking at has 58 tables and working out the relations between those tables is obviously going to be long-winded, so where do we start?

To decide what we need to do it is, of course, useful to understand something about the investigation. In this case, as discussed above, we are interested in file transfers between users.

There is a table named file transfers and so this would be a logical place to start looking. The content of the table looks as follows:

and the schema for the table:

Code:

CREATE TABLE FileTransfers
(
  FileTransferId   integer not null primary key autoincrement,
  ConversationId   integer not null,
  RemotePin        text not null,
  UserId           integer not null,
  ParticipantId    integer null,
  ContactId        integer null,
  Incoming         integer not null,
  State            integer not null,
  AbortReason      integer null,
  Path             text null,
  ContentType      text not null,
  Description      text not null,
  SuggestedFilename   text null,
  TotalSize        integer null,
  BytesTransferred integer not null, GroupId integer null,
  foreign key (UserId)   references Users (UserId),
  foreign key (ParticipantId)  references Participants (ParticipantId)  on delete set null,
  foreign key (ContactId)  references Contacts (ContactId)  on delete set null
)

You should see the foreign key references straight away and it shows us that the users, participants and contacts tables are all related to this table and we can see what columns are used to formulate the relationship. Useful information.

The users table looks as follows:

We can now create a simple join to start building some sort of report, what I would like to do is have a report that shows

The trasferID, file path and name, whether the file was sent or received, when and by whom. 

Before I do this I like to take a look at the base table, in this case, the FileTransfers table to get an idea of what I expect to see in my query. The FileTransfers table contains 22 rows and so it follows that my report, after I have created my JOINs, should also have 22 rows. This might seem a little obvious but taking stock and making sanity checks as the report is built will pick up any errors early in the process.

The fields we want from the FileTransfers table are:

FileTransferId, 
Path,
SuggestedFilename,
Incoming,
UserId

The field from Users is:

DisplayName

We can see from the foreign key description above that two tables are joined on the userId column, so we will use the same tables and columns in the foreign key for our JOIN.

As we want to have one row in the final report for each row in the FileTransfers table we want a LEFT JOIN, if you need a refresher on join types have a look at the article at this link.

The query to create this report is shown below and we can see that there are 22 rows in it as expected.:

The only thing missing is when the files were transferred. An examination of the FileTransfers table shows that this information is not stored in there and it is unlikely to be in Users, Participants or Contacts (I have checked – trust me). So how do we find out when the file was transferred? one method is to query the sqlite_master table to see if any tables reference the FileTransfers table (rather than the other way around, which is what we have been looking at so far).

The query to do this is shown below. Note that I tend to use “like” in my queries rather than “=” i.e.:

Code:

SELECT *
FROM sqlite_master
WHERE sqlite_master.sql LIKE '%references filetransfers%'

rather than

Code:

SELECT *
FROM sqlite_master
WHERE sqlite_master.sql = '%references filetransfers%'

This is simply because “=” is case sensitive in SQLite and “LIKE” is case insensitive, this just avoids missing something, particularly, as in this case, where the schema has capitalized portions of the table names.

There are three tables with foreign keys that reference the FileTransfers table, the most promising of these looks to be the TextMessages table, which has the following foreign key:

Code:

CREATE TABLE TextMessages
{
  ...
  foreign key (FileTransferId)  references FileTransfers (FileTransferId)  on delete set null,
  ...
)

So we take a quick look at the TextMessages table and see what is in the FileTransferId column, as this is the table and column referenced above:

Not what we were hoping for, there are no values at all in the FileTransferId column.

So we look at one of the other two tables that reference the FileTransfer table, the PictureTransfers table is referenced on both the SmallFileTransferId column and the LargeFileTransferId column. I have no idea what these mean – so let’s take a look at this table:

Now, this is more interesting, there are also 22 rows in this table and the SmallFileTransferId column has the same 22 unique IDs that we saw in the FileTransfers table. But there is still no date information.

So we do the same process as before and look to see what references the PictureTransfers table, and we see that the TextMessage table references PictureTransfers:

The line of interest here is reproduced below:

CREATE TABLE TextMessages
(
…
foreign key (PictureTransferId)
references PictureTransfers (PictureTransferId)
on delete set null,
…
)

This leads us back to the TextMessage table, but this time the referenced field is PictureTransferId, so we check out that column:

An examination of the TextMessage table shows that there is a timestamp column.

So before we create our final query if we summarise what we have found.

The FileTransfers table is referenced by the PictureTransfers table which is in turn referenced by the TextMessages table. We want a report showing each File Transfer along with various other data but particularly user name and date, so we want a report with 22 rows.

The final query is shown below along with the visual designer window. The incoming column has a yes/no boolean conversion applied and the timestamp column was a unix epoch date and is converted by the Browser as such. While the SQL looks complex with three JOINs, the visual query designer shows this in a much more intuitive way.

Of course, all of the above was created using the Forensic Browser for SQLite using drag and drop query building.

If you are a Forensic Browser user and have a similar problem I hope this helps and gives you an idea of how your could approach it, but if you do need further assistance then please do not hesitate to get in touch, it’s all part of the service.

The SQLite website has some very detailed information on Foreign Keys here:

https://www.sqlite.org/foreignkeys.html

How NOT to examine SQLite WAL files

by Angie | Nov 14, 2016 | Forensic Toolkit for SQLite

At a recent conference while talking about SQLite forensics I found out that some people still use non-forensic tools to investigate databases with WAL files and were quite happy that they would not miss anything of importance. This is something I disagree with very, very, strongly and I hope in this article to explain the very basics of how WAL files work and show why you should not use non-forensic tools to examine SQLite databases.

Most of the databases we examine are appended to as user interactions take place. Be it a web browser which contains tables related to browsing events whereby new URLs are appended to associated tables as and when the user navigates to them. Or a messaging application where each message to and from the user is appended to the end of a list of such messages and new contacts are likewise added to the end of a list of contacts. In the example below, we will consider a simplified database which is appended to in the same manner and we will show what happens when the user tries to hide an event by deleting it. 

While the example is simply the same sequence events as shown here will happen in the same way if a user chooses to delete an incriminating message or delete a URL of a side to hide the history of their visit from a real-world application.

For those of you who are not aware WAL files are Write Ahead Logs and simply put when a page is to be updated in the database the complete new page is appended to the WAL file and the old page is left in the DB unchanged. At some later point the latest copy of each page in the WAL file is copied back to the appropriate place in the DB and any new pages are written to the WAL starting from the beginning again.

When an application uses the SQLite library to read a record from the DB, the library determines whether the latest copy is in the DB or the WAL and reads it from the appropriate location.

Typically the pages are copied from the WAL to the DB either when it exceeds 1000 pages or when the application is shut down gracefully. So when investigating a DB which has an associated WAL then all of the most recent pages and changes to the DB are held in the WAL NOT in the DB.

The process that some people use, believing it will get all the latest pages (and also show the older version of these pages) is to rename the WAL and open the DB with a non-forensic tool believing this will show the old state of the database – this is correct but also very wrong. They then rename the WAL back and open the DB again believing that this will show the latest state of the database, again this is correct but very, very limiting.

The rest of this article will show why you SHOULD NOT use this technique and with a very simple example will show why.

The example is a simple database that has events appended from time to time. This could be some logging application or a messaging application. Our example has just one table (of course it could be and normally would be more complex) and the sequence of events for initializing the database, creating the table and populating the records is documented below. In the example a number of records are added 3 records are added, one then deleted and then another record is added. The SQLite commands are as follows (the order is important):

Open an SQLite command prompt creating a test database

Code:

SQLite3 atest.db

Put the DB into WAL mode

Code:

PRAGMA journal_mode = WAL;

Create a test table with one text column

Code:

CREATE TABLE testtable (textcol text);

Add 3 records (you could add more but this keeps it simple)

Code:

INSERT INTO testtable VALUES (‘our first text string’);
INSERT INTO testtable VALUES (‘our second text string’);
INSERT INTO testtable VALUES (‘our third text string’);

Delete the second record

Code:

DELETE FROM testtable where text col = ‘our second text string’;

Add one more record

Code:

INSERT INTO testtable VALUES (‘our fourth text string’);

Now kill the command shell – do not exit SQLite first, this will preserve the WAL file

The folder containing your test database should look as follows:

The database you created should be present along with the associated WAL file (you can ignore the .db-shm file)

Now make a backup/archive of the database and WAL file (so we can use them both again later) using whatever application you choose.

And now rename the WAL file so that SQLite does not associate it with the parent database

Code:

Ren atest.db-wal   atest.db-walx

We now want to open the database in a tool of our choosing – we could use the SQLite command prompt but for variation I will use the Firefox SQLite manager plugin.

SQLite Manager shows that there are no tables in the database at all:

And the database settings tab shows that the database is in WAL mode:

So why is the database empty? This is simply a function of the WAL mode as discussed above. All changes to the database are first written to the WAL until either the application is shut down gracefully (remember we killed the command shell – so our SQlite exe was NOT shut down gracefully) or more than 1000 pages (by default) are written to the WAL (ours is just eight 1K pages). But we renamed the WAL file so SQLite can no longer associate it with its parent database – hence it can’t find the records we added.

Now re-instate the database and the WAL file from your backup and, without renaming the WAL file, open the database in the Firefox SQLite manager plugin again (or any non-forensic SQLite tool). You can see from the screenshot below that all the records that we added are there and the record we deleted is not present – this is the live database and this is what we would expect to see.

If we look at the results using the Forensic Browser for SQLite we see the deleted record is still within the WAL and a complete picture of what has happened in this example. 

In more detail:

But why are there multiple copies of some of the records?

When a working copy database is created with the Browser and a WAL file is processed the additional columns supplied by the Forensic Browser for SQLite provide information about where the record was found.

The first additional column sfIsLive shows whether a record is a live record or ‘not live’. Live records are those that the user would see if they opened the database using a normal non-forensic SQLite database viewer. Where not live = false these are records that are deleted OR copies of live records.

sfSource shows where the record has been recovered from, in this case, all of the records are from the WAL file.

sfPage is the database page number that the WAL file is holding, in this case, all of the records are from DB page number 2.

sfCommit is the commit number (more on this shortly)

sfpk is just a unique number for each record – we will can this to refer to each record in our description of events below.

Some background

SQLite is an ACID compliant database manager and without going into too much detail 

SQLite maintains its integrity by using journals and transactions. Each transaction can be one or more writes (pages) to a database. The last write/page of a transaction is known as the commit frame. When using the SQLite command line as we have just done every write is a single transaction and every write is, therefore, the commit frame. So the sfCommit number shows all of the pages (and rows) that were written to a database in each transaction.

So looking at the example above we can see the sfCommit 1 (sfpk 4) comprised a single row (our first text string) written to frame2 of the WAL and representing page 2 of the database.

The second transaction sfCommit 2 is two rows (sfpk 5 & 6) this is a new page written to frame 3 of the WAL but again this is a new version of page 2 of the database. This transaction is the addition of the second row of the database but entails a complete rewrite of page 2 hence this new copy of page 2 has both rows.

The third transaction (sfpk 7-9) involves page 2 being rewritten again with the addition of the third entry to the table.

The fourth transaction (sfpk 10 & 11) again rewrites page 2 but in this case, the change is the deletion of the second row.

The final transaction (sfpk 1-3) is the addition of the fourth row of text.

So using the Forensic Browser for SQLite we can see the deleted record that we would not otherwise have seen using the ‘trick’ that some offer as a method of dealing with WAL files.

We can also see when this particular record was deleted, i.e. in transaction 4. In a normal application, such as a messaging app, there may be date and time information in transaction 4 that could show us approximately when this transaction occurred – or there might be date and time information in transactions 3 and 5 that would allow us to ‘bracket’ the deletion between two other events.

Imagine that this was a real-world database such as internet history or a messaging database on a mobile phone (we never normally shut down applications on phones) where a user had repeatably deleted browsing history or messages. There could be many WAL pages containing data that precede the most current page so potentially lots of pages containing data that can not be recovered using the non-forensic technique that is being proposed. 

There is another article on my web site that deals with this in more detail:
https://sandersonforensics.com/forum…eted-in-SQLite

Summary

Using non forensic tools in the manner describe above will show you either:

• the live files from the current database (when the WAL is present), or
• the state of the database before the first transaction in the WAL – i.e. just the records in the DB without any of the changes in any of the transactions in the WAL

Using an appropriate Forensic SQLite Browser such as the Forensic Browser for SQLite you can see all of the records from the DB along with all of the changes that have occurred (often many copies of the same page comprising multiple transactions).

Why can’t I see who sent that deleted IOS SMS message

by Angie | Nov 2, 2016 | Forensic Toolkit for SQLite

I have seen a number of posts on bulletin boards recently that refer to some of the mainstream software failing to be able to attribute a contact to a deleted message on IOS SMS.db recoveries. My previous post “SMS recovered records and contacts – three ways” shows another method of establishing a relationship between two sets of deleted records. However in this post, I wanted to show how you can use the database schema and in particular foreign key constraints and triggers to understand why deleting one item can have a knock-on effect and how the schema can be used explain why these records are no longer available.

My previous article is available here: https://sandersonforensics.com/forum…cts-three-ways

In summary – understanding triggers and foreign key definitions in a database schema can give a much better understanding of how tables relate to each other and give an overview of how the database “works”.

The following covers triggers and foreign key constraints in a little detail but full coverage is well beyond the scope of this article. Links to further detail for those interested is provided within the text, of course, if you are a Forensic Toolkit for SQLite user and need any help with this, or any other aspect of SQLite forensics, then please get in touch – it’s all part of the service.

Triggers

Triggers are database operations that are automatically performed when a specified database event occurs. It is important to understand that a trigger is a function of SQLite and the database it is defined on and once defined whether it runs or not is not within the control of the programmer, but is controlled by the settings of the database – i.e. the actions are carried out automatically by the SQLite library.

An example is the sms.db from an IOS device that maintains a table of deleted messages. By examining the code for the trigger we can see that after a record is deleted from the message table the guide of the deleted record from the message table is automatically added to the deleted_messages table:

A trigger can be created that will run before, after or instead of a delete, insert, or update event.

Code:

CREATE TRIGGER add_to_deleted_messages AFTER DELETE ON message
BEGIN
   INSERT INTO deleted_messages (guid) VALUES (OLD.guid);
END

The SQLite railroad diagram for a trigger is below:

Simplified we:

• Create a trigger and give it a name
• Decide when it executes (before/after/instead of) an event
• Decide what the event is (delete/insert/update)
• Define what the trigger does

Rather than create some triggers it might be useful to show some real world triggers and explain what they do.

The Viber database has the following trigger:

Code:

CREATE TRIGGER Delete_Contact AFTER DELETE ON Contact
BEGIN
   DELETE FROM ContactRelation WHERE ContactID = old.ContactID;
END

This trigger: 

• creates a trigger called insert_mms_pending_on_update
• that executes after
• a record in the pdu table is updated, when the new value for msg_box is changed to 4* and the new value for m_type = 128
• a new record is inserted into pending_msgs with values (1, new._id, new.m_type,0,0,0,0)

This trigger is of also interesting because it gives a bit of a clue as to what some of the values in pdu table mean. It would seem (without further investigation) that a record in the pdu table with m_type = 128 and msg_box = 4 could be a pending message of some sort.

Triggers are stored in the sqlite_master table and can be examined along with the tables on which they operate by running queries such as:

Code:

SELECT *
FROM sqlite_master WHERE type = ‘trigger’

To show all triggers in the schema.

SELECT *
FROM sqlite_master WHERE type = ‘trigger’ AND tbl_name = ‘<insert table name>’

To show the triggers for a specified table.

There is a wealth of information regarding triggers on the SQLite web site here https://sqlite.org/lang_createtrigger.html

Foreign keys

SQL foreign key constraints are used to enforce “exists” relationships between tables. The SQLite database engine will not allow a change to records that will break these constraints.

An example might be a messaging database which holds a table of contacts and a second table with the messages. It does not seem to make sense for there to be a record in the messages table with a contact_id field where there is no matching record in the contacts table with the same id. This can be achieved by a foreign key constraint applied to the table definition. The references clause ensures that for every message there must be a record in the contacts table linked to by the contacts_id column:

Code:

CREATE TABLE contacts (id integer primary key, name text)

CREATE TABLE messages (id integer primary key, contact_id integer REFERENCES contacts(id), message text)

With the above table definitions if you try to insert a record into the messages table with a contact_id that does not exist in the contacts table then SQLite will not allow you to break referential integrity and will raise an exception.

But what happens if you try and edit a record that is already in the database, or you delete a record? SQLite provides for this by allowing you to specify ON DELETE and ON UPDATE actions. The actions are:

NO ACTION – Do nothing
RESTRICT – prevents the update or delete action taking place
SET NULL – sets the child key of all rows mapping to the parent to NULL
SET DEFAULT – as above but sets to the default value
CASCADE – propagates the action on the parent key to all child keys

For instance the master.db file from a blackberry app contains an AppIcons table with a foreign key on the Apps table AppID.

Code:

CREATE TABLE "AppIcons"
(
   AppId          integer primary key not null,
   Data           blob not null,
   foreign key (AppId) REFERENCES Apps (AppId) ON DELETE CASCADE
)

Perhaps not surprisingly when an App is deleted from the parent Apps table all entries in the AppIcons table that match the AppId of the deleted application are also deleted automatically.

SQLite foreign keys are discussed in depth at this page:

You can probably see how this helps with our initial query. To summarise it – we have recovered a number of deleted messages from an SMS message table but we can’t find the related contact with who our users were communicating. We want to know why?

It may help further if we look at the structure of the SMS database and how the tables interrelate. The diagram below shows the different tables in the sms.db and shows the joins between the tables (all joins are left joins).

There are four main tables:

So, in summary, the database contains a series of conversations (chat table) each chat will have one or more messages (message table) associated with it, each chat can have one or more participants (handle table) and each message can have zero or more attachments (attachment table).

Each major table is joined to other tables via an intermediate join table (e.g. chat -> chat_handle_join -> handle) this allows one chat to have multiple participants without having to duplicate entries in the chat table or the handle table.

For the sake of brevity for the rest of this article, I will ignore the attachment and message_attachment_join tables.

To explain this further, let’s take a look at a real chat from my phone – chat number 109.
In the display below I have provided the SQL behind three queries that show the relevant data for chat 109.

It can be seen that chat 109 is between me and one friend (handle_id 109) so there is one entry in the chat_handle_join table for chat 109 that joins to the relevant contact (handle 108)

There are 42 messages in this conversation, so there are 42 entries in the chat_message_join table for chat 109 (only 7 shown for brevity) each of which points to one of the 42 messages in the message table.

Note that there is also a handle_id column in the message table that links directly to the handle table (the dotted line in the picture above). More on this later.

So what happens if we delete an entire conversation by deleting entry 109 in the chat table. To find out we need to look at the schema for the database.

It would seem sensible to start with the schema for the chat table and any triggers created on this table. The following shows the schema for the table. There are no triggers and we can see straight away that there are no foreign key constraints defined on this table.

First the table schema and the foreign keys:

CREATE TABLE chat_message_join (
   chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE,
   message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE,
   PRIMARY KEY (chat_id, message_id)
)

We can see from the above table schema that there are two primary key constraints.
chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE,

When a record is deleted from the chat table this instructs the SQLite engine to automatically delete any entry in the chat_message_join table where the chat_id matches the ROWID of the deleted entry from the chat table.

message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE,

The second foreign key constraint ensures that when a message is deleted from the message table that any entry in the chat_message_join table that has a message_id that matches the ROWID of the deleted message will also be deleted.

So far the constraints ensure that matching entries from the chat_message_join table are deleted when a row is deleted from either the chat or the message tables.
What we can’t see yet is how a message is deleted when its parent ‘chat’ record is deleted. For this we need to look at the bottom index in the screenshot above:

Code:

CREATE TRIGGER clean_orphaned_messages AFTER DELETE ON chat_message_join
BEGIN    
   DELETE FROM message WHERE
   (SELECT 1 FROM chat_message_join WHERE message_id = message.rowid   LIMIT 1) IS NULL;
END

1. CREATE TRIGGER clean_orphaned_messages AFTER DELETE ON chat_message_join

We can see from the first line that this trigger fires when a row is deleted from the chat_message_join table.

1. DELETE FROM message WHERE

Line three instructs the trigger to delete rows from the message table when the query in the following lines evaluates to true

1. SELECT 1 FROM chat_message_join WHERE message_id = message.rowid LIMIT 1) IS NULL;

Line 4 deletes any row in the message table that does not have a matching entry in the chat_message_join table.

So the foreign key constraint ensures that a delete on the chat table causes any matching records in chat_message_join to be deleted. The trigger fires when any row from the chat_message_join table is deleted and ensures that any rows in the message table that do not have parent record in the chat_message_join table are also deleted

A matching set of constraints exists in the chat_handle_join table ensure that when a chat or a handle is deleted then any associated rows in the chat_handle_join table are also deleted.

Code:

CREATE TABLE chat_handle_join (
chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE,
handle_id INTEGER REFERENCES handle (ROWID) ON DELETE CASCADE,
UNIQUE(chat_id, handle_id))

The trigger is slightly different

Code:

CREATE TRIGGER clean_orphaned_handles AFTER DELETE ON chat_handle_join
BEGIN    
   DELETE FROM handle WHERE
   handle.ROWID = old.handle_id    
   AND        
   (SELECT 1 from chat_handle_join WHERE handle_id = old.handle_id LIMIT 1) IS NULL    
   AND        
   (SELECT 1 from message WHERE handle_id = old.handle_id LIMIT 1) IS NULL;
END

After a delete in the chat_handle_join table, any handles with the same ROWID as the deleted chat_handle_join handle_id will be deleted, provided that there are no other rows in the chat_handle_join table that have the same handle_id and there are no rows left in the message table that also have a handle_id that matches the deleted handle_ID.

There is one further trigger, on the message table, that is relevant to our question:

Code:

CREATE TRIGGER after_delete_on_message AFTER DELETE ON message
BEGIN    
   DELETE FROM handle         
   WHERE
      handle.ROWID = OLD.handle_id    
      AND        
      (SELECT 1 FROM chat_handle_join WHERE handle_id = OLD.handle_id LIMIT 1) IS NULL    
      AND        
      (SELECT 1 FROM message WHERE handle_id = OLD.handle_id LIMIT 1) IS NULL    
      AND
      (SELECT 1 FROM message WHERE other_handle = OLD.handle_id LIMIT 1) IS NULL;
END

The trigger executes after a record is deleted from the message table and will delete any row in the handle table where the ROWID matches the handle_id of the deleted record, provided that the row in the handle table does not match a record in the chat_handle_join table and there is no additional record in the message table where either the handle_id or other_handle columns use the handle_id of the deleted record.

In answer to our question “So what happens if we delete an entire conversation by deleting entry 109 in the chat table.” (diagram reproduced below)

• The ROWID in the chat table is the primary key for this table and as such there can only be one entry with value 109
• Deleting this row causes a constraint violation with both the chat_message_join table and the chat_handle_join table due to the foreign key definitions
• The foreign key definitions have an ON DELETE CASCADE clause so all rows in the chat_message_join table and the chat_handle_join tables with a chat_id of 109 will also be deleted
• The clean_orphaned_messages trigger that fires after a delete on the chat_message_join table will ensure that any of the messages from the message table that do not have a matching entry in the chat_message_join table will be deleted, i.e. all 42 messages associated with chat 109
• The clean_orphaned_handles trigger that fires after a delete on the chat_handle_join will ensure that as long as the handle_id associated with chat 109 is not in use by any other chats then this handle will be deleted
• The after_delete_on_message trigger that fires after a delete on the message table has a similar action to the clean_orphaned_handles but also checks two other fields in the message table before deleting the associated handle*

*Note that this last trigger will also insure that if messages are deleted individually (rather than en-masse by deleting as above starting with the chat table) then when all messages that relate to a handle are deleted the corresponding handle will also be deleted.

So there you have it – deleting all of the messages relating to a conversation one by one, or deleting the entire conversation will ensure that the contact information is also deleted, provided the same contact is not part of any other conversation.

SMS recovered records and contacts – 3 ways

by Angie | Sep 13, 2016 | Forensic Toolkit for SQLite

In a recent forensic case involving recovered deleted SMS messages from an sms.db file on an IOS mobile device, none of the mainstream mobile phone forensic software made the link between sender and recipient for the recovered records of interest.

I have been asked a few times recently about obtaining the third party of a deleted IOS SMS message that has been recovered by the Forensic Browser. The procedure is simply to create a JOIN between two (or three – there are two ways of establishing the third party) tables and if the data is in the relevant tables then a link is made. Unfortunately, when messages are deleted, particularly when entire conversations are deleted, the primary keys on the all-important tables are often overwritten making these joins impossible.

When the case mentioned was looked at with the Browser neither of the JOINS described above allowed the investigators to ascertain who the third party in a deleted conversation was. So I was asked to take a look at the DB further and I managed to find a rather interesting, to me, third rather obscure route to make the link. This was made possible by the new Structured Storage Manager in the Forensic Browser for SQLite.

In this article, I’ll describe the structure of the sms.db database and how the different tables relate to each other. I’ll then explain the two ‘normal’ joins and why they usually don’t work when trying to ID the third party for a deleted message and I’ll show the third route mentioned above which can often work where the first two don’t.

First I’ll provide an overview of the relationships between the tables. I am not going to discuss all elements and constraints of the different tables as this goes beyond the scope of this article, but I’ll cover in overview an example of a user deleting a conversation to explain what happens and how data can be recovered.

Firstly the relationships between the different tables:

There are four main tables:

• Chat – contains an entry (row) for each chat (or conversation) between the ‘owner’ of the DB being examined and any of their contacts. Each chat is uniquely identified by the primary key of the table, the ROWID column. Both the chat identifier and the guid column contain the phone number or occasionally name of the third party to the conversation.
• Message – contains the details of each of the messages.
• Handle – contains the third party details in the id and uncanonicalized id fields.
• Attachment – contains a list of file attachment details.

So in summary the database contains a series of conversations (chat table) each chat will have one or more messages (message table) associated with it, each chat can have one or more participants (handle table) and each message can have zero or more attachments (attachment table).

Each major table is joined to other tables via an intermediate join table (e.g. chat handle join) this allows one chat to have multiple participants without having to duplicate entries in the chat table or the handle table.

Let’s take a look at a real, but anonymized, chat from my phone – chat number 109.
In the display below I have provided the SQL behind the queries that show the relevant data for chat 109, it can be seen that chat 109 is between me and one friend 

You can also see (the dotted line) that there is a further connection between the message and handle table (message.handle_id -> handle.ROWID) that is not explicitly defined by the table schemas.

What happens when a conversation is deleted?

Intuitively if you deleted a conversation from a phone you would expect all of the associated messages to be deleted and if you deleted all of the associated messages in a conversation then you would expect the associated entry in the conversation (chat) table to be deleted.

The same can be said for the handle table. The default set up for the sms.db is for the handle entry to be deleted when all message entries that point to it are deleted (more on this in another article).

So in summary when a conversation is deleted in its entirety the relevant entries in the message table are deleted and if there are no further messages which involve this contact the relevant row in the handle table is deleted.

So what should be self-evident from the diagram above is that if we recover a deleted text in the message table then the associated entry in the handle table must either exist or be recovered for us to establish who the third party in the conversation is, or we need to follow the links back to the chat table (each of which must also exist or be recovered).

Identifying the third party the ‘standard way’

If there is a single additional participant in the conversation then the handle can be obtained from the handle_id in the messages table and a simple join used to show the other party:

If the entry in the handle table is not present or cannot be recovered then providing that the relevant records in BOTH the chat_message_join and chat table are recovered then a slightly more complex join can be made to retrieve the same information.

If there are multiple participants, then the chain of tables must be followed back around, i.e.: message -> chat_message_join -> chat -> chat_handle_join -> handle – in order to recover all participants. I’ll leave this query to you to work on – it’s straight forward and just the same as the previous query – just involving more tables.

There is however a problem when dealing with deleted records and this relates to an SQLite artifact called freeblocks.

Freeblocks are structures within an SQLite database page that track all blocks of unused space greater than 3 bytes in size. They do this by writing a 4-byte structure to the start of each block of free space that includes the size of the current block and a pointer to the next block. When a record is deleted from an SQLite table it usually results in a new block of free space comprising the deleted record of which the first four bytes are overwritten by this 4-byte structure. The screenshot below shows a page with two freeblocks (the unused space before the first record is not part of the linked list of freeblocks) the freeblock structure is underlined (a freeblock pointer of 0x0000 signifies the last freeblock):

A major complication has now arisen that will affect our success rate when trying to recover records.

The first two entries in an SQLite record (row) are the Payload length followed by the ROWID.

In most cases, these two variable length integers take up less than 4 bytes of storage so when a record is deleted these two records are usually overwritten by the freeblock structure. As the ROWID usually records the primary key the loss of this value usually means that recovered records in different tables cannot be related to each other.

Luckily when two adjacent records are deleted at the same time a freeblock will be created that encompasses both the records, but only the start of the first record will be overwritten. Unfortunately, this does not help in our scenario because there would be only one chat record per conversation and normally only one other participant in the handle table in a conversation; and even if there were more than one other participant it is not likely that they will have consecutive handle_ids.

So what is the solution?

Happily conversations usually involve more than one message, normally lots, and they are very immediate in that a message often gets an immediate reply (message and reply are usually adjacent in the database). So we can hopefully recover lots of messages and hopefully some of them with intact (not overwritten) ROWID’s.

All we need to do is to link one of the recovered messages back to either the chat table or the handle table to establish who the third party participant was.

But before I show you how to do this I need to digress and talk about the Structured Storage Manager mentioned in the opening lines of this article. The Structured Storage Manager (SSM) can be used to break down structured storage (think binary plists, Facebook orca blobs etc.) into a table structure so that the Forensic Browser can query them and happily there is one such blob in the database that can help us.

The chat table contains a column called “properties” that often holds a binary plist.

Which when decoding using the Forensic Browser built in Binary Plist viewer it looks like this:

The interesting item here is the CKChatWatermarkMessageID integer which my testing shows the ID of one of the messages associated with this chat. In fact what seems to happen is that this blob is updated regularly and the messageID (and following timestamp) represents the last message in a conversation.

We can use the SSM to break down the binary plist into a table. To do this we run the SSM from the Tools menu:

In the following dialog, we identify the table and fieldname of the field holding the Binary Plist blob (properties) and we also identify the primary key for the table so we can relate the decoded data back to the original row. We also tell the SSM what table we want the new data stored in and what type of data it is (Binary Plist in this case):

Once we hit OK the new table is created and we can easily create a query that associates a chat identifier (phone number) with each message (CKChatWatermarkMessageID) from the decoded BPList:

The query above is a little complicated particularly when we need to use it in conjunction with another query so I want to simplify the above SQL by using a VIEW. This is best explained by example – I choose “Create view on current visual query” from the Queries menu:

I give this the name watermarks (it could, of course, be any meaningful name):

The newly created VIEW is a sort of virtual table so I can now run a new very much simpler query that exactly replaces the above:

The list of CKChatWatermarkMessageID and telephone numbers/contacts details above are from all recovered conversations, as well as from some conversations that are still live.

Finally, I want to find any one (or more) of the rows from the above query that has a CKChatWatermarkMessageID that is *IN* a list of any of the ROWIDs (message IDs) for any message with handle_id = 108 i.e. our deleted conversation.

A query showing these ROWIDs and a few extra fields for context is below:

Note that that list of messages that we have recovered from chat 108 is not complete and also some of the recovered messages may have had their ROWID overwritten as previously described. But, all we need is to be lucky enough to find one matching recovered message to determine who the conversation was with.

The highlighted *IN* above gives a clue as to the SQL syntax. We can use the SQLite in expression to filter on a list of values that are in a specified list, the format is:

Code:

SELECT * from xxx where expression IN (value1, value2, .... value_n)

value1, value2… can be replaced by a SQL query that returns just one column (known as a correlated subquery):

There you have it – we have managed to associate a message ID from the deleted conversation with the decoded binary plist from a recovered chat record with an overwritten primary key and thus establish the third party for all of the messages in chat 108. We have also managed to do what the mainstream mobile forensics tools failed to do and we therefore reiterate that although they quite rightly have their place you should always back up anything they find, or fail to find, with another method

Determining when a record was deleted in SQLite

by Angie | Mar 24, 2016 | Forensic Toolkit for SQLite

In a recent article, I discussed how I identified deleted records in a database that was using WAL journalling. In this article, I want to take this a little further and show how we can see what the live records were at a specific point in time and how we can timeline the frames in a WAL and use this to determine approximately when the records were deleted .gs.

To help with this I created a test messaging application with a few artificial messages and users to keep the demonstration simple.

The “application” I have developed has just two tables “messages” and “users” and I created a simple conversation series and added and deleted users and messages.

The Messages table, when viewed with the Firefox SQLite manager plugin, i.e. the live database as the application, would display it looks as follows:

The users table is even simpler:

The final step in setting the ‘picture’ is to show the database as it would be seen in the Forensic Browser with a simple join between the tables and the columns and the columns displayed with simple conversions so the report makes more sense:

That is how most SQLite viewers would see the database and all looks OK. So what happens when the database is opened with the Browser and the WAL journal is processed alongside the master database? The following screen shot shows just a few of the 101 messages that were recovered by the Forenisc Browser for SQLite, while there is a lot of duplication in this report (see later) there are also some records that don’t appear in the live database (some of which are highlighted) it is these records we will examine in the rest of this article.

So why 101 records? If you have read my previous article you will be aware that when a database is using a WAL journal any new or changed pages are updated and appended to the end of the WAL file, the associated database file is not touched at all until a WAL checkpoint occurs (typically when the WAL grows beyond 1000 pages). The other reason of course for the extra records is because some of them are deleted records that are not in the live database.

So how can we identify just the deleted records when there are exact copies of live records that are also held in the WAL?

The simplest method is to using something called a correlated subquery that first identifies the MD5 of each of the live records and then returns a query which shows everything other than a record that has the same MD5as the live records.

The query is:

Code:

SELECT *
FROM messages
WHERE sfcMD5 NOT IN (SELECT sfcMD5 FROM messages WHERE sfIsLive = 'True')

And results look like this, still some duplicated records (which is always to be expected when WALs are active) but all messages that we haven’t seen before:

This query, however, returns every row from the table and will include duplicate copies of any rows where there are multiple copies. We can filter this query further by simply adding a group by clause to the sfcmd5 column to just display one unique copy of each record based on the MD5 of all of the columns in the original database (i.e. not including those columns that start with sf…).

This query is further explained here:

So what else can the Forensic Browser do for us as part of an investigation?

Firstly we can use one of the parsing features of the Browser to use a commit or transaction number to limit the Browser when considering records to include in the list of live records.

The commit number for the last WAL frame containing the deleted records is highlighted above is 20 and WAL frame is frame 25. When the Browser is run and the WAL is identified the investigator gets the option to process all frames, none of the frames, or select the maximum frame. Note that all records are still processed, but the value selected will determine which records are marked as live:

The following screenshot shows the result of parsing with the last commit number set to 20. The results have been filtered to just show the live records and we can see at the top left that this is 16 of 101 records. So, in summary, this is the database/table as it was before the messages were deleted.

When were the records deleted?

As well as knowing what records were deleted it would of course be useful to determine when a record was deleted. The success of this technique depends on the content of the database and works best if there is a timestamp that is set automatically, in this example we are assuming that the “date” is the date a message was sent/received and is set automatically by the application, i.e. not manually by the user.

The first step is to try and work out what the last possible date that the last deleted record was live in the table. We can then determine:

1. The date and time of the last message in the table before the record was deleted
2. the date and time of the next message after the record was deleted.

The highlighted record below is the last occurrence of any of the deleted records, we can see that this is in commit number 20 (as discussed above).

To summarize what this shows:

• The record of interest is on DB page 5
• At commit 20 page 5 is updated and the record is live
• Commit 21 just affects page 6 and adds a record to the database
• At commit 22 page 5 is updated and the record is no longer in the table
• The record was therefore deleted in commit 22.

As we know the record was not present in page 5 at commit 22 the numbered bullet points above become:

1. The date and time of the last transaction in the table before commit 22 (LastLiveDate)
2. the date and time of the next (earliest) transaction after and including commit 22 and after (LatestDeleteDate)

If we can determine the date of the last record that was sent or received before commit number 22 then we know the last time that the record was in the live DB, the following query does this:

Getting the other bracketing date (the following date) is slightly more complex because WAL frames/pages after commit 22 can also include records that are live both before and after the deletion occured. The following SQL uses the query above as a sub query that returns the last live date, and finds the earliest date that follows the last live date from the following WAL pages.

Getting the other bracketing date (the following date) is slightly more complex because WAL frames/pages after commit 22 can also include records that are live, both before and after the deletion occurred. The following SQL uses the query above as a subquery that returns the last live date and finds the earliest date that follows the last live date from the following WAL pages.

Code:

insert into messages (id, date, sent, type, user_id, message) values (9, strfTime('%s','2016-03-04 09:22:02'), 0, 2, 1, 'Paul, be my friend, Darcy');
insert into users (id, username) values (3, 'Darcy');
insert into messages (id, date, sent, type, user_id, message) values (10, strfTime('%s','2016-03-04 09:25:43'), 1, 1, 3, 'Hiya mate - didn''t know you were on this app');
insert into messages (id, date, sent, type, user_id, message) values (11, strfTime('%s','2016-03-04 09:27:43'), 0, 1, 3, 'No time for pleasantries, Ive transferred the money from the company account');
insert into messages (id, date, sent, type, user_id, message) values (12, strfTime('%s','2016-03-04 09:29:22'), 0, 1, 3, 'This is really scary - I dont do illegal - no more after this');
insert into messages (id, date, sent, type, user_id, message) values (13, strfTime('%s','2016-03-04 09:29:22'), 1, 1, 3, 'OK good - thats it we are quits now');
insert into messages (id, date, sent, type, user_id, message) values (14, strfTime('%s','2016-03-04 10:03:21'), 0, 1, 2, 'Hi honey - are you working late? what time will you be home?');
insert into messages (id, date, sent, type, user_id, message) values (15, strfTime('%s','2016-03-04 10:05:10'), 1, 1, 2, 'about 8pm hopefully');
insert into messages (id, date, sent, type, user_id, message) values (16, strfTime('%s','2016-03-04 13:13:40'), 0, 1, 3, 'Oh - I forgot to say, delete any trace of this conversation');
insert into messages (id, date, sent, type, user_id, message) values (17, strfTime('%s','2016-03-04 14:08:21'), 0, 1, 2, 'Hi honey - did you get my message form earlier?');
delete from messages where (id >= 9 and id <= 13) or (id = 16);
insert into messages (id, date, sent, type, user_id, message) values (18, strfTime('%s','2016-03-04 17:05:08'), 1, 1, 2, 'been in a meeting, leaving shortly, quick pint with the boys and then ill be on the train');

This article has shown why it is essential to look at a database and WAL as a whole and not just look at the latest live incarnantion of the database, and also why we as investigators need to actively examine SQLite databases and not just rely on a canned report from a generic investigations application to produce our reports for us.

Identifying deleted records in DB and WAL

by Angie | Mar 23, 2016 | Forensic Toolkit for SQLite

I have been working with a user who is processing an Android mmssms.db with its associated WAL journal and it became clear that another forensic tool that he was using was not identifying deleted records. My colleague wanted to create a report showing just the deleted records from the SMS table for a specific phone number.

First a little back ground regarding Write Ahead Log (WAL) journals.

When a new record is to be written to an SQLite database and WAL journalling is in operation the SQLite engine identifies which page of the database needs to be updated with the new record and writes a new copy of that page to the WAL file (with the new record) but leaves the old page in the DB untouched. If subsequently another record is to be written to the same page then another copy of the page (with the new record and the previous new record) will be appended to the WAL file.

The same happens if a record is deleted – the page that needs to be updated (with the deleted record) is appended to the WAL and the DB left unchanged.

If you have been following all that you will realise that you can get multiple copies of a page in the WAL file and therefore multiple copies of the records in a page.

The Forensic Browser can process a DB along with the WAL file and will identify which of all of these records is the current “live” record but will still display all of the other records, because, as forensic investigators we need to see everything. If the record is not the actual live record that would be displayed by a normal SQLite browser, then it is marked as False in the sfIsLive column.

If you have really been following this you will realise that if a record is written to the WAL and then subsequently deleted you will get both copies of the page (one with the live record and one without it – i.e with it deleted) in the WAL file. The recovered deleted record of course will not be marked as live, because it is not.

The task therefore is to identify all the records for the user (last four digits of phone number = 9937) that have been deleted. the process I used was as follows.

Open the Forensic Browser for SQLite and create a case file – we will be creating some views later and these will be saved to the case file.

Open the DB, the associated WAL file and choose to recover records.

Choose to process all of the frames in the WAL

When the tables have all loaded we run our first query to select all of the records from the SMS table that belong to the user we are interested in. the query is

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxxx9937'

To make our final query simpler we now need to create a view based on this query. This is done from the queries menu and when prompted we give the view a name – I called it “all_9937”.

The query above can now be replaced by :
Code:

SELECT *
FROM all_9937

The next step is to create a similar view but only on the live records for this particular phone number. The Browser records which records are live and which are not by using ‘True’ or ‘False’ as appropriate in the sfisLive column. So the query to identify live records is simply

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxxx9937' and sms.sfisLive = 'True'

We create a view on this query and call it “live_9937”

The final task is to create a correlated subquery to return the difference between the two tables, this sounds more complex than it is and the query is simply:

Code:

SELECT *
FROM ALL_9937
WHERE ALL_9937._ID NOT IN (SELECT LIVE_9937._ID FROM LIVE_9937)

This in English simply says select all records from 9337 that are not present in the live records from 9937.

The final results looked like this

The views can be replaced by the original queries but this makes the SQL a little harder to follow:

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms._ID NOT IN
(SELECT sms._ID FROM sms WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms.sfIsLive = 'True')