mcpField
P
performing-sqlite-database-forensics

performing-sqlite-database-forensics

github.com/mukul975安全35.2k100 次安装8 周前更新
代码仓库

关于 performing-sqlite-database-forensics

用于检查浏览器、消息应用等所用 SQLite 数据库的数字取证技能。除 SQL 查询外，还涉及 B-tree 页面、空闲列表、WAL 文件和未分配空间，以查找可能残留的已删除记录。

主要功能

  1. 01检查 SQLite B-tree 页面结构
  2. 02分析空闲列表中的已删除数据
  3. 03检查 WAL 事务文件
  4. 04搜索页面未分配空间
  5. 05解码应用时间戳

适用场景

  1. 01恢复已删除记录的残留数据
  2. 02检查浏览器和消息数据库
  3. 03将 WAL 数据与数据库内容关联

安装

复制下面的命令，在终端中运行。

$npx skills add https://github.com/mukul975/anthropic-cybersecurity-skills --skill performing-sqlite-database-forensics

安装后在 Claude Code 中输入/performing-sqlite-database-forensics确认已加载。

包含的文件

除 SKILL.md 外，安装时一起下载的参考资料和脚本。

LICENSE
assets/
template.md
references/
api-reference.md
standards.md
workflows.md
scripts/
agent.py
process.py

详细介绍

执行 SQLite 数据库取证

概述

SQLite 是世界上部署最广泛的数据库引擎，几乎所有移动应用、Web 浏览器以及许多桌面应用都用它来存储用户数据。在数字取证中，SQLite 数据库是关键证据来源，包含浏览器历史记录、消息记录、通话记录、GPS 位置、应用偏好设置和缓存内容。取证分析不止于简单的 SQL 查询，还要检查内部 B 树页面结构、包含已删除记录的空闲列表页、保留事务历史的预写日志（WAL）文件，以及数据库页面中未分配的空间——删除后可恢复的数据可能仍留存其中。

使用场景

  • 在开展涉及执行 SQLite 数据库取证的 security 评估时
  • 在针对相关安全事件遵循事件响应流程时
  • 在执行定期安全测试或审计活动时
  • 在通过动手测试验证安全控制措施时

前置条件

  • DB Browser for SQLite（sqlitebrowser）
  • SQLite 命令行工具（sqlite3）
  • Python 3.8+ 及 sqlite3 模块
  • Belkasoft Evidence Center 或 Axiom（商业软件）
  • 十六进制编辑器（HxD、010 Editor）用于手动检查页面
  • 理解 B 树数据结构

SQLite 内部结构

数据库头部（前 100 字节）
偏移大小描述
016魔数字符串："SQLite format 3\000"
162页面大小（512-65536 字节）
181文件格式写入版本
191文件格式读取版本
244文件更改计数器
284数据库大小（以页为单位）
324首个空闲列表主干页编号
364空闲列表总页数
524文本编码（1=UTF-8，2=UTF-16le，3=UTF-16be）
964版本有效期编号
页面类型
类型ID描述
B 树内部节点0x05内部表节点
B 树叶子节点0x0D包含实际记录的表叶子页
索引内部节点0x02内部索引节点
索引叶子节点0x0A索引叶子页
空闲列表主干页-跟踪已释放的页面
空闲列表叶子页-包含可恢复数据的已释放页面
溢出页-大型记录的延续部分

已删除记录恢复

方法 1：空闲列表页分析

当记录被删除时，SQLite 可能会将其页面放入空闲列表，而不是立即覆盖它们。

import struct
import sqlite3
import os


