Google's BigQuery is quite amazing. It let's you process and analyse large datasets with simple SQL queries. If you're a security researcher, you may have already seen how powerful BigQuery can be without even knowing it (i.e. Censys.io).

The first thing that caught my eye about Google's BigQuery was the public datasets. The four datasets I found most interesting were the following:

• updated weekly: Contents from 2.9M public, open source licensed repositories on GitHub
• updated quarterly: All of the publicly available data for StackOverflow
• updated daily: All stories and comments from Hacker News from its launch in 2006
• updated daily: Every certificate from certificate transparency logs (access needs to be requested)
• updated bi-weekly: HTTPArchive's dataset obtained by crawling Alexa's Top 1M list

These datasets can be used to create wordlists that reflect current technologies.

Of particular interest to me, was that we can generate wordlists that are not years outdated (i.e. raft-large-files.txt)

This post details how we processed and created better web discovery dictionaries from using publicly available datasets such as GitHub, StackOverflow, HackerNews and HTTPArchive.

Table of contents:

1. Introducing the Commonspeak tool
2. Directory and file names
3. Parameter names
4. Subdomains

Introducing:

Commonspeak is a tool that was developed to automate the process of extracting wordlists from BigQuery. All of the techniques described in this blog post have been turned into bash scripts. These scripts are particularly useful for generating wordlists on a recurring basis.

You can get Commonspeak here:

https://github.com/pentest-io/commonspeak

If you'd like to download the datasets from this blog post, they are available here:

https://github.com/pentest-io/commonspeak/archive/master.zip

Installation instructions can be found here.

Here's how it looks generating subdomains from the HackerNews dataset:

Commonspeak is still under active development and pull requests are very welcome :)

We're always looking to improve BigQuery and create unique datasets.

Extracting directory and file names

When testing a web application, extending an attack surface is extremely valuable. Having a large list of realistic directories and files to use with a tool such as dirsearch or gobuster helps immensely with this process.

We found that the most useful data (directories, filenames and subdomains) came from parsing URLs recorded from HTTPArchive, StackOverflow and HackerNews.

We can construct a SQL script to analyse every stored HTTP request in HTTPArchive's public dataset. The table we're interested in is httparchive.runs.latest_requests as it contains every request made in the latest run.

From this table, we will extract full URLs using the url column and we will filter this data by extension by using the ext column.

This data will then be processed by a user defined function (UDF) that utilises the URI.js library in order to extract relevant information.

Our first query is responsible for extracting every URL with a .php extension to a new BigQuery table. This is done for optimisation, as we will be running other queries on this dataset:

SELECT  
  url,
  COUNT(url) AS cnt
FROM  
  [httparchive:runs.latest_requests]
WHERE  
  ext = "php"
GROUP BY  
  url
ORDER BY  
  cnt DESC;

Once this query is completed, we can then run the following query (which contains the UDF) in order to extract every filename that contains a .php extension in the latest HTTPArchive dataset:

CREATE TEMPORARY FUNCTION getPath(x STRING)  
RETURNS STRING  
  LANGUAGE js AS """
  function getPath(s) {
    try {
      return URI(s).filename(true);
    } catch (ex) {
      return s;
    }
  }
  return getPath(x);
"""
OPTIONS (  
  library="gs://commonspeak-udf/URI.min.js"
);

SELECT  
  getPath(url) AS url
FROM  
  `crunchbox-160315.commonspeak.httparchive_php`
GROUP BY url  

By calling URI(s).filename, we are able to extract the filename for every URL in our table.

If we only want directory names, we can use URI(s).directory(true).

CREATE TEMPORARY FUNCTION getPath(x STRING)  
RETURNS STRING  
  LANGUAGE js AS """
  function getPath(s) {
    try {
      return URI(s).directory(true);
    } catch (ex) {
      return s;
    }
  }
  return getPath(x);
"""
OPTIONS (  
  library="gs://commonspeak-udf/URI.min.js"
);

SELECT  
  getPath(url) AS url
FROM  
  `crunchbox-160315.commonspeak.httparchive_php`
GROUP BY url  

We can also extract path names (e.g. /foo/bar/test.php) by using URI(s).pathname(true):

CREATE TEMPORARY FUNCTION getPath(x STRING)  
RETURNS STRING  
  LANGUAGE js AS """
  function getPath(s) {
    try {
      return URI(s).pathname(true);
    } catch (ex) {
      return s;
    }
  }
  return getPath(x);
"""
OPTIONS (  
  library="gs://commonspeak-udf/URI.min.js"
);

SELECT  
  getPath(url) AS url
FROM  
  `crunchbox-160315.commonspeak.httparchive_php`
GROUP BY url  

