Search Cross Certificates in CCADB

search_cross_certificates_in_ccadb.py

import csv
import os
import re
from cryptography import x509
from cryptography.x509.oid import ExtensionOID

# Download from https://www.ccadb.org/resources
# TODO support to download and cache the CSV
CSV_PATH = "AllCertificateRecordsReport.csv"

with open(CSV_PATH, newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    rows = list(reader)

# Get all root certificates
root_names = {
    row["Certificate Name"]
    for row in rows
    if row["Certificate Record Type"] == "Root Certificate"
}

# And search intermediate certificates with names that match any of the root certificate names
matches = [
    row for row in rows
    if (
        row["Certificate Record Type"] == "Intermediate Certificate"
        and row["Certificate Name"] in root_names
    )
]

# Reorder by notBefore (most recent first)
matches.sort(key=lambda r: r["Valid From (GMT)"], reverse=True)

for row in matches:
    print(row["Certificate Name"] + " (notBefore: " + row["Valid From (GMT)"] + ") - " + "https://crt.sh/?q=" + row["SHA-256 Fingerprint"])