5

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

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

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

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

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

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

  • Спасибо, но зависит ли это от знания класса подсети?

    от matiasf
  • @matiasf: Это так. Посмотрите на обновление.

    от
  • Отличный ответ - а как же IPv6? Это вообще возможно?

    от
  • 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

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

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

  • 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)))