SQLite Truncated Database Recovery

This skill provides systematic approaches for recovering data from SQLite database files that have been corrupted through binary truncation. It emphasizes understanding the SQLite file format before attempting recovery and avoiding common pitfalls that lead to multiple failed iterations.

When to Use This Skill

This skill applies when:

A SQLite database file cannot be opened with standard
```
sqlite3
```
commands
The database error indicates corruption or malformed data
File size is smaller than expected (suggesting truncation)
Standard recovery tools like
```
.recover
```
command fail
Manual binary parsing of SQLite page structure is required

Initial Assessment Strategy

Before writing any recovery code, perform a thorough analysis of the corrupted file:

Step 1: Examine File Characteristics

bash

# Check file size and basic properties
ls -lh database.db
file database.db

# Create hex dump for analysis
hexdump -C database.db | head -100

Key observations to make:

File size: SQLite pages are typically 4096 bytes. Check if size aligns with page boundaries
Magic bytes: Valid SQLite files start with "SQLite format 3\000" (16 bytes)
First byte after header: Identifies page type (0x0d = table leaf page with actual data)

Step 2: Identify Corruption Pattern

Common truncation scenarios:

Header-only file: Only the 100-byte header remains
Missing header: File starts with a data page (first byte is 0x0d, 0x05, 0x0a, or 0x02)
Partial page: File ends mid-page, truncating some cells

If the file lacks the standard "SQLite format 3" magic header but starts with 0x0d, this indicates the file contains only a table leaf page without the database header.

Step 3: Try Standard Tools First

Always attempt standard recovery before manual parsing:

bash

# Check if sqlite3 can read the file
sqlite3 database.db ".schema" 2>&1
sqlite3 database.db "SELECT * FROM sqlite_master" 2>&1

# Try built-in recovery
sqlite3 database.db ".recover" > recovered.sql 2>&1

# Try integrity check
sqlite3 database.db "PRAGMA integrity_check;"

If these fail with "database disk image is malformed" or similar errors, proceed to manual binary parsing.

SQLite Page Structure Overview

Understanding the page structure is essential before writing recovery code.

Table Leaf Page Layout (Page Type 0x0d)

Offset  Size   Description
------  ----   -----------
0       1      Page type (0x0d for table leaf)
1       2      First freeblock offset (big-endian)
3       2      Number of cells on page (big-endian)
5       2      Cell content area start offset (big-endian)
7       1      Fragmented free bytes count
8+      varies Cell pointer array (2 bytes per cell, big-endian)
...            [Gap/free space]
End            Cell data (grows backward from page end)

Cell Structure

Each cell contains a database row:

[Payload size: varint]
[Row ID: varint]
[Header size: varint]
[Serial type 1: varint]
[Serial type 2: varint]
...
[Column 1 value]
[Column 2 value]
...

Varint Encoding

SQLite uses variable-length integers (varints):

Bytes 1-8: Use 7 bits for data, high bit (0x80) indicates continuation
Byte 9: Uses all 8 bits (no continuation)

Serial Types

Serial types indicate how to interpret column data:

Type	Size	Meaning
0	0	NULL
1	1	8-bit signed integer
2	2	16-bit big-endian signed integer
3	3	24-bit big-endian signed integer
4	4	32-bit big-endian signed integer
7	8	IEEE 754 64-bit float (big-endian)
8	0	Integer constant 0
9	0	Integer constant 1
N >= 12, even	(N-12)/2	BLOB
N >= 13, odd	(N-13)/2	Text string (UTF-8)

Example: Serial type 0x21 (33) = text string of length (33-13)/2 = 10 bytes.

Recovery Approach

Build a Single, Modular Script

Avoid creating multiple separate recovery scripts. Instead, build one script iteratively with clear debug output:

python

import struct
import json

DEBUG = True

def read_varint(data, offset):
    """Read SQLite variable-length integer."""
    value = 0
    for i in range(9):
        if offset + i >= len(data):
            return None, offset
        byte = data[offset + i]
        if i == 8:
            value = (value << 8) | byte
            return value, offset + i + 1
        value = (value << 7) | (byte & 0x7f)
        if (byte & 0x80) == 0:
            return value, offset + i + 1
    return value, offset

def decode_value(data, offset, serial_type):
    """Decode value based on serial type."""
    if serial_type == 0:
        return None, offset
    elif serial_type == 1:
        return struct.unpack('>b', data[offset:offset+1])[0], offset + 1
    elif serial_type == 2:
        return struct.unpack('>h', data[offset:offset+2])[0], offset + 2
    elif serial_type == 4:
        return struct.unpack('>i', data[offset:offset+4])[0], offset + 4
    elif serial_type == 7:
        return struct.unpack('>d', data[offset:offset+8])[0], offset + 8
    elif serial_type == 8:
        return 0, offset
    elif serial_type == 9:
        return 1, offset
    elif serial_type >= 12:
        if serial_type % 2 == 0:
            length = (serial_type - 12) // 2
            return data[offset:offset+length], offset + length
        else:
            length = (serial_type - 13) // 2
            return data[offset:offset+length].decode('utf-8', errors='replace'), offset + length
    return None, offset

Parse Incrementally with Debug Output

Parse one cell completely and verify before processing all cells:

python

