Chapter 18 — Exercises

Twenty-eight problems in seven groups (A–G), mixing concept checks, hands-on labs (query a profile read-only, convert a timestamp, recover deleted history, carve cached images, build the timeline, write the report), and judgment questions. Hands-on labs assume a practice image and the SQLite/Hindsight/BrowsingHistoryView/undark toolset from Appendix J — Practice Images and Lab Setup. (answer in Appendix) = worked solution in Answers to Selected. ⭐ = stretch. Always work on extracted copies of the profile, opened read-only — the original is sacred, and merely pointing a default sqlite3 connection at the evidence can checkpoint the WAL and change both the file and its hash.

Group A — SQLite: the engine under (almost) every browser

18.1 SQLite is the load-bearing skill of this chapter. (a) Give the 16-byte magic that begins every SQLite database, in both ASCII and hex. (b) Explain, in two or three sentences, why a browser running in WAL mode means the file named History is often not the whole History database, and name the two companion files you must copy with it. (c) State exactly what an examiner loses by copying History alone in a case where the decisive activity happened in the last hour the machine was on. (answer in Appendix)

18.2 State the two cardinal rules of forensic SQLite handling. (a) Explain what a default sqlite3 connection can do to a profile database the instant you open it, and why that is a chain-of-custody problem on the original and a clarity problem even on a copy. (b) Write the four-step disciplined workflow (preserve → copy → read-only → optional immutable). (c) Explain the trade-off of the URI flag immutable=1: what it guarantees, and what it makes SQLite ignore (and therefore what you must then parse yourself).

18.3 ⭐ When a user clears their history, the browser issues SQL DELETE statements. (a) Describe what a DELETE actually does to a row at the page level — does it erase the bytes? (b) Name the two places inside a live database where the old row content lingers, and name the single operation that finally destroys it. (c) Tie this to the book's first recurring theme in one sentence, and explain why it makes "I cleared my history" a hollow defense.

18.4 ⭐ You carve a candidate SQLite file out of unallocated space and dump its first 32 bytes. The two bytes at offset 0x10 (16) are 20 00. (a) That field is the page size, stored big-endian — give its decimal value. (b) What special case does a stored value of 00 01 (decimal 1) encode for the page size, and why can a naive parser misread it? (c) Why does recognizing the SQLite header signature in unallocated space matter before you can recover any deleted rows — which chapter's technique do you reach for first?

Group B — Chromium: History, Cookies, Login Data, Web Data

18.5 The Chromium History database carries most of its value in three tables. (a) Name them and state, in a phrase each, what one row of each represents. (b) Write the SQL join that pairs the per-URL summary with the per-visit detail. (c) Name the single most forensically important column in the visits table and explain, in one sentence, the distinction it lets you draw between intent and incident. (d) The downloads table and keyword_search_terms add two more kinds of proof: state what a single downloads row establishes (name three of its columns) and what keyword_search_terms gives you that a bare URL does not. (answer in Appendix)

18.6 Decode these four Chromium visits.transition values (you may treat each as already an integer): 1, 3, 0x10000007, 0x80000000. For each, give the core type (transition & 0xFF) by name and list any qualifier bits set. Then state which of the four represent a deliberate user navigation you could defensibly call "the user went here," and which are automatic activity you must not count as a visit.

18.7 Cookies answer questions history cannot. (a) Distinguish a session cookie from a persistent cookie by the flag that separates them. (b) Explain what a session cookie for mail.google.com with a recent last_access_utc proves that a mere history visit to the domain does not. (c) A cookie's encrypted_value begins with the ASCII tag v10. Name the encryption scheme, say where the key lives, and state in one sentence what it costs to read the plaintext — and which chapter owns that work.

18.8 ⭐ The Login Data database's logins table is evidence even when you never decrypt a single password. (a) List four columns you can read and report without decryption, and write one sentence of the form "this account, at this site, last used on this date" that such columns support. (b) Explain why "47 saved logins (metadata only)" is a legitimate, defensible finding. (c) Name two stores in this chapter (besides Login Data) whose mere existence proves a site was visited even after History was cleared, and explain the mechanism for one of them.

