Given a subnet range and a list of IPs select all rows which the IPs fall between

Question

I am working on modeling various aspects of a network in a database. One of the more annoying issues that we are dealing is creating subnet ranges and then determining if a given set of IPs are within those ranges. Our current model accounts for the differences between IPv4 and IPv6 with the following columns:

[subnet_sk]      [int] IDENTITY(1,1) NOT NULL,
[ipv6_network]   [char](39)          NULL,
[ipv6_broadcast] [char](39)          NULL,
[ipv4_network]   [char](15)          NULL,
[ipv4_broadcast] [char](15)          NULL,
[network_type]   [char](4)           NOT NULL

The above schema makes a few assumptions which are important to point out. We are utilizing fully expanded IPs (192.168.001.001 vs. 192.168.1.1) for storage and comparison. We made this decision because of the issues surrounding storing IPv6 addresses numerically in SQL server (bigints are unsigned meaning we would have to make use of six columns to represent IPv6).

Given this table schema it is pretty easy to write one off select statements to determine if an IP of either type is between the ranges in the table:

select *
  from subnet
 where '1234:0000:0000:0000:fc12:00ab:0042:1050'
       between ipv6_network
           and ipv6_broadcast

-- or alternatively for IPv4

select *
  from subnet
 where '192.168.005.015'
       between ipv4_network
           and ipv4_broadcast

What is more difficult is given a list of IPs determine which of those are in-between the subnet ranges. The list of IPs would be provided by user input and are not stored in the database. Obviously for data stored in the database I can do a similar join as in the example below.

For example a user could provide 1234:0000:0000:0000:fc12:00ab:0042:1050, 192.168.001.001 and 192.168.1.1. The only solution I have come up with is to use a table-valued function to split a list of IPs and perform a join using a between:

-- this only covers the IPv4 addresses from the above list a similar query would
-- be used for IPv6 and the two queries could be unioned
select sub.*
  from fn_SplitList('192.168.001.001,192.168.005.015',',') split
       join subnet sub
         on split.Data
             between sub.ipv4_network
                 and sub.ipv4_broadcast

While utilizing a split function works it feels hacky. I spent the better part of the morning sniffing around common table expressions, but couldn't think of an implementation that would work. Ideally a single select would determine whether to bounce a given string off the IPv4 or IPv6 columns, but if that's not possible I can separate the list before handing the collection of IPs off to the database.

