tools

SecurityTrails Blog · Jun 17 · by Esteban Borges

Meet SQL Explorer: One of the Best Alternatives to Shodan

Reading time: 4 minutes
Listen to this article

In this modern age of IP reconnaissance and security research combined with the ever-growing list of software services accessible via the public internet, it’s critical to stay ahead of the curve.

With hundreds, if not thousands, of websites being launched every day, the increasing size of the internet makes it nearly impossible to manually scan and build reliable reports. Internet scanning, as it’s commonly called, can often be too slow for timely catching of security vulnerabilities when done manually. And that’s even within small to medium-sized organizations.

Tools like Shodan make handling this task easier by using Shodan dorks to find and filter out specific bits of information (such as web servers running a particular software version in a specific city or country).

SQL Explorer: An SQL-like, similar alternative to Shodan

Today we’re introducing you to SQL Explorer, a Shodan-esque tool supercharged with the ability to use the SQL language syntax, and one of the many enterprise-grade features offered by SurfaceBrowser™.

With the ability to use conditional statements like OR, AND, NOT and more, SQL Explorer offers unmatched flexibility and control over queries being searched for.

Take your intelligence to the next levelDiscover SurfaceBrowser™ and SQL Explorer

To get intelligence about hosts or IPs, let’s see some practical examples of how SQL Explorer helps security researchers to perform queries similar to Shodan.

Databases

SQL Explorer can be used to find database instances that are exposed to the public internet, by filtering out the ports they serve off of.

To lookup MongoDB based databases running on port 27017:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  ip.ports_real = '27017'

Similarly, let’s take a look at REDIS, a popular NoSQL-style database which runs on port 6379. To lookup REDIS-powered databases we use the following query:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  ip.ports_real = '6379'

If searching for ElasticSearch instances available on the public internet, we can use this query:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  ip.ports_real = '9200'

To look up a specific ASN with port 9200, replace ASN-NUMBER with the ASN-NUMBER in question:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  ip.asn.number = 'ASN-NUMBER'
  AND ip.ports_real = '9200'

Further, to look up all ElasticSearch instances whose DNS is served by ns1.digitalocean.com:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.ports_real = '9200'

Software versions

Software versions are an important aspect to consider, whether you’re doing IP research or securing your own organization’s websites. And while running the latest software can help keep you secure, the growing number of websites in any one organization makes keeping track of numerous versions an often challenging task.

For example, let’s consider WordPress, which powers all sorts of websites (including ecommerce) and remains one of the most popular CMS (content management systems) on the internet.

For starters, let’s find all WordPress-powered websites running on ns1.digitalocean.com:

Youtube Video



For this case, the query would look like:

SELECT
  domain.hostname,
  ip.http.meta_generator
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.meta_generator LIKE '%WordPress%'

Next, let’s take a look for older versions of WordPress, say 4.9.13 (which is quite an old version, considering version 5.7.2 is the latest version at the time of this writing):

older versions of WordPress

The query would look like:

SELECT
  domain.hostname,
  ip.http.meta_generator
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.meta_generator LIKE '%WordPress 4.9.13%'

The above query lists all WordPress-powered websites running on the DigitalOcean nameserver “ns1.digitalocean.com” that, in turn, run the old and vulnerable 4.9.13 version.

Operating systems

Operating systems are the core of a web application. They run everything in the web stack from storing files to running the database, the web server and more.

To filter out Ubuntu-powered servers, we use the following query:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%Ubuntu%'

Similarly, if we wish to filter out CentOS, a popular RHEL clone-powered website:

a popular RHEL clone-powered website

The query would look like:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%CentOS%'

The above queries list all websites powered by Ubuntu or CentOS running on the DigitalOcean nameserver “ns1.digitalocean.com”.

Web servers

When a website or web service is attacked, web servers are first in the line of fire. That’s because a web server is responsible for accepting or denying requests from the client or attacker—and passing it onto the backend for further processing. Which means that an out-of-date web server can lead to security issues that allow attackers to access your server’s filesystem and more.

In this example, let’s look at finding Nginx-powered websites running at DigitalOcean off their nameserver “ns1.digitalocean.com”:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%nginx%'

And if we wish to further filter out the web server version, to a specific Nginx version like 1.4.6:

A specific Nginx version

The query would look like:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%nginx/1.4.6%'

Similarly, we can also look up Apache-powered websites:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%Apache%'

We can also filter by version. For example, to filter down to Apache version 2.2.15:

SELECT
  domain.hostname,
  ip.http.headers.server
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.http.headers.server LIKE '%Apache/2.2.15%'

Exposed ports

Ports are similar to the doors of a house: they can be open, they can be closed, or they can be opened when needed for an allowed request. This makes knowing which ports are open and which are closed of the utmost importance; if left unsecured with an open port, a web service like a database is tempting data theft and other attacks.

Similarly, having SSH on the default port 22 can be an open invitation to automated brute force attacks.

To find domains and subdomains owned by Netflix with port 22 open:

SELECT
  domain.hostname,
  http.title,
  ip.ports.port
FROM
  hosts
WHERE
  domain.apex = "netflix.com"
  AND ip.ports_real = '22'

From a wider list of websites with port 22 open, most likely running OpenSSH/SSH, let’s take a look at the domain nameserver ns1.digitalocean.com (which belongs to the popular cloud provider DigitalOcean) to search through the websites it serves with port 22 open/available:

Websites with port 22 open

The query would look like:

