Deleting a Thing

Flag

H7CTF{7adf9695fb655c752810}

Evidence

messages.db schema:

CREATE TABLE messages(_id INTEGER PRIMARY KEY, thread TEXT, sender TEXT, ts INTEGER, body TEXT);

Step 1 — Read the live table

$ sqlite3 messages.db "SELECT * FROM messages;"
1  general  alice   1710001000000  lunch at 1?
2  general  bob     1710001100000  sure, the usual spot
3  general  alice   1710001200000  cool, see you
5  ops      mallory 1710002050000  burn this after you read it
6  general  bob     1710002100000  anyone seen the build?
7  general  carol   1710002200000  ci is green now

Nothing incriminating — the suspect's story holds. But notice the rowid gap: _id 4 is missing while 1,2,3,5,6,7 are present. Message 4 was deleted, not never-existed.

PRAGMA freelist_count returns 0, so nothing sits on the freelist — the content is still in the leaf page itself.

Step 2 — Find the ghost in the page

$ strings -a messages.db | grep -E '[0-9a-f]{40,}'
26073560251303150a564f025a5d521658054357545d064c5f000b

A 54-character hex blob in page 3 that no live row references. The leaf page 3 (0x2000) still holds it inside its unallocated gap, wedged between the cell for rowid 5 and the cell for rowid 3:

$ xxd -s 0x2F3B -l 80 messages.db
00002f3b: 0000 4e13 1b05 796f 7073 6d61 6c6c 6f72 7901  ..N...yopsmallory.
00002f4b: 8e24 0fd0 8032 3630 3733 3536 3032 3531 332e  .$..2607356025133.
00002f5b: 3031 3530 6135 3634 6630 3235 6135 6435 3231  0150a564f025a5d52
00002f6b: 3136 3538 3035 3433 3537 3534 3564 064c 5f00 0b...1658054357545d..
00002f7b: 2503 0600 1b17 0527 6765 6e65 7261 6c61 6c69  %.....generalalli
00002f8b: 6365 018e 2403 9b80 636f 6f6c 2c20 7365 6520 79  c.$....cool, see y

Hand-parsing it as a SQLite table-leaf cell (layout [payload size][rowid][record header][data]):

Serial types decode consistently: 0x13 → 3-byte text (ops), 0x1b → 7-byte text (mallory), 0x05 → 6-byte integer (ts), 0x79 → 54-byte text (the body). The rowid is the one field that was overwritten, but the _id gap in the live table places it at row 4, thread ops, from mallory.

Step 3 — Deobfuscate

secure.xml gives the scheme:

<string name="obfuscation_key">n0v4ch4t</string>
<string name="stored_body_encoding">hex(xor(body, obfuscation_key))</string>

So the body is hex(XOR(plaintext, "n0v4ch4t")) with a repeating 8-byte key — reverse it: unhex then XOR with the same repeating key.

key  = b'n0v4ch4t'
blob = '26073560251303150a564f025a5d521658054357545d064c5f000b'
print(bytes(b ^ key[i % len(key)] for i, b in enumerate(bytes.fromhex(blob))).decode())

H7CTF{7adf9695fb655c752810}

Verdict

Mallory sent the message in the ops thread at 1710002000000, then "burn this after you read it" (_id 5) and deleted _id 4. The DELETE only unhooked the row from the b-tree — the plaintext(ish) record, sender, thread and timestamp stayed verbatim in the unallocated space of leaf page 3, with the flag XOR-masked under a key that shipped in the same extraction. The claim of "nothing ugly" dies at the _id gap.