Working with SQLite

• Working with SQLite
  • Installation
  • The sqlite3 Shell
    • Essential dot-commands
    • Quick inspection workflow
  • Schema Inspection
    • sqlite_master / sqlite_schema
    • PRAGMA statements
  • Core SQL
    • Selecting and filtering
    • Aggregation
    • Joins
    • Subqueries and CTEs
    • Useful built-in functions
  • Timestamps
    • Converting Chrome timestamps
    • Converting Firefox timestamps
  • Forensic Artifacts
    • Browser databases
      • Chrome / Chromium
      • Firefox
    • macOS artifacts
    • iOS / iPadOS (via backup or extraction)
    • Android
  • WAL Mode and Forensic Implications
  • Recovering Deleted Rows
    • VACUUM and freelist pages
    • Carving with external tools
  • Exporting Data
    • To CSV
    • To SQL dump
    • Comparing two databases
  • Python Integration
  • Quick Reference
    • Common investigative queries
  • References

SQLite is the most widely deployed database engine in existence. Unlike client-server databases, it stores the entire database in a single cross-platform file. This makes it ideal for embedded use — and it is embedded everywhere: every browser, every smartphone, most desktop applications, and a large fraction of operating system subsystems all use SQLite to store structured data. For forensic investigators and network analysts, fluency with SQLite is essential: browser history, cookies, saved credentials, chat logs, call records, and network tool output are all routinely stored in SQLite databases.

Installation

SQLite’s command-line shell (sqlite3) is available on all major platforms.

# Debian / Ubuntu / Kali
sudo apt install sqlite3

# macOS (already installed; or via Homebrew)
brew install sqlite

# Windows (via winget)
winget install SQLite.SQLite
# or download the precompiled shell from https://www.sqlite.org/download.html

Verify:

sqlite3 --version
# 3.45.1 2024-01-30 ...

The sqlite3 Shell

Open a database file (created if it does not exist):

sqlite3 path/to/database.db

For forensic work, open read-only to avoid accidental modification:

sqlite3 -readonly path/to/database.db

Essential dot-commands

Dot-commands are shell directives, not SQL. They are not terminated with a semicolon.

Quick inspection workflow

sqlite3 -readonly unknown.db
.headers on
.mode column
.tables
.schema
SELECT * FROM some_table LIMIT 5;
.quit

Schema Inspection

sqlite_master / sqlite_schema

Every SQLite database has a built-in catalog table that lists all objects:

SELECT type, name, tbl_name, sql
FROM sqlite_master
WHERE type IN ('table', 'index', 'view', 'trigger')
ORDER BY type, name;

sqlite_schema is an alias for sqlite_master introduced in SQLite 3.33.0.

PRAGMA statements

PRAGMA commands query or set internal database properties.

-- List all columns in a table
PRAGMA table_info(urls);

-- List indexes on a table
PRAGMA index_list(urls);

-- List all databases attached to this connection
PRAGMA database_list;

-- Check database integrity
PRAGMA integrity_check;

-- Show journal mode (delete, wal, memory, off)
PRAGMA journal_mode;

-- Show page size and page count
PRAGMA page_size;
PRAGMA page_count;

PRAGMA table_info(t) returns one row per column: cid, name, type, notnull, dflt_value, pk.

Core SQL

SQLite supports standard SQL with minor differences from PostgreSQL/MySQL.

Selecting and filtering

SELECT url, title, last_visit_time
FROM urls
WHERE url LIKE '%example.com%'
ORDER BY last_visit_time DESC
LIMIT 20;

Aggregation

-- Count visits per domain
SELECT
    rtrim(ltrim(url, 'https://'), '/') AS domain,
    COUNT(*) AS visits
FROM urls
GROUP BY domain
ORDER BY visits DESC
LIMIT 10;

Joins

-- Chrome: combine urls and visits tables
SELECT
    u.url,
    u.title,
    v.visit_time,
    v.visit_duration
FROM visits v
JOIN urls u ON v.url = u.id
ORDER BY v.visit_time DESC
LIMIT 25;

Subqueries and CTEs

Common Table Expressions (CTEs) make complex queries readable:

