Как я определял провайдера по IP

    Постановка задачи достаточно тривиальна: нужно по IP адресу пользователя определить провайдера. Эти данные далее должны использоваться в своей системе аналитики, а также должна быть возможность сверить их с данными, например Google или Ripe.

    Сразу скажу, что код, который будет приведен в статье — не идеален. Используемый язык программирования — PHP (конечно же лучше использовать для подобных задач C или Perl). БД — MySQL (тут лучше выбрать БД, которая будет по шустрее и может обрабатывать большое кол-во селектов. Например Tarantool). Но в повседневной жизни хватит и данных технологий\языков.

    Поиск БД и извлечение из нее данных


    Итак, приступим. Откуда же взять данные по провайдерам, да еще и желательно бесплатно? Очень быстро выбор пал на сервис RIPE. Минусом было то, что вся БД у них хранится в текстовых файлах. Некоторое время «погуглив» я не нашел конвертера для MySQL. Что ж, это немного осложняло задачу. Но не беда, вспоминая достаточно известный ролик и фразу из него: «Ты же специалист», я решил поискать описание файлов БД и написать парсер данных из текстового формата в MySQL.

    Но и тут была небольшая засада. Описание полей есть, а вот связи между таблицами мне найти не удалось (может, конечно, плохо искал). А вот это уже стало достаточно ощутимой проблемой.

    Далее я начал думать не в правильную сторону и попытался как-то сопоставить поля из файлов «налету». То есть запускал скрипт по парсингу одного файла (очень «костыльно» его парсил, что вспоминаю об этом примерно так: Праздник, ты напился и ничего не помнишь. А на следующий день тебе друзья рассказывают, как ты голый бегал по подъезду и орал матерные частушки и тебе безумно стыдно).

    В итоге через несколько часов (да-да, именно через несколько часов) я осознал, что творю ерунду и решили все данные загнать в MySQL. Благо в ходе изучения данных удалось понять какие поля мне нужны для сопоставления.

    Импорт данных в нашу БД


    Так как поля были известны, я создал следующие таблицы:

    CREATE TABLE `test_inetnum` (
      `sip` bigint(20) unsigned NOT NULL,
      `eip` bigint(20) unsigned NOT NULL,
      `org` varchar(255) NOT NULL,
      PRIMARY KEY (`sip`,`eip`),
      KEY `idx_org` (`org`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `test_organization` (
      `organisation` varchar(255) NOT NULL,
      `org-name` varchar(255) NOT NULL,
      PRIMARY KEY (`organisation`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `test_route` (
      `sip` bigint(20) unsigned NOT NULL,
      `eip` bigint(20) unsigned NOT NULL,
      `origin` varchar(255) NOT NULL,
      PRIMARY KEY (`sip`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `test_aut_num` (
      `aut-num` varchar(255) NOT NULL,
      `org` varchar(255) NOT NULL,
      PRIMARY KEY (`aut-num`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    Поля в таблицах sip и eip — это декодированные ip2long IP адреса начала диапазона и его конца.

    Класс для парсинга данных:

    <?php
    
    namespace Ripe;
    
    
    class Ripe
    {
      /**
       * @var string - папка для сохранения файлов от RIPE
       */
      public $folder;
    
      /**
       * @var int - время, хранения файла
       */
      public $time = 86400;
    
      /**
       * Ripe constructor.
       *
       * @param string $folder
       */
      function __construct($folder = '')
      {
        if (empty($folder))
        {
          $folder = __DIR__ . '/../../config/ripe';
        }
    
        if (!is_dir(__DIR__ . '/../../config/ripe'))
        {
          mkdir(__DIR__ . '/../../config/ripe', 0777, true);
        }
        $this->folder = $folder;
      }
    
      /**
       * Нужно ли обновлять файл.
       *
       * @param string $file
       *
       * @return bool
       */
      function needUpdate($file = '')
      {
        $current = time();
        if ($current - filectime($this->folder . '/' . $file) > $this->time)
        {
          return true;
        }
        return false;
      }
    
      /**
       * Загрузка файла.
       *
       * @param string $url
       */
      function download($url = '')
      {
        if (!empty($url)
            && true === $this->needUpdate($url)
        )
        {
          system("cd " . realpath($this->folder) . " && wget ftp://ftp.ripe.net/ripe/dbase/split/" . $url . " && gunzip $url");
        }
      }
    
      /**
       * Чтение файла по блокам.
       *
       * @param string $file
       * @param        $callback
       */
      function read($file = '', $callback)
      {
        if (is_file($this->folder . '/' . $file))
        {
          $f = fopen($this->folder . '/' . $file, 'r');
          if (!empty($f))
          {
            $string = [];
            while (($buffer = fgets($f)) !== false)
            {
              // запоминаем блок
              if ("\n" != $buffer)
              {
                $string[] = trim($buffer);
              }
              else
              {
                $blockArray = [];
                // дошли до конца блока
                for ($i = 0; $i < $ic = count($string); $i++)
                {
                  if (strpos($string[$i], ': ') === false)
                  {
                    break;
                  }
                  else
                  {
                    $arBlockData = explode(": ", $string[$i]);
                    if (!empty($arBlockData))
                    {
                      $key = trim($arBlockData[0]);
                      $value = trim($arBlockData[1]);
                      if (!empty($blockArray[$key]))
                      {
                        $blockArray[$key] .= $value . "\n";
                      }
                      else
                      {
                        $blockArray[$key] = $value;
                      }
                    }
                  }
                }
                // callback
                if (!empty($callback)
                    && is_callable($callback)
                    && !empty($blockArray)
                )
                {
                  call_user_func_array($callback, [
                      $blockArray,
                      $file
                  ]);
                }
                $string = '';
              }
            }
          }
        }
      }
    }

    Я пишу данные блоками, то есть блок получили, сразу записали. Для более лучшей производительности конечно же лучше данные вставлять пачками. Код для загрузки и для записи в БД организаций выглядит так:

    $ripe->download('ripe.db.organisation.gz');
    $ripe->read('ripe.db.organisation', function ($block, $file) {
      $ripeRoute = new \Ripe\RipeOrganization();
      $ripeRoute->save($block);
    });

    Поля в таблицу заносятся по тем же ключам, что есть в файлах. Если нужно изменить поля, то у метода save есть обработчик «перед вставкой», с помощью которого можно поменять названия полей и писать в измененные.

    Анализ данных и получение итоговой таблицы.


    Теперь, когда данные получены, можно создать итоговую таблицу, по которой уже и будет проходить само определение.

    CREATE TABLE `test_ripe` (
      `sip` bigint(20) unsigned NOT NULL,
      `eip` bigint(20) unsigned NOT NULL,
      `org_code` varchar(100) NOT NULL,
      `org_name` varchar(255) NOT NULL,
      PRIMARY KEY (`sip`,`eip`),
      KEY `idx_org_name` (`org_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Теперь осталось самое простое — это в итоговую таблицу перенести необходимые данные.

    Все решается парой запросов:

    select * from test_inetnum as t1
    inner join test_organization as t2 on t1.org = t2.organisation;
    
    select * from test_route as t1
    inner join test_aut_num as t2 on t1.origin = t2.`aut-num`
    inner join test_organization as t3 on t2.org = t3.organisation;

    Полученные данные загоняем в таблицу test_ripe и получаем счастье.

    Результаты


    Результаты превзошли мои ожидания. Определение провайдера работает достаточно точно (протестировал на пуле адресов). Также, приятным бонусом оказалось то, что по этой базе определение работает лучше чем у 2ip

    Ну и собственно определить провайдера по текущей таблице можно таким запросом:

    SELECT * FROM `test_ripe` WHERE `sip` <= '33554435' AND `eip` >= '33554435' ORDER BY `eip` DESC LIMIT 1

    Сортировка в запросе обязательно нужна, так как бывает, что один диапазон входит в другой.
    • +8
    • 10,3k
    • 9
    Поделиться публикацией
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 9
      +1
      В таблице лучше хранить не начала и концы диапазонов, а блоки netnum/netmask, если диапазон нужно выразить несколькими такими блоками — то разбивать (но, обычно, адреса и выдают блоками). Поиск делать по ip & netmask == netnum, и сортировать по убыванию по количеству бит в netmask. Такую структуру можно вообще без базы данных создать, в виде префиксного дерева — будет работать очень быстро. Но даже в вашей схеме можно было бы использовать функцию INET_ATON(), чтобы запрос выглядел опрятнее.

      В вашем варианте сортировка может работать не совсем правильно, при одинаковых sip надо искать наименьший eip.

      И ещё момент, у вас в примере запроса sip и eip сравниваются с разными адресами — вы что-то хотели этим проиллюстрировать, или опечатка?
        0
        Про сортировку вы правы, так же как и про разный ip. Это были опечатки — исправил. Спасибо.
        +5

        кажется статья о том, как вы скачивали архив и дамп загоняли в БД, но не об определении провайдера по IP.

          0
          Не согласен с вами. В статье рассказан опыт получения, создания БД по провайдерам, а также вывод провайдера по ip адресу клиента в виде запроса.
          +2
          Ripe, это не весь интернет.

          Проще всего воспользоваться готовой базой от maxmind:
          dev.maxmind.com/geoip/geoip2/geolite2-asn-csv-database

          Кроме ipv4 ещё бывают ipv6.
          Выше вам указали на INET_ATON, для ipv6 надо использовать INET6_ATON.

          p.s. Мой ipv6 адрес от домашнего провайдера:
          2a00:1c78:1:1e95:1114:6cd:600a:111
            0
            Ripe, это не весь интернет.
            да, вы правы.
            Кроме ipv4 ещё бывают ipv6.
            в ripe они есть, но в статье не рассмотрены
            Проще всего воспользоваться готовой базой от maxmind:
            dev.maxmind.com/geoip/geoip2/geolite2-asn-csv-database

            бесплатная версия же урезана. А полная версия только за деньги.
              0
              Возможно, но в исходных условиях было сказано «IP -> Country Code». Для этого достаточно и урезанной версии. Далее, с учетом того, что анонсы более специфичные, чем /24, фильтруются, можно считать, что все адреса из блока /24 с вероятностью приближающейся к 1 будут иметь один Country Code (и один Organization Id), соответственно, и проверку можно значительно ускорить / упростить за счет другой структуры таблицы в БД.

              Касательно взаимосвязи между объектами в RIPEdb: www.ripe.net/manage-ips-and-asns/db/support/documentation/ripe-database-documentation/rpsl-object-types
            +1
            В базе, сделано верно, надо хранить именно диапазон, начало и конец в виде чисел (не слушайте глупости других комментаторов).
            Когда потребуется повысить скорость поиска, то сможете выкинуть SQL и перейти на более быструю базу в которой по sip, eip построить базу с BTREE индексами, они отлично ложатся именно на диапазон.
            Тогда поиск станет моментальным, а скорость ответа возрастёт минимум в 100 раз.
            Как пример, решение с бд в 40 миллионов записей (все провайдеры мира), база данных Tarantool, язык golang.
            Скорость нахождения ответа через api (REST/JSON) — 1.8мс-2мс. То есть, фактически все реальные затраты это кодирование/декодирование json + tcp/ip конекты. :)

            Кстати, по некоторым табличкам не хватает индексов, а они сильно влияют на скорость поиска в SQL!

            Ну а по теме самой статьи и её сути, могу сказать только то что мельчает хабр, ежегодный постоянный регресс. И это печально… Целая статья о скачивании и заливке базы в бд :(
            Грустно это.
              0
              Нужно больше таких статей.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое