Cookbook

Ready-to-run recipes. Each uses the sample files in the repository's data/ folder. Recipes marked network need internet access.

Data analysis

Revenue per customer from a CSV and a JSON Lines file

sqx -f users=data/users.csv -f events=data/events.jsonl "
  SELECT u.name, COUNT(e.id) AS events, ROUND(SUM(e.amount), 2) AS revenue
  FROM users u LEFT JOIN events e ON e.user_id = u.id
  GROUP BY u.id ORDER BY revenue DESC"

Events by country and device

cat data/events.jsonl | sqx -t events -f users=data/users.csv "
  SELECT u.country, json_extract(e.meta, '$.device') AS device, COUNT(*) AS n
  FROM events e JOIN users u ON u.id = e.user_id GROUP BY 1, 2"

Slow endpoints from a TSV

cat data/metrics.tsv | sqx "SELECT service, endpoint, latency_ms FROM stdin WHERE latency_ms > 300"

Totals from a headerless CSV

cat data/headerless.csv | sqx -no-header "SELECT c2 AS city, SUM(c3) AS orders FROM stdin GROUP BY c2"

HTML and content

Titles, descriptions and headings from stored pages

sqx -f pages=data/pages.jsonl "
  SELECT url_path(url) AS page, html_title(html) AS title,
         html_meta(html, 'description') AS description, html_tag_texts(html, 'h2') AS h2
  FROM pages"

Images missing alt text

sqx -f pages=data/pages.jsonl "
  SELECT url_path(url) AS page,
         json_array_length(html_attr(html, 'img', 'src')) AS images,
         json_array_length(html_attr(html, 'img', 'alt')) AS with_alt
  FROM pages"

Most-linked external sites

sqx -f pages=data/pages.jsonl "
  SELECT url_host(link.value) AS external_host, COUNT(*) AS links
  FROM pages p, json_each(html_links(p.html)) AS link
  WHERE link.value LIKE 'http%' AND url_host(link.value) != url_host(p.url)
  GROUP BY external_host ORDER BY links DESC"

Duplicate content

sqx -f pages=data/pages.jsonl "
  SELECT substr(sha256(html_text(html)), 1, 12) AS content_hash, COUNT(*) AS pages,
         GROUP_CONCAT(url_path(url), ' | ') AS urls
  FROM pages GROUP BY content_hash HAVING pages > 1"

Secrets, tracking IDs and logs

Email addresses exposed in pages

sqx -f pages=data/pages.jsonl "
  SELECT DISTINCT url_path(p.url) AS page, m.value AS email
  FROM pages p, json_each(re_extract_all(p.html, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}')) AS m"

Analytics IDs and leaked API keys

sqx -f pages=data/pages.jsonl "
  SELECT url_path(url) AS page,
         re_extract(html, '(UA-[0-9]+-[0-9]+|G-[A-Z0-9]+)', 1) AS tracking_id,
         re_extract(html, 'api_key=([A-Za-z0-9_]+)', 1) AS leaked_key
  FROM pages WHERE html REGEXP 'api_key=|UA-|G-'"

High-entropy tokens from public IPs

cat data/access_log.csv | sqx "
  SELECT client_ip, url_query(request_url, 'token') AS token,
         ROUND(shannon_entropy(url_query(request_url, 'token')), 2) AS entropy
  FROM stdin
  WHERE NOT ip_is_private(client_ip) AND url_query(request_url, 'token') IS NOT NULL
  ORDER BY entropy DESC"

Attack patterns and scanners in an access log

cat data/access_log.csv | sqx "
  SELECT ts, client_ip, user_agent, request_url FROM stdin
  WHERE request_url REGEXP '(?i)(\.\./|%27|or%201%3d1|/etc/passwd)'
     OR user_agent REGEXP '(?i)sqlmap|nikto|python-requests'"

Website audit network

Warm the cache

cat data/urls.txt | sqx prefetch -c 8

Status, timing, redirects and content type

cat data/urls.txt | sqx -i lines "
  SELECT url, http_status(url) AS status, http_time_ms(url) AS ms,
         http_final_url(url) AS final_url, http_header(url, 'content-type') AS type,
         html_title(url) AS title, http_error(url) AS error
  FROM stdin ORDER BY status"

SEO: meta descriptions and H1 count

cat data/urls.txt | sqx -i lines "
  SELECT url, html_meta(url, 'description') AS description,
         json_array_length(html_tag_texts(url, 'h1')) AS h1_count
  FROM stdin WHERE http_status(url) = 200"

Outbound link graph

cat data/urls.txt | sqx -i lines "
  SELECT url_host(stdin.url) AS source, url_host(link.value) AS target, COUNT(*) AS links
  FROM stdin, json_each(html_links(stdin.url)) AS link
  WHERE url_host(link.value) != url_host(stdin.url)
  GROUP BY 1, 2 ORDER BY links DESC LIMIT 15"

Duplicate pages across sites

cat data/urls.txt | sqx -i lines "
  SELECT sha256(html_text(url)) AS hash, COUNT(*) AS pages, GROUP_CONCAT(url, ' | ') AS urls
  FROM stdin WHERE http_status(url) = 200 GROUP BY hash HAVING pages > 1"

DNS and email security network

SPF and DMARC for a list of domains

cat data/companies.csv | sqx "
  SELECT company, domain, dns_ip(domain) AS ip,
         CASE WHEN dns_txt_json(domain) LIKE '%v=spf1%' THEN 'yes' ELSE 'MISSING' END AS spf,
         CASE WHEN dns_txt_json('_dmarc.' || domain) LIKE '%v=DMARC1%' THEN 'yes' ELSE 'MISSING' END AS dmarc
  FROM stdin"

Hosts that resolve to private IPs

cat data/urls.txt | sqx -i lines "
  SELECT url_host(url) AS host, dns_ip(url) AS ip, ip_is_private(dns_ip(url)) AS is_private
  FROM stdin GROUP BY host"

Persistent workspace and broken-link report

sqx import -db output/project.db -t users    data/users.csv
sqx import -db output/project.db -t events   data/events.jsonl
sqx import -db output/project.db -t sitemaps data/sitemaps.csv
sqx import -db output/project.db -t targets  data/targets.jsonl
sqx tables -db output/project.db

Join sitemaps with live HTTP results network and export a Markdown report:

sqx -db output/project.db -save broken_links "
  SELECT t.category, t.owner, s.loc AS url, http_status(s.loc) AS status, http_error(s.loc) AS error
  FROM sitemaps s JOIN targets t ON url_host(s.loc) = t.domain
  WHERE http_status(s.loc) >= 400 OR http_status(s.loc) = 0"

sqx -db output/project.db -o md "SELECT * FROM broken_links ORDER BY status DESC" > output/broken_links.md

Run every example

The repository includes a script that runs all of these recipes and prints each command first:

./scripts/run_examples.sh
./scripts/run_examples.sh --online