To make it easier for answering I have created a SQL Fiddle of the above. Is there a mechanism in SQL (I'd prefer to not use T-SQL) given a list of IPs to determine which existing subnet ranges those IPs fall between? Is the above schema even the right approach to the problem would a different data model lead to an easier solution?

I do not have good network knowledge. I want to know is that only fn_SplitList() that is bothering you or you want to find some better way to handle the entire scenario ? — Ravi Singh, Jun 13 '13 at 06:13
@RaviSingh given this data model I would have the same question if we were working with a list of dates trying to determine if they fell within a range. So my issue is that I feel like fn_SplitList is a way around the problem but it feels like there is a cleaner solution out there. That said if you told me "your schema is wrong and your query would be easier if your data model was setup this way," I'd be all ears. So if you can't help with the modeling but can help with a better query that doesn't use fn_SplitList I'm very interested in your solution. — ahsteele, Jun 13 '13 at 06:55
Stumbled across this late, but for reference's sake here are some similar threads discussing string splits in SQL Server: http://stackoverflow.com/questions/2647/split-string-in-sql and http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — rutter, Jul 28 '13 at 01:55

Christopher Bonitz · Answer 1 · 2013-06-24T06:37:43.340

this is not a complete solution, but more a idea to another design, i was thinking instead of doing typical SQL comparison, why not try using logical comparison. Knowing very little of the sql implementation i have tried fooling around with bitwise comparison, (with bigint)

there is much optimization to be done, however i think there is a possibility that it might help,

a small demonstration where i compare 4 ip's (192.168.1.1 and 3 more), i use them as bigints cause a int is too small, and i need to use the logical bitwise comparison, (more info here http://msdn.microsoft.com/en-us/library/ms174965.aspx)

select * from (
    select cast(192168001001 as bigint) as ip union all
    select cast(192168001002 as bigint) as ip union all
    select cast(192168002001 as bigint) as ip union all
    select cast(192168002002 as bigint) as ip
) as ip_table
where ip & cast(192168001000 as bigint) = cast(192168001000 as bigint)

as you can see i (AND/&) the IP and the network address then i compare this to the network address, if its a match, it falls under this range

correct me if i'm wrong, i need to think more about this, very interesting stuff indeed

Result

Edit: As commented below, bigint is too small for IPv6, so this sadly does not work, the bitwise (AND) operation can not be done with binary data type, it will only accept integer types...

bigint works really well for IPv4 addresses. Unfortunately SQL Server doesn't allow for unsigned bigints which makes bitwise comparisons difficult for IPv6 as you need three columns to represent an IPv6 address as a bigint. — ahsteele, Jun 21 '13 at 21:44

score 1 · Answer 2 · answered Jun 24 '13 at 07:41

I have been looking at your SQL Fiddle, playing around with the query in question,

to be 100% clear, you need a query to find all ranges that a list of Host addresses falls under.

so you could act as if your hosts is a list/table of data, and then inner join subnets onto it (or left join if you need it to appear even without subnet)

select *
from (
  select '192.168.001.001' as ip union
  select'192.168.005.015') as hosts
inner join subnet
  on ip between ipv4_network and ipv4_broadcast

i got 4 results (there was two subnets who matched each record)

score 1 · Answer 3 · edited May 23 '17 at 12:03

Have you considered storing both ipv6 and ipv4 formats in one column?

Storing IP addresses in Microsoft SQL Server

It would require a conversion of the arbitrary source data for you comparison (or the other way) but at least you could avoid needing two separate queries to check.

I'd then be inclined to form a CTE from your source data (FOR XML?) and then join to your database table (subnet).

score 0 · Answer 4 · answered Aug 14 '13 at 21:03

I would tend to address this issue using either [xml] or [heirarchyid] (http://technet.microsoft.com/en-us/library/bb677290.aspx) and treat the data as a tree. It becomes relatively straight forward then to build a tree based on existing subnets with a [tree].[run] @subnet method that runs the tree and finds the node that matches @subnet. By treating the data as a tree (which it is, in fact) and building out common tree handling (recursive) methods you should be able to easily get to a point where you find a node if exists or insert it and get the next and prior nodes.

I can provide more detailed examples if this is of interest, but its not a trivial solution so I'm not going to spend the time on it otherwise. What I show here is a simple prototype that finds the node that is the parent (as a simple match) of input mask. I submit this as an example only, but if the solution interests you I can provide more detail or you can readily see how to build out a solution using these techniques.

Peace, Katherine

    use [test_01];

    go

    if schema_id(N'tree') is null
        execute (N'create schema tree');

    go

    if object_id(N'[tree].[run]',
                 N'FN') is not null
        drop function [tree].[run];

    go

    create function [tree].[run] (
        @network      [xml],
        @mask_to_find [sysname],
        @position     [sysname]
    )
    returns [sysname]
    as
        begin
            declare @quad [sysname] = substring(@mask_to_find,
                        0,
                        charindex(N'.',
                                  @mask_to_find,
                                  0));

            set @mask_to_find = substring(@mask_to_find,
                                          charindex(N'.', @mask_to_find, 0) + 1,
                                          len(@mask_to_find));
            set @network = @network.query('/*[@quad=sql:variable("@quad")]/*');

            if(@network.value('count (/*)',
                              'int') > 0)
                begin
                    set @position = coalesce(@position + N'.', N'') + @quad;
                end
            else
                set @position = coalesce(@position + N'.', N'') + N'000';

            if (@@nestlevel < 4)
                return [tree].[run] (@network,
                                     @mask_to_find,
                                     @position);

            return @position;
        end

    go

    declare @network [xml] = N'<subnet quad="255" >
            <subnet quad="255" >
                <subnet quad="192" />
                <subnet quad="255" />
            </subnet>
        </subnet>
        <subnet quad="10" />';
    declare @mask_to_find [sysname] = N'255.255.190.000';
    declare @position [sysname];

    select [tree].[run] (@network,
                         @mask_to_find,
                         @position)

    go

Given a subnet range and a list of IPs select all rows which the IPs fall between

4 Answers4