SQL functions

sqx is SQLite plus extra functions for URLs, HTTP, HTML, DNS, regular expressions and hashing. Every built-in SQLite function, including the JSON functions, works too.

URL functions

These parse URLs locally and never touch the network.

Function	Returns	Example
`url_host(url)` `url_domain(url)`	Lowercase hostname	`'https://Sub.Example.com:8080/a'` → `sub.example.com`
`url_scheme(url)`	Scheme	`'https://example.com'` → `https`
`url_path(url)`	Path (`/` if empty)	`'https://example.com/blog?x=1'` → `/blog`
`url_query(url, key)`	One query parameter	`('https://x.com/?id=42', 'id')` → `42`
`url_ext(url)`	Lowercase file extension	`'https://cdn.com/app.min.js?v=2'` → `.js`
`url_norm(url)`	Lowercase host, no fragment, sorted query	`'https://example.com/?b=2&a=1#top'` → `https://example.com/?a=1&b=2`
`url_join(base, ref)`	Resolved URL	`('https://a.com/dir/page.html', '../img.png')` → `https://a.com/img.png`

sqx "SELECT url_host('https://Sub.Example.com:8080/a') AS host,
            url_norm('https://example.com/?b=2&a=1#top') AS normalized"

HTTP functions

Each URL is fetched once and stored in the cache, however many of these functions you call.

Function	Returns
`http_status(url)`	Status code, or `0` on a network error
`http_body(url)`	Response body
`http_header(url, name)`	One header (case-insensitive)
`http_headers_json(url)`	All headers as a JSON object, names in lowercase
`http_time_ms(url)`	Fetch duration in milliseconds
`http_final_url(url)`	URL after redirects
`http_size(url)`	Body size in bytes
`http_error(url)`	Error message, or `NULL`

Check security headers for a list of sites:

cat urls.txt | sqx -i lines "
  SELECT url, http_header(url, 'strict-transport-security') AS hsts,
         http_header(url, 'content-security-policy') IS NOT NULL AS has_csp
  FROM stdin"

HTML functions

Each function accepts either a URL, fetched through the cache, or a raw HTML string.

Function	Returns
`html_title(x)`	Text of `<title>`
`html_meta(x, key)`	`content` of `<meta name\|property\|http-equiv="key">`
`html_text(x)`	Plain text without tags, scripts, styles, `noscript`, comments or the doctype
`html_links(x)`	JSON array of `<a href>` links. Made absolute when `x` is a URL; `#anchors` and `javascript:` links are skipped.
`html_tag_texts(x, tag)`	JSON array of the inner text of every `<tag>`
`html_attr(x, tag, attr)`	JSON array of attribute values

Combine the JSON-array functions with json_each(...) to get one row per item, or json_array_length(...) to count them:

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"

DNS and network functions

Function	Returns
`dns_ip(host_or_url)`	First resolved address
`dns_ips_json(host_or_url)`	JSON array of every address
`dns_txt_json(host_or_url)`	JSON array of TXT records (SPF, DMARC, verification tokens)
`ip_is_private(ip)`	`1` for loopback, private or link-local addresses, otherwise `0`

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"

Regular expressions

Patterns use Go RE2 syntax. Add (?i) at the start for a case-insensitive match.

Function	Returns
`text REGEXP pattern`	`1` on a match
`re_match(text, pattern)`	Same as `REGEXP`, written as a function
`re_extract(text, pattern, group)`	One capture group (`0` is the whole match)
`re_extract_all(text, pattern)`	JSON array of every match

cat data/access_log.csv | sqx "
  SELECT ts, client_ip, request_url FROM stdin
  WHERE request_url REGEXP '(?i)(\.\./|%27|/etc/passwd)'"

Hashing and entropy

Function	Returns
`sha256(text)`	Hex SHA-256 digest
`shannon_entropy(text)`	Entropy in bits per character. Random tokens and secrets score high.

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"