Validate This #03

The Broken Hash

Platform Any platformLevel IntermediateTime about 40 minTools Lab Tools (in your browser), or sqlite3 and a hash utility

Practice data only. Every message, name, handle and case detail in this lab is fictional, and "Huddle" is a made-up messaging app. We built the files to practice evidence integrity and SQLite WAL behavior. They are not an extraction from a real device, but the SQLite behavior they show is real.

The scenario

A phone was extracted on November 10, 2026, and the Huddle app's database was exported with its -wal and -shm files and hashed. Two days later, a colleague (Examiner B) copied the database to their desktop to give the detective "a quick look" before an interview, then sent you the working copy for the full exam.

You hash the working copy. It doesn't match the acquisition log. Examiner B's note says no changes were made. The detective wants to know two things: is the working copy any good, and did we lose anything?

Download

Download the lab files (ZIP, 5 KB)

The ZIP holds two folders: evidence/ (the files as exported, with acquisition_hashes.txt) and working-copy/ (the database you received, with Examiner B's note.txt). Here are the hashes as published:

FileSHA-256
evidence/huddle.db78ea59cd6cf899ec4e8695a49ba8bb60bb7416b8b0292afe9c13574d8ec02e3e
evidence/huddle.db-wal1af5484cf7fb8e908342e462126f9912872bb28d4f56428b1de075b0cc845716
evidence/huddle.db-shma21ace7121e62f48e33527b822a1149e704b453d2314b9e00903da8c6c72fee5
evidence/acquisition_hashes.txt4b9328b0064a8b33bacdb68ada669b85ec80eec2218260c8169d772dc8e72193
working-copy/huddle.db559e468b933b15a7e6c383c22694d58bae2df20067cde3c57ed950ddf00c42fb
working-copy/note.txtc014cdea7bd568ba563f5fe63fef44af4038af7424de86b62006e149c9b05754
validate-this-03.zipbab2cb993cc213d95653e30c6d92490417c08c35d30b5d40fe5b31104f955a85

Don't repeat Examiner B's mistake. Opening evidence/huddle.db in an ordinary SQLite tool while its -wal sits beside it can change it. Work on copies, and re-hash when you're done. The Lab Tools SQLite viewer reads files into memory and never writes to them, and its Hash Calculator will compare your files against the pasted acquisition log.

The challenge

  1. Hash every file in both folders. Which ones match the acquisition log?
  2. What is in evidence/ that is missing from working-copy/?
  3. Read the evidence three ways: huddle.db alone, huddle.db with its WAL applied, and the working copy. How many messages does each hold? Which view does the working copy match?
  4. What did opening the copy actually do? Cite the evidence in the WAL, the shm and the database pages.
  5. One message exists in only one of the three views. Which is it, what does it say, and when was it sent (Eastern)?
  6. Is that message anywhere in the working copy, even as leftover bytes? Why not?
  7. Was "No changes made" accurate? How would you describe what happened, fairly?
  8. What should happen next with the evidence, the working copy and your report?
  9. Bonus: the file change counter at offset 24 of the database header is the same in the evidence file and the working copy. Why doesn't that prove nothing changed?

Check your answers

Short answers to the key facts. Answers are checked in your browser against hashed values, so the page source won't give them away. Your entries are saved on this device only.

How many files in working-copy/ match a hash in the acquisition log?
How many messages does working-copy/huddle.db hold?
Which view of the evidence does the working copy match?
How many WAL frames had been copied into the evidence main file at extraction (nBackfill)?
ID of the message that exists only in the evidence main file
When was that message sent? (Eastern, 24-hour)
Which unit number does it mention?
Is any of that message's text in the working copy?

Hints

Hint 1: three views

In Lab Tools, select evidence/huddle.db by itself first. Then select all three evidence files together and switch between Main file only and With WAL applied. Then open working-copy/huddle.db on its own. With sqlite3, open copies, and use file:huddle.db?immutable=1 to read the main file without touching the WAL.

Hint 2: what a clean close does

When the last connection to a WAL-mode database closes normally, SQLite checkpoints: it copies the committed pages from the WAL into the main file, then deletes the -wal and -shm files. The person who opened it never pressed "save".

Hint 3: why the deleted text didn't survive

Look at the WAL's copy of page 4, the message table, in the hex viewer. Then search it for any word from the deleted message. Huddle runs SQLite with secure_delete on, which zeroes deleted content.

Walkthrough

Show the full solution

1. Hashes

All three files in evidence/ match the acquisition log. working-copy/huddle.db does not; no file in the working copy matches. Paste the log into the Hash Calculator, select every file, and the "In log?" column shows it at a glance.

2. What's missing

working-copy/ has no huddle.db-wal and no huddle.db-shm. SQLite deleted them when the browser closed the database.

3. Three views

cp evidence/huddle.db* /tmp/ev/
sqlite3 "file:/tmp/ev/huddle.db?immutable=1" "SELECT count(*) FROM message;"   -- 8: main file alone
sqlite3 /tmp/ev/huddle.db "SELECT count(*) FROM message;"                      -- 9: WAL applied (this checkpoints the copy!)
sqlite3 "file:working-copy/huddle.db?immutable=1" "SELECT count(*) FROM message;"  -- 9

The main file alone holds 8 messages (IDs 1–8). With the WAL applied there are 9: message 7 is gone and messages 9 and 10 are new. The working copy holds the same 9 rows as the WAL-applied view. The working copy is the evidence with its WAL checkpointed into it.

4. What opening it did

  • The WAL: 4 frames in 2 committed transactions. Frames 1–2 rewrite pages 4 and 5 (the message table and its message_thread index) to delete message 7. Frames 3–4 rewrite the same pages to add messages 9 and 10.
  • The shm: mxFrame is 4 and nBackfill is 0. At extraction, none of those frames had been copied into the main file.
  • The pages: compare the two huddle.db files byte by byte (cmp -l, or the hex viewer at offsets 0x3000 and 0x4000). Only pages 4 and 5 differ, and they now match the last WAL frames for those pages.
  • The header: bytes 18–19 are still 02 02 (WAL mode) in both files, so nothing about the change shows up there either.

The browser didn't need to save anything. Reading the database applied the WAL; closing it checkpointed the WAL into the main file and removed it.

5. The message that's gone

Message 7, from the phone's owner to "Dee", sent at sent_ms 1794113529000: 11:52:09 PM EST on November 7, 2026. It reads "out. unit 214 was empty except the safe". It is a live row in the evidence main file, and it is gone once the WAL is applied, so the app deleted it before the extraction.

6. Not in the working copy

Search the working copy for "safe" or "214": no hits. With secure_delete on, the app zeroed the row's bytes when it deleted it, so the WAL's copy of page 4 holds no trace of it. The only copy of message 7 was the old page 4 in the evidence main file. The checkpoint replaced that page with the WAL's version. In the working copy, the message is gone for good.

7. "No changes made"

Examiner B didn't edit anything on purpose, and the note is honest about what they intended. But the file did change. A fair description: opening and closing the working copy in a SQLite browser checkpointed the write-ahead log into the database file and deleted the WAL and shm files. This changed the file's hash and removed the only recoverable copy of one deleted message from the working copy.

8. What next

  • Go back to the evidence (or the original extraction), confirm its hashes still match the log, and make fresh working copies.
  • Don't use the altered working copy as a basis for findings. Keep it and Examiner B's note, and document what happened.
  • Examine copies with read-only methods: an in-memory viewer, or ?immutable=1/?mode=ro in sqlite3, and re-hash afterward.
  • In your report, explain the hash mismatch plainly. An honest, documented explanation is defensible. A mismatch that you can't explain is not.

Bonus: the change counter

The SQLite file format documentation explains that in WAL mode, changes are tracked through the WAL index, so the file change counter might not be incremented on each transaction. An unchanged counter is not proof that nothing changed. The hash is.

Report wording

The working copy of huddle.db received on November 12, 2026 does not match the SHA-256 value recorded at extraction. Examination of the original exported files showed that the working copy's content is identical to the original database with its write-ahead log applied. This is consistent with the working copy having been opened and closed in SQLite software, which checkpoints the write-ahead log into the database file. All findings in this report are based on the original exported files, whose hash values were verified against the acquisition log before and after examination.

Go further

This lab pairs with Validate This #01, where the same kind of uncheckpointed WAL hides a deleted conversation. Evidence handling and hash verification are covered in both of our virtual courses.