Query history with SQL

01Why query history with SQL

The GUI search bar and crusader history search are fast for "find requests that mention X." But some questions are relational, not full-text: which hosts return the most 500s, grouped by status? which endpoints echo a parameter back verbatim? which responses share a body hash, so I can collapse duplicates? Those are one GROUP BY away in SQL and awkward any other way.

crusader sql exposes the project's history database directly, opened in read-only mode. Every command prints JSON to stdout — {columns, rows, count, limit, truncated} — so it pipes cleanly into jq, a script, or an agent. It is the same data the rest of the app reads; you are just querying it with the full expressiveness of SQLite instead of a search box.

02List the tables

Start by seeing what's in the database. tables is the default subcommand, so these are equivalent:

crusader sql
crusader sql tables

That enumerates every table and virtual table in the current project's database — the canonical way to discover what you can query, since the exact set depends on what you've captured and imported (a Burp import, for example, adds extra tables). Use it before assuming a table exists.

03Inspect a schema

Before you write a query, check the columns. schema with no argument dumps every table's definition; pass a table name to scope it:

# columns + types for one table
crusader sql schema exchanges

# every table's schema
crusader sql schema

This is the authoritative source for column names — read it rather than guessing. The exchanges table below lists the columns you'll reach for most often.

04Run a query

Pass a single SELECT as a quoted argument:

crusader sql query "SELECT id, method, host, path, status FROM exchanges ORDER BY id DESC LIMIT 20"

For longer or multi-line statements, read the SQL from a file or stdin instead of fighting shell quoting:

# from a file
crusader sql query --file report.sql

# from stdin (pipe or heredoc)
echo "SELECT host, COUNT(*) AS n FROM exchanges GROUP BY host ORDER BY n DESC" \
  | crusader sql query --stdin

Two flags shape execution:

The result is JSON: columns (the selected column names in order), rows (arrays of values), count, the effective limit, and truncated. Any BLOB value — for example the raw request/response bytes — is base64-encoded so the output stays valid JSON; decode it on your side if you need the bytes.

05The read-only guard

The connection is opened read-only, and a query guard sits in front of it. It enforces three rules:

• One statement only. A single query per call — no stacked or chained statements.
• Reads only. SELECT, WITH (CTEs), and EXPLAIN are allowed, along with a set of safe read-only PRAGMAs (for example schema introspection). Everything that writes or changes structure is rejected: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, and friends.
• No side effects. Because the handle itself is read-only, even a query that slipped past the parser couldn't modify the file.

The practical upshot: your capture is safe to hand to a script or an LLM agent for analysis. A malformed or hostile query returns an error or an empty result — it can't corrupt or delete your history.

06The exchanges table

Every captured HTTP exchange — from the proxy, crusader send, Repeater, extensions, and more — is one row in exchanges. These are the columns you'll query most:

Full-text search lives in a separate FTS5 virtual table, exchange_fts. That is what the GUI search bar, crusader history search, and the MCP history.search tool query — the GUI does not expose raw SQL. For text matching, FTS is usually faster than a LIKE scan; reach for crusader sql when you need joins, aggregates, or columns FTS doesn't index.

Companion tables

The same database holds more than HTTP exchanges. Expect to see findings (the thin SQLite findings store), endpoint_meta, ws_frames (decoded WebSocket frames), beacon_hits / beacon_sessions / beacon_payloads (out-of-band callbacks), and request_activity. After a Burp import you'll also find burp_import_raw and burp_import_repeater. Always confirm the live set with crusader sql tables — it's the source of truth.

07Example queries

Copy-paste starting points. Each is a single read-only statement.

Newest 20 requests

crusader sql query "SELECT id, method, host, path, status FROM exchanges ORDER BY id DESC LIMIT 20"

Status distribution per host

crusader sql query "SELECT host, status, COUNT(*) AS n FROM exchanges GROUP BY host, status ORDER BY n DESC LIMIT 25"

Server errors that leak the word "error" in the body

crusader sql query "SELECT id, url FROM exchanges WHERE response_body LIKE '%error%' AND status >= 500 LIMIT 50"

Distinct endpoints behind a target host

crusader sql query "SELECT DISTINCT method, path FROM exchanges WHERE host LIKE '%target%' ORDER BY path LIMIT 200"

Duplicate responses by body hash (collapse the noise)

crusader sql query "SELECT body_hash, COUNT(*) AS n, MIN(url) AS sample FROM exchanges WHERE body_hash != '' GROUP BY body_hash HAVING n > 1 ORDER BY n DESC LIMIT 25"

08External read-only access

It's a normal SQLite file, so any SQLite client can read it — the sqlite3 CLI, a GUI like DB Browser for SQLite, or your language's SQLite driver. The database lives under your workspace:

Agent memory is a sibling file (agent_memory.db) in the same directory — a separate database, not part of exchanges. The whole workspace relocates if you set CRUSADER_HOME.

# ad-hoc read-only query with the sqlite3 CLI
sqlite3 ~/.crusader/history.db "SELECT url, status, COUNT(*) FROM exchanges WHERE host LIKE '%target%' GROUP BY url, status ORDER BY 3 DESC LIMIT 20;"

Want a guide that isn't here yet? Email hello@crusaderproxy.com.