How to use the SQL API

The SecurityTrails SQL API provides a powerful SQL-like query interface to our data via certain API endpoints.

The SQL API endpoint replaces any prototype DSLv2 endpoints and is production-ready.
Please contact sales if you'd like to subscribe to it (it's not currently included in retail packages)

Introduction

The SQL API interface allows you to build flexible and complex queries across our data sets with fast results.
This document will explain how to interact with our API using different programming languages.

API Endpoint Details

Authentication

To authenticate with the API you need to create an API Key inside your SecurityTrails™ Account Panel, instructions on how to do it can be found here:

https://securitytrails.com/support/knowledge-base/account-api/#htoc-api-keys.

Query structure

The following code shows the structure of a basic SQL query:

SELECT attribute FROM table WHERE condition = "value"

For a full reference of properties and operators please check the following link:
https://securitytrails.com/reference/sql

Tables

  • Hosts - the hosts table contains all information regarding domains, hostnames, and other related hostname details.
  • IPs - the IPs table contains all related information regarding IP addresses, ASNs, and other IP-related details.

Creating a Query

Once you know what information you want to search inside the database, it’s time to create the query that will extract the desired information.

We’re going to show a series of examples that will guide you through the full power of the SecurityTrails™ SQL Interface.

This first query will show a basic query that extracts all hostnames that contain the apex domain google.com:

SELECT domain.hostname FROM hosts WHERE domain.apex = "google.com"

Results can be seen below:

domain.hostname

google.com

plus.google.com

maps.google.com

play.google.com

docs.google.com

support.google.com

sites.google.com

developers.google.com

...

Instructions can be combined with different operators, rapidly increasing the complexity of the query. This example is for getting an associated IP address for each discovered hostname with the same apex domain:

SELECT 
  domain.hostname, 
  dns.a.value.ip
FROM
  hosts
WHERE
  domain.apex = 'google.com'
AND
  dns.a.value.ip IS NOT Null

Results can be seen below:

domain.hostname

dns.a.value.ip

google.com

172.217.0.46

plus.google.com

142.250.73.238

maps.google.com

172.217.0.46

play.google.com

172.217.12.238

docs.google.com

172.217.2.110

support.google.com

142.250.81.206

sites.google.com

142.250.188.206

developers.google.com

142.250.73.238

...

...

Several values can be searched obtaining complex associated values with different multi-attribute queries, such as the following:

SELECT
  isp.name,
  asn.owner,
  asn.number,
  address
FROM
  ips
WHERE
  ports.port IN ('5060', '5051')
 AND 
  geo.owner.country_iso_code = 'US'

This query will bring all IPs and their related ISP name, ASN number, and owner that have TCP ports 5060 and 5061 open and are geolocated within the US.

isp.name

asn.owner

asn.number

address

Shippensburg-university-01

SHIPPENSBURG-UNIVERSITY-01

398663

157.160.86.53

Cloudflare

Cloudflare, Inc.

13335

172.65.142.3

Hewlett-Packard Company

Hewlett-Packard Company

71

15.250.71.186

Alameda County Office of Education

Alameda County Office of Education

6099

206.110.217.210

Michigan Technological University

Michigan Technological University

62618

141.219.1.88

Emerging Markets Communications

Emerging Markets Communications

32806

216.130.35.250

AT&T Internet Services

AT&T Corp.

3423

148.184.47.103

Level 3 Communications

State University of New York at Buffalo

3685

8.35.163.204

Hewlett-Packard Company

Hewlett-Packard Company

71

15.252.13.86

...

...

...

...

All of these queries can be shaped in SQL Explorer and taken into the API for automatic results extraction.

Request and Response Structure

Requests should be done using a JSON-formatted string sent via POST messages, that will query the endpoint and send the SQL-like instruction to the API, the structure should follow this guidelines:

{ "query": "SQL Query"}

An example implementation of a SQL query using the above scructure would be the following:

{ "query": "SELECT domain.hostname FROM hosts WHERE tags.title IN ('cPanel/WHM Software')" }

Responses are JSON-formatted as well and return the following structure and parameters:

{
  "query":"SELECT domain.hostname FROM hosts WHERE tags.title IN ('cPanel/WHM Software')",
  "id":"bc01cf0b6744bdbaee51df5190284f6d",
  "total": {
            "value": "1500", 
            "relation": "lte"
  }, 
  "records":[
              { 
               "domain":
                 {
                  "hostname": "gmpg.org"
                 },
                 {
                  "hostname": "reacts.ru"
                 },
                 {
                  "hostname": "caniuse.com"
                 },
                 {...}
              }
   ]
}

An explanation of the returned response payload follows:

  • query (the executed query)
  • id (scrolling unique ID)
  • total (an approximate number of results obtained by the query)
  • value (an approximate count of results obtained in the database)
  • relation (the relation between the obtained value and the total, equal, less-than-equal, greater-than-equal)
  • records (an array containing the responses Max. 100 records)
{ 
  "query": Executed SQL query,
  "id": ID to query the scrolling URL,
  "total": {
            "value": aproximate_count_of_results, 
            "relation": eq | lte | gte
  },          
  "records": [ array_with_results ]
}

