tools enterprise security

SecurityTrails Blog · Nov 05 · by Esteban Borges

Introducing Regex (RLIKE) Support for Hostnames in SQL Explorer

Reading time: 2 minutes

A couple of months ago we were delighted to share big news about one of the hottest features of SurfaceBrowser™: SQL Explorer. Introducing this new SQL-based feature allowed many of our customers to perform classic SQL-based queries against our massive infosec database and all its host and IP attributes.

Today we’re excited to announce the immediate availability of Regex (RLIKE) support for hostnames in SQL Explorer. Let’s see it in action with a few examples.

Find all domains that start with the word “paypal”

The code will be similar to this:

select
  domain.hostname
from
  hosts
where
  domain.hostname rlike "paypal[a-z]*.*"

Take a look at the following screencast to see the results:

As you can see by using Regex (RLIKE) you can find any strings with particular patterns, in this case we used domain.hostname rlike “paypal[a-z].”, just as an example, but there are many possibilities. Let’s see some additional variations.

Let’s say we want to avoid subdomains in our results. We simply need to add “AND domain.subdomain is null” to our previous query:

avoid subdomains

The output will look like:

Avoid subdomains result

And is numeric regex supported? Well, of course!

For this, we can alter the previous select to include the RLIKE query as shown below:

domain.hostname rlike "paypal<1-100>.*"

The result will include domains such as:

numeric Regex

Find paypal-based domain names that are using self-signed certificates and Let’s Encrypt SSLs

In this case, we’ll repeat the previous syntax, but we can add another attribute such as ssl.is_self_signed = true, which will match only self-signed SSL certificates:

Find paypal-based domain names

Another variation may be to combine this with ssl.issuer.organization = “Let’s Encrypt”, as this can unveil all the domain names that are using free SSL certificates, which is pretty common in some phishing-based websites.

SSL certificates issued by Let's Encrypt

Once you have your results ready, click ‘Add to downloads’ to get all these domains packaged into JSON and CSV formats:

Domains packaged into JSON and CSV formats

We can take all these examples even further by combining other host and IP attributes that are present in our SurfaceBrowser™ SQL Explorer interface.

If you’re interested in playing with the rest of the supported Regex syntaxes for domain.hostname, you may want to check the official ES docs.

We’re already working on adding new supported endpoints for this Regex feature. Stay tuned!

Access SQL Explorer today

The SQL Explorer preview is now available to interested customers.
Want to get a head start in using this valuable new feature?

ESTEBAN BORGES

Esteban is a seasoned security researcher and cybersecurity specialist with over 15 years of experience. Since joining SecurityTrails in 2017 he’s been our go-to for technical server security and source intelligence info.