Вопрос по ip, mysql, subnet – Проверьте, есть ли IP в подсети

5

У меня есть таблица A с IP-адресами (ipNumeric), хранящимися как беззнаковые целые, и таблица B с подсетями (subnetNumeric):

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

Я хотел бы проверить, является ли этот IP-адрес членом подсети.

Подсети - это класс A, B и C.

Возможно ли это сделать в разумные сроки в MySQL на лету или следует предварительно рассчитать диапазоны подсетей?

Ваш Ответ

4   ответа
0

Это функция 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 & lt; = x & lt; = 255

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

У меня есть решение, когда адреса хранятся в виде строк. Так что если вы хотите сделать эту часть своего алгоритма, вы можете использовать мое решение здесь.

Это немного сложно, особенно для IPv6. В IPv6 необязательно могут быть сжатые сегменты, например 1 :: 1, что эквивалентно 1: 0: 0: 0: 0: 0: 0: 1, что является основной причиной, по которой это сложно.

Java-библиотека IP-адреса создаст 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();
}

Ouptut:

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

Сначала создайте таблицы для поддержки тестирования.

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"));

Теперь сделайте выбор, находя совпадения между таблицами на основе адреса & amp; маска = подсеть. Здесь мы предполагаем подсети класса 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                 |
+------------------------+----------------------------+
11

Конечно, это выполнимо. Идея состоит в том, что мы вычисляем маску подсети, устанавливая самые значимые биты равными 1, столько, сколько продиктовано классом подсети. Для класса C это было бы

SELECT -1 << 8;

Затем, И маска подсети с IP-адресом, который у вас есть; если IP-адрес находится внутри подсети, результат должен быть равен адресу подсети - стандартный сетевой материал. Итак, мы заканчиваем с:

SELECT (-1 << 8) & INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");

Update: Да, нужно знать класс сетиor маска подсети (которая является эквивалентной информацией). Подумайте, как вы могли бы справиться со случаем, когда подсетьX.Y.0.0 если у вас не было этой информации. ЭтоX.Y.0.0/16 или жеX.Y.0.0/8 где третий октетjust happens быть 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
@matiasf: Это так. Посмотрите на обновление.
Отличный ответ - а как же IPv6? Это вообще возможно?

Похожие вопросы