logo标签列表

检查IP是否在子网中。

mysqlipsubnet
8

我有一个名为A的表，其中存储了无符号整数形式的IP地址(ipNumeric)，还有一个名为B的表，其中存储了子网(subnetNumeric)：

INET_NTOA(ipNumeric) = 192.168.0.1
INET_NTOA(subnetNumeric) = 192.168.0.0

我想检查这个IP地址是否属于一个子网。

这些子网分为A类、B类和C类。

在MySQL中，是否有可能在运行时以合理的时间完成此操作，或者应该预先计算子网范围？

5个回答
15

可以做到。我们的想法是通过将网络掩码中最高有效位设置为1来计算子网掩码，其数量由子网类别决定。对于C类子网，这将是

SELECT -1 << 8;

然后，将子网掩码与您拥有的IP地址进行AND运算；如果IP地址在子网内，则结果应等于子网地址--标准的网络知识。因此，我们最终得到：

SELECT (-1 << 8) & INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");
更新： 是的，需要知道网络类别 或 子网掩码（这是等效的信息）。考虑如果没有这些信息，你该如何处理子网为X.Y.0.0 的情况。这是X.Y.0.0/16 还是 X.Y.0.0/8？第三个八位组恰好为0怎么办？不知道该怎么确定。
如果您知道子网掩码，则可以将查询编写为：
SELECT (-1 << (33 - INSTR(BIN(INET_ATON("255.255.255.0")), "0"))) &
       INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");
谢谢，但这是否依赖于知道子网类别？ - matiasf
1
很好的回答 - 但IPv6呢？那是可能的吗？ - TSG
@TSG 在十进制中计算IPv6将会得到一个128位的数字（相比IPv4的简单32位），我不确定在MySQL中是否可以对如此大的数字进行数学运算（例如除法和按位运算）。 - undefined
2

这是我们使用的MySQL函数。

DELIMITER $$
DROP FUNCTION IF EXISTS `ip_in_subnet_mask`$$
CREATE DEFINER=`root`@`%` FUNCTION `ip_in_subnet_mask`(ip VARCHAR(20), subnet VARCHAR(20), netmask VARCHAR(20)) RETURNS TINYINT(1)
    DETERMINISTIC
BEGIN
    RETURN (INET_ATON(ip) & INET_ATON(netmask)) = INET_ATON(subnet);
END$$
DELIMITER ;

例如：查找所有t.ip在192.168.0.x范围内的行，其中0<=x<=255。

 SELECT *
 FROM t
 WHERE
    ip_in_subnet_mask(t.ip, "192.168.0.0", "255.255.255.0")
1

首先创建表来支持测试。

