Shift Left Join

Shift Left Join: Using Shodan to test AWS Public IPs

Shift Left Join Security: Threat hunting AWS IPs with Shodan and SQL.

David Boeke
8 min. read - Apr 04, 2021

Front Matter

Structured Query Language (SQL): A domain-specific language for working with data and data structures. It is particularly useful in handling relations (joining) data across entities. SQL is 50 years old and is still the most popular language for data work.

Shodan is a search engine for Internet connected devices, ranging from internet connected cameras to cloud servers.

Steampipe is an open source CLI that uses Postgres Foreign Data Wrappers to instantly query cloud APIs using SQL. The Steampipe plugin for Shodan enables use of SQL to query host metadata, open ports, DNS info and even potential exploit information. This metadata can be made even more powerful when joined with data from other cloud services.

The Objective

Automate checks to find open ports and vulnerabilities for AWS resources.

Step 1: What EC2 instances have public IPs?

Using the Steampipe CLI we can query for AWS EC2 instances with public IPs:

steampipe cli
>
select
title,
public_ip_address as ip_addr
from
aws_ec2_instance
where
public_ip_address is not null;

  +---------------------+-------------------+
  | title               | public_ip_address |
  +---------------------+-------------------+
  | Redhat 8 Test       | 42.93.36.148      |
  | Amazon Linux 2 Test | 42.239.34.42      |
  | Ubuntu 20 Test      | 42.239.46.31      |
  | Ubuntu 18 Test      | 42.94.153.81      |
  +---------------------+-------------------+
        

Step 2: Initiate Shodan Scan

To ensure Shodan has the latest information on these instances, we will initiate an on-demand scan using the Shodan CLI. The CLI command uses the format shodan scan submit <ip address>.

We use Steampipe’s --output=csv and --header=false options to just a list of IP addresses delimited with newlines:

We can assign that output to a variable and then iteratively call the Shodan CLI:

Step 3: Install and test Shodan plugin

steampipe cli
   
$ steampipe plugin install shodan
Updated plugin: shodan@latest v0.0.1
Documentation:  https://hub.steampipe.io/plugins/turbot/shodan
 
$ steampipe query
Welcome to Steampipe v0.3.5
For more information, type .help
 
> .inspect shodan
+------------------------+-----------------------------------------------------------------------------------------+
| TABLE                  | DESCRIPTION                                                                             |
+------------------------+-----------------------------------------------------------------------------------------+
| shodan_account_profile | Information about the Shodan account linked to the caller.                              |
| shodan_api_info        | Information about the API plan belonging to the given API key.                          |
| shodan_dns_reverse     | Hostnames defined for the given IP.                                                     |
| shodan_domain          | Get all the subdomains and other DNS entries for the given domain.                      |
| shodan_exploit         | List the exploits requested for this account.                                           |
| shodan_host            | ISP, geolocation, open ports and other info about a host at a given IP address.         |
| shodan_host_service    | Publicly accessible services that have been found on a given host during a Shodan scan. |
| shodan_port            | Ports returns a list of port numbers that the crawlers are looking for.                 |
| shodan_protocol        | List of the protocols that can be used when launching an Internet scan.                 |
| shodan_scan            | List the scans requested for this account.                                              |
| shodan_search          | Search the internet for hosts matching the query parameters.                            |
| shodan_service         | List of the services Shodan can detect.                                                 |
+------------------------+-----------------------------------------------------------------------------------------+
 
To get information about the columns in a table, run .inspect {connection}.{table}
 
>
        

Step 4: Check if Shodan scans are complete

steampipe cli
>
select id, status from shodan_scan;

  +------------------+------------+
  | id               | status     |
  +------------------+------------+
  | Wd29N6mNt1pM9EfY | DONE       |
  | lJyP683eCjduMoSy | PROCESSING |
  | D1pAzPLBZ8hcK9rr | PROCESSING |
  | wj21Ho3hAUb3nN3E | DONE       |
  +------------------+------------+
        

Step 5: Join Shodan and AWS Data

Once all of the scans are complete we can now join our EC2 instance information with the shodan scan information.

steampipe cli
>
select
instance_id,
ports,
vulns,
security_groups
from
aws_ec2_instance
left join
shodan_host on public_ip_address = ip
where
public_ip_address is not null;

+---------------------+----------+--------------------+-------------------------------------------------------------+
| instance_id         | ports    | vulns              | security_groups                                             |
+---------------------+----------+--------------------+-------------------------------------------------------------+
| i-0dc60dd191cb84239 | <null>   | <null>             | [{"GroupId":"sg-042fe79169eb42818","GroupName":"lockdown"}] |
| i-042a51a815773780d | [80,22]  | <null>             | [{"GroupId":"sg-042042bac705630f4","GroupName":"bastion"}]  |
| i-00cf426db9b8a58b6 | [22]     | <null>             | [{"GroupId":"sg-0423f79169eb42818","GroupName":"default"}]  |
| i-0e97f373db42dfa3f | [22,111] | ["CVE-2018-15919"] | [{"GroupId":"sg-0423f79169eb42818","GroupName":"default"}]  |
+---------------------+----------+--------------------+-------------------------------------------------------------+
        

Other #shiftleftjoin opportunities with Shodan:

What TLS ciphers are enabled on my load balancers?

steampipe cli
>
select
lb.title,
s.ip,
h.ssl->'cert'->'expired' as cert_exp,
h.ssl->'versions' as allowed_ssl_ciphers,
h.ssl->'cipher'->'bits' as bits
from
aws_ec2_application_load_balancer lb,
shodan_search s,
shodan_host_service h
where
s.query = lb.dns_name
and h.ip = s.ip
and h.port = 443;

+------------------+----------+-------------------------------------------------------------+------+
| title            | cert_exp | allowed_ssl_ciphers                                         | bits |
+------------------+----------+-------------------------------------------------------------+------+
| shodan-test-alb1 | false    | ["TLSv1","-SSLv2","-SSLv3","TLSv1.1","TLSv1.2","-TLSv1.3"]  | 128  |
| shodan-test-alb2 | false    | ["-TLSv1","-SSLv2","-SSLv3","-TLSv1.1","TLSv1.2","TLSv1.3"] | 256  |
+------------------+----------+-------------------------------------------------------------+------+
        

What elastic IP addresses have open ports?

steampipe cli
>
select
allocation_id,
public_ip,
ports
from
aws_vpc_eip
left join
shodan_host on public_ip = ip;

  +----------------------------+---------------+----------+
  | allocation_id              | public_ip     | ports    |
  +----------------------------+---------------+----------+
  | eipalloc-0f9ae42acece4855f | 3.42.34.26    | [80,443] |
  | eipalloc-0268097c2fea42da2 | 18.42.215.212 | [22,53]  |
  +----------------------------+---------------+----------+
        

Thank you Shodan Team!

We think it is incredible to have such a useful service to integrate with for security testing against dynamic cloud environments. A huge shout out to the entire Shodan team for making it accessible via their API.