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. Use when performing forensic analysis of sqlite databases to recover deleted records.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
performing-sqlite-database-forensics
description
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. Use when performing forensic analysis of sqlite databases to recover deleted records.
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.
When to Use
Trigger phrases:
"performing sqlite database forensics"
"Perform forensic analysis of SQLite databases to recover deleted records from fr"
When conducting security assessments that involve performing sqlite database forensics
When following incident response procedures for related security events
When performing scheduled security testing or auditing activities
When validating security controls through hands-on testing
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
This section covers sqlite internal structure for performing sqlite database forensics.
Ensure all prerequisites are met before proceeding
Follow the documented workflow steps in sequence
Record results and any anomalies encountered during this phase
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
More from this repository
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
This section covers deleted record recovery for performing sqlite database forensics.
Ensure all prerequisites are met before proceeding
Follow the documented workflow steps in sequence
Record results and any anomalies encountered during this phase
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.
