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.