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

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

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:

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

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.

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

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.

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

Foreign keys

Categories

SQLite Forensics Book by Paul Sanderson