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
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.
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).
Searching coronavirus-related domain certificates
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).
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.
Oddly enough, we found a few certificates published throughout the internet that fall exactly in between these cases. Check out the results below:
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.
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?