首页 文章

如何在SQL中计算IPv6 CIDR路由前缀？

提问于
浏览 次
3

我一直在使用在SQL Server 2012实例上运行的T-SQL从IPv4和IPv6地址范围生成CIDR . 通常我们的应用程序（在db之外）负责计算CIDR，但我目前需要在数据库中进行CIDR计算 . 由于IPv6太大而无法存储在 bigint 数据类型中，因此我们将IP地址存储为 binary(4) 或 binary(16) .

如果有点丑陋的话，计算IPv4范围的路由前缀相对简单：

declare @ipv4_begin binary(4)
       ,@ipv4_end binary(4)

set @ipv4_begin = 0xC0A80000 -- '192.168.000.000'
set @ipv4_end = 0xC0A8FFFF   -- '192.168.255.255'

select 32 - LOG(
                Cast(@ipv4_end As bigint)
                - Cast(@ipv4_begin As bigint) + 1, 2
               ) as ipv4_route_prefix

遗憾的是，针对IPv6修改的相同查询不起作用 . 它不起作用的原因是因为IPv6收件人大于 bigint 数据类型中存储的收件人（我们使用 binary(4) 和 binary(16) 进行存储的原因）：

declare @ipv6_begin binary(16)
       ,@ipv6_end binary(16)

set @ipv6_begin = 0xFC000000000000000000000000000000 -- fc00:: 
set @ipv6_end = 0xFC00000000000000FFFFFFFFFFFFFFFF   -- fc00::ffff:ffff:ffff:ffff

-- This will cause error: 'An invalid floating point operation occurred.'
select 128 - LOG(
                 Cast(@ipv6_end As bigint)
                 - Cast(@ipv6_begin As bigint) + 1, 2
                ) as ipv6_route_prefix

除了不成熟的按位操作（最终不起作用）之外，我还没有提出任何可以在数据库中进行此计算的操作 .

可以从T-SQL中的IPv6地址范围计算IPv6 CIDR的路由前缀吗？

sql-server tsql sql-server-2012 network-programming ipv6

1 回答

  • 2

    好吧，你已经有了一个巧妙的IPv4技巧 - 只需将值斩到我们可以处理的最大块中并重复这个技巧 .

    SELECT ISNULL(MIN(32 - B + N), 128) 
FROM (VALUES
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    1, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  1, 4)
        ) + 1, 2),  0),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    5, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  5, 4)
        ) + 1, 2), 32),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    9, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  9, 4)
        ) + 1, 2), 64),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,   13, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin, 13, 4)
        ) + 1, 2), 96)
) AS Bits(B, N)
WHERE B <> 0;

    我们确定每个块中第一个设置位的位置，然后选择最低的这个位 - 如果没有这样的位，则所有位都匹配（ ISNULL 涵盖了这种情况） . 如果用"32"替换"128"，这也适用于IPv4，但显然你已经有了一个表达式 . 我们可以将它打包成一个适用于以下两者的函数：

    CREATE FUNCTION dbo.CidrPrefixFromRange(@ip_begin VARBINARY(16), @ip_end VARBINARY(16)) 
RETURNS TABLE AS
RETURN
    SELECT ISNULL(MIN(32 - B + N), DATALENGTH(@ip_begin) * 8) AS Prefix
    FROM (VALUES
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    1, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  1, 4)
            ) + 1, 2),  0),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    5, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  5, 4)
            ) + 1, 2), 32),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    9, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  9, 4)
            ) + 1, 2), 64),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,   13, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin, 13, 4)
            ) + 1, 2), 96)
    ) AS Bits(B, N)
    WHERE B <> 0;

    样品用途：

    -- 192.168.100.0 - 192.168.103.255
SELECT * FROM dbo.CidrPrefixFromRange(0xc0a86400, 0xc0a867ff) -- /22

-- 192.168.0.0 - 192.168.255.255
SELECT * FROM dbo.CidrPrefixFromRange(0xC0A80000, 0xC0A8FFFF) -- /16

-- fc00:: - fc00::ffff:ffff:ffff:ffff
SELECT * FROM dbo.CidrPrefixFromRange(
    0xFC000000000000000000000000000000,
    0xFC00000000000000FFFFFFFFFFFFFFFF
) -- /64

-- 127.0.0.1 - 127.0.0.1
SELECT * FROM dbo.CidrPrefixFromRange(0x7f000001, 0x7f000001) -- /32

    没有关于这是多么有效的承诺...如果你想要效率，这不是你想要在T-SQL中做的事情 . :-)

    附录：我使用表值函数而不是更简单的标量值函数（毕竟，我们只返回一个值）的原因是标量值函数perform far worse inside a query . 内联表值函数可以有效地预见到这样的用途 - 任何东西都比标量值函数更好 .

    回复于

相关问题