"""email-auth-survey-sp500.py: what the S&P 500 companies publish for SPF and DMARC, read from public DNS. Three steps, each one reproducible from the files the previous step saved: domains the company list -> domains.csv. Reads a saved copy of the English Wikipedia article "List of S&P 500 companies" (table id="constituents": symbol, security, GICS sector, CIK) and of each company's own article, whose infobox "Website" field gives the company's web domain (the registrable part: www.example.com and investor.example.com both give example.com). Two share classes of one company (same CIK) count once. collect domains.csv -> answers.jsonl.gz. For each domain, two DNS TXT questions through one public recursive resolver: _dmarc. and itself (the SPF record), then the TXT record of every name that SPF record includes or redirects to, recursively. Each distinct name is asked once per run. At most 4 queries a second. Every answer is saved as the raw DNS reply (base64 wire format) with its UTC time. analyse domains.csv + answers.jsonl.gz -> aggregate CSV + printed report. Offline: it re-parses the saved replies and runs the record checks of spf-dkim-dmarc-explained-rfc-9989.py (same folder) over them. The output is aggregate only: shares of companies, overall and by GICS sector. It names no company. DKIM is not surveyed: a DKIM key lives at ._domainkey., and selectors cannot be listed from DNS. Run: python email-auth-survey-sp500.py domains --raw raw/wikipedia --out domains.csv python email-auth-survey-sp500.py collect --domains domains.csv --out answers.jsonl.gz [--resolver 1.1.1.1] python email-auth-survey-sp500.py analyse --domains domains.csv --answers answers.jsonl.gz --csv out.csv Standard library only. Tested with Python 3.13. Sends nothing but DNS TXT queries (UDP 53, TCP 53 when a reply is truncated) to the resolver you choose; it never connects to the companies' own servers. """ import argparse import base64 import collections import csv import datetime as dt import gzip import hashlib import html import importlib.util import json import os import random import re import socket import statistics import struct import sys import time import urllib.parse HERE = os.path.dirname(os.path.abspath(__file__)) _spec = importlib.util.spec_from_file_location("rfcreader", os.path.join(HERE, "spf-dkim-dmarc-explained-rfc-9989.py")) R = importlib.util.module_from_spec(_spec) _spec.loader.exec_module(R) SECOND_LEVEL = {"co", "com", "net", "org", "ac", "gov", "ne", "or"} # example.co.uk, example.com.au MIN_GAP = 0.25 # seconds between queries: <= 4 a second # ------------------------------------------------------------------------------------------ domains --- def _strip(s): return re.sub(r"\s+", " ", html.unescape(re.sub(r"<[^>]+>", "", s))).strip() def parse_list(page): m = re.search(r']*id="constituents".*?', page, re.S) rows = [] for tr in re.findall(r"]*>(.*?)", m[0], re.S)[1:]: tds = re.findall(r"]*>(.*?)", tr, re.S) if len(tds) < 7: continue link = re.search(r'href="(?:https://en\.wikipedia\.org)?/wiki/([^"#]+)"', tds[1]) rows.append({"symbol": _strip(tds[0]), "security": _strip(tds[1]), "gics_sector": _strip(tds[2]), "cik": _strip(tds[6]).zfill(10), "article": urllib.parse.unquote(html.unescape(link[1])) if link else ""}) rev = re.search(r'"wgRevisionId":(\d+)', page) return rows, rev[1] if rev else "" def infobox_website(page): start = re.search(r']*class="infobox-label"[^>]*>\s*(?:<[^>]+>)*\s*Website\s*(?:<[^>]+>)*\s*\s*]*>(.*?)', page[start.start():], re.S) if not m: return "", "no Website row" a = re.search(r'href="((?:https?:)?//[^"]+)"', m[1]) if not a: return "", "no link" u = html.unescape(a[1]) return (u if u.startswith("http") else "https:" + u), "" def registrable(url): host = (urllib.parse.urlsplit(url).hostname or "").lower().rstrip(".") labels = host.split(".") n = 3 if len(labels) >= 3 and len(labels[-1]) == 2 and labels[-2] in SECOND_LEVEL else 2 return ".".join(labels[-n:]) if len(labels) >= 2 else "" def article_file(title): return "article_" + hashlib.sha1(title.encode("utf-8")).hexdigest()[:16] + ".html.gz" def build_domains(raw_dir): page = gzip.open(os.path.join(raw_dir, "list_of_sp500_companies.html.gz")).read().decode("utf-8") rows, rev = parse_list(page) out, seen = [], {} for r in rows: if r["cik"] in seen: seen[r["cik"]]["symbols"] += " " + r["symbol"] continue fp = os.path.join(raw_dir, article_file(r["article"])) art = gzip.open(fp).read().decode("utf-8") if os.path.exists(fp) else "" site, why = infobox_website(art) if art else ("", "article not fetched") rec = {"cik": r["cik"], "symbols": r["symbol"], "security": r["security"], "gics_sector": r["gics_sector"], "article": r["article"], "article_file": article_file(r["article"]), "website": site, "domain": registrable(site) if site else "", "note": why} seen[r["cik"]] = rec out.append(rec) return out, rev, len(rows) def write_domains(rows, path): with open(path, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=list(rows[0])) w.writeheader() w.writerows(rows) # -------------------------------------------------------------------------------------------- DNS --- def query_wire(name, resolver, timeout=5.0): """One TXT question; returns (transport, raw reply bytes).""" qid = random.randrange(65536) q = struct.pack(">HHHHHH", qid, 0x0100, 1, 0, 0, 0) + R._encode_name(name) + struct.pack(">HH", 16, 1) with socket.socket(socket.AF_INET, socket.SOCK_DGRAM) as s: s.settimeout(timeout) s.sendto(q, (resolver, 53)) msg = s.recv(4096) transport = "udp" if struct.unpack(">H", msg[2:4])[0] & 0x0200: with socket.create_connection((resolver, 53), timeout=timeout) as s: s.sendall(struct.pack(">H", len(q)) + q) need = struct.unpack(">H", R._recv_exact(s, 2))[0] msg = R._recv_exact(s, need) transport = "tcp" if struct.unpack(">H", msg[:2])[0] != qid: raise RuntimeError("DNS reply id mismatch") return transport, msg def parse_wire(msg): """(rcode, [txt, ...]) from a raw reply: the same decoding as dns_txt() in the RFC reader.""" flags, qd, an = struct.unpack(">HHH", msg[2:8]) pos = 12 for _ in range(qd): pos = R._skip_name(msg, pos) + 4 txts = [] for _ in range(an): pos = R._skip_name(msg, pos) rtype, _c, _t, rdlen = struct.unpack(">HHIH", msg[pos:pos + 10]) pos += 10 rdata = msg[pos:pos + rdlen] pos += rdlen if rtype != 16: continue parts, i = [], 0 while i < len(rdata): parts.append(rdata[i + 1:i + 1 + rdata[i]]) i += 1 + rdata[i] txts.append(b"".join(parts).decode("utf-8", "replace")) return flags & 0x000F, txts class Recorder: """A lookup() for the RFC reader that asks the resolver once per name and appends every reply to a log.""" def __init__(self, resolver, out): self.resolver, self.out, self.cache, self.last = resolver, out, {}, 0.0 def __call__(self, name): key = name.lower().rstrip(".") if key in self.cache: return self.cache[key] rec = {"name": key, "type": "TXT", "resolver": self.resolver} for attempt in range(3): wait = self.last + MIN_GAP - time.monotonic() if wait > 0: time.sleep(wait) self.last = time.monotonic() rec["utc"] = dt.datetime.now(dt.timezone.utc).strftime("%Y-%m-%dT%H:%M:%S.%fZ") try: rec["transport"], msg = query_wire(key, self.resolver) rec["wire_b64"] = base64.b64encode(msg).decode() rec.pop("error", None) break except (OSError, RuntimeError, ValueError) as e: rec["error"] = f"{type(e).__name__}: {e}" if isinstance(e, ValueError): break time.sleep(2.0 * (attempt + 1)) rec["attempts"] = attempt + 1 self.out.write(json.dumps(rec) + "\n") self.cache[key] = parse_wire(base64.b64decode(rec["wire_b64"])) if "wire_b64" in rec else (-1, []) return self.cache[key] def collect(domains_csv, out_path, resolver): doms = sorted({r["domain"] for r in csv.DictReader(open(domains_csv, encoding="utf-8")) if r["domain"]}) with gzip.open(out_path, "wt", encoding="utf-8") as out: rec = Recorder(resolver, out) for i, d in enumerate(doms, 1): rec("_dmarc." + d) R.check_spf(d, rec) if i % 50 == 0: print(f"{i}/{len(doms)} domains, {len(rec.cache)} names asked", flush=True) print(f"collect: {len(doms)} domains, {len(rec.cache)} distinct names asked, resolver {resolver}") # ---------------------------------------------------------------------------------------- analyse --- class Replay: """lookup() served from the saved replies; notes when a domain's check touched a failed query.""" def __init__(self, answers_path): self.ans, self.meta = {}, {} for line in gzip.open(answers_path, "rt", encoding="utf-8"): r = json.loads(line) self.ans[r["name"]] = parse_wire(base64.b64decode(r["wire_b64"])) if "wire_b64" in r else None self.meta[r["name"]] = r self.failed = False def __call__(self, name): key = name.lower().rstrip(".") if key not in self.ans: raise KeyError(f"{key} was not asked in this run") if self.ans[key] is None or self.ans[key][0] == 2: # no reply, or SERVFAIL self.failed = True return (-1, []) return self.ans[key] def dmarc_facts(domain, lookup): rcode, txts = lookup("_dmarc." + domain) looks = [t for t in txts if re.match(r"\s*v\s*=\s*dmarc1", t, re.I)] valid = [R.parse_dmarc(t) for t in txts] valid = [v for v in valid if v is not None] f = {"status": "none"} if len(valid) > 1: f["status"] = "multiple" # RFC 9989: several records = no record elif len(valid) == 1: f["status"] = "valid" elif looks: f["status"] = "malformed" # starts like DMARC but fails the v=DMARC1-first rule if f["status"] != "valid": return f t = valid[0] rua = [u for u in R.dmarc_uris(t.get("rua")) if re.match(r"[A-Za-z][A-Za-z0-9+.-]*:", u)] p = t.get("p", "").lower() f["p"] = p if p in R.DMARC_VALUES["p"] else "missing or invalid" f["p_effective"] = f["p"] if f["p"] in R.DMARC_VALUES["p"] else ("none" if rua else "no policy") if t.get("t", "n").lower() == "y" and f["p_effective"] in ("quarantine", "reject"): f["p_effective"] = "none" if f["p_effective"] == "quarantine" else "quarantine" sp = t.get("sp", "").lower() f["sp"] = sp if sp in R.DMARC_VALUES["sp"] else ("" if "sp" not in t else "invalid") f["pct"] = t.get("pct") f["rua"] = bool(rua) hosts = [u.split("@", 1)[1].split("!")[0].strip().lower().rstrip(".") for u in rua if u.lower().startswith("mailto:") and "@" in u] f["rua_external"] = bool(hosts) and any(not (h == domain or h.endswith("." + domain)) for h in hosts) f["ruf"] = bool(R.dmarc_uris(t.get("ruf"))) for k in ("t", "np", "psd"): f["tag_" + k] = k in t f["adkim_s"] = t.get("adkim", "r").lower() == "s" f["aspf_s"] = t.get("aspf", "r").lower() == "s" f["historic"] = sorted(k for k in R.DMARC_HISTORIC if k in t) f["bad_value"] = any(k in t and t[k].lower() not in allowed for k, allowed in R.DMARC_VALUES.items()) return f def spf_ending(domain, lookup, hops=0): """The 'all' that ends evaluation for an unlisted sender, following redirect= when there is no 'all'.""" rcode, txts = lookup(domain) recs = R.spf_records(txts) if not recs or hops > 10: return None try: terms = R.parse_spf(recs[0]) except ValueError: return None for q, name, arg, is_mod in terms: if name == "all" and not is_mod: return q + "all" red = [arg for q, name, arg, is_mod in terms if is_mod and name == "redirect"] return spf_ending(red[0], lookup, hops + 1) if red and "%" not in red[0] else None def spf_facts(domain, lookup): rcode, txts = lookup(domain) recs = R.spf_records(txts) f = {"status": "none" if not recs else "multiple" if len(recs) > 1 else "one"} if not recs: return f res = R.check_spf(domain, lookup) probs = res["problems"] f["syntax_error"] = any("syntax error" in p for p in probs) f["missing_include"] = any("as an include or redirect target" in p for p in probs) f["lookups"] = res["lookups"] f["over10"] = res["lookups"] > 10 f["void_over2"] = res["void"] > 2 f["ptr"] = any("'ptr' SHOULD NOT" in p for p in probs) f["ending"] = spf_ending(domain, lookup) or "no all" f["permerror"] = f["status"] == "multiple" or f["syntax_error"] or f["missing_include"] or f["over10"] return f def analyse(domains_csv, answers_path): rows = [r for r in csv.DictReader(open(domains_csv, encoding="utf-8"))] rp = Replay(answers_path) out, seen = [], set() for r in rows: if not r["domain"]: out.append({**r, "excluded": "no website domain"}) continue if r["domain"] in seen: # e.g. a spun-off company still listing its parent's website out.append({**r, "excluded": "domain shared with a company listed earlier"}) continue seen.add(r["domain"]) rp.failed = False d, s = dmarc_facts(r["domain"], rp), spf_facts(r["domain"], rp) out.append({**r, "excluded": "DNS lookup failed" if rp.failed else "", "dmarc": d, "spf": s}) return out, rp def pct(n, d): return 100.0 * n / d if d else 0.0 def metrics(group): """(metric, count, denominator) for one group of surveyed companies.""" n = len(group) dm = [c["dmarc"] for c in group] va = [d for d in dm if d["status"] == "valid"] sp = [c["spf"] for c in group] sp1 = [s for s in sp if s["status"] != "none"] m = [("companies", n, n), ("dmarc_valid_record", len(va), n), ("dmarc_no_record", sum(d["status"] == "none" for d in dm), n), ("dmarc_record_error", sum(d["status"] in ("multiple", "malformed") for d in dm), n)] for p in ("reject", "quarantine", "none", "missing or invalid"): m.append((f"dmarc_p_{p.replace(' ', '_')}", sum(d["p"] == p for d in va), n)) for p in ("reject", "quarantine"): m.append((f"dmarc_enforcing_{p}_effective", sum(d["p_effective"] == p for d in va), n)) m += [("dmarc_pct_present", sum(d["pct"] is not None for d in va), len(va)), ("dmarc_pct_100", sum(d["pct"] == "100" for d in va), len(va)), ("dmarc_pct_0", sum(d["pct"] == "0" for d in va), len(va)), ("dmarc_pct_other", sum(d["pct"] not in (None, "100", "0") for d in va), len(va)), ("dmarc_rua", sum(d["rua"] for d in va), len(va)), ("dmarc_rua_external", sum(d["rua_external"] for d in va), len(va)), ("dmarc_ruf", sum(d["ruf"] for d in va), len(va)), ("dmarc_tag_t", sum(d["tag_t"] for d in va), len(va)), ("dmarc_tag_np", sum(d["tag_np"] for d in va), len(va)), ("dmarc_tag_psd", sum(d["tag_psd"] for d in va), len(va)), ("dmarc_historic_rf_or_ri", sum(any(k in ("rf", "ri") for k in d["historic"]) for d in va), len(va)), ("dmarc_sp_weaker_than_p", sum(bool(d["sp"]) and d["sp"] in ("none", "quarantine", "reject") and ["none", "quarantine", "reject"].index(d["sp"]) < ["none", "quarantine", "reject"].index(d["p"]) for d in va if d["p"] in ("none", "quarantine", "reject")), len(va)), ("dmarc_adkim_strict", sum(d["adkim_s"] for d in va), len(va)), ("dmarc_aspf_strict", sum(d["aspf_s"] for d in va), len(va)), ("dmarc_bad_tag_value", sum(d["bad_value"] for d in va), len(va)), ("spf_record", len(sp1), n), ("spf_no_record", sum(s["status"] == "none" for s in sp), n), ("spf_multiple_records", sum(s["status"] == "multiple" for s in sp), n)] for e in ("-all", "~all", "?all", "+all", "no all"): m.append((f"spf_ending_{e}", sum(s.get("ending") == e for s in sp1), len(sp1))) m += [("spf_over_10_lookups", sum(s["over10"] for s in sp1), len(sp1)), ("spf_9_or_10_lookups", sum(s["lookups"] in (9, 10) for s in sp1), len(sp1)), ("spf_syntax_error", sum(s["syntax_error"] for s in sp1), len(sp1)), ("spf_include_without_record", sum(s["missing_include"] for s in sp1), len(sp1)), ("spf_permerror_any", sum(s["permerror"] for s in sp1), len(sp1)), ("spf_void_over_2", sum(s["void_over2"] for s in sp1), len(sp1)), ("spf_ptr", sum(s["ptr"] for s in sp1), len(sp1)), ("neither_spf_nor_dmarc", sum(c["dmarc"]["status"] == "none" and c["spf"]["status"] == "none" for c in group), n), ("both_reject_and_spf_minus_all", sum(c["dmarc"].get("p") == "reject" and c["spf"].get("ending") == "-all" for c in group), n)] return m def lookups_median(group): v = [c["spf"]["lookups"] for c in group if c["spf"]["status"] != "none"] return statistics.median(v) if v else 0 def report(rows, rp, csv_path=None): surveyed = [c for c in rows if not c.get("excluded")] sectors = sorted({c["gics_sector"] for c in surveyed}) groups = [("all", surveyed)] + [(s, [c for c in surveyed if c["gics_sector"] == s]) for s in sectors] times = sorted(m["utc"] for m in rp.meta.values()) read = times[0][:10] # the day the DNS answers were read table = [] for g, members in groups: for metric, k, d in metrics(members): table.append({"group": g, "metric": metric, "count": k, "of": d, "share_pct": f"{pct(k, d):.1f}", "retrieved": read}) table.append({"group": g, "metric": "spf_lookups_median", "count": lookups_median(members), "of": "", "share_pct": "", "retrieved": read}) if csv_path: with open(csv_path, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=["group", "metric", "count", "of", "share_pct", "retrieved"], lineterminator="\n") w.writeheader() w.writerows(table) excl = collections.Counter(c["excluded"] for c in rows if c.get("excluded")) print(f"answers: {len(rp.meta)} names asked, {times[0][:19]}Z to {times[-1][:19]}Z, " f"{sum('error' in m for m in rp.meta.values())} with no reply, {sum(m.get('transport') == 'tcp' for m in rp.meta.values())} over TCP") print(f"companies: {len(rows)} listed, {len(surveyed)} surveyed, excluded: {dict(excl) or 'none'}") print(f"distinct domains surveyed: {len({c['domain'] for c in surveyed})}") a = {r["metric"]: r for r in table if r["group"] == "all"} for k in a: r = a[k] print(f" {k:34s} {r['count']:>5} / {r['of']:<5} {r['share_pct']}%" if r["of"] != "" else f" {k:34s} {r['count']}") print("by sector (companies | DMARC reject | quarantine | none | no valid record | SPF -all | SPF over 10 lookups):") for g, members in groups[1:]: mm = {x[0]: x for x in metrics(members)} n = len(members) cell = lambda key: f"{pct(mm[key][1], mm[key][2]):.0f}%" print(f" {g:24s} {n:>3} | {cell('dmarc_p_reject')} | {cell('dmarc_p_quarantine')} | {cell('dmarc_p_none')} | " f"{pct(n - mm['dmarc_valid_record'][1], n):.0f}% | {cell('spf_ending_-all')} | {cell('spf_over_10_lookups')}") return table def main(): ap = argparse.ArgumentParser(description=__doc__.split("\n")[0]) sub = ap.add_subparsers(dest="cmd", required=True) a = sub.add_parser("domains") a.add_argument("--raw", required=True) a.add_argument("--out", required=True) b = sub.add_parser("collect") b.add_argument("--domains", required=True) b.add_argument("--out", required=True) b.add_argument("--resolver", default="1.1.1.1") c = sub.add_parser("analyse") c.add_argument("--domains", required=True) c.add_argument("--answers", required=True) c.add_argument("--csv") args = ap.parse_args() if args.cmd == "domains": rows, rev, n = build_domains(args.raw) write_domains(rows, args.out) print(f"list revision {rev}: {n} rows, {len(rows)} companies, {sum(bool(r['domain']) for r in rows)} with a domain, " f"{len({r['domain'] for r in rows if r['domain']})} distinct domains") elif args.cmd == "collect": collect(args.domains, args.out, args.resolver) else: rows, rp = analyse(args.domains, args.answers) report(rows, rp, args.csv) if __name__ == "__main__": main()