CREATE TABLE a (ipNumeric INT UNSIGNED);
INSERT INTO a (ipNumeric) VALUES(INET_ATON("172.16.40.101"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("192.168.1.12"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.1.5.51"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.7.1.78"));      
CREATE TABLE b (subnetNumeric INT UNSIGNED);
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("192.168.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("10.1.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("172.16.0.0"));

现在执行一个选择操作，在表格之间基于地址和掩码 = 子网的匹配。这里我们假设是 B 类子网 (255.255.0.0)。无需进行位移，因为我们可以使用 INET_ATON() 函数。

SELECT INET_NTOA(a.ipNumeric),INET_NTOA(b.subnetNumeric) FROM a,b 
       WHERE (a.ipNumeric & INET_ATON("255.255.0.0")) = b.subnetNumeric;

+------------------------+----------------------------+
| INET_NTOA(a.ipNumeric) | INET_NTOA(b.subnetNumeric) |
+------------------------+----------------------------+
| 192.168.1.12           | 192.168.0.0                |
| 10.1.5.51              | 10.1.0.0                   |
| 172.16.40.101          | 172.16.0.0                 |
+------------------------+----------------------------+
0

当地址被存储为字符串时，我有一个解决方案。因此，如果您希望将其作为算法的一部分，可以在此处使用我的解决方案。

这有点棘手，特别是对于IPv6。在IPv6中，可以选择性地压缩段，例如1::1，它等同于1:0:0:0:0:0:0:1，这是它棘手的主要原因。

IPAddress Java库将生成mysql SQL以搜索给定子网中的地址。该链接更详细地描述了此问题。免责声明：我是项目经理。

基本算法是获取子网地址的网络部分，然后获取该部分的每个变体（例如上面的两个字符串是完整地址1::1的变体），然后计算段分隔符的数量，然后在正在匹配的地址上执行mysql子字符串，但也计算正在匹配的地址中的总分隔符数。

以下是示例代码：

public static void main(String[] args) throws IPAddressStringException {
    System.out.println(getSearchSQL("columnX", "1.2.3.4/16"));
    System.out.println(getSearchSQL("columnX", "1:2:3:4:5:6:7:8/64"));
    System.out.println(getSearchSQL("columnX", "1::8/64"));
}

static String getSearchSQL(String expression, String ipAddressStr) throws IPAddressStringException {
    IPAddressString ipAddressString = new IPAddressString(ipAddressStr);
    IPAddress ipAddress = ipAddressString.toAddress();
    IPAddressSection networkPrefix = ipAddress.getNetworkSection(ipAddress.getNetworkPrefixLength(), false);
    StringBuilder sql = new StringBuilder("Select rows from table where ");
    networkPrefix.getStartsWithSQLClause(sql, expression);
    return sql.toString();
}

输出：

Select rows from table where (substring_index(columnX,'.',2) = '1.2')
Select rows from table where (substring_index(columnX,':',4) = '1:2:3:4')
Select rows from table where ((substring_index(columnX,':',4) = '1:0:0:0') OR ((substring_index(columnX,':',2) = '1:') AND (LENGTH (columnX) - LENGTH(REPLACE(columnX, ':', '')) <= 5)))
0

这是我检查IP地址是否为本地（或特殊/保留，即非公共）的方法：

SELECT ip
FROM `network`
WHERE TRUE

OR (INET_ATON(ip) & INET_ATON('255.0.0.0')) = INET_ATON('0.0.0.0') # 0.0.0.0/8
OR (INET_ATON(ip) & INET_ATON('255.0.0.0')) = INET_ATON('10.0.0.0') # 10.0.0.0/8
OR (INET_ATON(ip) & INET_ATON('255.0.0.0')) = INET_ATON('127.0.0.0')    # 127.0.0.0/8
OR (INET_ATON(ip) & INET_ATON('255.255.0.0')) = INET_ATON('169.254.0.0')    # 169.254.0.0/16
OR (INET_ATON(ip) & INET_ATON('255.240.0.0')) = INET_ATON('172.16.0.0') # 172.16.0.0/12
OR (INET_ATON(ip) & INET_ATON('255.192.0.0')) = INET_ATON('100.64.0.0') # 100.64.0.0/10
OR (INET_ATON(ip) & INET_ATON('255.255.255.0')) = INET_ATON('192.0.0.0')    # 192.0.0.0/24
OR (INET_ATON(ip) & INET_ATON('255.255.255.0')) = INET_ATON('192.0.2.0')    # 192.0.2.0/24
OR (INET_ATON(ip) & INET_ATON('255.255.0.0')) = INET_ATON('192.168.0.0')    # 192.168.0.0/16
OR (INET_ATON(ip) & INET_ATON('255.255.255.0')) = INET_ATON('198.51.100.0') # 198.51.100.0/24
OR (INET_ATON(ip) & INET_ATON('255.254.0.0')) = INET_ATON('198.18.0.0') # 198.18.0.0/15
OR (INET_ATON(ip) & INET_ATON('255.255.255.0')) = INET_ATON('203.0.113.0')  # 203.0.113.0/24
OR (INET_ATON(ip) & INET_ATON('240.0.0.0')) = INET_ATON('240.0.0.0')    # 240.0.0.0/4
OR (INET_ATON(ip) & INET_ATON('255.255.255.255')) = INET_ATON('255.255.255.255') # 255.255.255.255
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接