WITH top_sites AS (
    SELECT url, COUNT(*) AS n
    FROM visits v JOIN urls u ON v.url = u.id
    GROUP BY url
    ORDER BY n DESC
    LIMIT 20
)
SELECT * FROM top_sites;

Useful built-in functions

Timestamps

SQLite has no native date type. Applications store timestamps as integers, reals, or strings — the epoch and unit vary per application.

Converting Chrome timestamps

Chrome stores timestamps as microseconds since 1601-01-01 (the Windows FILETIME epoch):

-- Subtract the offset between 1601 and 1970 (11644473600 seconds)
SELECT
    url,
    title,
    datetime(last_visit_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS visited
FROM urls
ORDER BY last_visit_time DESC
LIMIT 20;

Converting Firefox timestamps

Firefox stores timestamps as microseconds since the Unix epoch:

SELECT
    p.url,
    p.title,
    datetime(h.visit_date / 1000000, 'unixepoch', 'localtime') AS visited
FROM moz_historyvisits h
JOIN moz_places p ON h.place_id = p.id
ORDER BY h.visit_date DESC
LIMIT 20;

Forensic Artifacts

Browser databases

Browsers lock their databases while running. Copy the file out of the profile directory before opening it, or use a live imaging tool.

Chrome / Chromium

Default profile locations:

Key databases and tables:

Worked example — recent downloads:

sqlite3 -readonly History
.headers on
.mode column

SELECT
    datetime(start_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS started,
    tab_url,
    target_path,
    received_bytes,
    total_bytes,
    state       -- 0=in progress, 1=complete, 2=cancelled, 4=interrupted
FROM downloads
ORDER BY start_time DESC
LIMIT 20;

Firefox

Default profile locations:

Key databases and tables:

Worked example — bookmark tree:

sqlite3 -readonly places.sqlite
.headers on
.mode column

SELECT
    b.title,
    p.url,
    datetime(b.dateAdded / 1000000, 'unixepoch', 'localtime') AS added,
    datetime(b.lastModified / 1000000, 'unixepoch', 'localtime') AS modified
FROM moz_bookmarks b
JOIN moz_places p ON b.fk = p.id
WHERE b.type = 1    -- 1=bookmark, 2=folder, 3=separator
ORDER BY b.dateAdded DESC;

macOS artifacts

Worked example — TCC permission grants:

sqlite3 -readonly /Library/Application\ Support/com.apple.TCC/TCC.db
.headers on
.mode column

SELECT
    client,
    service,
    CASE auth_value
        WHEN 0 THEN 'denied'
        WHEN 1 THEN 'unknown'
        WHEN 2 THEN 'allowed'
        WHEN 3 THEN 'limited'
        ELSE CAST(auth_value AS TEXT)
    END AS permission,
    datetime(last_modified, 'unixepoch', 'localtime') AS modified
FROM access
ORDER BY service, client;

iOS / iPadOS (via backup or extraction)

iTunes/Finder backups and forensic extractions expose many SQLite databases:

Android

Root or a forensic extraction tool (Cellebrite, AXIOM, etc.) is required to read most of these from a live device.

WAL Mode and Forensic Implications

SQLite’s default journal mode is rollback journal (producing a -journal file). Many modern applications — including Chrome, Firefox, and most iOS databases — explicitly configure Write-Ahead Log (WAL) mode instead, which produces -wal and -shm companion files.

In WAL mode:

• Writes go to the .wal file first; reads may come from either the main database or the WAL.
• The WAL is periodically checkpointed (merged back into the main file) and then reset.
• Forensically: the .wal file may contain records not yet checkpointed — always collect it alongside the main database.
• If the .wal is missing and the database is in WAL mode, the main file may be in an inconsistent state.

# Check the journal mode of a database
sqlite3 -readonly database.db "PRAGMA journal_mode;"

# Collect all associated files
cp database.db database.db-wal database.db-shm /evidence/

Recovering Deleted Rows

SQLite marks deleted rows as free space but does not immediately zero them. Until the pages are reused or the database is vacuumed, the original data may still be present in the file.

VACUUM and freelist pages

-- Check whether the database has been vacuumed recently
PRAGMA freelist_count;
-- High freelist_count means many deleted-but-not-reclaimed pages exist

Carving with external tools

• sqlite_forensics / undark — parse the raw file to extract rows from free pages
• sqlparse / sqliteparser — Python-based SQLite page parser
• bulk_extractor — carves SQLite records from disk images or memory dumps
• Autopsy / FTK / Cellebrite — commercial tools with built-in SQLite recovery

Manual carving: SQLite records begin with a variable-length integer (varint) encoding the payload length. The SQLite file format specification at https://www.sqlite.org/fileformat2.html documents the on-disk layout in detail.

Exporting Data

To CSV

sqlite3 -readonly History \
  -cmd ".headers on" \
  -cmd ".mode csv" \
  -cmd ".output urls.csv" \
  "SELECT url, title, last_visit_time FROM urls;"

Or from within the shell:

.headers on
.mode csv
.output report.csv
SELECT * FROM urls ORDER BY last_visit_time DESC;
.output stdout

To SQL dump

sqlite3 database.db .dump > backup.sql

Comparing two databases

# Show rows in db_a but not in db_b (by URL)
sqlite3 -readonly db_a.db "ATTACH 'db_b.db' AS b;
SELECT url FROM urls
EXCEPT
SELECT url FROM b.urls;"

Python Integration

Python’s standard library includes sqlite3 — no third-party packages needed.

import sqlite3
from datetime import datetime, timezone, timedelta

CHROME_EPOCH_OFFSET = 11644473600  # seconds between 1601-01-01 and 1970-01-01

def chrome_ts(micros: int) -> datetime:
    return datetime.fromtimestamp(micros / 1_000_000 - CHROME_EPOCH_OFFSET, tz=timezone.utc)

con = sqlite3.connect("file:History?mode=ro", uri=True)  # read-only
con.row_factory = sqlite3.Row  # enables column access by name

with con:
    rows = con.execute(
        "SELECT url, title, last_visit_time FROM urls ORDER BY last_visit_time DESC LIMIT 50"
    ).fetchall()

for row in rows:
    print(chrome_ts(row["last_visit_time"]), row["url"])

con.close()

Key points:

• uri=True with ?mode=ro opens the file read-only at the OS level.
• row_factory = sqlite3.Row allows row["column_name"] access.
• Always use parameterized queries (? placeholders) when inserting user-supplied values to prevent SQL injection.

# Safe: parameterized
cur.execute("SELECT * FROM urls WHERE url LIKE ?", (f"%{domain}%",))

# Unsafe: string formatting
cur.execute(f"SELECT * FROM urls WHERE url LIKE '%{domain}%'")  # never do this

Quick Reference

Common investigative queries

-- Most visited sites
SELECT url, visit_count FROM urls ORDER BY visit_count DESC LIMIT 20;

-- Activity in a specific time window (Chrome)
SELECT
    url,
    datetime(last_visit_time/1000000 - 11644473600, 'unixepoch', 'localtime') AS ts
FROM urls
WHERE last_visit_time/1000000 - 11644473600
    BETWEEN strftime('%s','2025-03-01') AND strftime('%s','2025-03-31')
ORDER BY last_visit_time;

-- Search terms extracted from Google URLs
SELECT
    url,
    datetime(last_visit_time/1000000 - 11644473600, 'unixepoch', 'localtime') AS ts
FROM urls
WHERE url LIKE '%google.%/search?%q=%'
ORDER BY last_visit_time DESC;

-- Downloads with suspicious extensions
SELECT
    target_path,
    tab_url,
    datetime(start_time/1000000 - 11644473600, 'unixepoch', 'localtime') AS ts
FROM downloads
WHERE target_path LIKE '%.exe'
   OR target_path LIKE '%.ps1'
   OR target_path LIKE '%.bat'
   OR target_path LIKE '%.vbs'
ORDER BY start_time DESC;

References

• SQLite Documentation — official reference
• SQLite File Format — on-disk structure for forensic carving
• SQLite PRAGMA statements — full PRAGMA reference
• SQLite date and time functions — timestamp conversion functions
• Chrome source: history schema — authoritative table definitions
• Firefox places.sqlite schema — Mozilla wiki on the Places database
• Kristinn Gudjonsson, Mastering Python Forensics — chapter on SQLite forensics
• Cory Altheide & Harlan Carvey, Digital Forensics with Open Source Tools — SQLite artifact analysis