Reverse Engineering an Undocumented Binary Format to SQL
I recently had to get data out of an old piece of software from 2020 whose files looked unreadable: a few hundred binary files, no documentation, and I wanted to query them with SQL. This post is about the method, how to find the containers, the record layouts and the links between files from the data alone, and how to check you’re not fooling yourself along the way.
It’s not encrypted, it’s raw Deflate#
The payloads look random, entropy close to 8 bits per byte, so the first instinct is “encrypted”. A search for a zlib header (78 9c, 78 da) finds nothing. But compressed data looks random too, and a lot of software uses Deflate without the zlib header. So let’s try a raw Deflate decode (wbits=-15) at every offset and keep what decodes to text:
import sys, zlib
data = open(sys.argv[1], 'rb').read()
for off in range(len(data)):
try:
out = zlib.decompressobj(-15).decompress(data[off:off + 65536])
except zlib.error:
continue
if len(out) > 8 and sum(32 <= c < 127 or c == 0 for c in out) / len(out) > 0.9:
print(off, out[:40])
On names.bdf this prints hits at offsets 68, 86, 90, 107 and 122. Two of them are false positives: 90 and 107 decode the tail of the stream that starts at 86, Deflate happily resyncs in the middle of a stream sometimes. Keep the hit that decodes the most and look at what sits right before it:
00000000: 0000 0000 0000 0000 0000 0000 5200 0000 ............R...
00000010: 0000 0000 0000 0000 0000 0000 7600 0000 ............v...
00000020: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00000030: 4000 0000 0000 0000 0000 0000 0000 0000 @...............
00000040: 0e00 0c00 732a 4d49 4f2d 51c8 c9cc 4b65 ....s*MIO-Q...Ke
00000050: 0000 2000 1e00 0b2e 49cc 4b49 2c4a 6108 .. .....I.KI,Ja.
At 0x40 there’s 0e00 0c00 then the stream at 68: u16 14 then u16 12, the compressed and the decoded length. 64 + 4 + 14 = 82 = 0x52, where the next [u16 comp][u16 dec] header starts. Blocks chain to the end of file with no gap:
offset 0 16 x u32le slot table (absolute block offsets, 0 = empty)
offset 64 [u16 comp_len][u16 dec_len][raw deflate ...]
[u16 comp_len][u16 dec_len][raw deflate ...]
... until EOF, no gap
And the header? 0x52, 0x76 and 0x40 are exactly the three block offsets. It’s a sparse slot table: slot 3 → 82, slot 7 → 118, slot 12 → 64. Note that slot order is not chain order, the block of slot 12 comes first in the file. That’s the kind of detail that makes a header look like garbage until you compare it with the offsets you already know.
The real files used other widths (u24 lengths, a much bigger slot table) but the shape was the same. The slot count showed up again and again in other files, and it turned out to be an id space: the slot is the brand id, a part points to its description with a byte offset inside the inflated block of its brand. A number that repeats across files is almost always a key.
Not one format, a family of them#
Classifying the files by shape (size, first bytes, does a Deflate chain parse to EOF) gives several families. Start with the smallest files, you can read them entirely and they’re often the Rosetta stone for the big ones.
1. Ids defined by position#
The parts live in parts.bdf, one self-delimiting record per part. The offsets live in another file, parts.idx, which is just a u32 list:
00000000: 1000 0000 2400 0000 3600 0000 0000 0000 ....$...6.......
00000010: 0000 0000 4800 0000 5a00 0000 ....H...Z...
Entry i is the offset of part i in parts.bdf, or 0 for an unused id. The part id is not stored anywhere, it is the position. Non-zero values are strictly increasing, and a record ends where the next non-zero entry starts. On the real data these holes came in runs, reserved id ranges at the start of each brand.
2. Bitfields and flags#
The record at 0x10:
00000010: 0300 3f00 0200 0000 0b73 f6d0 3534 3430 ..?......s..5440
00000020: 6060 0000 03 ``...
+0 u16 brand id 3
+2 u16 flags 0x003f: has_ean b0, has_links b1, compressed b2,
number length b3-7 (7)
+4 u32 links offset (0 = none) 2
+8 u8 data length 11
+9 raw deflate (data length bytes) "CH-1100" + u16 description offset
9 + 11 = 20 bytes, 0x10 + 20 = 0x24, the second entry of parts.idx. Every record must land exactly on the next offset, that’s how you know the head is right. In the real file the head was 17 bytes of packed bitfields, with one byte I never understood, and it’s documented as unknown rather than guessed.
To find a bitfield, take a field whose meaning you know from elsewhere (a length, a count) and slide it over the bits until it matches on every record. The flags fall out the same way: a bit that is set exactly on the records that have an extra block is the flag for that block.
3. Detail lists by counting#
ean.bdf holds the barcodes, with no part id at all:
00000000: 0111 fc34 b8a4 0300 0002 18fc 34b8 a403 ...4........4...
u8 n then n x u64. The link to parts is by counting: the k-th record belongs to the k-th part (ascending id) that has has_ean set. 3 records in the file, 3 parts with the flag. On the real data this rule held on every section, with record counts equal to flag counts on millions of rows, that’s a strong signal. Later, pointers found inside the part body confirmed it.
4. Plausibility checks#
bikes.bdf is a u16 count then 12-byte records: u32 bike_id, a u32 bitfield and 4 bytes I can’t explain. The first record has the bitfield 0x028d0a91:
bf = 0x028d0a91
year_from = (bf & 0x7f) + 1990 # 2007
year_to = (bf >> 7 & 0x7f) + 1990 # 2011
frame_cm = bf >> 14 & 0x7f # 52
frame_in = bf >> 21 & 0x3f # 20
The base year is not in the file, you find it by checking a record you know the answer of. And redundant fields are a gift: round(frame_cm / 2.54) == frame_in on every record means both fields are cut at the right bits. On the real data the same kind of check (two units of the same quantity) held on 99.95% of the rows, the rest being typos in the source.
5. Pages of slots of leaves#
The big linkage files had a nested layout: a compressed page that decodes to a slot table, then one compressed leaf per non-empty slot, then the next page, the walk consuming the whole file. Leaves were TLV streams (u16 type, u16 n, u8 flag, value) where the value type comes from a separate criteria table, not from the bytes. One type packed its length across n and a flag bit, found only because one leaf deep in the file wouldn’t end where it should. Walk everything, not a sample.
6. Language pages with holes#
Translated texts came as one compressed page per language, each page a run of fixed-width NUL-padded slots, an index giving slot starts in the reference language. Some languages had shorter pages: untranslated entries were simply omitted, nothing marked them. I aligned them as a subsequence (equal slot widths, texts identical to the reference as anchors) and checked it by similarity against a close language. It’s a heuristic, and it’s flagged as such in the output.
7. A B-tree index as a checksum#
A few files were B-tree indexes, prefix-compressed keys (u8 shared chars, the new chars, a pointer). Everything in them was derivable from the other files, which makes them the best checksum you can get: the keys of the index must equal the pairs you decoded elsewhere, 0 missing, 0 extra.
A second codec#
Deflate wasn’t the only codec. Some blocks start with 0xff and don’t inflate. Skip the marker and what follows is a variant of Okumura’s LZARI (LZSS with arithmetic coding, from the 80s), reimplementing it in Python decoded those blocks to exactly their announced length.
The file that looked encrypted#
One file resisted for a long time. It had a clean index (an entry per brand, offsets into a details region, a descriptions region and a logos region) but every payload was close to 8 bits of entropy per byte. Raw Deflate at the offsets given by the index failed, LZARI failed, so it got filed as “encrypted”. The agent then spent a good while on cipher hunting: XOR with every constant it could find, stream ciphers, chained byte schemes, all at chance level.
What cracked it was a much dumber idea I threw at it: take a solid sample of the file, remove all the bitmaps (the index tells us where each logo starts and ends), and look at what we’re left with. Without the logos in the way, what’s left is a few small blobs of text-sized lengths. And on those, raw Deflate at small start offsets (0 to 11) immediately printed street addresses and e-mail domains. Nothing was encrypted, the payloads just had a slightly different framing than everywhere else: [u16 comp_len][u32 dec_len] before the stream, a bare u24 decoded length before the logos. The whole file then decoded, names, contact details per country and 24-bit BMP logos, on every brand but one broken logo.
In hindsight the hints were there: blob lengths were all even and not multiples of 8 or 16 (not what a block cipher gives you), and the logos were not uniformly random at all, their first bytes repeated a lot. The lesson I keep from it: high entropy is not encryption. Before trying a single key, strip what you already understand, and retry the codecs you already know on what’s left, at a few small offsets.
Two files are still BLOBs: high entropy, lengths always a multiple of 16, no repeated block at any alignment. After the previous story I’m careful with the word, they’re undecoded, not proven encrypted, and I stopped there.
How I verified things#
Reverse engineering a format without a spec is mostly about not fooling yourself. A layout is only “verified” when one of these holds, and the notes say which one:
- Chains to EOF. A container walk must consume the file exactly, no residue, and every block must inflate to its announced length.
- Counts match across files. Record counts equal flag counts, sums of per-row counts equal the row count of the target file, index entries equal records.
- Cross-file joins resolve. Every code resolves in its key table, every id points at an existing row.
- Plausibility. Redundant fields agree,
year_to >= year_from, a record you know the answer of decodes to that answer.
Then a PRAGMA foreign_key_check on the final database.
Note that this proves structural consistency, not meaning. Counts that match and foreign keys that resolve tell me the bytes are cut at the right places and point at the right rows. They don’t tell me that a flag I named is_old actually means “old”, or that a heuristically aligned translation is the right one. That’s why the schema carries a provenance marker on every column, [V] when the meaning was checked against something independent, [U] when it’s only a name.
From bytes to tables#
The first pass converts each file to SQL with column names close to the file layout. A second pass loads them into SQLite and builds a normalized schema on top. For example:
CREATE TABLE part (
part_id INTEGER PRIMARY KEY, -- [V] position in parts.idx
brand_id INTEGER NOT NULL REFERENCES brand(brand_id), -- [V] +0
part_number TEXT NOT NULL, -- [V] first bytes of the inflated record
flag_b8 INTEGER NOT NULL -- [U] flags bit 8, meaning unknown
);
CREATE TABLE bike (
bike_id INTEGER PRIMARY KEY,
year_from INTEGER, year_to INTEGER, -- [V] 7-bit, 1990 + raw
frame_cm INTEGER, -- [V] matches frame_in on every row
row_idx INTEGER NOT NULL UNIQUE -- [V] row in bikes.bdf, what link lists contain
);
CREATE TABLE part_bike (
part_id INTEGER NOT NULL REFERENCES part(part_id),
bike_id INTEGER NOT NULL REFERENCES bike(bike_id),
PRIMARY KEY (part_id, bike_id)
);
row_idx is why the second pass exists: the link lists in links.bdf store row numbers of bikes.bdf, not bike ids. The build resolves them once and the final table only holds real keys. Then “which bikes does this part fit” is a plain join:
SELECT b.bike_id, b.year_from, b.year_to, b.frame_cm
FROM part p
JOIN part_bike l ON l.part_id = p.part_id
JOIN bike b ON b.bike_id = l.bike_id
WHERE p.part_number = 'CH-1100';
On the real data, a few gigabytes of binary files became a large SQLite database, a hundred tables, millions of parts and hundreds of millions of links. What’s left unknown (two files, a handful of flags) is listed in a “known gaps” section instead of being guessed.
How it was run#
This was done over a few sessions with Claude Sonnet 5 agents, each one with a brief, strict rules and a set of notes files as shared memory: one notes file per family of files, with only verified facts and file:offset evidence. The notes are what made it work, a new session picks up where the last one stopped and the “UNVERIFIED” markers stop a guess from becoming a fact. I reviewed the claims, asked for the cross checks, and killed a few wrong turns (an early note described a “second-level region” in an offset file, it was just the first entries of the same list).
Two practical lessons:
- Run Python with
python3 -Iwhen it reads files you didn’t write: isolated mode ignores thePYTHON*environment variables and doesn’t put the script directory or the current directory on the import path, so a strayzlib.pynext to the data never gets imported. It doesn’t make parsing hostile bytes safe, and it doesn’t limit memory or CPU. - Run the heavy jobs as a single
niceprocess: a parallel run took down this very website.
If you have an old binary format lying around, start with the smallest files, look for the numbers that repeat, and never trust a layout that doesn’t land exactly on EOF!