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

    My previous article is available here: http://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”.

    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:

    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 Delete_Contact
    • that executes after
    • a record is deleted from the contact table
    • deletes any record in the ContactRelation table where the ContactID value = the deleted (old) ContactID from the Contact table


    The Android mmssms.db has the following trigger:

    Code:
    CREATE TRIGGER insert_mms_pending_on_update AFTER UPDATE ON pdu
    WHEN
       new.m_type=128 
       AND
       new.msg_box=4  AND old.msg_box!=4
       BEGIN  
          INSERT INTO pending_msgs    (proto_type,     msg_id,     msg_type,     err_type,     err_code,     retry_index,     due_time)   VALUES     (1,      new._id,      new.m_type,0,0,0,0);
        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.

    Code:
    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)
    Code:
    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 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!



    First the table schema and the foreign keys:

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

    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

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