You should also filter out expired certs as they aren't usable.
> On Jul 19, 2017, at 8:30 AM, Alex Gaynor via dev-security-policy > <dev-security-policy@lists.mozilla.org> wrote: > > I think there might be a bug in your SQL, one of the offending certs is > issued by "C=US, O=U.S. Government, OU=Department of Homeland Security, > OU=Certification Authorities, OU=DHS CA4", who are revoked using OneCRL. > > Alex > > On Wed, Jul 19, 2017 at 10:08 AM, Rob Stradling via dev-security-policy < > dev-security-policy@lists.mozilla.org> wrote: > >> On 18/07/17 16:57, Hanno Böck via dev-security-policy wrote: >> <snip> >> >>> (Due to limitations in the search methodology - scraping crt.sh >>> search results and looping through tlds - I only searched for ..tld. It >>> would certainly be valuable to search further.) >>> >> >> Here's a report of all "double dot" certs known to crt.sh that are useable >> for server authentication and chain to a root trusted by Mozilla: >> >> https://docs.google.com/spreadsheets/d/18rvkdAd9A_N9_i2jIVhN >> QVWODGhRtIT1iYoVms7Wb2w/edit?usp=sharing >> >> >> P.S. >> For anyone interested, here's the SQL I executed on the crt.sh DB to >> produce this report: >> >> SELECT c.ID, x509_notBefore(c.CERTIFICATE), x509_notAfter(c.CERTIFICATE), >> array_to_string(array_agg(DISTINCT ci.NAME_VALUE), CHR(10)), ca.NAME >> FROM certificate_identity ci, ca, certificate c >> WHERE ci.NAME_VALUE LIKE '%..%' >> AND ci.NAME_TYPE IN ('dNSName', 'commonName') >> AND ci.ISSUER_CA_ID = ca.ID >> AND ci.CERTIFICATE_ID = c.ID >> AND EXISTS ( >> SELECT 1 >> FROM ca_trust_purpose ctp >> WHERE ci.ISSUER_CA_ID = ctp.CA_ID >> AND ctp.TRUST_PURPOSE_ID = 1 -- Server Authentication >> AND ctp.TRUST_CONTEXT_ID = 5 -- Mozilla >> ) >> AND x509_isEKUPermitted(c.CERTIFICATE, '1.3.6.1.5.5.7.3.1') >> GROUP BY c.ID, x509_notBefore(c.CERTIFICATE), >> x509_notAfter(c.CERTIFICATE), ci.NAME_VALUE, ca.NAME >> ORDER BY ca.NAME, x509_notAfter(c.CERTIFICATE) DESC; >> >> -- >> Rob Stradling >> Senior Research & Development Scientist >> COMODO - Creating Trust Online >> >> >> _______________________________________________ >> dev-security-policy mailing list >> dev-security-policy@lists.mozilla.org >> https://lists.mozilla.org/listinfo/dev-security-policy >> > _______________________________________________ > dev-security-policy mailing list > dev-security-policy@lists.mozilla.org > https://lists.mozilla.org/listinfo/dev-security-policy _______________________________________________ dev-security-policy mailing list dev-security-policy@lists.mozilla.org https://lists.mozilla.org/listinfo/dev-security-policy
