Exclude Precertificate

[Shubham Agarwal]

Is there any method/url using which I can exclude pre-certificate while searching for any domain on crt.sh ?

[Rufus Buschart]

To my knowlege not in the web front end. You can do so if you directly connect to the DB and execute your queries there. But be aware: There should be no reason to exclude Pre-Certificates since they are an oath by the CA to issue the corresponding 'leaf' certificate.

[Jürgen Auer]

There are (minimal) two different tools, both created from users from the Letsencrypt forum ( https://community.letsencrypt.org/ ).

https://tools.letsdebug.net/cert-search - user @_az - uses crt.sh as source.

https://check-your-website.server-daten.de/ - own tool - Certspotter + crt.sh as source.

Both aggregate pre- and leaf-certificates.

Am Montag, 15. Juli 2019 11:25:13 UTC+2 schrieb Shubham Agarwal:

[Rob Stradling]

I just added a "Deduplicate (pre)certificate pairs?" checkbox to crt.sh's Advanced Options.

[Steven]

When I use the "deduplicate (pre)certificate pairs" option, I have only found the pre-certificate, not the leaf certificate. Is that the desired output?  Or should "ORDER BY c2.ID DESC" or something similar be added to the query so that the leaf certificate is preferred?

[r...@sectigo.com]

Hi Steven.

This is the relevant part of the query:

                      AND NOT EXISTS (

                          SELECT 1

                              FROM certificate c2

                              WHERE x509_serialNumber(c2.CERTIFICATE) = x509_serialNumber(cai.CERTIFICATE)

                                  AND c2.ISSUER_CA_ID = cai.ISSUER_CA_ID

                                  AND c2.ID < cai.CERTIFICATE_ID

                                  AND x509_tbscert_strip_ct_ext(c2.CERTIFICATE) = x509_tbscert_strip_ct_ext(cai.CERTIFICATE)

                              LIMIT 1

                      )

It ensures that everything except the lowest crt.sh ID in the set (of precertificate and/or corresponding certificate) is filtered out.

Since crt.sh IDs are allocated incrementally, and since precertificates exist before their corresponding certificates, it's not surprising (although it's certainly not guaranteed) that a precertificate will tend to have a lower crt.sh ID than the corresponding certificate.

> Is that the desired output?

In my opinion, yes.  Returning the first match gives a more predictable result, because you never know if/when a further match might become known to crt.sh.  I've found this predictability to be useful for some use cases.

You could change the "<" for ">" in that part of the query to make it always return the highest crt.sh ID.  To make the query always prefer a certificate (if known to the log) over the corresponding precertificate would require more coding effort.