从IP确定国家-IPv6 [英] Determine country from IP - IPv6

问题描述

在我的项目中，我在postgres（plpgsql）中有一个函数，该函数根据给定的IP地址确定国家/地区：

In my project, I have a function in postgres (plpgsql) that determines country from a given ip address:

CREATE OR REPLACE FUNCTION get_country_for_ip(character varying)
  RETURNS character varying AS
$BODY$
declare
    ip  ALIAS for $1;
    ccode   varchar;
    cparts  varchar[];
    nparts  bigint[];
    addr    bigint;
begin
    cparts := string_to_array(ip, '.');
    if array_upper(cparts, 1) <> 4 then
        raise exception 'gcfi01: Invalid IP address: %', ip;
    end if;
    nparts := array[a2i(cparts[1])::bigint, a2i(cparts[2])::bigint, a2i(cparts[3])::bigint, a2i(cparts[4])::bigint];
    if(nparts[1] is null or nparts[1] < 0 or nparts[1] > 255 or
       nparts[2] is null or nparts[2] < 0 or nparts[2] > 255 or
       nparts[3] is null or nparts[3] < 0 or nparts[3] > 255 or
       nparts[4] is null or nparts[4] < 0 or nparts[4] > 255) then
        raise exception 'gcfi02: Invalid IP address: %', ip;
    end if;

    addr := (nparts[1] << 24) | (nparts[2] << 16) | (nparts[3] << 8) | nparts[4];
    addr := nparts[1] * 256 * 65536 + nparts[2] * 65536 + nparts[3] * 256 + nparts[4];

    select into ccode t_country_code from ip_to_country where addr between n_from and n_to limit 1;
    if ccode is null then
        ccode := '';
    end if;
    return ccode;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

这可能不是最有效的方法，但它确实可以做到。请注意，它使用一个内部表（ ip_to_country ），该表包含以下数据（数字n_from和n_to是 long 地址范围的开始和结束的值：

This may not be the most efficient, but it does the job. Note that it uses an internal table (ip_to_country), which contains data as below (the numbers n_from and n_to are the long values of the start and end of address ranges:

  n_from  |   n_to   | t_country_code 
----------+----------+----------------
        0 | 16777215 | ZZ
 16777216 | 16777471 | AU
...

现在我们也开始研究IPv6寻址-和我需要为IPv6地址添加类似的功能，也有一组类似的IPv6数据，如下所示：

Now we are starting to look at the IPv6 addressing as well - and I need to add similar functionality for IPv6 addresses. I have a similar set of data for IPv6, which looks like this:

 t_start     | t_end                                   | t_country_code
-------------+-----------------------------------------+----------------
 ::          | ff:ffff:ffff:ffff:ffff:ffff:ffff:ffff   | ZZ
 100::       | 1ff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  | ZZ
...
 2000::      | 2000:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ZZ
...
 2001:1200:: | 2001:1200:ffff:ffff:ffff:ffff:ffff:ffff | MX
...

现在，给定IP地址： ：1 ，我如何（1）检查其是否为有效的IPv6地址，以及（2）获取相应的国家/地区映射？

Now, given an IP address ::1, how do I (1) check that it's a valid IPv6 address and (2) get the corresponding country mapping?

推荐答案

我相信我找到了解决方案。它涉及先修改数据，然后再对输入进行一些按摩。

I believe I found the solution. It involves modifying the data first and then some massaging of the input. Here's what worked.

首先，需要对数据进行转换，以使所有地址都已满，且不会缩短，并且去除了分号分隔符。我的问题中显示的样本数据将转换为：

First, the data needs to be converted so that all addresses are full, without shortening, with semicolon separators removed. The sample data shown in my question is converted to:

 t_start                          | t_end                            | t_country_code
----------------------------------+----------------------------------+----------------
 00000000000000000000000000000000 | 00ffffffffffffffffffffffffffffff | ZZ
 01000000000000000000000000000000 | 01ffffffffffffffffffffffffffffff | ZZ
...
 20000000000000000000000000000000 | 2000ffffffffffffffffffffffffffff | ZZ
...
 20011200000000000000000000000000 | 20011200ffffffffffffffffffffffff | MX
...

这就是存储在数据库中的内容。

This is what is stored in the database.

下一步是将代码中收到的IP地址转换为相同格式。这是使用以下代码在PHP中完成的（假定 $ ip_address 是传入的IPv6地址）：

The next step was to convert the IP address received in the code to be in the same format. This is done in PHP with the following code (assume that $ip_address is the incoming IPv6 address):

$addr_bin = inet_pton($ip_address);                                                                                                              
$bytes = unpack('n*', $addr_bin);
$ip_address = implode('', array_map(function ($b) {return sprintf("%04x", $b); }, $bytes));

现在变量 $ ip_adress 将包含全部IPv6地址，例如

Now variable $ip_adress wil contain the full IPv6 address, for example

:: => 00000000000000000000000000000000
2001:1200::ab => 200112000000000000000000000000ab

依此类推。

现在您只需将此完整地址与数据库中的范围进行比较即可。我向数据库添加了第二个函数来处理IPv6地址，如下所示：

Now you can simply compare this full address with the ranges in the database. I added a second function to the database to deal with IPv6 addresses, which looks like this:

CREATE OR REPLACE FUNCTION get_country_for_ipv6(character varying)
  RETURNS character varying AS
$BODY$
declare
    ip  ALIAS for $1;
    ccode   varchar;
begin
    select into ccode t_country_code from ipv6_to_country where addr between n_from and n_to limit 1;
    if ccode is null then
        ccode := '';
    end if;
    return ccode;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

最后，在我的php代码中，我添加了基于调用一个或另一个Postgres函数的代码输入ip_address。

Finally, in my php code I added the code that calls one or the other Postgres function based on the input ip_address.

这篇关于从IP确定国家-IPv6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！