从SELECT查询中直接匹配IP和IP + CIDR的方法是否存在？

Question

从SELECT查询中直接匹配IP和IP + CIDR的方法是否存在？

mysqlipmaskbitmaskcidr

23

类似这样：

SELECT COUNT(*) AS c FROM BANS WHERE typeid=6 AND (SELECT ipaddr,cidr FROM BANS) MATCH AGAINST 'this_ip';

这样你就不需要先从数据库获取所有记录，然后逐个匹配。

如果 c > 0 那么就匹配成功了。

BANS 表：

id int auto incr PK
typeid TINYINT (1=hostname, 4=ipv4, 6=ipv6)
ipaddr BINARY(128)
cidr INT
host VARCHAR(255)

数据库：MySQL 5

查询时已知 IP 和 IPv 类型（4 或 6）。

例如，IP 的二进制格式为 ::1。

例如，封禁的 IP 是 ::1/64。

- raspi

raspi，我知道这个问题有点老了，但是...你的CIDR列是网络掩码中一位的数量吗？所以对于IPv6，它总是64，而对于IPv4，它是0到32之间的数字？虽然我想0可能会禁止所有地址... :) - ErikE

5个回答

4

IPv4地址、网络地址和子网掩码都是UINT32数字，并以“点分十进制”的人类可读形式呈现。内核中的路由表代码在检查地址是否属于给定的网络空间（网络/子网掩码）时执行非常快速的按位与比较。这里的诀窍是将点分十进制IP地址、网络地址和子网掩码作为UINT32存储在您的表中，然后执行相同的32位按位与匹配。例如：

SET @test_addr = inet_aton('1.2.3.4');
SET @network_one = inet_aton('1.2.3.0');
SET @network_two = inet_aton('4.5.6.0');
SET @network_netmask = inet_aton('255.255.255.0');

SELECT (@test_addr & @network_netmask) = @network_one AS IS_MATCHED;
+------------+
| IS_MATCHED |
+------------+
|          1 |
+------------+

SELECT (@test_addr & @network_netmask) = @network_two AS IS_NOT_MATCHED;
+----------------+
| IS_NOT_MATCHED |
+----------------+
|              0 |
+----------------+

- Paul

要从24而不是255.255.255.0中获取@network_netmask的值，您可以使用以下代码：((POWER(2,32)-1)<<(32-@mask)) & (POWER(2,32)-1);其中@mask是24的值。来源 - undefined

3

对于 IPv4，您可以使用：

SET @length = 4;

SELECT  INET_NTOA(ipaddr), INET_NTOA(searchaddr), INET_NTOA(mask)
FROM  (
  SELECT
        (1 << (@length * 8)) - 1 & ~((1 << (@length * 8 - cidr)) - 1) AS mask,
        CAST(CONV(SUBSTR(HEX(ipaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS ipaddr,
        CAST(CONV(SUBSTR(HEX(@myaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS searchaddr
  FROM  ip
) ipo
WHERE ipaddr & mask = searchaddr & mask

- Quassnoi

谢谢。我很确定不需要表格来做CIDR，而是可以像这样动态计算/创建掩码。我相信掩码可以简化为 ~((1 << cidr)-1)，其中 ((1<<cidr)-1) 将最右边的 cidr 位设置为 true，而 NOT, 即 ~ 反转无符号长整型中的所有位，使最右边的 cidr 位为 false，所有左侧的位都为 true。使用程序员界面的Win7+计算器在这方面非常有帮助。 - user1899861

1

将IP地址范围生成为整数

如果你的数据库不支持复杂的位运算，你可以使用简化的基于整数的方法。

以下示例使用PostgreSQL：

select (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) 
        as network,

       (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) + cast(
          pow(256, (32 - cast(split_part('4.0.0.0/8', '/', 2) as bigint)) / 8) - 1 as bigint
        ) as broadcast;

- Stephen Blum

0

嗯，你可以建立一个CIDR掩码表，将其连接起来，然后使用ban block IP地址的掩码和IP进行AND操作（MySQL中为&），这样做是否符合您的需求？

如果您不想建立掩码表，可以使用x=64或x=32(根据需要而定)计算掩码：-1 << (x-cidr)。

- MarkusQ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Guss · Accepted Answer

记住，IP地址不是文本地址，而是数字ID。我有一个类似的情况（我们正在进行地理IP查找），如果您将所有IP地址存储为整数（例如，我的IP地址是192.115.22.33，因此它存储为3228767777），那么您可以使用右移运算符轻松查找IP。

所有这些类型的查找的缺点是您无法受益于索引，每次查找时都必须进行完整的表扫描。上述方案可以通过同时存储CIDR网络的网络IP地址（范围的开始）和广播地址（范围的结束）来改进，因此例如要存储192.168.1.0/24，您可以存储两列：

network     broadcast
3232235776, 3232236031

然后你可以通过以下方式进行匹配：

SELECT count(*) FROM bans WHERE 3232235876 >= network AND 3232235876 <= broadcast

这将允许您将CIDR网络存储在数据库中，并通过利用快速数字索引快速有效地与IP地址匹配。

以下为讨论备注：

MySQL 5.0包括一种名为"索引合并交集"的范围查询优化功能，可以加速此类查询（避免全表扫描），只要满足以下条件：

存在一个多列索引与查询中的列完全匹配且按顺序排列。因此 - 对于上面的查询示例，索引需要是(network, broadcast)。
所有数据都可以从索引中检索。对于COUNT(*)是正确的，但对于SELECT * ... LIMIT 1则不正确。

MySQL 5.6包括一种名为MRR的优化功能，也可以加速完整行检索，但超出了本答案的范围。