Subnet (or CIDR) IP control at T-SQL

I don't know how to exactly explain, but there is a problem about selecting and comparing query IP subnet. For example, there is a list for IP address and I have another CIDR/subnet mask list (X.X.X.0/24 etc). How can I learn that each IP address in first list is in CIDR/subnet mask list via T-SQL?

For example:

IP: 172.28.112.23 -> false

IP: 172.28.111.33 -> true

IP List Output:

SubNet Output:

Solution

You want to do exactly what a computer would do to determine if an ip address is in a subnet- ie:

1) convert the network address, subnet mask and test address to binary.

2) Check if (Network Address & Subnet Mask) = (Test Address & Subnet mask)
(& represents bitwise AND)
If this comparison is true the test address is within the subnet

The key to understanding this is to realise that IP addresses (and subnet masks) are just 32 bit numbers.
A bitwise and between 2 32 bit numbers creates a new 32 bit number with a 1 in the position where there was a 1 in both of the 2 numbers being compared, and a 0 otherwise.

EG: 1010 & 1100 = 1000 because the first digit is 1 in both numbers (yielding a 1 in the result for the first digit), but the 2nd 3rd and 4th digits are not (so give 0 in the result for the 2nd 3rd and 4th digits).

SQL Server cannot do a bitwise and between 2 binary numbers unfortunately, but it works fine between decimal representations (ie when converted to BIGINT datatype).

Therefore I'd propose you create a function that converts your IP addresses to BIGINT datatype firstly

CREATE FUNCTION dbo.fnIPtoBigInt
(
    @Ipaddress NVARCHAR(15) -- should be in the form '123.123.123.123'
)
RETURNS BIGINT
AS
BEGIN
 DECLARE @part1 AS NVARCHAR(3) 
 DECLARE @part2 AS NVARCHAR(3) 
 DECLARE @part3 AS NVARCHAR(3)
 DECLARE @part4 AS NVARCHAR(3)

 SELECT @part1 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
 SELECT @Ipaddress = SUBSTRING(@Ipaddress, LEN(@part1) + 2, 15)
 SELECT @part2 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
 SELECT @Ipaddress = SUBSTRING(@Ipaddress, LEN(@part2) + 2, 15)
 SELECT @part3 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
 SELECT @part4 = SUBSTRING(@Ipaddress, LEN(@part3) + 2, 15)

 DECLARE @ipAsBigInt AS BIGINT
 SELECT @ipAsBigInt =
    (16777216 * (CAST(@part1 AS BIGINT)))
    + (65536 * (CAST(@part2 AS BIGINT)))
    + (256 * (CAST(@part3 AS BIGINT)))
    + (CAST(@part4 AS BIGINT))

 RETURN @ipAsBigInt

END

GO

Then you can easily implement a function to test if an address is in a subnet:

CREATE FUNCTION dbo.fnIsIpaddressInSubnet
(
    @networkAddress NVARCHAR(15), -- 'eg: '192.168.0.0'
    @subnetMask NVARCHAR(15), -- 'eg: '255.255.255.0' for '/24'
    @testAddress NVARCHAR(15) -- 'eg: '192.168.0.1'
)
RETURNS BIT AS
BEGIN
    RETURN CASE WHEN (dbo.fnIPtoBigInt(@networkAddress) & dbo.fnIPtoBigInt(@subnetMask)) 
        = (dbo.fnIPtoBigInt(@testAddress) & dbo.fnIPtoBigInt(@subnetMask)) 
    THEN 1 ELSE 0 END
END

To make this a bit easier for you you'll probably want a function that can convert '/24' to a BigInt too.
'/24' is a shorthand way of writing 255.255.255.0 - ie a 32bit number with the first 24bits set to 1 (and the remaining 8 bits set to 0)

CREATE FUNCTION dbo.fnSubnetBitstoBigInt
(
    @SubnetBits TINYINT -- max = 32
)
RETURNS BIGINT
AS
BEGIN

 DECLARE @multiplier AS BIGINT = 2147483648
 DECLARE @ipAsBigInt AS BIGINT = 0
 DECLARE @bitIndex TINYINT = 1
 WHILE @bitIndex <= @SubnetBits
 BEGIN
    SELECT @ipAsBigInt = @ipAsBigInt + @multiplier
    SELECT @multiplier = @multiplier / 2
    SELECT @bitIndex = @bitIndex + 1
 END

 RETURN @ipAsBigInt

END

GO

If you create the following additional function the conversion becomes easy

CREATE FUNCTION dbo.fnIsIpaddressInSubnetShortHand
(
    @network NVARCHAR(18), -- 'eg: '192.168.0.0/24'
    @testAddress NVARCHAR(15) -- 'eg: '192.168.0.1'
)
RETURNS BIT AS
BEGIN
    DECLARE @networkAddress NVARCHAR(15)
    DECLARE @subnetBits TINYINT

    SELECT @networkAddress = LEFT(@network, CHARINDEX('/', @network) - 1)
    SELECT @subnetBits = CAST(SUBSTRING(@network, LEN(@networkAddress) + 2, 2) AS TINYINT)

    RETURN CASE WHEN (dbo.fnIPtoBigInt(@networkAddress) & dbo.fnSubnetBitstoBigInt(@subnetBits)) 
        = (dbo.fnIPtoBigInt(@testAddress) & dbo.fnSubnetBitstoBigInt(@subnetBits)) 
    THEN 1 ELSE 0 END
END

i.e.

SELECT dbo.fnIsIpaddressInSubnetShorthand('192.168.2.0/24','192.168.3.91') -- returns 0
SELECT dbo.fnIsIpaddressInSubnetShorthand('192.168.2.0/24','192.168.2.91') -- returns 1