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?

Answer 1

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

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...