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)
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
API Endpoint Details
- Endpoint URL - https://api.securitytrails.com/v1/query/scroll
- HTTP Method - POST
- Authentication - using your SecurityTrails™ API Key
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
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
- 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
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.ipFROM hostsWHERE domain.apex = 'google.com'AND dns.a.value.ip IS NOT NullResults 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, addressFROM ipsWHERE 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.
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
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.
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 requestsurl = "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 mainimport ( "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 = truehttp.verify_mode = OpenSSL::SSL::VERIFY_NONErequest = 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_bodyNodeJS
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.
Updated about 1 year ago
