Проверьте, находится ли IP в сетевой маске CIDR (диапазон)

У меня есть 2 таблицы ip и cidr.

В первом я храню IP. (таблица из 2 столбцов, id, ip), вот пример (значения вымышленные):

id | ip
---+-------------
 1 | 172.922.2.10
---+-------------
 2 | 194.22.10.13

Во втором я храню сетевую маску CIDR (таблица из 2 столбцов, идентификатор, cidr), вот пример (значения вымышленные):

id | cidr
---+-------------
 1 | 26.232.49.0/20
---+---------------
 2 | 14.44.182.0/24

Есть ли способ сделать запрос mysql, чтобы проверить, находятся ли IP-адреса из первой таблицы в диапазоне любой из моих сетевых масок cidr?

Примечание. Чтобы преобразовать сетевую маску cidr в диапазон IP-адресов, нажмите здесь


mysql linux ip cidr
person paulalexandru    schedule 20.08.2016    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Я бы лично рекомендовал использовать postgres, так как он имеет тип данных CIDR и мощные функции, но есть интересное обсуждение того, как делать подобные вещи в MySQL.

http://planet.mysql.com/entry/?id=29283

person tink    schedule 20.08.2016
comment
Дело в том, что мой виртуальный хостинг не поддерживает postgres. - person paulalexandru; 20.08.2016
comment
Это позор. Ссылка поможет? - person tink; 20.08.2016
comment
Я занимаюсь этим прямо сейчас, но лучше написать решение в своем ответе, потому что ссылка может исчезнуть через некоторое время, и после этого никто не сможет получить никакой пользы от вашего ответа. - person paulalexandru; 20.08.2016
comment
Справедливый комментарий. Я не уверен, что это будет считаться плагиатом, если я скопирую и вставлю оттуда. - person tink; 20.08.2016
comment
Ну, вы также можете указать источник, и я думаю, все будет в порядке. Либо можно как-то изменить тексты или сделать проще, ИДК определение плагиата. - person paulalexandru; 20.08.2016
comment
Я бы, например, не стал выбирать платформу, основанную на типе данных cidr, который можно написать за 10 минут. Боже Луиза! :п - person Drew; 21.08.2016
comment
@ Дрю, я бы тоже не стал. Просто бесстыдная заглушка для моей любимой базы данных. ;} - person tink; 21.08.2016
comment
как только я получу свое золото в mysql, я присоединюсь к вам: p - person Drew; 21.08.2016

arrow_upward
0
arrow_downward

Это всплыло в моем связанном проекте, и это, кажется, лучший результат Google для вопроса, так что вы получили ответ!

create function get_lowest_ipv4(cidr char(18)) returns bigint deterministic return INET_ATON(SUBSTRING_INDEX(cidr, '/', 1));
create function get_highest_ipv4(cidr char(18)) returns bigint deterministic return get_lowest_ipv4(cidr) + (0x100000000 >> SUBSTRING_INDEX(cidr,'/', -1)) - 1;

Затем вы можете сделать ... from ip_map where INET_ATON("ip.add.re.ss") between get_lowest_ipv4(ip) AND get_highest_ipv4(ip)

Поскольку вы объявляете функции детерминированными, они будут кэшироваться внутри mysql, и вычисление нужно будет запустить только один раз. Тогда это будет просто «целое число больше y и меньше x», что будет фактически мгновенным.

MySQL [astpp]> set @cidr="10.11.0.0/16";
Query OK, 0 rows affected (0.00 sec)

MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
|              168493056 |               168558591 | 10.11.0.0                         | 10.11.255.255                      |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.00 sec)

MySQL [astpp]> set @cidr="10.11.12.1/32";
Query OK, 0 rows affected (0.00 sec)

MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
|              168496129 |               168496129 | 10.11.12.1                        | 10.11.12.1                         |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.01 sec)

MySQL [astpp]>

Единственное важное предостережение заключается в том, что вы должны вставлять ДЕЙСТВИТЕЛЬНЫЕ CIDR. Например, 10.11.12.13/24 недопустимо. Это IP-адрес ВНУТРИ сети 10.11.12.0/24.

Если вы не можете проверить CIDR перед их вставкой (по какой-то безумной причине), вы можете изменить get_lowest_ipv4, чтобы выполнить побитовое сравнение с источником, но это гораздо менее элегантно.

INET_ATON(SUBSTRING_INDEX(`ip`, '/', 1)) & 0xffffffff ^((0x1 <<(32 -  SUBSTRING_INDEX(`ip`, '/', -1))) -1 )

Является (непроверенным) способом сопоставления недействительных CIDR.

person xrobau    schedule 10.06.2018