18.9 Lab — query a Chromium History read-only. On a practice image, extract a Chromium History with its -wal and -shm companions and hash all three into your chain-of-custody worksheet (Appendix F). Open the copy read-only and run three queries: (a) the 25 most recently visited URLs with their WebKit time converted to UTC inside SQL; (b) all visits whose core transition is TYPED (1) or FORM_SUBMIT (7), oldest first; (c) the downloads table, projecting target_path, tab_url, received_bytes, and start time in UTC. Report the earliest TYPED visit and its URL, and one download with its source URL and save path. Then state, in one sentence each, why you opened the copy read-only and why the -wal had to be alongside it. Finally, run the same History through Hindsight or BrowsingHistoryView and confirm your earliest TYPED visit matches — the two-tool corroboration a cross-examiner expects.

Group C — Firefox and Safari: the same evidence, different schema and epoch

18.10 Firefox stores the same evidence as Chromium with a different schema and a different epoch. (a) Name the database that combines history and bookmarks, and its two core history tables. (b) Give the Firefox epoch by name and its conversion to Unix seconds. (c) Firefox's visit_type mirrors Chromium's transition but with its own numbering — give the values for LINK, TYPED, and DOWNLOAD. (d) Open and recently-closed tabs live in sessionstore.jsonlz4 and the sessionstore-backups\ folder, wrapped in Mozilla's LZ4 container — give the 8-byte magic that begins such a file and explain why recovering this artifact can preserve a session (URLs and even form contents) the user believed was gone. (answer in Appendix)

18.11 ⭐ The moz_cookies table contains the single nastiest unit trap in browser forensics. A row shows creationTime = 1710579063000000, lastAccessed = 1710580001000000, and expiry = 1742115063. (a) Convert all three to UTC, applying the correct divisor to each — and state explicitly which column is the odd one out and why. (b) Show the absurd date you would get if you wrongly divided expiry by 1,000,000, and explain why that wrong answer is itself a useful sanity-check signal. (c) Write the one-line note you would put in your report to disclose the per-column unit handling.

18.12 Safari keeps the same evidence in Apple's idioms and on a third epoch. (a) Name the SQLite history file and the epoch (with zero date) Safari uses. (b) Bookmarks, Reading List, and downloads are binary plists — give the 8-byte magic that begins one and name one tool that converts it to readable XML. (c) Safari cookies use a proprietary format — give the 4-byte magic that begins Cookies.binarycookies and explain in one sentence why you must not try to read it as text. (d) The Reading List, stored inside Bookmarks.plist, records the URL, a preview, sometimes the full saved page, and the date added — explain why this artifact can preserve content a user read but never explicitly saved, and how that parallels the cache lesson of Group E.

Group D — Browser timestamps: calculate and verify

18.13 Convert the Chromium last_visit_time value 13355010535000000 to a UTC date and time, showing each step: divide by 1,000,000, subtract 11,644,473,600, then resolve the Unix seconds to a calendar date. Sanity-check your answer — it should land in March 2024. (If you have worked the anchor case, you will recognize the result as a saved-login date_last_used from the worked example.) (answer in Appendix)

18.14 Convert two Firefox values: a places visit visit_date = 1710579063000000 and a moz_cookies expiry = 1742115063. (a) Give both as UTC, applying PRTime to the first and Unix seconds to the second. (b) State the relationship between the two times in plain language. (c) Explain why presenting either as bare numbers, without naming the epoch, is the error opposing counsel most easily exposes.

18.15 ⭐ Two more epochs. (a) Convert the Safari visit_time = 732272801 (Mac/Cocoa seconds since 2001) to UTC. (b) Convert the IE/legacy-Edge WebCacheV01.dat FILETIME 133550540620000000 (100-ns ticks since 1601) to UTC. (c) Both should land on 2024-03-16 — one in the 09:06 minute, one at 09:14:22. Explain what it tells you that four different epochs across four browsers can all describe the same Saturday morning, and why a single conversion error would silently corrupt a multi-browser timeline.

Group E — Cache and the private-browsing myth

18.16 The cache records what the user actually saw, and it is a textbook dual-use artifact. (a) Explain why cached images can be carved by file signature even when the cache index is corrupt — give the JPEG and PNG magic. (b) State the two pieces of metadata a cache entry attaches to each resource that make a carved image "not an anonymous blob." (c) Describe, in one sentence each, what the same carved cached image means to a 💾 recovery technician and to a 🔍 forensic examiner. (d) Contrast the two cache layouts: Chromium's Simple Cache (Cache_Data\ with data_# block files and standalone f_###### files) versus Firefox's cache2 (entries named by the uppercase SHA-1 of the URL, with metadata appended at the end of each file). Explain where, in each, you would look for the source URL of a recovered resource. (answer in Appendix)

