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

I have seen a number of posts on bulletin boards recently that refer to some of the main stream 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 affect and how the schema can be used explain why these records are no longer available.

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”.

For corporate or law enforcement investigators a fully functional demo 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 following covers triggers and foreign key constraints in a little detail but a 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.

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

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 guid of the deleted record from the message table is automatically added to the deleted_messages table:

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:

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:

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.

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 was 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:

Chat

Contains an entry (row) for each chat (i.e. thread 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.

Message

contains the details of each of the messages

Handle

Contains the third party details – i.e. this is the contacts or participants table

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 -> 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 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 ths table.

What about the intermediate joining tables. The chat_message_join table has the following schema and triggers. This is more interesting!

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.

Code:

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

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

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

Deleteing 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.