Browser Artifacts

-- Query Chrome History database (copy the file first -- Chrome locks it while open)
-- Open with DB Browser for SQLite or sqlite3

-- Recent browsing history with human-readable timestamps
SELECT
    urls.url,
    urls.title,
    urls.visit_count,
    datetime(visits.visit_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS visit_time_local,
    urls.typed_count   -- > 0 means user typed the URL directly (vs clicked a link)
FROM visits
JOIN urls ON visits.url = urls.id
ORDER BY visits.visit_time DESC
LIMIT 500;

-- Downloads
SELECT
    target_path,
    tab_url,               -- page the download was initiated from
    referrer,              -- HTTP referrer (where they came from)
    total_bytes,
    datetime(start_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS download_started,
    datetime(end_time   / 1000000 - 11644473600, 'unixepoch', 'localtime') AS download_ended,
    state  -- 1=in progress, 2=complete, 3=cancelled
FROM downloads
ORDER BY start_time DESC;

-- Search for specific domains (e.g., known phishing infrastructure)
SELECT
    url,
    datetime(visit_time / 1000000 - 11644473600, 'unixepoch') AS visit_time_utc
FROM visits
JOIN urls ON visits.url = urls.id
WHERE url LIKE '%suspicious-domain.com%'
ORDER BY visit_time;

-- Query Chrome Cookies database
-- Note: Cookie values are encrypted with DPAPI (Windows) — raw value not visible
-- But metadata (domain, name, timestamps) is unencrypted and forensically valuable

SELECT
    host_key,           -- domain the cookie belongs to
    name,               -- cookie name (e.g., "sessionid", "PHPSESSID", ".AspNetCore.Session")
    path,
    datetime(creation_utc   / 1000000 - 11644473600, 'unixepoch') AS created,
    datetime(last_access_utc / 1000000 - 11644473600, 'unixepoch') AS last_accessed,
    datetime(expires_utc     / 1000000 - 11644473600, 'unixepoch') AS expires,
    is_httponly,
    is_secure,
    is_persistent       -- 0 = session cookie (deleted on browser close)
FROM cookies
WHERE host_key LIKE '%target-domain.com%'
ORDER BY creation_utc DESC;

-- For decrypting cookie values (requires DPAPI — must run as the user):
-- pip install pycookiecheat
-- python3 -c "from pycookiecheat import chrome_cookies; print(chrome_cookies('https://example.com'))"

-- Firefox places.sqlite — history and bookmarks
-- Timestamps are microseconds since Unix epoch (1970-01-01)

-- Browsing history
SELECT
    url,
    title,
    visit_count,
    datetime(last_visit_date / 1000000, 'unixepoch') AS last_visited,
    typed                   -- 1 = user typed URL directly
FROM moz_places
WHERE visit_count > 0
ORDER BY last_visit_date DESC
LIMIT 500;

-- Downloads (stored in moz_annos table)
SELECT
    moz_places.url          AS downloaded_url,
    moz_annos.content       AS local_path_or_status,
    datetime(moz_annos.dateAdded / 1000000, 'unixepoch') AS download_date,
    moz_annos.anno_attribute_id
FROM moz_annos
JOIN moz_places ON moz_annos.place_id = moz_places.id
WHERE moz_annos.anno_attribute_id IN (
    SELECT id FROM moz_anno_attributes WHERE name LIKE '%download%'
);

# Hindsight: automated Chrome forensics timeline
# pip3 install hindsight

# Analyze Chrome profile from KAPE collection
python3 -m hindsight \
  -i "D:/evidence/C/Users/jsmith/AppData/Local/Google/Chrome/User Data" \
  -o chrome_analysis \
  -f xlsx               # output format: xlsx, csv, sqlite, json

# Output includes:
#   Web History sheet      — URLs, visit times, typed vs clicked
#   Downloads sheet        — file downloads with referrers
#   Cookies sheet          — cookie metadata (values encrypted)
#   Autofill sheet         — form field data saved by browser
#   Saved Passwords sheet  — login data (encrypted values + domains)
#   Extensions sheet       — installed browser extensions
#   Cache Analysis         — URLs found in browser cache records