18.17 Cache evidence forces the law to separate automatic caching from knowing possession (handle this clinically — procedure and law only). (a) Explain why "the file is in the cache" does not, by itself, establish that a user knowingly possessed contraband. (b) List four independent indicators of intent you would look for to corroborate (or fail to corroborate) deliberate access. (c) Identify the single transition value an examiner must never inflate into "the user viewed this," and explain why an AUTO_SUBFRAME cache hit is the classic over-claim that gets findings excluded.

18.18 Dismantle the incognito myth precisely. (a) State the one thing private browsing actually does (the in-memory profile) and the one category of protection it provides. (b) List six layers, below or around the browser, that still capture a private session, and for each name the artifact or command that exposes it. (c) Explain why "I used incognito, so there's nothing to find" is a confession of method, not a defense. (d) Name two things that do persist from a private session by the user's own deliberate action (not a leak), and explain why a crashed private session can occasionally survive in the SNSS Sessions\ tab-recovery data — the 4-byte signature to look for.

18.19 ⭐ Lab — break your own incognito. On a test machine you control, open a private window, visit three distinctive domains, then (without rebooting) capture the evidence the chapter promises survives: run Get-DnsClientCache and confirm the domains; note the persistent History is clean; if you can, capture RAM and strings the browser process for the URLs; and collect SRUDB.dat for the per-app byte count. Report which of your three domains you recovered from which layer, and which layer would have been lost had you powered the machine off first (tying to the order-of-volatility discipline of Chapter 15 — Live Response and Triage).

Group F — Cloud sync, deleted-history recovery, and the anchor case

18.20 Cloud-sync clients are a second road off the machine, and they leave local footprints. Build a three-row table for Dropbox, Google Drive for Desktop, and OneDrive: for each, give the local store you would parse, and state in a phrase what it proves about exfiltration (e.g., linked account, synced filenames, file-level activity). Then (b) explain why Dropbox's info.json is useful even when the .dbx databases are SQLCipher-encrypted and unreadable; (c) name the two tables inside Google Drive's metadata_sqlite_db you would enumerate, and what the adjacent content_cache\ adds; and (d) explain why OneDrive's obfuscated .odl log files are worth parsing despite the obfuscation, and which chapter owns the server-side cloud picture that the local footprints only hint at. (answer in Appendix)

