Introduction
SQLite databases are treasure troves of digital evidence found in many apps, from chat messages to transaction logs. But what happens when records are deleted? Enter freeblocks—hidden sections of unused space within database pages that retain remnants of deleted data. These freeblocks are a goldmine for forensic investigators, enabling the recovery of critical records and uncovering hidden evidence.
This blog demystifies SQLite freeblocks, walking you through how they work, how to locate them with tools like hex editors, and how to recover deleted records. Whether you’re an investigator or a digital forensics enthusiast, this guide will equip you with essential techniques to extract hidden data.
What are Freeblocks in SQLite?
Freeblocks are sections of unused space within an SQLite database page. When a record is deleted, the corresponding space isn’t immediately overwritten unless a secure erase mechanism is enabled. Instead, it’s marked as a freeblock, available for future writes.
In this process:
- The pointer to the deleted record is removed.
- The page header is updated to reflect the removal.
- If applicable, the offset pointing to the first freeblock is adjusted.
These freeblocks often retain remnants of the original data, making them a critical focus for forensic investigators aiming to recover deleted records.
Example:
In the screenshot below, we examine an SQLite database using DB Browser. The “messages” table shows two live records, but forensic clues suggest the presence of additional deleted records.
How Freeblocks Fit in SQLite Page Headers
To locate and analyze freeblocks, we first need to understand the structure of SQLite database pages. Each page contains a header that provides critical metadata, including pointers to freeblocks.
The table below outlines the header structure for a 0D page type:
| Offset | Size | Description |
|---|---|---|
| 0 | 1 Byte | Page Type |
| 1 | 2 Bytes | Byte offset to the the first freeblock |
| 3 | 2 Bytes | Number of cells on the page |
| 5 | 2 Bytes | Offset to the first byte of cell content |
| 7 | 1 Byte | Number of fragmented free bytes in cells |
Key Takeaway:
The value at offset 1 in the page header points to the first freeblock. If this value is 0x0000, there are no freeblocks on the page.
Using Hex Editors to Analyze Freeblocks
To locate the first freeblock, we open the database in a hex editor. The two-byte value at offset 1 of the page header provides the offset to the freeblock’s location.
In the example below:
- The hex editor highlights offset
0x03A9(decimal 937) in red, which points to the location of the first freeblock.
Navigating to this offset reveals the start of the freeblock. Below, we examine the cell located at offset 937.
Decoding Freeblock Headers
Each freeblock contains a four-byte header comprising:
- First 2 bytes: Offset to the next freeblock (
0x0000if none exists). - Second 2 bytes: Total size of the freeblock, including the header itself.
Example:
At offset 0x03A9, the freeblock header indicates:
- The next freeblock is at offset
0x03EA(decimal 1002). - The size of the freeblock is
0x0024(36 bytes, including the header).
Recovering Deleted Records Using Freeblocks
Freeblocks often contain remnants of deleted records, enabling forensic investigators to recover partial or complete data. In the screenshot below, the payload of a deleted record is intact and recoverable despite its pointer being removed.
Example:
- The deleted record at offset
0x03A9contains the message: “Not much. How are you?” - Key fields remain intact, including the message content, even though other metadata has been overwritten.
We analyze subsequent freeblocks to uncover additional deleted records. At offset 0x03EA, the freeblock header reveals:
- The freeblock size is
0x0016(22 bytes). - This is the final freeblock in the chain, as indicated by
0x0000in the next freeblock pointer field.
The below screenshot highlights the entire cell area of the second freeblock in the chain where the deleted message was, “Hi Andy!”
Identifying Additional Deleted Records
But there happens to be one more deleted record on this page not referenced by the freeblock chain. Looking back at the page header, there were only 2 records on this page according to the two-byte value at offset 3 (0x00002). And since there are only 2 records, there are only two pointers in the pointer array. Those two pointers point to record #2 – “Hey, what’s up?” and record #4 – “Hey, I was wondering if you have any Scooby snax.” You may notice that there is another long message located further up the page.
The two-byte value at offset 5 in the page header is the offset to the cell content area, essentially where the next record would be written, that is unless it can fit into one of the freeblocks. That value is 0x0363 which is decimal offset 867.
The arrow in the above screenshot is pointing to the page offset 867. This is the start of the cell content, meaning the next record will be added just above this record. The deleted record above this contains the message, “I happen to get a new supply in last night. You’re in luck. How much you need?”
The database considers this data as unallocated space. It might as well be filled with 0x00 as there is no pointer in the pointer array that addresses it.
If a record that is too large to fit within the two freeblocks on the page is added to the table this page belongs to, it will overwrite the message, which happens to be record #5.
Let’s see how it looks now that we’ve identified the two freeblocks along with the third deleted record that is waiting to be overwritten:
After analyzing the database in its raw format, we can conclude that it did contain more than just the 2 “live” records that the database viewer displayed.
Record #1 was likely the message, “Hi Andy!” while record #3 was likely the message, “Not much. How are you?”. Record #5 still has its record number within its cell identified as the message, “I happen to get a new supply in last night. You’re in luck. How much you need?”
Conclusion
Freeblocks are a powerful tool in forensic investigations, offering a window into deleted records and unallocated data. By mastering these techniques, investigators can uncover the digital traces that databases leave behind.
Ready to take your SQLite forensics skills to the next level? Explore our advanced training programs or contact us to learn how we can help you become a forensic expert.
