查询Maxmind GeoLite2 City阻止SQL Server中的csv获取IPv6和IPv4地址的geoname

Maxmind有两个免费的GeoLite2 cvs文件，我们可以用它来查找与IP地址范围相关的位置 . 我想在SQL Server中创建一个使用导入的表（[GeoLite2_City_Blocks]和[GeoLite2_City_Locations]）的存储过程，获取在IP地址中传递的varchar（45）字符串并返回与该IP地址关联的位置 . 我原本以为网上会存在相关的T-SQL代码，但我找不到它 .

我检查了Querying GeoLite2 Country CSV in SQL，但我根本不明白Prefix_Length（在Blocks表中称为Network_mask_Length），是或是 . 如果它们与CIDR前缀相同（参见：http://en.wikipedia.org/wiki/IPv6_subnetting_reference），我会更加困惑，因为Network_mask_Length可能是118,119，或维基百科页面上未显示的任何其他数字 .

我已经看到一个网页在SQL Server中讨论IPv6，建议使用零进行填充以进行比较 . 我想不能简单地查询免费的GeoLite2而不至少在该表的最终IP地址中添加一个额外的列？

我想我可以从IPv4地址前面删除“:: ffff：”，如果这是正确的说法 . 但我仍然没有得到如何将network_mask_length（前缀）字段转换为我理解的东西 . 然后填零IPv6地址，但后来我仍然不知道该前缀是什么 .

我的问题是：

是否有可以让我走上正轨的示例代码？要么，
创建一个接受IPv4或IPv6地址的proc并返回相关位置或geoname_id需要做什么？

2 回答

7

前缀长度是网络地址中的有效位数 . 对于任何IPv6地址，这是0-128之间的数字，因为IPv6地址的长度为128位 . 例如，前缀为128的网络只包含一个IP地址 . 前缀为127的网络将包含两个地址 . 126将有4，依此类推 .

正如您所注意到的，:: FFFF：10.11.12.13形式的IPv6地址对应于删除了':: FFFF：'的IPv4地址 . ':: FFFF：'对应于地址的前96位 . 如果IPv6地址的前缀长度为126，则IPv4地址的前缀长度为30（即，最后两位是重要的） .

Google search应该显示代码片段以将CIDR表示法转换为范围 . 如果您只关心IPv4，最简单的方法是预先过滤数据，删除:: FFFF：，从前缀长度减去96，并丢弃未映射到IPv4的地址范围 .

回复于 2024-04-30T03:29:34+08:00

我使用此查询（实际上是它的MySQL）来导入块文件，将IP地址转换为从 - 到IP范围以执行简单的 BETWEEN 查询 .

我在表格中添加了两个字段： start_ip 和 end_ip . start_ip 填充 network_start_ip 的数值， end_ip 填充了使用 network_mask_length 计算的ip范围的结尾 . 正如@oschwald所说，我从前缀长度减去了96并删除了:: FFFF： .

LOAD DATA LOCAL INFILE 'GeoLite2-City-Blocks.csv' 
INTO TABLE geoip_blocks 
COLUMNS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES STARTING BY '::ffff:' -- import only ipv4 and cut off the ipv6 prefix
IGNORE 2 LINES 
(network_start_ip, network_mask_length, geoname_id, 
  registered_country_geoname_id, represented_country_geoname_id, postal_code, 
  latitude, longitude, is_anonymous_proxy, is_satellite_provider)
SET start_ip = inet_aton(network_start_ip),  -- set start ip as given
  end_ip = inet_aton(network_start_ip) + 
   (pow(2, (32 - (network_mask_length -96))) - 1) - 1, -- calc end_ip using mask
  network_mask_length = network_mask_length - 96 -- subtract 96 as we use ipv4

使用查询数据

SELECT geoname_id
FROM geoip_blocks
WHERE
INET_ATON('123.123.123.123') BETWEEN start_ip AND end_ip
LIMIT 1

回复于 2024-04-30T03:29:34+08:00

查询Maxmind GeoLite2 City阻止SQL Server中的csv获取IPv6和IPv4地址的geoname_id

2 回答

相关问题