18.21 Lab — recover deleted history in three layers. On a practice image where history was cleared, recover it through all three layers from the chapter. (a) Layer 1: open the preserved trio read-only and report any rows that lived only in the -wal. (b) Layer 2: run a freelist/page-slack parser (undark --freespace, walitean, or FQLite) against the History copy and report a recovered URL with its visit time. (c) Layer 3: if the History file itself had been deleted, describe how you would recover or carve the file first (which signature, which chapters' techniques) before applying Layers 1–2. State the recoverability window and what closes it.

18.22 Lab — recover the cloud-upload session (anchor case #2). Return to user jrivera on image WS-ENG-04.E01. The live Edge History ends abruptly at 2024-03-16 09:14:22 UTC. (a) Explain why a clean cutoff — history that stops rather than tapers — is itself dated evidence, and to what other artifact from Chapter 16 — Windows Forensics that second corresponds. (b) From the recovered (deleted) rows below, identify which navigations are deliberate (decode the transition) and quote the row that records the user planning the wipe in his own words. (c) Name two corroborating sources — one that quantifies the upload volume, one that confirms the accounts — and the personal-vs-corporate significance of each.

RECOVERED (deleted) Edge history rows — WS-ENG-04, user jrivera (UTC)
2024-03-16 08:47:55  TYPED        https://www.google.com/search?q=how+to+permanently+delete+browser+history
2024-03-16 08:49:12  TYPED        https://www.dropbox.com/login
2024-03-16 08:51:03  TYPED        https://drive.google.com/drive/my-drive
2024-03-16 08:58:40  FORM_SUBMIT  https://drive.google.com/...upload   (12 items)
2024-03-16 09:06:41  LINK         https://mail.google.com/mail/u/1/#sent
2024-03-16 09:11:20  TYPED        https://drive.google.com/drive/my-drive

18.23 ⭐ The same cloud-sync items table can serve both disciplines. (a) Describe how a 💾 recovery technician uses a Google Drive metadata_sqlite_db to help a client who lost a laptop — what do they hand the client, and why is no carving required? (b) Describe how a 🔍 forensic examiner uses the same table in an exfiltration case. (c) State the one sentence that captures the dual lens for this artifact.

18.24 A VACUUM is the recovery technician's and the examiner's shared enemy when chasing deleted rows. (a) Explain what VACUUM does to the file and why it destroys recoverable deleted rows for good. (b) Name two everyday actions that can trigger a VACUUM or otherwise overwrite freed pages. (c) Connect this to themes two (image early) and five (know your limits) in one sentence each.

Group G — Judgment, reporting, and the progressive project

18.25 Match the verb to the artifact. For each statement, say whether the data supports it and, if not, rewrite it defensibly: (a) "A visit row in this profile proves the user was watching the screen." (b) "This bookmark with date_added proves the user deliberately saved the site." (c) "A history row in this synced profile proves the activity happened on this workstation." Tie each answer to a specific limitation discussed in the chapter (visit-vs-presence, deliberate-vs-incidental, sync ambiguity). (answer in Appendix)

18.26 ⭐ Write the report. Draft a 150-word Findings paragraph answering counsel's question — "Did jrivera upload proprietary CAD files to a personal cloud account from this machine before resigning?" — that a competent cross-examiner could not impeach. Cite each artifact by store and table (recovered history, Login Data, Web Data, SRUM, DNS cache), attribute every per-user artifact to the correct account, decode the transitions you rely on, and state at least two limitations (e.g., volume-matches-but-contents-unproven; sync ambiguity; undecrypted values). Keep finding strictly separate from inference, per Chapter 26 — The Forensic Report. Then list the two sentences an opposing expert would attack first, and tighten them.

18.27 Counsel wants you to write "the user viewed and possessed the images." Your evidence is a set of JPEGs carved from Cache_Data, each with a source URL and fetch time, but every cache entry's referring transition is AUTO_SUBFRAME, there are no matching search terms, no downloads, and visit_count for the host is 1 with a sub-second visit_duration. (a) What can you defensibly state? (b) What must you decline to state, and how do you phrase the limitation? (c) Explain how this is the mirror image of the anchor case, where TYPED navigations and a search term did establish intent — and why the difference is decisive.

18.28 Progressive project — the browser/internet layer. Add this chapter's evidence to your Forensic Case File (introduced in Chapter 5, acquired in Chapters 14–15, layered with OS artifacts in Chapters 16–17). (1) Extract and preserve every browser profile on your image — for Chromium, the History/Cookies/Login Data/Web Data databases with their -wal/-shm companions plus the root Local State; for Firefox, places.sqlite/cookies.sqlite/formhistory.sqlite (+ -wal) and logins.json/key4.db; for Safari, History.db, the plists, and Cookies.binarycookies; for any IE/legacy Edge, WebCacheV01.dat. Hash every file. (2) Parse and convert — run an automated parser (Hindsight / BrowsingHistoryView / ESEDatabaseView) for breadth, confirm load-bearing rows with your own read-only SQL, convert each store's epoch correctly, decode transitions/visit types, and cross-check one key finding with a second tool. (3) Recover what was deleted — mine the WAL and freelist, carve cached images, recover a deleted database file if needed. (4) Reach the internet side — pull the DNS cache (if live), parse SRUM for per-app bytes, and enumerate cloud-sync footprints; test any private-browsing claim against these layers. (5) Answer and source four questions — what was deliberately visited and when; what was downloaded/uploaded and to/from where; which accounts were logged in; what deleted or private-mode activity is recoverable. (6) Add a sourced, epoch-correct, transition-aware browser/internet timeline in UTC with local-time annotation, every line tied to a hashed artifact and flagged with its limitation. List the extracted files and their hashes in your submission; you will merge this timeline into the master in Chapter 21 — Timeline Analysis and fold it into the report in Chapter 26.

Self-check. You have mastered this chapter when you can take a raw image and answer four questions with cited, epoch-correct artifacts — what was deliberately visited, what moved on or off the machine, which accounts were active, and what survived a clear or an incognito session — and when you instinctively preserve the db/-wal/-shm trio, open it read-only, decode transition & 0xFF before saying "the user went here," and never present a timestamp without naming its epoch. If you can recover the anchor-case upload session in Group F and defend each line under imagined cross-examination, you are ready for Chapter 19 — Email, Chat, and Social Media Forensics, where the conversation moves off the web page and into the inbox and the DM.