Recently, I decided to take a look at Steampipe again. I like SQL and the structure it provides, and after playing around a bit I figured: “Wouldn’t it be cool to write a plugin for the immensely popular projectdiscovery tools?”. That is exactly what I did and you can find the source code for it here: https://github.com/sensepost/steampipe-plugin-projectdiscovery.

overview

For the purposes of footprinting, everything you can do with steampipe you can do with a bash script. You technically don’t need SQL. However, with bash you always need to bust out some text wrangling with tools like sed and awk. That in itself isn’t bad, but the data is inherently unstructured and error-prone as a result. Instead, if we could have our data in a database, we could do arbitrary lookups, join and more!

example

Out of the box, steampipe has plenty of plugins. One of them is the crt.sh plugin often used to find subdomains based on certificate transparency logs. Using that plugin, we could run a query for domains related to Reddit (ref: their bugbounty scope) via steampipe while filtering out wildcards like this.

with raw_domains as (
  select
    distinct jsonb_array_elements_text(dns_names) as domain
  from
    crtsh_certificate
  where
    query = 'reddit.com'
)
select
  *
from
  raw_domains
where
  domain like '%reddit.com'
  and domain not like '*%'
order by
  domain

The result? A list of domains, just like you would have come to expect.

+---------------------------+
| domain                    |
+---------------------------+
| ads-api.reddit.com        |
| alb.reddit.com            |
| amp.reddit.com            |
| h.reddit.com              |
| m.reddit.com              |
| pixel.reddit.com          |
| reddit.com                |
| tls-test-1.reddit.com     |
| tls-test-2.reddit.com     |
| vip.reddit.com            |
| www--reddit.com           |
| www.h.reddit.com          |
| www.m.reddit.com          |
| www.tls-test-1.reddit.com |
| www.vip.reddit.com        |
+---------------------------+

Now, imagine you wanted to check the IP Address each of these domains resolved to? The net plugin comes in handy here!

with domains as (
  select
    distinct jsonb_array_elements_text(dns_names) as domain
  from
    crtsh_certificate
  where
    query = 'reddit.com'
)
select
  domains.domain, net_dns_record.ip
from
  domains
left join net_dns_record on net_dns_record.domain = domains.domain
where
  domains.domain like '%reddit.com'
  and domains.domain not like '*%'
  and net_dns_record.type = 'A'
  and net_dns_record.ip is not null
order by
  domains.domain

By joining net_dns_record, steampipe would do a DNS record for each of the enumerated domains, and by selecting the ip field from that table, we can see the results as well.

+---------------------------+-----------------+
| domain                    | ip              |
+---------------------------+-----------------+
| ads-api.reddit.com        | 151.101.193.140 |
| ads-api.reddit.com        | 151.101.1.140   |
| ads-api.reddit.com        | 151.101.65.140  |
| ads-api.reddit.com        | 151.101.129.140 |
| alb.reddit.com            | 151.101.193.140 |
| alb.reddit.com            | 151.101.65.140  |
| alb.reddit.com            | 151.101.1.140   |
| alb.reddit.com            | 151.101.129.140 |
| amp.reddit.com            | 199.232.253.140 |
| h.reddit.com              | 199.232.253.140 |
| m.reddit.com              | 199.232.253.140 |
| pixel.reddit.com          | 151.101.1.140   |
| pixel.reddit.com          | 151.101.65.140  |
| pixel.reddit.com          | 151.101.193.140 |
| pixel.reddit.com          | 151.101.129.140 |

[ ... snip ... ]

Now, what if we were curious about the CDN these IP’s were hosted on? Well, with the new projectdiscovery_cdncheck table, we can leverage cdncheck to get that information.

select
  target,
  cdn
from
  projectdiscovery_cdncheck
where target in (
  with domains as (
    select
      distinct jsonb_array_elements_text(dns_names) as domain
    from
      crtsh_certificate
    where
      query = 'reddit.com'
  )
  select
    rtrim(net_dns_record.ip::text, '/32')
  from
    domains
  left join net_dns_record on net_dns_record.domain = domains.domain
  where
    domains.domain like '%reddit.com'
    and domains.domain not like '*%'
    and net_dns_record.type = 'A'
    and net_dns_record.ip is not null
)

While the results in this case aren’t that interesting, I’m sure you get the idea :D

+-----------------+--------+
| target          | cdn    |
+-----------------+--------+
| 199.232.253.140 | fastly |
| 151.101.65.140  | fastly |
| 151.101.1.140   | fastly |
| 151.101.193.140 | fastly |
| 151.101.129.140 | fastly |
+-----------------+--------+

The tables I implemented are:

• asnmap
• cdncheck
• chaos (needs an api key)
• cloudlist
• dnsx
• httpx
• katana
• naabu
• subfinder
• tlsx

You can see them all here: https://github.com/sensepost/steampipe-plugin-projectdiscovery/tree/master/projectdiscovery.

conclusion

While this is not something entirely new, I think it’s an interesting experiment to get more structured data out of your workflow, and a projectdiscovery steampipe plugin is one such way that could work.