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

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:

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: 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: 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:

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: 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:

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: 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