Extracting parameter names for specific technologies

Often when performing content discovery, it's common to come across scripts (PHP, JSP, ASPX, etc) which may have input parameters that you don't know. Using these datasets, an attacker can attempt to blindly guess these parameters.

Using the GitHub dataset and PHP as an example, we can extract user input parameter names that come from $_REQUEST, $_GET or $_POST.

SELECT  
  line AS query_parameter,
  COUNT(*) AS count
FROM  
  FLATTEN( (
    SELECT
      SPLIT(SPLIT(REGEXP_EXTRACT(content, r'.*\$_(?:REQUEST|GET|POST)[\[](?:\'|\")([^)$]*)(?:\"|\')[\]]'), '\n'), ';') AS line,
    FROM (
      SELECT
        id,
        content
      FROM
        [bigquery-public-data:github_repos.contents]
      WHERE
        REGEXP_MATCH(content, r'.*\$_(?:REQUEST|GET|POST)[\[](?:\'|\")([^)$]*)(?:\"|\')[\]]')) AS C
    JOIN (
      SELECT
        id
      FROM
        [bigquery-public-data:github_repos.files]
      WHERE
        path LIKE '%.php'
      GROUP BY
        id) AS F
    ON
      C.id = F.id), line)
GROUP BY  
  query_parameter
HAVING  
  query_parameter IS NOT NULL
ORDER BY  
  count DESC
LIMIT 10000  

Running this query will process 2.07 TB of data. The cost of running this can be reduced by extracting the PHP files to a separate table and then using that table for all future queries.

Extracting subdomains

Using better quality datasets allows an attacker to identify obscure assets and extend their attack surface.

Using the HackerNews dataset we are able to extract ~63,000 unique subdomains.

We optimised our queries by extracting all of these URL's to a new table before running additional queries.

SELECT  
  url,
  COUNT(url) AS cnt
FROM  
  [bigquery-public-data:hacker_news.full]
GROUP BY  
  url
ORDER BY  
  cnt DESC;

We can then run the following query on our table in order to extract all subdomains:

CREATE TEMPORARY FUNCTION getSubdomain(x STRING)  
RETURNS STRING  
  LANGUAGE js AS """
  function getSubdomain(s) {
    try {
      return URI(s).subdomain();
    } catch (ex) {
      return s;
    }
  }
  return getSubdomain(x);
"""
OPTIONS (  
  library="gs://commonspeak-udf/URI.min.js"
);

SELECT  
  getSubdomain(url) AS subdomain
FROM  
  `crunchbox-160315:hackernews_2017_10_22.urls`
GROUP BY subdomain  

The final output looks like this:

While these public datasets contain useful subdomains, it's possible to extract larger, higher quality lists by parsing certificate transparency logs. Ryan Sears of CaliDog Security has done the heavy lifting for importing this into BigQuery, this can be seen here. Ryan maintains this dataset and updates it on a daily basis.

If you'd like access to Ryan's BigQuery Certificate Transparency dataset, I recommend contacting him (his details can be found at the end of his blog post). Included in commonspeak is a script that parses and extracts useful subdomains from his dataset on BigQuery.

We've created the following query to extract subdomains from Ryan's dataset:

language sql  
CREATE TEMPORARY FUNCTION getSubdomain(x STRING)  
RETURNS STRING  
  LANGUAGE js AS """
  // Helper function for error handling
  function getSubdomain(s) {
    try {
      var url = 'http://' + s;
      var subdomain = URI(url).subdomain();
      if (subdomain == '*' || subdomain == ' ') {
        // do nothing
      } else {
        // clean subdomain further
        if (subdomain.startsWith('*.')) {
          subdomain = subdomain.replace('*.', '');
        }
        return subdomain;
      }
    } catch (ex) {
      return s;
    }
  }
  return getSubdomain(x);
"""
OPTIONS (  
  library="gs://commonspeak-udf/URI.min.js"
);

SELECT  
  getSubdomain( dns_names ) AS dns_names, APPROX_COUNT_DISTINCT(dns_names) AS cnt
FROM  
  `crunchbox-160315.ctl_2017_11_22.all_dns_names`
GROUP BY  
  dns_names
ORDER BY  
  cnt DESC
LIMIT  
  1000000;

This image represents the top one million subdomains extracted by parsing 3,482,694,371 records in the dataset.

The queries shown above have mostly been integrated into commonspeak. The generation of these wordlists can be automated by using cron jobs to run the commonspeak wordlist generation scripts on a regular basis.

I believe I was the first one to write about using BigQuery for content discovery / offensive purposes. Feel free to follow me on Twitter or check out Assetnote and our corporate blog which contains numerous articles similar to this.