Meet SQL Explorer: One of the Best Alternatives to Shodan

Meet SQL Explorer: One of the Best Alternatives to Shodan

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.

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.

Source of Article