Selecting certificates based on revocation status

[Adriano Santoni]

Sorry if I am asking an old question, but I cannot find an answer in past emails.

What's the right way -in SQL - to select certificates that are (or are Not) revoked, regardless of other selection criteria?

TIA

Adriano

[r...@sectigo.com]

Hi Adriano.  There are multiple revocation methods available.  If "Revoked by CRL" is good enough for your needs, then...

crt.sh's crl_monitor application checks every known CRL every 4 hours, and stores the CRL entries in the "crl_revoked" table, which has CA_ID and SERIAL_NUMBER columns.

You can join "crl_revoked.CA_ID" to "certificate.ISSUER_CA_ID", and you can join "crl_revoked.SERIAL_NUMBER" to "x509_serialNumber(certificate.CERTIFICATE)".