news tips

SecurityTrails Blog · Sep 03 · by Nicolas Pence

Product Update: SurfaceBrowser™ - SQL Explorer: SSL Certificate Scraping Showcase

Reading time: 3 minutes

One of our newest SurfaceBrowser™ features is SQL Explorer. This cool new feature allows you to combine the power of structured query language with our huge records database, allowing you to extract the most information possible.

Today we’ll share some easy-to-follow examples that can help you find interesting, and even unusual, data on SSL certificates issued around the world. In case you want more information regarding this and other helpful innovative features, join the loop and keep track of new products in our changelog page.

Let’s dive in!

Listing SSL certificates issued by location

As you may know, the Vatican City is the smallest country in the world, but is it so small that nobody issues certificates there? We can find out by querying with two different properties: ssl.subject.country and ssl.issuer.country

Listing SSL certificates

This query shows several items that are very common in SQL language, and a few extra properties like ssl.domains (which shows the actual domains covered by the certificate) and domain.hostname (which shows the fully-qualified name of the device). You can order the results by the property you want, in an ascendant or descendant fashion.

Finding self-issued certificates

Self-issued certificates are intriguing. Occasionally they reveal long-forgotten systems that can be investigated, compromised, then maybe even exploited and used as a lateral-movement platform.

Finding self-issued certificates

For this example, ssl.is_self_signed = true shows all results of certificates that are not being signed by a certificate authority, commonly known as a CA. The result we see above is the combination of these self-signed certificates plus ssl.subject.country = “CN” (which filters all results to those certificates that claim to belong to a Chinese entity).

In this example, to complement our previous article on finding coronavirus domains we’re looking for a specific keyword inside ssl.domains to find suspicious targets, or to track anything you like. Additionally, we’ll query all the certificates that are self-signed and expired (using 2020-09-01 formatted as yyyy-mm-dd in Unix timestamp).

Searching coronavirus-related domain certificates

Above you can appreciate the different possible results and query combinations used to achieve this.

Finding certificates with misconfigured dates

Going one step further, we wanted to showcase examples of certificates invalid on both dates. Its ssl.not_before and ssl.not_after dates should not be outside boundaries; meaning that not_before shouldn’t be in the future (or before Jan 1st, 1970) and not_after shouldn’t be a date before our current date.

Finding certificates with misconfigured dates

Oddly enough, we found a few certificates published throughout the internet that fall exactly in between these cases. Check out the results below:

Finding complex situations regarding certificates

As you can see, this new feature is quite powerful—it can help you with finding complex situations regarding certificates.

Downloading query results

Once you have your desired results, you can simply click Add to downloads and place the result in your list of downloadable results. For added convenience, you can select JSON or CSV to obtain the information in either of those available formats.

Downloading query results

Once downloaded, you may delete your results or leave them there as you add others to your collection.

How to access SQL Explorer

SQL Explorer is a preview feature we’re making available to interested users.
Want to be among the first line of researchers using this brand new interface?