Querying IP addresses and CIDR ranges with DuckDB

I had a use case that eventually required performing IP address lookups in a given list of CIDR ranges, as I maintain an open source project that gathers IP address range data from public cloud providers, and also wrote an article in my blog about analyzing this data as well.

I had a look at the DuckDB docs, and found the inet extension that provides some functionality around IP addresses. Unfortunately, it offers no direct way to determine whether a given IP address is part of a CIDR range.

After some research, I found a StackOverflow answer that showed how this could be done with basic functions in Postgres. I decided to create three DuckDB macros to implement the needed IP address lookup functionality.

The plan

Basically, both the starting (network) and ending (broadcast) IP addresses of a CIDR range need to be cast to integers, to be able to determine if a given IP address (also cast to an integer) lies within the derived integer value boundaries.

Deriving the network address value

CREATE OR REPLACE MACRO network_from_cidr(cidr_range) AS (
    cast(string_split(string_split(cidr_range, '/')[1], '.')[1] as bigint) * (256 * 256 * 256) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[2] as bigint) * (256 * 256      ) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[3] as bigint) * (256            ) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[4] as bigint)
);

Deriving the broadcast address value

CREATE OR REPLACE MACRO broadcast_from_cidr(cidr_range) AS (
    cast(string_split(string_split(cidr_range, '/')[1], '.')[1] as bigint) * (256 * 256 * 256) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[2] as bigint) * (256 * 256      ) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[3] as bigint) * (256            ) +
    cast(string_split(string_split(cidr_range, '/')[1], '.')[4] as bigint)) + 
    cast(pow(256, (32 - cast(string_split(cidr_range, '/')[2] as bigint)) / 8) - 1 as bigint
);

Final macro for the IP address lookup

CREATE OR REPLACE MACRO ip_within_cidr(ip, cidr_range) AS (
    network_from_cidr(ip || '/32') >= network_from_cidr(cidr_range) AND network_from_cidr(ip || '/32') <= broadcast_from_cidr(cidr_range)
);

Sample SQLs

-- Transform to network start address
select network_from_cidr('4.0.0.0/8')
-- Transform to broadcast address
select broadcast_from_cidr('4.0.0.0/8')
-- Checks
select ip_within_cidr('4.0.0.0', '4.0.0.0/8') -- true
select ip_within_cidr('4.255.255.255', '4.0.0.0/8') -- true
select ip_within_cidr('3.255.255.255', '4.0.0.0/8') -- false
select ip_within_cidr('5.0.0.0', '4.0.0.0/8') -- false

Try it out yourself

You can try the described functionalities in my free online SQL Workbench by clicking on this link. It will open a new browser window, and create all three macros and run a test query accordingly.