🔎 dns.sql

Have you ever wanted to query DNS using SQL? No? Well now you can 🤪

dns.sql is a sqlite3 extension that allows you to query DNS using SQL. Load it into your sqlite3 environment and run,

SELECT * FROM dns WHERE domain = FQDN('riyazali.net') AND nameserver = SystemResolver();

The above query yields a result similar to:

domain         section  class  type  ttl  nameserver                    data
-------------  -------  -----  ----  ---  --------------------  ------------------------
riyazali.net.  answer   IN     A     289  udp://192.168.1.1:53  {"A":"185.199.110.153"}
riyazali.net.  answer   IN     A     289  udp://192.168.1.1:53  {"A":"185.199.111.153"}
riyazali.net.  answer   IN     A     289  udp://192.168.1.1:53  {"A":"185.199.108.153"}
riyazali.net.  answer   IN     A     289  udp://192.168.1.1:53  {"A":"185.199.109.153"}

Usage

You can download the extension using sqlpkg or download pre-built binaries for Linux and MacOS from the releases page.

sqlpkg install riyaz-ali/dns.sql

Alternatively, you can use bindings for one of the following languages.

Language	Install
Python	pip install sqlite-dns
Node.js	npm install sqlite-dns

From source

To build from source, you can use the cmd/shared/shared.go target.

> go build -o libdns.so -buildmode=c-shared cmd/shared/shared.go

Then, to load the extension into your sqlite3 shell:

sqlite> .load libdns.so

Schema

The module provides the following virtual tables and SQL functions:

• DNS() is a table-valued function module that provides the main lookup functionality. It contains the following columns:

  • domain is the FQDN of the query / name being resolved.
    It needs to be a fully-qualified name. Use FQDN() function (defined below) to ensure the name you pass here is fully qualified.
  • section is the setion in the DNS response where this resource record appeared. Valid values include answer, authority and extra
  • class is the class code of the DNS record
  • type is the type of the resource records
  • ttl is the Time-to-live value before the record must be refetched / refreshed.
  • nameserver is either the authoritative or recursive nameserver that answered the query.
    When querying, this is a required parameter and must be provided. Use one of ClassicResolver(), TlsResolver() or a formatted http url (for DoH)
  • data is the JSON-formatted implementations of dns.RR

• SearchList() is a table-valued function module that provides search list resolution functionality. It contains the following columns:

  • partial is the user-provided partial input to the function. This is a HIDDEN column.
  • ndots is the user-provided value for the ndots option. This is a HIDDEN column.
  • fqdn is the resolved FQDN based on system's search list and ndots

  Assuming system's search list is ns1.svc.cluster.local, svc.cluster.local, cluster.local, and ndots is 5 _{(example taken from http://redd.it/duj86x)}

  SELECT * FROM search_list('app.ns2', 5)
  

  fqdn                          
  ------------------------------
  app.ns2.ns1.svc.cluster.local.
  app.ns2.svc.cluster.local.    
  app.ns2.cluster.local.        
  app.ns2. 
  

• FQDN(name) is a custom scalar function that takes in name and returns a formatted, fully-qualified domain name.

• ClassicResolver(protocol, host, port) is a custom scalar function and builds a well-formatted resolver url for use as a dns.nameserver constraint.
  Supported protocol values include udp and tcp. Specify 53 as default port.

• TlsResolver(remote, port, hostname) is a custom scalar function and builds a well-formatted resolver url for use as a dns.nameserver constraint. It builds a url used by DoT resolver. hostname is used to verify the server's TLS certificate.

• SystemResolver() is a custom scalar function and builds a well-formatted resolver url for use as a dns.nameserver constraint. It reads from system's DNS configuration and returns a well-formed url. It reads from /etc/resolv.conf. This resolver is not supported on Windows.

To use DoH, specify a valid url to a DoH service (like https://cloudflare-dns.com/dns-query), eg:

SELECT * FROM dns
  WHERE domain = FQDN('riyazali.net') AND nameserver = 'https://cloudflare-dns.com/dns-query';

License

MIT License Copyright (c) 2023 Riyaz Ali. Refer to LICENSE for full text.