logo标签列表

子网（或CIDR）IP控制在T-SQL中的应用

t-sqlip-addresssubnetcidr
4
我不知道该如何准确地解释，但存在一个有关选择和比较查询IP子网的问题。例如，有一个IP地址列表，我还有另一个CIDR /子网掩码列表（X.X.X.0/24等）。如何通过T-SQL学习第一个列表中每个IP地址是否在CIDR/subnet mask列表中？
例如：
IP: 172.28.112.23 -> false IP: 172.28.111.33 -> true IP列表输出：
请参见链接：IP List 子网输出：
请参见链接：enter image description here
3个回答
7
您想要做的就是计算机用来确定IP地址是否在子网中所做的事情，即：
1) 将网络地址、子网掩码和测试地址转换为二进制。
2) 检查 (网络地址 & 子网掩码) = (测试地址 & 子网掩码)
(& 代表按位与)
如果这个比较是真的，则测试地址在子网内。
理解这一点的关键是要意识到IP地址（和子网掩码）只是32位数字。对两个32位数字进行按位与运算会创建一个新的32位数字，在两个被比较的数字中都有1的位置上放置1，否则放置0。
例如：1010 & 1100 = 1000，因为第一个数字在两个数字中都是1（导致第一个数字的结果为1），但第二个、第三个和第四个数字不是（因此第二个、第三个和第四个数字的结果为0）。
不幸的是，SQL Server不能在两个二进制数之间进行按位与运算，但在十进制表示（即转换为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）。请保留HTML标记。
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

即，也就是说。
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
0

完整解决方案

CREATE
OR ALTER FUNCTION dbo.IPv4SubnetContainsIPAddress (
  @net AS VARCHAR(15),
  @mask AS VARCHAR(15),
  @ip AS VARCHAR(15)
) RETURNS tinyint AS BEGIN DECLARE @result AS tinyint IF LEN(@mask) <= 2
SELECT
  @mask = m
FROM
  (
    VALUES
      (0, '0.0.0.0'),
      (1, '128.0.0.0'),
      (2, '192.0.0.0'),
      (3, '224.0.0.0'),
      (4, '240.0.0.0'),
      (5, '248.0.0.0'),
      (6, '252.0.0.0'),
      (7, '254.0.0.0'),
      (8, '255.0.0.0'),
      (9, '255.128.0.0'),
      (10, '255.192.0.0'),
      (11, '255.224.0.0'),
      (12, '255.240.0.0'),
      (13, '255.248.0.0'),
      (14, '255.252.0.0'),
      (15, '255.254.0.0'),
      (16, '255.255.0.0'),
      (17, '255.255.128.0'),
      (18, '255.255.192.0'),
      (19, '255.255.224.0'),
      (20, '255.255.240.0'),
      (21, '255.255.248.0'),
      (22, '255.255.252.0'),
      (23, '255.255.254.0'),
      (24, '255.255.255.0'),
      (25, '255.255.255.128'),
      (26, '255.255.255.192'),
      (27, '255.255.255.224'),
      (28, '255.255.255.240'),
      (29, '255.255.255.248'),
      (30, '255.255.255.252'),
      (31, '255.255.255.254'),
      (32, '255.255.255.255')
  ) AS o (i, m)
WHERE
  i = @mask
SELECT
  @result = IIF(Count(*) = 4, 1, 0)
FROM
  (
    SELECT
      *,
      IIF(
        o_ip BETWEEN o_subnet
        AND o_broadcast,
        1,
        0
      ) AS eq
    FROM
      (
        SELECT
          *,
          o_net & o_mask AS o_subnet,
          o_net | (255 - o_mask) AS o_broadcast
        FROM
          (
            SELECT
              o_net,
              o_mask,
              o_ip
            FROM
              (
                VALUES
                  (1, CAST(PARSENAME(@net, 4) AS INTEGER)),
                  (2, CAST(PARSENAME(@net, 3) AS INTEGER)),
                  (3, CAST(PARSENAME(@net, 2) AS INTEGER)),
                  (4, CAST(PARSENAME(@net, 1) AS INTEGER))
              ) AS c1 (i, o_net)
              LEFT JOIN (
                SELECT
                  i,
                  o_mask
                FROM
                  (
                    VALUES
                      (1, CAST(PARSENAME(@mask, 4) AS INTEGER)),
                      (2, CAST(PARSENAME(@mask, 3) AS INTEGER)),
                      (3, CAST(PARSENAME(@mask, 2) AS INTEGER)),
                      (4, CAST(PARSENAME(@mask, 1) AS INTEGER))
                  ) AS c2 (i, o_mask)
              ) AS c2 ON c1.i = c2.i
              LEFT JOIN (
                SELECT
                  i,
                  o_ip
                FROM
                  (
                    VALUES
                      (1, CAST(PARSENAME(@ip, 4) AS INTEGER)),
                      (2, CAST(PARSENAME(@ip, 3) AS INTEGER)),
                      (3, CAST(PARSENAME(@ip, 2) AS INTEGER)),
                      (4, CAST(PARSENAME(@ip, 1) AS INTEGER))
                  ) AS c3 (i, o_ip)
              ) AS c3 ON c1.i = c3.i
          ) AS t
      ) AS t
  ) AS t
WHERE
  eq = 1 RETURN @result END
GO
SELECT
  dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '255.255.224.0', '192.168.40.1') -- returns 0
SELECT
  dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '19', '192.168.40.1') -- returns 0
SELECT
  dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '255.255.192.0', '192.168.80.1') -- returns 1
SELECT
  dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '18', '192.168.80.1') -- returns 1
这种方法相比于已接受的答案有什么好处？ - Jeremy Caney
优点在于您只需要创建一个函数，此外也没有循环。 - saw-friendship
0

这不是答案本身，而是使James S answer中的某个函数更易于阅读和可能更有效的方法。

SQL Server有一个函数来处理从数据库对象名称获取部分。 这些名称有4个部分[Server] .[Database] .[Schema]. [Object]。 因此，以下代码允许您获取模式名称。 索引从右侧起作用

SELECT PARSENAME('[myServer].[master].[sys].[objects]', 2)

并没有说你不能将其用于IP地址。而且由于它对SQL的操作非常基础，我认为它已经被高度优化了。

CREATE FUNCTION dbo.fnIPtoBigInt
(
    @Ipaddress NVARCHAR(15) -- should be in the form '123.123.123.123'
)
RETURNS BIGINT
AS
BEGIN
 DECLARE @ipAsBigInt AS BIGINT
 SELECT @ipAsBigInt =
    (16777216 * (CAST(PARSENAME(@Ipaddress, 4) AS BIGINT)))
    + (65536 * (CAST(PARSENAME(@Ipaddress, 3) AS BIGINT)))
    + (256 * (CAST(PARSENAME(@Ipaddress, 2) AS BIGINT)))
    + (CAST(PARSENAME(@Ipaddress, 1) AS BIGINT))

 RETURN @ipAsBigInt

END

GO
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接