In case the records response exceeds the 100 results limit, the use of the id will be needed to conduct a results scrolling explained in the next section.

Multiple results downloading

In case the query produces a response payload that exceeds the 100 results limit , a scrolling approach will be needed to obtain the additional information. Each scrolling query will extract 100 more records until the total amount is completed.

The user is expected to check the number of records received in order to calculate how many queries are needed to download all the resulting information.

{
  "query":"SELECT domain.hostname FROM hosts WHERE tags.title IN ('cPanel/WHM Software')",
  "id": bc01cf0b6744bdbaee51df5190284f6d
  [...]
}

To gather the additional sets of information, the user is expected to do a set of additional authenticated GET requests to the same endpoint followed by the Scroll ID obtained in the first response (bc01cf0b6744bdbaee51df5190284f6d in this example).

curl --request GET \
  --url 'https://api.securitytrails.com/v1/query/scroll/bc01cf0b6744bdbaee51df5190284f6d' \
  --header 'apikey: YOUR_API_KEY'

After the end of data is achieved the user should stop querying the endpoint as it may incur in API plan overages.

Search data by using SQL queries and the API endpoint

In this section, we’re enumerating different ways to query our API endpoint and embed the desired SQL queries using different programming languages.

API Endpoint URL: https://api.securitytrails.com/v1/query/scroll

Bash

curl --request POST \
  --url 'https://api.securitytrails.com/v1/query/scroll' \
  --header 'apikey: YOUR_API_KEY' \
  --header 'Content-Type: application/json' \
  --data '{"query":"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \"google.com\""}'

Python

import requests

url = "https://api.securitytrails.com/v1/query/scroll"

querystring = { "page": "1" }
sql_query = "SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \"google.com\""
payload = { "query": sql_query}

headers = {
    "Content-Type": "application/json",
    "APIKEY": "YOUR_API_KEY"
}

response = requests.request("POST", url, json=payload, headers=headers, params=querystring)

print(response.text)

Java

OkHttpClient client = new OkHttpClient();
MediaType mediaType = MediaType.parse("application/json");

RequestBody body = RequestBody.create(mediaType, "{\"query\":\"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \\\"google.com\\\"\"}");
Request request = new Request.Builder()
  .url("https://api.securitytrails.com/v1/query/scroll")
  .post(body)
  .addHeader("Content-Type", "application/json")
  .addHeader("APIKEY", "YOUR_API_KEY")
  .build();

Response response = client.newCall(request).execute();

PHP

<?php

$curl = curl_init();
curl_setopt_array($curl, [
  CURLOPT_URL => "https://api.securitytrails.com/v1/prototype/dslv2?page=1",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\"query\":\"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \\\"google.com\\\"\"}",
  CURLOPT_HTTPHEADER => [
    "APIKEY: YOUR_API_KEY",
    "Content-Type: application/json"
  ],
]);

$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

?>

Golang

package main

import (
    "fmt"
    "strings"
    "net/http"
    "io/ioutil"
)

func main() {

    url := "https://api.securitytrails.com/v1/query/scroll"
    payload := strings.NewReader("{\"query\":\"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \\\"google.com\\\"\"}")
    req, _ := http.NewRequest("POST", url, payload)

    req.Header.Add("Content-Type", "application/json")
    req.Header.Add("APIKEY", "YOUR_API_KEY")

    res, _ := http.DefaultClient.Do(req)
    defer res.Body.Close()

    body, _ := ioutil.ReadAll(res.Body)

    fmt.Println(res)
    fmt.Println(string(body))

}

Ruby

require 'uri'
require 'net/http'
require 'openssl'

url = URI("https://api.securitytrails.com/v1/query/scroll")

http = Net::HTTP.new(url.host, url.port)
http.use_ssl = true
http.verify_mode = OpenSSL::SSL::VERIFY_NONE

request = Net::HTTP::Post.new(url)
request["Content-Type"] = 'application/json'
request["APIKEY"] = 'YOUR_API_KEY'

request.body = "{\"query\":\"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \\\"google.com\\\"\"}"
response = http.request(request)

puts response.read_body

NodeJS

const fetch = require('node-fetch');

let url = 'https://api.securitytrails.com/v1/query/scroll';

let options = {
  method: 'POST',
  qs: {include_ips: 'false', page: '1', scroll: 'false'},
  headers: {'Content-Type': 'application/json', APIKEY: 'YOUR_API_KEY'},
  body: JSON.stringify({query: 'SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = "google.com"'})
};

fetch(url, options)
  .then(res => res.json())
  .then(json => console.log(json))
  .catch(err => console.error('error:' + err));

Javascript

fetch("https://api.securitytrails.com/v1/query/scroll", {
  "method": "POST",
  "headers": {
    "Content-Type": "application/json",
    "APIKEY": "YOUR_API_KEY"
  },
  "body": "{\"query\":\"SELECT domain.hostname, dns.a.value.ip, ip.asn.owner FROM hosts WHERE domain.apex = \\\"gogole.com\\\"\"}"
})

.then(response => {
  console.log(response);
})

.catch(err => {
  console.error(err);
});

If you need and additional proof-of-concept code in a different programming language please contact the SecurityTrails™ Support Team.