def parse_cell(data, cell_offset, debug=DEBUG):
    """Parse a single cell with detailed debug output."""
    if debug:
        print(f"\nParsing cell at offset {cell_offset} (0x{cell_offset:04x})")

    # Read payload size
    payload_size, offset = read_varint(data, cell_offset)
    if debug:
        print(f"  Payload size: {payload_size}")

    # Read row ID
    row_id, offset = read_varint(data, offset)
    if debug:
        print(f"  Row ID: {row_id}")

    # Read header size
    header_size, header_start = read_varint(data, offset)
    if debug:
        print(f"  Header size: {header_size}")

    # Parse serial types
    serial_types = []
    current = header_start
    header_end = offset + header_size
    while current < header_end:
        st, current = read_varint(data, current)
        serial_types.append(st)

    if debug:
        print(f"  Serial types: {serial_types}")

    # Parse values
    values = []
    for st in serial_types:
        val, current = decode_value(data, current, st)
        values.append(val)

    if debug:
        print(f"  Values: {values}")

    return {'row_id': row_id, 'values': values}

Common Pitfalls and Prevention

Pitfall 1: Not Understanding the Corruption Pattern

Mistake: Assuming the file has a standard SQLite header when it may only contain a data page.

Prevention: Always examine the first few bytes with hexdump. If the file starts with 0x0d instead of "SQLite format 3", the header is missing. Adjust parsing offsets accordingly (no 100-byte header offset needed).

Pitfall 2: Multiple Script Iterations

Mistake: Creating many separate recovery scripts (recover1.py, recover2.py, etc.) based on trial and error.

Prevention:

Read the hex dump thoroughly first and annotate the structure manually
Build one script with debug flags
Reference the SQLite file format specification before coding

Pitfall 3: Reading Strings Beyond Their Boundaries

Mistake: Reading string data without checking the serial type length, resulting in incorrect strings (e.g., "testword052" instead of "testword05").

Prevention: Always calculate string length from serial type:

length = (serial_type - 13) // 2

. Read exactly that many bytes.

Pitfall 4: Syntax Errors in Generated Code

Mistake: Missing spaces in operators like

if48

instead of

if 48

, or

12and

instead of

12 and

Prevention: Validate syntax before running:

bash

python3 -m py_compile recovery_script.py

Pitfall 5: Wrong Byte Order

Mistake: Reading multi-byte integers with little-endian instead of big-endian.

Prevention: SQLite uses big-endian for all multi-byte integers. Always use

struct.unpack('>...', data)

with the

prefix.

Pitfall 6: Not Handling Truncation Gracefully

Mistake: Script crashes when encountering truncated data at end of file.

Prevention: Check bounds before every read operation:

python

def safe_read(data, offset, length):
    if offset + length > len(data):
        return None
    return data[offset:offset+length]

Verification Strategy

Step 1: Validate Cell Count

Compare the number of cells reported in the page header (offset 3-4) with actual cells found.

Step 2: Validate Data Patterns

If expected patterns are known (e.g., words matching "testwordXY"), verify extracted strings match the pattern.

Step 3: Check Value Ranges

Verify extracted numeric values are within expected ranges. Watch for:

Unexpected negative numbers (sign bit interpretation)
Very large numbers (byte order issues)
NaN or infinity for floats

Step 4: Compare with Expected Output Format

Before finalizing output, ensure JSON structure matches requirements:

python

# Validate output structure
for record in recovered_data:
    assert 'word' in record and 'value' in record
    assert isinstance(record['word'], str)
    assert isinstance(record['value'], (int, float))

Output Generation

Format recovered data according to the required output specification:

python

def generate_output(recovered_rows, output_path):
    """Format and save recovered data."""
    results = []
    for row in recovered_rows:
        if len(row['values']) >= 2:
            results.append({
                'word': row['values'][0],
                'value': row['values'][1]
            })

    with open(output_path, 'w') as f:
        json.dump(results, f, indent=2)

    print(f"Recovered {len(results)} records to {output_path}")
    return results

Summary Checklist

Before writing recovery code:

Examined file with hexdump to understand corruption extent
Identified whether header is present or missing
Tried standard SQLite tools first
Reviewed SQLite file format specification

During implementation:

Using a single script with debug output (not multiple scripts)
Validated Python syntax before running
Using big-endian byte order for all multi-byte integers
Calculating string lengths from serial types
Handling truncation with bounds checking

After recovery:

Verified cell count matches expectation
Validated string patterns if known
Checked numeric value ranges
Confirmed output format matches requirements

sqlite-db-truncate

NPX Install

Tags

SKILL.md Content

SQLite Truncated Database Recovery

When to Use This Skill

Initial Assessment Strategy

Step 1: Examine File Characteristics

Step 2: Identify Corruption Pattern

Step 3: Try Standard Tools First

SQLite Page Structure Overview

Table Leaf Page Layout (Page Type 0x0d)

Cell Structure

Varint Encoding

Serial Types

Recovery Approach

Build a Single, Modular Script

Parse Incrementally with Debug Output

Common Pitfalls and Prevention

Pitfall 1: Not Understanding the Corruption Pattern

Pitfall 2: Multiple Script Iterations

Pitfall 3: Reading Strings Beyond Their Boundaries

Pitfall 4: Syntax Errors in Generated Code

Pitfall 5: Wrong Byte Order

Pitfall 6: Not Handling Truncation Gracefully

Verification Strategy

Step 1: Validate Cell Count

Step 2: Validate Data Patterns

Step 3: Check Value Ranges

Step 4: Compare with Expected Output Format

Output Generation

Summary Checklist