def analyze_freelist(db_path: str) -> dict:
    """Analyze SQLite freelist to identify pages containing deleted data."""
    with open(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 in range(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


def extract_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)

    with open(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")
            with open(output_file, "wb") as out:
                out.write(page_data)

    return len(info["leaf_pages"])
方法 2：WAL（预写日志）分析

WAL 文件包含尚未检查点写回主数据库的待处理事务。

def parse_wal_header(wal_path: str) -> dict:
    """Parse SQLite WAL file header and frame inventory."""
    with open(wal_path, "rb") as f:
        header = f.read(32)
        magic = struct.unpack(">I", header[0:4])[0]
        file_format = struct.unpack(">I", header[4:8])[0]
        page_size = struct.unpack(">I", header[8:12])[0]
        checkpoint_seq = struct.unpack(">I", header[12:16])[0]
        salt1 = struct.unpack(">I", header[16:20])[0]
        salt2 = struct.unpack(">I", header[20:24])[0]

        wal_info = {
            "magic": hex(magic),
            "format": file_format,
            "page_size": page_size,
            "checkpoint_sequence": checkpoint_seq,
            "frames": []
        }

        # Parse frames (24-byte header + page_size data each)
        frame_offset = 32
        frame_num = 0
        file_size = os.path.getsize(wal_path)

        while frame_offset + 24 + page_size <= file_size:
            f.seek(frame_offset)
            frame_header = f.read(24)
            page_number = struct.unpack(">I", frame_header[0:4])[0]
            db_size_after = struct.unpack(">I", frame_header[4:8])[0]

            wal_info["frames"].append({
                "frame_number": frame_num,
                "page_number": page_number,
                "db_size_pages": db_size_after,
                "offset": frame_offset
            })
            frame_offset += 24 + page_size
            frame_num += 1

    return wal_info
方法 3：页面内未分配空间

活动 B 树页面中已删除的单元格会将数据留在单元格指针数组与单元格内容区之间的未分配区域中。

def analyze_unallocated_space(db_path: str, page_number: int) -> dict:
    """Analyze unallocated space within a specific B-tree page."""
    with open(db_path, "rb") as f:
        header = f.read(100)
        page_size = struct.unpack(">H", header[16:18])[0]
        if page_size == 1:
            page_size = 65536

        offset = (page_number - 1) * page_size
        f.seek(offset)
        page_data = f.read(page_size)

        # Parse page header (8 or 12 bytes depending on type)
        page_type = page_data[0]
        first_freeblock = struct.unpack(">H", page_data[1:3])[0]
        cell_count = struct.unpack(">H", page_data[3:5])[0]
        cell_content_offset = struct.unpack(">H", page_data[5:7])[0]
        if cell_content_offset == 0:
            cell_content_offset = 65536

        header_size = 12 if page_type in (0x02, 0x05) else 8
        cell_pointer_end = header_size + cell_count * 2

        unallocated_start = cell_pointer_end
        unallocated_end = cell_content_offset
        unallocated_size = unallocated_end - unallocated_start

        return {
            "page_number": page_number,
            "page_type": hex(page_type),
            "cell_count": cell_count,
            "unallocated_start": unallocated_start,
            "unallocated_end": unallocated_end,
            "unallocated_size": unallocated_size,
            "unallocated_data": page_data[unallocated_start:unallocated_end].hex()
        }

常见取证数据库

应用程序数据库文件关键表
ChromeHistoryurls、visits、downloads、keyword_search_terms
Firefoxplaces.sqlitemoz_places、moz_historyvisits
SafariHistory.dbhistory_items、history_visits
WhatsAppmsgstore.dbmessages、chat_list
Signalsignal.sqlitesms、mms
iMessagesms.dbmessage、handle、chat
Android SMSmmssms.dbsms、mms、threads
Skypemain.dbMessages、Conversations

时间戳解码

from datetime import datetime, timedelta

def decode_chrome_timestamp(chrome_ts: int) -> datetime:
    """Convert Chrome/WebKit timestamp to datetime (microseconds since 1601-01-01)."""
    epoch_delta = 11644473600
    return datetime.utcfromtimestamp((chrome_ts / 1000000) - epoch_delta)

def decode_unix_timestamp(unix_ts: int) -> datetime:
    """Convert Unix timestamp to datetime."""
    return datetime.utcfromtimestamp(unix_ts)

def decode_mac_absolute_time(mac_ts: float) -> datetime:
    """Convert Mac Absolute Time (seconds since 2001-01-01)."""
    mac_epoch = datetime(2001, 1, 1)
    return mac_epoch + timedelta(seconds=mac_ts)

def decode_mozilla_timestamp(moz_ts: int) -> datetime:
    """Convert Mozilla PRTime (microseconds since Unix epoch)."""
    return datetime.utcfromtimestamp(moz_ts / 1000000)

参考资料

示例输出

$ python3 sqlite_forensics.py --db /evidence/chrome/Default/History \
    --wal /evidence/chrome/Default/History-wal \
    --journal /evidence/chrome/Default/History-journal \
    --output /analysis/sqlite_report

SQLite Database Forensic Analyzer v2.0
========================================
Database:    /evidence/chrome/Default/History
Size:        48.2 MB
SQLite Ver:  3.39.5
Page Size:   4096 bytes
Total Pages: 12,345
Encoding:    UTF-8

[+] Analyzing WAL (Write-Ahead Log)...
    WAL file:       History-wal (2.1 MB)
    WAL frames:     512
    Checkpointed:   No (contains uncommitted data)
    Recoverable rows from WAL: 234

[+] Analyzing journal file...
    Journal file:   History-journal (0 bytes - rolled back)

[+] Scanning for deleted records (freelist pages)...
    Freelist pages:     456
    Deleted records recovered: 1,892

[+] Analyzing table: urls
    Active rows:     12,456
    Deleted rows:    1,234 (recovered from freelist)
    WAL-only rows:   89

--- Recovered Deleted URLs (Last 10) ---
Row ID | URL                                              | Title                    | Visit Count | Last Visit (UTC)
-------|--------------------------------------------------|--------------------------|-------------|---------------------
89234  | https://mega.nz/folder/xYz123#key=AbCdEf        | MEGA                     | 5           | 2024-01-16 03:20:00
89235  | https://transfer.sh/abc123/data.7z               | transfer.sh              | 1           | 2024-01-16 03:25:00
89240  | https://temp-mail.org/en/                        | Temp Mail                | 3           | 2024-01-15 13:00:00
89241  | https://browserleaks.com/ip                      | IP Leak Test             | 1           | 2024-01-15 12:55:00
89245  | https://www.virustotal.com/gui/file/a1b2c3...    | VirusTotal               | 2           | 2024-01-15 14:30:00
89250  | https://github.com/gentilkiwi/mimikatz/releases  | Mimikatz Releases        | 1           | 2024-01-15 16:00:00
89260  | https://raw.githubusercontent.com/.../payload.ps1| GitHub Raw               | 1           | 2024-01-15 14:34:00
89270  | https://pastebin.com/edit/kL9mN2pQ               | Pastebin - Edit          | 2           | 2024-01-15 14:42:00
89280  | https://duckduckgo.com/?q=clear+browser+history  | DuckDuckGo               | 1           | 2024-01-17 22:00:00
89285  | https://duckduckgo.com/?q=anti+forensics+tools   | DuckDuckGo               | 1           | 2024-01-17 22:05:00

[+] Analyzing table: downloads
    Active rows:     234
    Deleted rows:    12 (recovered)

--- Recovered Deleted Downloads ---
Row ID | Filename               | URL                                    | Size      | Start Time (UTC)
-------|------------------------|----------------------------------------|-----------|---------------------
5012   | payload.ps1            | https://raw.githubusercontent.com/...  | 4,096     | 2024-01-15 14:34:00
5015   | mimikatz_trunk.zip     | https://github.com/.../releases/...    | 1,892,352 | 2024-01-15 16:00:00
5018   | netscan_portable.zip   | https://www.softperfect.com/...        | 5,242,880 | 2024-01-15 15:05:00

[+] Slack space analysis...
    Pages with slack space data: 234
    Partial strings recovered:   67 fragments

Summary:
  Total records analyzed:  14,578 (active) + 3,126 (deleted/WAL)
  Evidence-relevant URLs:  23 (flagged)
  Deleted downloads:       12 (3 tool-related)
  Anti-forensics evidence: Browser history deletion detected
  Report: /analysis/sqlite_report/sqlite_forensics.json
  Recovered DB: /analysis/sqlite_report/History_recovered.db

评论

支持 Markdown