Unable to perform Like % query anymore

[asad ali]

Hello Folks,

I'm new to cert.sh, so please ignore my newbie query. I wanted to do MATCH LIKE query

with syntax as

Type: Common Name    Match: LIKE   vpn  Search: 'tesla.com'

when I run I get no results, which is not what I want I want all identities which has vpn keyword, if i do without the Match it works.

Any help

regards

Asad

[r...@sectigo.com]

Hi Asad.  The crt.sh web interface doesn't currently support that type of search.  However, you can slightly modify the SQL query that the web interface uses (click the "Show SQL?" option in the Advanced settings, then look at the bottom of the results page), and then run that query against crt.sh:5432 (see https://groups.google.com/g/crtsh/c/sUmV0mBz8bQ/m/K-6Vymd_AAAJ for connection details).

Try running the query below.  It uses the Full Text Search index to find all certs for tesla.com (including subdomains), and then it uses LIKE to filter out everything that doesn't contain "vpn".

WITH ci AS (
    SELECT min(sub.CERTIFICATE_ID) ID,
           min(sub.ISSUER_CA_ID) ISSUER_CA_ID,
           array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES,
           x509_commonName(sub.CERTIFICATE) COMMON_NAME,
           x509_notBefore(sub.CERTIFICATE) NOT_BEFORE,
           x509_notAfter(sub.CERTIFICATE) NOT_AFTER,
           encode(x509_serialNumber(sub.CERTIFICATE), 'hex') SERIAL_NUMBER
        FROM (SELECT cai.*
                  FROM certificate_and_identities cai
                  WHERE plainto_tsquery('certwatch', 'tesla.com') @@ identities(cai.CERTIFICATE)
                      AND cai.NAME_VALUE LIKE ('%vpn%.tesla.com')
                      AND cai.NAME_TYPE = '2.5.4.3' -- commonName
                  LIMIT 10000
             ) sub
        GROUP BY sub.CERTIFICATE
)
SELECT ci.ISSUER_CA_ID,
        ca.NAME ISSUER_NAME,
        array_to_string(ci.NAME_VALUES, chr(10)) NAME_VALUE,
        ci.ID ID,
        le.ENTRY_TIMESTAMP,
        ci.NOT_BEFORE,
        ci.NOT_AFTER,
        ci.SERIAL_NUMBER
    FROM ci
            LEFT JOIN LATERAL (
                SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP
                    FROM ct_log_entry ctle
                    WHERE ctle.CERTIFICATE_ID = ci.ID
            ) le ON TRUE,
         ca
    WHERE ci.ISSUER_CA_ID = ca.ID
    ORDER BY le.ENTRY_TIMESTAMP DESC NULLS LAST;