Re: New feature: Direct access to the crt.sh DB

[=JeffH]

> On Wednesday, June 7, 2017 at 7:27:34 AM UTC-7, Rob Stradling wrote:
>
> The https://crt.sh/ web interface makes it easy to do simple queries,
> but it's not flexible enough to support all sorts of advanced
> queries. So we've spun up some additional instances of the crt.sh DB
> that can be publicly accessed directly.
>
> If you have the PostgreSQL client software installed, you can login
> as follows: $ psql -h crt.sh -p 5432 -U guest certwatch

so PostgreSQL has some global directory service such that the client can
find "certwatch" with only the latter name?


> Feel free to poke around and run some queries. :-)
>
> BTW, if you need any SQL inspiration, try appending "&showSQL=y" to
> any search URL. e.g., https://crt.sh/?q=www.comodo.com&showSQL=Y

cool :)


> Any questions, please ask here.

Ok -- I'd like to search for all certs issued with a particular
substring in "Identity" and not from a given "CA ID" -- how might one do
that?

And is it possible to do that via the web interface?

thanks!

=JeffH

[Rob Stradling]

On Monday, June 12, 2017 at 7:52:49 PM UTC+1, =JeffH wrote:

> On Wednesday, June 7, 2017 at 7:27:34 AM UTC-7, Rob Stradling wrote:
 >
 > The https://crt.sh/ web interface makes it easy to do simple queries,
 > but it's not flexible enough to support all sorts of advanced
 > queries.  So we've spun up some additional instances of the crt.sh DB
 > that can be publicly accessed directly.
 >
 > If you have the PostgreSQL client software installed, you can login
 > as follows: $ psql -h crt.sh -p 5432 -U guest certwatch

so PostgreSQL has some global directory service such that the client can
find "certwatch" with only the latter name?

No, "certwatch" is the local database name.  "-h crt.sh" and "-p 5432" specify the internet host/port to connect to.

 

 > Feel free to poke around and run some queries.  :-)
 >
 > BTW, if you need any SQL inspiration, try appending "&showSQL=y" to
 > any search URL.  e.g., https://crt.sh/?q=www.comodo.com&showSQL=Y

cool :)

 > Any questions, please ask here.

Ok -- I'd like to search for all certs issued with a particular
substring in "Identity" and not from a given "CA ID" -- how might one do
that?

This query searches for all certs that have one or more Identity substrings of "comodo" and that were not issued by CA IDs 1113 or 43:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (1113,43)

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

And is it possible to do that via the web interface?

You can add "&excludeCAs=<comma-separated list of CA IDs>" to various sorts of search URL.

e.g., Compare these two searches:

https://crt.sh/?q=secure.comodo.com

https://crt.sh/?q=secure.comodo.com&excludeCAs=1113,43

The web interface can handle prefix and suffix searches well, but substring searches are problematic because no index can be used.  Without an index, the query will fall foul of the automatic query killer (an every-minute cron job that terminates any query that has been running for over a minute).

The automatic query killer doesn't run on the direct-access crt.sh DB instances, so long-running queries should run to completion.  The query above currently takes about 17 minutes to complete.

thanks!

=JeffH

[=JeffH]

On Tuesday, June 13, 2017 at 5:05:49 AM UTC-7, Rob Stradling wrote:

On Monday, June 12, 2017 at 7:52:49 PM UTC+1, =JeffH wrote:

> On Wednesday, June 7, 2017 at 7:27:34 AM UTC-7, Rob Stradling wrote:
 >
 > The https://crt.sh/ web interface makes it easy to do simple queries,
 > but it's not flexible enough to support all sorts of advanced
 > queries.  So we've spun up some additional instances of the crt.sh DB
 > that can be publicly accessed directly.
 >
 > If you have the PostgreSQL client software installed, you can login
 > as follows: $ psql -h crt.sh -p 5432 -U guest certwatch

so PostgreSQL has some global directory service such that the client can
find "certwatch" with only the latter name?

No, "certwatch" is the local database name.  "-h crt.sh" and "-p 5432" specify the internet host/port to connect to.

doh!  but of course.... how silly of me....   8^}

btw, if one is on a Mac OSX, installs homebrew, then does `brew install postgresql` at the command line, then one will end up with `/usr/local/bin/psql`

 > Feel free to poke around and run some queries.  :-)
 >
 > BTW, if you need any SQL inspiration, try appending "&showSQL=y" to
 > any search URL.  e.g., https://crt.sh/?q=www.comodo.com&showSQL=Y

cool :)

 > Any questions, please ask here.

Ok -- I'd like to search for all certs issued with a particular
substring in "Identity" and not from a given "CA ID" -- how might one do
that?

This query searches for all certs that have one or more Identity substrings of "comodo" and that were not issued by CA IDs 1113 or 43:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (1113,43)

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

thanks!


=JeffH

[=JeffH]

 > BTW, if you need any SQL inspiration, try appending "&showSQL=y" to
 > any search URL.  e.g., https://crt.sh/?q=www.comodo.com&showSQL=Y

Seems like it is case sensitive and it really wants a capital "Y", as in the example URL above.

i.e. "&showSQL=Y"

HTH,

=JeffH

[=JeffH]

On Tuesday, June 13, 2017 at 5:05:49 AM UTC-7, Rob Stradling wrote:

