In this short article I want to introduce you to SQLite Forensic Explorer, which shows the structure of an SQLite database in a very visual way and show how it can be used to find deleted SQLite data (that can’t be recovered by other tools) including forensically valuable data from within SQLite indexes.
SQLite Forensic Explorer is only available as part of the SQLite Forensic Toolkit, more information here.
First a brief overview of the SQLite Forensic Explorer display which is divided into 5 main areas, each of which is explained below:
4. Decoded page
5. Raw hex
1. Tables - This shows the main tables in the database, both as defined in the sqlite_master table and in “normal” grid like form in which you can view all of the rows in each table. Selecting a table (or index) from the sqlite_master table allows SQLite Forensic Explorer to identify the root page of the B-Tree that forms that table. We can see from the second screenshot below that the messages table has its root at page 14.[*]
(Raw table view – shows individual records)
(sqlite_master table – identifies start page of B-Tree for each table)
2. Pages - The B-Tree itself is shown here, but for ease of viewing it is show on its side (with the root at the left rather than at the top). We can see that the root at page 14 is for the messages table and that this table has 6 leaf nodes (at pages 92, 99, 121, 96, 125 and 129). Selecting an interior or leaf node here causes the remaining three displays to be populated.
3. Records - Shows the decoded data for each record in the node in comma separated form. Clicking on a record here causes the decode page data to highlight the complete record in both the decoded page and raw hex displays.
4. Decoded page - This shows every byte decoded as per the SQLite page format. Selecting a field from the record in the Decoded page display causes those bytes to be highlighted in the raw hex display. You can select either a complete object such as the payload data to see all of this data highlighted, or just a cell within the payload to see this discrete item highlighted.
5. Raw hex - Selecting the start of a record in the raw hex display causes SQLite Forensic Explorer to try and decode the bytes from that location and find a match for one of the tables from the schema. More on this later when we discuss deleted data.
Recovering deleted data.
When records are added, modified and removed from SQLite databases, complex logic is utilised to ensure that the B-Tree structure complies with certain rules. This means that the B-Tree structure is regularly modified with records being moved from node to node and new nodes being added and deleted. Although an empty node (i.e. a node with no records) is wiped by SQLite, nodes can and do contain unallocated space and this space can and often does contain deleted or modified data (or a copy of data that has been moved elsewhere).
In the screenshot below “unused space” in the decoded page data has been selected and SQLite Forensic Explorer has highlighted these unused bytes in the raw hex display. It is quite easy to see there is a lot of data in this unallocated space.
As SQLite Forensic Explorer has previously parsed the table schemas, moving the cursor through the hex display allows the program to try and decode the data from the cursor position and see if it matches any of the tables. When a match is found a dialog is popped up as shown below:
Hitting the “Insert into database” button will copy this record into a new database - On the first time this option is called SQLite Forensic Explorer will prompt the user to select either a complete copy database (i.e. all records and indexes) or a blank copy database (just the table structure with no records).
SQLite Forensic Explorer and SQLite indexes
Indexes are an often overlooked source of forensic information, but a complex index such as those found in Skype and Firefox can provide a great source of intelligence.
For instance Skype has an index on the messages table, with the schema below:
CREATE INDEX IX_Messages_timestamp_chatname ON Messages (timestamp, chatname)
An SQLIte index B-Tree is very similar to a table B-Tree with difference being that tables only have data in their leaf nodes, whereas indexes have data in both interior and leaf nodes. SQLite Forensic Explorer allows the user to navigate through the nodes of an index B-Tree exactly as they would through a table B-Tree.
SQLite Forensic explorer is unique in its abilities to show the content of an index and in the example below we can see the Unix timestamp of a series of conversations between r3scue193 and sandersonforensics-lappy. Clearly this could be useful in any forensic examination especially if this is deleted information or changed information in a WAL file (this will be the subject of another article).
As indexes also grow when tables grow it is not unusual to see deleted data from indexes as in this example below where the root node of the IX_Messages_timestamp_chatname index contains deleted data in the unallocated space .