SELECT
  domain.hostname,
  ip.ports.port
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.ports_real = '22'

Similarly, we can look for other ports, such as Port 21 (FTP) and Port 23 (Telnet), with the following queries.

To look up servers/websites with Telnet available:

SELECT
  domain.hostname,
  ip.ports.port
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.ports_real = '23'

To look up servers/websites with FTP available:

SELECT
  domain.hostname,
  ip.ports.port
FROM
  hosts
WHERE
  dns.ns.value.host = "ns1.digitalocean.com"
  AND ip.ports_real = '21'

To find websites and ports open on a specific IP address, we use the following query:

SELECT
  domain.hostname,
  ip.address,
  ip.ports.port
FROM
  hosts
WHERE
  ip.address = "IP.Address.Here"

And to find open ports and websites hosted on the IP “8.8.8.8”:

find open ports and websites hosted on the IP 8.8.8.8

The query would look like:

SELECT
  domain.hostname,
  ip.address,
  ip.ports.port
FROM
  hosts
WHERE
  ip.address = "8.8.8.8"

SSL certificates

We’ve shared in the past the dangers of using self-signed SSL certificates and that they can often lead to security issues. Considering the growing number of domains and subdomains in one’s organization, it’s critical to find and replace them with valid signed SSL certificates.

This next query can be used to find and filter out subdomains and domains with self-signed certificates (remember to replace “your-domain.here” with your organization’s domain name):

SELECT
  domain.hostname,
  ip.ssl.is_self_signed
FROM
  hosts
WHERE
  domain.apex = 'your-domain.here'
  AND ip.ssl.is_self_signed = true

As an example, let’s look up the self-signed SSL certificates General Electric (GE) has, by using the following query:

SELECT
  domain.hostname,
  ip.ssl.is_self_signed
FROM
  hosts
WHERE
  domain.apex = 'ge.com'
  AND ip.ssl.is_self_signed = true

Similarly, to look for self-signed SSL certificates at Netflix:

SELECT
  domain.hostname,
  ip.ssl.is_self_signed
FROM
  hosts
WHERE
  domain.apex = 'netflix.com'
  AND ip.ssl.is_self_signed = true

Filtering by ASN/network

Using SQL Explorer makes it possible to filter by ASN as well, for looking at a specific network’s web applications, IP addresses and open ports.

In this example, we’ll find and filter out web applications, IP addresses and open ports on AS13335 (CloudFlare):

SELECT
  domain.hostname,
  ip.address,
  ip.ports.port
FROM
  hosts
WHERE
  ip.asn.number = '13335'

Filtering by location

Large organizations have web services on nearly all habitable continents. Therefore, filtering results by location becomes critical when trying to find services running out of one particular region, which can be a city, state or country.

For example, let’s try to find Netflix services running out of the city of Ashburn in the state of Virginia, in the United States:

find Netflix services

The query would look like:

SELECT
domain.hostname,
ip.geo.owner.city_name
FROM
hosts
WHERE
domain.apex = 'netflix.com'
AND ip.geo.owner.city_name = 'Ashburn'

Looking for a wider area, such as a country? Let’s try to find all Netflix domains and subdomains located in the United States, along with the city they’re in:

SELECT
  domain.hostname,
  ip.geo.owner.city_name,
  ip.geo.owner.country_iso_code
FROM
  hosts
WHERE
  domain.apex = 'netflix.com'
  AND ip.geo.owner.country_iso_code = 'US'

Now, what if you wish to NOT see results from the city of Ashburn? Just as with any SQL query, you can use the != (NOT) operator as well:

NOT see results from the city of Ashburn

The query would look like:

SELECT
  domain.hostname,
  ip.geo.owner.city_name,
  ip.geo.owner.country_iso_code
FROM
  hosts
WHERE
  domain.apex = 'netflix.com'
  AND ip.geo.owner.country_iso_code = 'US'
  AND ip.geo.owner.city_name != 'Ashburn'

Filtering via tags

At times we may wish to filter via tags; for example, as in looking up web services and websites tagged in the Akamai CDN, a large and popular content delivery network:

SELECT
  domain.hostname,
  ip.address,
  ip.ports.port
FROM
  hosts
WHERE
  tags.title = 'Akamai - CDN'

Similarly, to look up websites and web services tagged as Microsoft:

Look up websites and web services tagged as Microsoft

The query would look like:

SELECT
  domain.hostname,
  ip.address,
  ip.ports.port
FROM
  hosts
WHERE
  tags.title = 'Microsoft'

Regarding further usage of the tags.title key, we can also look up specific software vendors and their products.

Take, for example cPanel, the most popular web hosting control panel used for commercial web hosting. Using the query below, we can find and list all cPanel-powered servers and the open ports found on them:

SELECT
  address,
  ports.port
FROM
  ips
WHERE
  tags.title = 'cPanel/WHM Software'

Summary

Using the SecurityTrails™ SQL Explorer tool allows you to streamline and optimize your IP recon workflow by allowing you to combine multiple queries and conditions using the popular SQL syntax format.

With its ability to use and combine conditional statements such as NOT, AND, OR and more, you’re able to build out the SQL statement to find exactly the type of results you’re looking for.

Taking leverage of the SecurityTrails™ dataset of hosts and IPs, combined with SQL operators, one can filter out specific software versions, operating systems, cities, countries, latitudes, organizations, open ports and much more when looking for a specific subset of hosts or IPs.

Take your intelligence gathering to the next level:

Esteban Borges Blog Author
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.