In Search of the Lost Note (ROCSC Quals 2026) — SQLite WAL Forensics

1. What you get

Two files: app.apk and case.zip — a backup of the app's data directory.

Unzipping case.zip gives:

case/
  shared_prefs/security.xml   ← crypto parameters
  databases/notes.db          ← SQLite database
  databases/notes.db-shm      ← shared memory
  databases/notes.db-wal      ← write-ahead log

The target is a note that doesn't appear through normal SQL queries — it was written to the WAL but never committed.

2. The SQLite pitfall

First instinct: open notes.db with Python and run a SELECT.

import sqlite3
con = sqlite3.connect("case/databases/notes.db")
cur = con.cursor()
cur.execute("SELECT id, created_at FROM notes ORDER BY id DESC LIMIT 5")
print(cur.fetchall())

I got a few dummy notes — no flag. That's by design.

SQLite's WAL mode works by appending new page images to the WAL file instead of writing them directly to the database. A commit boundary is a WAL frame where db_size != 0. Any frames after the last commit boundary are uncommitted — SQLite's normal read path ignores them entirely. The flag note is sitting in one of those uncommitted frames. Opening the DB normally also risks triggering a checkpoint, which could destroy the WAL. So I parsed the WAL directly as raw bytes.

3. Extracting crypto params from shared_prefs

shared_prefs/security.xml had everything needed for key derivation:

<string name="salt_b64">base64-encoded-salt</string>
<int name="pbkdf2_iter" value="100000" />
<int name="dk_len" value="32" />

The key is derived with PBKDF2-HMAC-SHA256, but not just from the PIN. The password is "{PIN}:{pepper}", where the pepper is buried in native code. So I had to dig into the .so next.

4. Reversing the pepper out of libnative.so

I extracted the APK and pulled the native library:

unzip -q app.apk -d _apk
# libnative.so lives under lib/<arch>/

A quick strings pass didn't reveal the pepper in plain text — expected. I opened it in Ghidra and looked for strings containing a :: separator. The pepper follows a scheme like v2::rocsc-… and it was sitting there once I knew what to search for.

With the pepper in hand, key derivation was straightforward:

import hashlib, base64

salt = base64.b64decode(salt_b64)
password = f"{PIN}:{pepper}".encode()
key = hashlib.pbkdf2_hmac("sha256", password, salt, iters, dklen)

5. Parsing the WAL manually

The WAL format is straightforward once you look it up: a 32-byte header, then a sequence of frames. Each frame is a 24-byte header followed by a full page image (typically 4096 bytes).

I split frames into committed (at or before the last frame with db_size != 0) and uncommitted (everything after), then built an overlay map: page_number → page_bytes, with uncommitted pages layered on top.

WAL header: 32 bytes
Frame header: 24 bytes
  pgno    (4 bytes) — which database page this image replaces
  db_size (4 bytes) — non-zero marks a commit boundary
  ...
Frame body: page_size bytes (usually 4096)

6. B-tree traversal to find the note

SQLite stores all tables as B-trees. To read the notes table from my custom overlay, I needed to:

1. Parse page 1 (sqlite_master) to find the notes table's root page number.
2. Traverse the B-tree from that root, using the overlay instead of the raw DB file.

The two page types that matter:

• 0x05 — interior table page (child page pointers)
• 0x0D — leaf table page (actual records)

For each leaf cell I read the payload size and row ID as SQLite varints, then parsed the record. Records that only appeared in the uncommitted overlay were the new, "invisible" ones — which is exactly where the flag note was hiding.

7. AES-GCM + MessagePack decode

Each note's payload is a blob structured as ciphertext || tag (16 bytes). The nonce is derived deterministically from the note's created_at timestamp (milliseconds) — this is the challenge-specific scheme reversed from libnative.so: the timestamp is packed as a little-endian 8-byte integer, zero-padded to 16 bytes, then SHA256-hashed with a fixed prefix and truncated to 12 bytes for AES-GCM:

import hashlib

def nonce_from_ts(ts: int) -> bytes:
    b = ts.to_bytes(8, "little").ljust(16, b"\x00")
    return hashlib.sha256(b"no" + b).digest()[:12]

Then decrypt with AES-GCM and unpack the result with MessagePack:

from Crypto.Cipher import AES
import msgpack

cipher = AES.new(key, AES.MODE_GCM, nonce=nonce)
plaintext = cipher.decrypt_and_verify(ct[:-16], ct[-16:])
obj = msgpack.unpackb(plaintext, raw=False)
print(obj["body"])  # → ROCSC{...}

8. Putting it all together

The full solve chain:

1. Parse security.xml → get salt, iters, dklen.
2. Extract libnative.so from APK → find the pepper string in Ghidra.
3. Derive the AES key with PBKDF2 from PIN:pepper.
4. Read DB and WAL as raw bytes — don't touch sqlite3 again.
5. Parse WAL frames → split into committed / uncommitted page maps.
6. Use the full overlay to find the notes root page via sqlite_master.
7. Traverse the B-tree; collect records only present in uncommitted pages.
8. For each new record: derive nonce from created_at, AES-GCM decrypt, msgpack decode.
9. The body of the decrypted note contains ROCSC{...}.

Most of the time went into understanding the WAL format and carefully rebuilding the page overlay without corrupting anything. The AES-GCM + MessagePack step at the end followed naturally once the overlay was working.

9. Complete solve script

Self-contained. Extracts the pepper from the APK automatically (needs unzip and strings in $PATH), then does the full WAL traversal and decryption. Pass the PIN as an environment variable.

pip install pycryptodome msgpack

#!/usr/bin/env python3
"""
ROCSC SecNotes — Solve Script
Usage:
    unzip -q case.zip -d case
    PIN=12345678 python3 solve.py

Environment:
    PIN          — numeric PIN (from policy in security.xml or brute-forced)
    CASE_DIR     — path to extracted case directory (default: ./case)
    APK_PATH     — path to app.apk (default: ./app.apk)
"""

import base64, hashlib, os, struct, subprocess, xml.etree.ElementTree as ET
from pathlib import Path
from Crypto.Cipher import AES
import msgpack

CASE = Path(os.environ.get("CASE_DIR", "case")).resolve()
APK  = Path(os.environ.get("APK_PATH",  "app.apk")).resolve()
PIN  = os.environ.get("PIN", "12345678")

# ── helpers ──────────────────────────────────────────────────────────────

def varint(buf, off):
    v = 0
    for i in range(9):
        b = buf[off + i]
        v = (v << 8 | b) if i == 8 else (v << 7 | (b & 0x7f))
        if i == 8 or not (b & 0x80):
            return v, off + i + 1
    return v, off + 9

