Fetch data from GeoIP database saved in local using PHP

Question

Time ago I used to access to Maxmind local copy of GeoIP database with some simple functions in php.

Basically their database was with a schema like:

CREATE TABLE `geoip_city_blocks` (
    `startIpNum` INT(10) UNSIGNED NOT NULL,
    `endIpNum` INT(10) UNSIGNED NOT NULL,
    `locId` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`startIpNum`, `endIpNum`),
    INDEX `startIpNum` (`startIpNum`),
    INDEX `endIpNum` (`endIpNum`),
    INDEX `locId` (`locId`)
)

In order to get country/city informations for a certain IP you had just to convert it to it's numeric counterpart with a:

$numeric_ip = ip2num($ip);

where ip2num() is:

function ip2num($ip) {

        $ip = explode(".",$ip);
        return (( (int) $ip[0] ) * 16777216) + (( (int) $ip[1] ) * 65536) + (( (int) $ip[2] ) * 256) + (( (int) $ip[3] ) * 1);

    }

And then cast a simple query:

SELECT * FROM geoip_city_blocks AS blocks LEFT JOIN geoip_city_locations AS locations ON (blocks.locId = locations.locId) WHERE ".$numeric_ip." >= startIpNum AND ".$numeric_ip." <= endIpNum LIMIT 1

This was good because with any database MySQL, SQLite, Postgre.. and so on you can cas that query and just compare 2 integers..

With the new version of GeoIP you have this new kind of schema:

CREATE TABLE blocks(
  "network" TEXT,
  "geoname_id" TEXT,
  "registered_country_geoname_id" TEXT,
  "represented_country_geoname_id" TEXT,
  "is_anonymous_proxy" TEXT,
  "is_satellite_provider" TEXT
);

Where network is espressed in a way like 120.120.120.120/8 like CIDR addresses.. and there's no StartIpNum and EndIpNum

You can see it in the image:

How can I cast a query now that I can't search via StartIPNum and EndIpNum ?

Look this topic: https://stackoverflow.com/questions/45656070/select-cidr-that-is-in-range-of-ip — Felippe Duarte, Aug 20 '18 at 17:41
@stickybit I'm using both MySQL and another set of script that relies on a SQLite (lastly I'm using more and more SQLite for various reasons). Both of them with the same table schema. — user3450548, Aug 20 '18 at 17:53

score 2 · Answer 1 · answered Aug 22 '18 at 20:12

Here's a work around I used myself:

Theres no way of knowing what the network is, so the easiest solution is to look at an ip address as if it were formatted like so: a.b.c.d which could be 24.185.38.192 where a = 24, c = 38, etc.

Get a substring containing only a, b, and c:

$ip_substring = 'a.b.c.';

Then perform a do-while loop that will end when either a valid value is returned or all networks are checked. That query will search the database for any value like your substring with a % wildcard to include any hosts and masks:

$ip_substring .= '%';
$search_ip = (string)$ip_substring;

$sql = "SELECT * FROM blocks WHERE network LIKE $search_ip";

After performing this query capture the result as an array, foreach through it then you can check each individual network to see if your ip exists within it:

foreach ( $result as $r ) {
     $network = $r['network'];

     // converts the network (CIDR) to an ip range
     $range = array();
     $cidr = explode( '/', $network );
     $range[0] = long2ip( ( ip2long( $cidr[0] )) & (( -1 << ( 32 - (int)$cidr[1] ) )) );
     $range[1] = long2ip( ( ip2long( $range[0] )) + pow( 2, (32 - (int)$cidr[1])) - 1 );

     // check that ip is within range
     if ( ( ip2long( $ip ) >= ip2long( $range[0] ) ) &&
          ( ip2long( $ip ) <= ip2long( $range[1] ) ) ) {
          // you can use the associated postal_code etc this is the row you were looking for
          echo $r['postal_code'];
     }
}

Have some logic to deal with the instance where your foreach didn't return a valid network, in which case you would need to broaden your search more by removing the last character in your ip_substring:

meaning: on last loop through you searched for 24.185.38 and returned nothing, then this time broaden your search to 24.185.3. This is just a rough and not wholly efficient way of doing this, but it works if you need a solution now.

Fetch data from GeoIP database saved in local using PHP

1 Answers1