Perform forensic analysis of SQLite databases to recover deleted records from freelists and WAL files, decode encoded timestamps, and extract evidence from browser history, messaging apps, and mobile device databases.
Perform forensic analysis of SQLite databases to recover deleted records from freelists and WAL files, decode encoded timestamps, and extract evidence from browser history, messaging apps, and mobile device databases.
SQLite is the most widely deployed database engine in the world, used by virtually every mobile application, web browser, and many desktop applications to store user data. In digital forensics, SQLite databases are critical evidence sources containing browser history, messaging records, call logs, GPS locations, application preferences, and cached content. Forensic analysis goes beyond simple SQL queries to examine the internal B-tree page structures, freelist pages containing deleted records, Write-Ahead Log (WAL) files preserving transaction history, and unallocated space within database pages where recoverable data may persist after deletion.
Prerequisites
DB Browser for SQLite (sqlitebrowser)
SQLite command-line tools (sqlite3)
Python 3.8+ with sqlite3 module
Belkasoft Evidence Center or Axiom (commercial)
Hex editor (HxD, 010 Editor) for manual page inspection
Understanding of B-tree data structures
SQLite Internal Structure
Database Header (First 100 Bytes)
Offset
Size
Description
0
16
Magic string: "SQLite format 3\000"
16
2
Page size (512-65536 bytes)
18
1
File format write version
19
1
File format read version
24
4
File change counter
28
4
Database size in pages
32
4
First freelist trunk page number
36
4
Total freelist pages
52
4
Text encoding (1=UTF-8, 2=UTF-16le, 3=UTF-16be)
96
4
Version-valid-for number
Page Types
Type
ID
Description
B-tree Interior
0x05
Internal table node
B-tree Leaf
0x0D
Table leaf page containing actual records
Index Interior
0x02
Internal index node
Index Leaf
0x0A
Index leaf page
Freelist Trunk
-
Tracks freed pages
Freelist Leaf
-
Freed page with recoverable data
Overflow
-
Continuation of large records
Deleted Record Recovery
Method 1: Freelist Page Analysis
When records are deleted, SQLite may place their pages on the freelist rather than overwriting them immediately.
import struct
import sqlite3
import os
defanalyze_freelist(db_path: str) -> dict:
"""Analyze SQLite freelist to identify pages containing deleted data."""withopen(db_path, "rb") as f:
# Read header
header = f.read(100)
page_size = struct.unpack(">H", header[16:18])[0]
if page_size == 1:
page_size = 65536
first_freelist_page = struct.unpack(">I", header[32:36])[0]
total_freelist_pages = struct.unpack(">I", header[36:40])[0]
freelist_info = {
"page_size": page_size,
"first_freelist_page": first_freelist_page,
"total_freelist_pages": total_freelist_pages,
"trunk_pages": [],
"leaf_pages": []
}
if first_freelist_page == 0:
return freelist_info
# Walk the freelist trunk chain
trunk_page = first_freelist_page
while trunk_page != 0:
offset = (trunk_page - 1) * page_size
f.seek(offset)
page_data = f.read(page_size)
next_trunk = struct.unpack(">I", page_data[0:4])[0]
leaf_count = struct.unpack(">I", page_data[4:8])[0]
leaves = []
for i inrange(leaf_count):
leaf_page = struct.unpack(">I", page_data[8 + i * 4:12 + i * 4])[0]
leaves.append(leaf_page)
freelist_info["trunk_pages"].append({
"page_number": trunk_page,
"next_trunk": next_trunk,
"leaf_count": leaf_count,
"leaf_pages": leaves
})
freelist_info["leaf_pages"].extend(leaves)
trunk_page = next_trunk
return freelist_info
defextract_freelist_content(db_path: str, output_dir: str):
"""Extract raw content from freelist pages for analysis."""
info = analyze_freelist(db_path)
os.makedirs(output_dir, exist_ok=True)
withopen(db_path, "rb") as f:
page_size = info["page_size"]
for page_num in info["leaf_pages"]:
offset = (page_num - 1) * page_size
f.seek(offset)
page_data = f.read(page_size)
output_file = os.path.join(output_dir, f"freelist_page_{page_num}.bin")
withopen(output_file, "wb") as out:
out.write(page_data)
returnlen(info["leaf_pages"])
Method 2: WAL (Write-Ahead Log) Analysis
The WAL file contains pending transactions that have not yet been checkpointed back to the main database.