def serial_size(st):
    sizes = {0:0,1:1,2:2,3:3,4:4,5:6,6:8,7:8,8:0,9:0}
    return sizes.get(st, (st-12)//2 if st >= 12 else 0)

def decode_serial(st, data):
    if st in (0,8,9): return {0:None,8:0,9:1}[st]
    n = serial_size(st)
    raw = data[:n]
    if st == 7: return struct.unpack(">d", raw)[0]
    if st >= 12:
        return raw.decode("utf-8","replace") if st % 2 == 1 else raw
    return int.from_bytes(raw, "big", signed=True)

def parse_record(payload):
    hs, p = varint(payload, 0)
    serials, idx = [], p
    while idx < hs:
        st, idx = varint(payload, idx)
        serials.append(st)
    data, out, pos = payload[hs:], [], 0
    for st in serials:
        n = serial_size(st)
        out.append(decode_serial(st, data[pos:]))
        pos += n
    return out

def read_payload(db, page_size, overlay, pgno, size, start, reserved):
    page    = overlay.get(pgno, db[(pgno-1)*page_size : pgno*page_size])
    usable  = page_size - reserved
    max_loc = usable - 35
    min_loc = ((usable - 12) * 32) // 255 - 23
    local   = size if size <= max_loc else (min_loc + (size - min_loc) % (usable - 4))
    out     = bytearray(page[start : start + local])
    if size <= local:
        return bytes(out)
    ovfl = int.from_bytes(page[start + local : start + local + 4], "big")
    rem  = size - local
    while ovfl and rem > 0:
        op   = overlay.get(ovfl, db[(ovfl-1)*page_size : ovfl*page_size])
        nxt  = int.from_bytes(op[:4], "big")
        ch   = op[4 : 4 + min(rem, usable-4)]
        out.extend(ch); rem -= len(ch); ovfl = nxt
    return bytes(out)

def iter_records(db, page_size, overlay, root, reserved):
    stack = [root]
    while stack:
        pgno = stack.pop()
        page = overlay.get(pgno, db[(pgno-1)*page_size : pgno*page_size])
        base = 100 if pgno == 1 else 0
        pt   = page[base]
        nc   = int.from_bytes(page[base+3:base+5], "big")
        pb   = base + (12 if pt == 0x05 else 8)
        if pt == 0x05:
            rm = int.from_bytes(page[base+8:base+12], "big")
            for i in range(nc):
                off  = int.from_bytes(page[pb+2*i:pb+2*i+2], "big")
                stack.append(int.from_bytes(page[off:off+4], "big"))
            if rm: stack.append(rm)
        elif pt == 0x0d:
            for i in range(nc):
                off  = int.from_bytes(page[pb+2*i:pb+2*i+2], "big")
                sz, p = varint(page, off)
                rid, p = varint(page, p)
                pl   = read_payload(db, page_size, overlay, pgno, sz, p, reserved)
                yield rid, parse_record(pl)

# ── step 1: parse security.xml ───────────────────────────────────────────

def parse_prefs():
    root = ET.parse(CASE / "shared_prefs/security.xml").getroot()
    kv   = {}
    for ch in root:
        n = ch.attrib.get("name")
        if ch.tag == "string": kv[n] = ch.text or ""
        elif ch.tag == "int":  kv[n] = int(ch.attrib["value"])
    return base64.b64decode(kv["salt_b64"]), kv["pbkdf2_iter"], kv["dk_len"]

# ── step 2: extract pepper from libnative.so ─────────────────────────────

def get_pepper():
    adir = Path("/tmp/_rocsc_apk")
    adir.mkdir(exist_ok=True)
    subprocess.run(["unzip","-qo", str(APK), "-d", str(adir)], check=True)
    so   = next(adir.rglob("libnative.so"))
    hits = [l for l in subprocess.check_output(["strings", str(so)],
             errors="ignore", text=True).splitlines()
            if "::" in l and ("pepper" in l.lower() or "rocsc" in l.lower())]
    if not hits:
        raise RuntimeError("pepper not found in libnative.so")
    return sorted(hits, key=len, reverse=True)[0]

# ── step 3: WAL parsing ───────────────────────────────────────────────────

def parse_wal(wal, page_size):
    if len(wal) < 32 or wal[:4] not in (b'\x37\x7f\x06\x82', b'\x37\x7f\x06\x83'):
        raise RuntimeError("invalid WAL magic")
    frames, off = [], 32
    while off + 24 + page_size <= len(wal):
        pgno   = int.from_bytes(wal[off:off+4],   "big")
        db_sz  = int.from_bytes(wal[off+4:off+8], "big")
        page   = wal[off+24 : off+24+page_size]
        frames.append((pgno, db_sz, page))
        off   += 24 + page_size
    last_commit = max((i for i,(p,d,pg) in enumerate(frames) if d != 0), default=-1)
    committed, uncommitted = {}, {}
    for i, (pgno, _, page) in enumerate(frames):
        (committed if i <= last_commit else uncommitted)[pgno] = page
    return committed, uncommitted

# ── step 4: nonce derivation ──────────────────────────────────────────────

def nonce_from_ts(ts):
    b = ts.to_bytes(8, "little").ljust(16, b"\x00")
    return hashlib.sha256(b"no" + b).digest()[:12]

# ── main ──────────────────────────────────────────────────────────────────

def main():
    salt, iters, dklen = parse_prefs()
    pepper = get_pepper()
    print(f"[+] pepper: {pepper}")

    key = hashlib.pbkdf2_hmac("sha256",
              f"{PIN}:{pepper}".encode(), salt, iters, dklen)

    db_bytes  = (CASE / "databases/notes.db").read_bytes()
    wal_bytes = (CASE / "databases/notes.db-wal").read_bytes()

    _ps = int.from_bytes(db_bytes[16:18], "big")
    page_size = 65536 if _ps == 1 else _ps
    reserved  = db_bytes[20]

    committed, uncommitted = parse_wal(wal_bytes, page_size)
    overlay  = {**committed, **uncommitted}
    print(f"[+] WAL: {len(committed)} committed, {len(uncommitted)} uncommitted pages")

    # find notes root page from sqlite_master
    root_pg = None
    for _, cols in iter_records(db_bytes, page_size, overlay, 1, reserved):
        if len(cols) >= 5 and cols[0] == "table" and cols[1] == "notes":
            root_pg = cols[3]; break
    if root_pg is None:
        raise RuntimeError("notes table not found")
    print(f"[+] notes root page: {root_pg}")

    committed_ids = {rid for rid,_ in
                     iter_records(db_bytes, page_size, committed, root_pg, reserved)}

    # use full overlay so interior pages in committed frames are accessible;
    # filter out rowids already present in committed state
    for rid, cols in iter_records(db_bytes, page_size, overlay, root_pg, reserved):
        if rid in committed_ids: continue
        blob = next((c for c in reversed(cols) if isinstance(c,(bytes,bytearray))), None)
        ts   = next((c for c in cols if isinstance(c,int) and c > 1_000_000_000_000), None)
        if blob is None or ts is None: continue
        nonce = nonce_from_ts(ts)
        try:
            pt  = AES.new(key, AES.MODE_GCM, nonce=nonce).decrypt_and_verify(blob[:-16], blob[-16:])
            obj = msgpack.unpackb(pt, raw=False)
            if "body" in obj:
                print(f"[+] FLAG: {obj['body']}")
                return
        except Exception:
            pass
    print("[-] flag not found")

if __name__ == "__main__":
    main()