This query searches for all certs that have one or more Identity substrings of "comodo" and that were not issued by CA IDs 1113 or 43:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (1113,43)

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

 so the above works for me, thanks! Now I'd like to update the query to be something like this, but my SQL fu is very lacking:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (

           SELECT ca.ID
              FROM ca
               WHERE lower(ca.NAME) LIKE "%foo%"
            OR
            SELECT ca.ID
              FROM ca
               WHERE lower(ca.NAME) LIKE "%bar%"
           )

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

anyone have any tips?

THANKS,

=JeffH

[Rob Stradling]

Fixed.  It'll accept y or Y now.

[Rob Stradling]

On Thursday, June 15, 2017 at 1:24:38 AM UTC+1, =JeffH wrote:

<snip> 

 so the above works for me, thanks! Now I'd like to update the query to be something like this, but my SQL fu is very lacking:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (
           SELECT ca.ID
              FROM ca
               WHERE lower(ca.NAME) LIKE "%foo%"
            OR
            SELECT ca.ID
              FROM ca
               WHERE lower(ca.NAME) LIKE "%bar%"
           )

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

anyone have any tips?

Try this:

SELECT ci.ISSUER_CA_ID, ci.CERTIFICATE_ID, min(ci.NAME_VALUE)

    FROM certificate_identity ci

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (
            SELECT ca.ID
                FROM ca
                WHERE lower(ca.NAME) LIKE '%foo%'

                    OR lower(ca.NAME) LIKE '%bar%'
           )

[Hodges, Jeff]

thanks! that works :)

[=JeffH]

How might one go about enhancing the below SELECT where clause to also factor-in having `ct_log_entry.ENTRY_TIMESTAMP` be after a given date, such as for example 31-Mar-2017 ?

thanks!

[Rob Stradling]

On Monday, June 19, 2017 at 10:32:19 PM UTC+1, =JeffH wrote:

How might one go about enhancing the below SELECT where clause to also factor-in having `ct_log_entry.ENTRY_TIMESTAMP` be after a given date, such as for example 31-Mar-2017 ?

Hi Jeff.  A cert can be (and frequently is) submitted to multiple CT logs, and so it will have multiple ct_log_entry records.  I presume you're only interested in the earliest ENTRY_TIMESTAMP for each cert.  Try this:

SELECT ci.ISSUER_CA_ID,

       ci.CERTIFICATE_ID,

       min(ci.NAME_VALUE) NAME_VALUE,

       min(ctle.ENTRY_TIMESTAMP) EARLIEST_ENTRY_TIMESTAMP

    FROM certificate_identity ci, ct_log_entry ctle

    WHERE lower(ci.NAME_VALUE) LIKE '%comodo%'

        AND ci.ISSUER_CA_ID NOT IN (

            SELECT ca.ID

                FROM ca

                WHERE lower(ca.NAME) LIKE '%foo%'

                    OR lower(ca.NAME) LIKE '%bar%'

           )

        AND ci.CERTIFICATE_ID = ctle.CERTIFICATE_ID

    GROUP BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID

    HAVING min(ctle.ENTRY_TIMESTAMP) > DATE '2017-03-31'

    ORDER BY ci.ISSUER_CA_ID, ci.CERTIFICATE_ID;

[=JeffH]

Nice -- thanks Rob :)  Seems to work.

wrt multiple CT Logs, this page lists the ones you are monitoring: https://crt.sh/monitored-logs

the crt.sh database includes all of those logs?

thx, =JeffH

[Rob Stradling]

On Friday, June 23, 2017 at 8:24:35 PM UTC+1, =JeffH wrote:

Nice -- thanks Rob :)  Seems to work.

wrt multiple CT Logs, this page lists the ones you are monitoring: https://crt.sh/monitored-logs

the crt.sh database includes all of those logs?

Yes.

[Charles Fierro]

Just curious, is there a way to edit the date to something more like:

HAVING min(ctle.ENTRY_TIMESTAMP) > Todaysdate - 1

I've tried a few different ways that usually work, but can't seem to get it working here.

[Charles Fierro]

Oh answered my own question! Didn't realize postgresql had so many differences in time/dates.

In case anyone else needs an answer, it should look like: 

AND (ctle.ENTRY_TIMESTAMP > (now() - interval '1 day'))

[Geek Unit]

Thanks Jeff! This is awesome.

Question since I'm not sure which fields to request.

Trying to find OrgSubject.countryName <> OrgSubject.jurisdictionCountryName

[Steven]

I recommend retrieving this certificate by ID, and determining if it is reasonable. 

https://crt.sh/?id=445718131

  SELECT *, x509_subjectName(cai.CERTIFICATE) SUBJECT_NAME
  FROM certificate_and_identities cai
  WHERE cai.CERTIFICATE_ID = 445718131
        x509_subjectName(cai.CERTIFICATE) ILIKE '%jurisdictionc%'

The "showSQL=y" option in the web UI should help you the rest of the way.

If you need more certificate examples, this Censys.io query can be modified easily to return additional certificates you can test against.

parsed.subject.country: GR AND parsed.subject.jurisdiction_country: * AND NOT parsed.subject.jurisdiction_country: GR AND NOT tags.raw: "precert"

https://censys.io/certificates?q=parsed.subject.country%3A+GR+AND+parsed.subject.jurisdiction_country%3A+*+AND+NOT+parsed.subject.jurisdiction_country%3A+GR+AND+NOT+tags.raw%3A+%22precert%22