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

- такой же как Forbes, только лучше.

Постановка задачи достаточно тривиальна: нужно по 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

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

Вы можете помочь и перевести немного средств на развитие сайта



Комментарии (9):

  1. ToSHiC
    /#10615184

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

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

    И ещё момент, у вас в примере запроса sip и eip сравниваются с разными адресами — вы что-то хотели этим проиллюстрировать, или опечатка?

    • vitalykhy
      /#10615214

      Про сортировку вы правы, так же как и про разный ip. Это были опечатки — исправил. Спасибо.

  2. zelenin
    /#10615202

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

    • vitalykhy
      /#10615216

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

  3. ExH
    /#10615240

    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

    • vitalykhy
      /#10615246

      Ripe, это не весь интернет.
      да, вы правы.
      Кроме ipv4 ещё бывают ipv6.
      в ripe они есть, но в статье не рассмотрены
      Проще всего воспользоваться готовой базой от maxmind:
      dev.maxmind.com/geoip/geoip2/geolite2-asn-csv-database

      бесплатная версия же урезана. А полная версия только за деньги.

      • kornix
        /#10617184

        Возможно, но в исходных условиях было сказано «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

  4. bosom
    /#10617284

    В базе, сделано верно, надо хранить именно диапазон, начало и конец в виде чисел (не слушайте глупости других комментаторов).
    Когда потребуется повысить скорость поиска, то сможете выкинуть SQL и перейти на более быструю базу в которой по sip, eip построить базу с BTREE индексами, они отлично ложатся именно на диапазон.
    Тогда поиск станет моментальным, а скорость ответа возрастёт минимум в 100 раз.
    Как пример, решение с бд в 40 миллионов записей (все провайдеры мира), база данных Tarantool, язык golang.
    Скорость нахождения ответа через api (REST/JSON) — 1.8мс-2мс. То есть, фактически все реальные затраты это кодирование/декодирование json + tcp/ip конекты. :)

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

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

  5. php7
    /#10619132

    Нужно больше таких статей.