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
