tsql - T-SQL 中的子网(或 CIDR)IP 控制

Question

我不知道如何准确解释，但是在选择和比较查询IP子网时存在问题。例如，有一个 IP 地址列表，我有另一个 CIDR/子网掩码列表(X.X.X.0/24 等)。如何通过 T-SQL 了解第一个列表中的每个 IP 地址都在 CIDR/子网掩码列表中？

例如:

IP:172.28.112.23 -> 假

IP:172.28.111.33 -> 真

IP 列表输出:



子网输出:

Answer 1

您想完全按照计算机执行的操作来确定 IP 地址是否在子网中 - 即:

1) 将网络地址、子网掩码和测试地址转换为二进制。

2)检查是否(网络地址和子网掩码)=(测试地址和子网掩码)
(& 代表按位与)
如果此比较为真，则测试地址在子网内

理解这一点的关键是要意识到 IP 地址(和子网掩码)只是 32 位数字。
按位和在 2 个 32 位数字之间创建一个新的 32 位数字，在被比较的 2 个数字中都有 1 的位置为 1，否则为 0。

EG:1010 & 1100 = 1000 因为两个数字的第一个数字都是 1(在第一个数字的结果中产生一个 1)，但第二个第三个和第四个数字不是(所以在第二个第三个和第四个数字的结果中给出 0第 4 位数字)。

不幸的是，SQL Server 不能在 2 个二进制数之间进行按位运算，但它在十进制表示之间(即转换为 BIGINT 数据类型时)可以正常工作。

因此，我建议您首先创建一个将 IP 地址转换为 BIGINT 数据类型的函数

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

然后您可以轻松实现一个功能来测试地址是否在子网中:

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

为了让这对您来说更容易一些，您可能需要一个也可以将 '/24' 转换为 BigInt 的函数。
'/24' 是 255.255.255.0 的简写方式 - 即一个 32 位数字，前 24 位设置为 1(其余 8 位设置为 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

如果您创建以下附加功能，转换变得容易

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

IE。

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