[SQL Server] GeoIPを利用してIPアドレスの国情報を取得する方法

inno
2014-10-25 14:37 4811 0
MS-SQL / SQL Server
GeoIPを利用してIPアドレスの国情報を取得する方法。

GeoIPを利用してIPアドレスの国情報を取得することができます。
GeoIPのデータは世界のIPの国情報があるデータで、毎月上旬に更新されます。

ダウンロードは下記のリンクを参考してください。

GeoIPCountryWhois.csv download : 

上記のページで「GeoLite Country」のCSV/ZIPをダウンロードしてください。

では、「GeoIPCountryWhois.csv」データをDBテーブルに保存する為に下記のテーブルを作成しましょう。

-- #### テーブル作成
CREATE TABLE [dbo].[GeoIP](
[begin_ip] [varchar](20) NOT NULL,
[end_ip] [varchar](20) NOT NULL,
[begin_num] [numeric](18, 0) NOT NULL,
[end_num] [numeric](18, 0) NOT NULL,
[country] [varchar](5) NOT NULL,
[name] [varchar](50) NOT NULL
) ON [PRIMARY]

-- #### INDEX作成
CREATE NONCLUSTERED INDEX [IX_GeoIP_1] ON [dbo].[GeoIP] 
(
[begin_num] ASC
,[end_num] ASC
)


ダウンロードした「GeoIPCountryWhois.csv」ファイルの中には","(ダブルコーディション)区切りのCSVですので、
BULK INSERTする時はXMLフォーマットを使って登録します。
「GeoIp.xml」ファイルを作成して下記のxml内容を入れてください。

-- #### GeoIp.xml 作成。BULK INSERTを利用してデータを一括登録するため準備
<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="&quot;\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="StartIp" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="EndIp" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="StartNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="5" NAME="EndNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="6" NAME="Con1" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="Con2" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>


では、下記のSQLを使ってデータを登録してください。

-- #### BULK INSERTを利用してデータを一括登録
BULK INSERT dbo.GeoIP
FROM
'C:\innoya\GeoIPCountryWhois.csv'
WITH 
(
FORMATFILE = 'C:\innoya\GeoIp.xml'
)

これでデータは一括で登録された。

では、チェックするストアドプロシージャを作成しましょう。

CREATE PROCEDURE [dbo].[pCheckGeoIP]
@IpAddr varchar(15)
, @RtnCountry char(2) output

AS
SET NOCOUNT ON

SET @RtnCountry = ''

-- #### IPを数字に変更 #### START ####

DECLARE @IpNum numeric(18,0)
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint

SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)

SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4

-- #### IPを数字に変更 #### END ####

-- 国のIPをチェック
SELECT TOP 1 @RtnCountry = country 
FROM dbo.GeoIP WITH (NOLOCK) 
WHERE begin_num <= @IpNum AND end_num >= @IpNum


IP Addressを渡せば国のコードがリータンされます。
実際に下記の例文で実行して結果を確認してみましょう。

DECLARE @RtnCountry char(2)
EXEC dbo.pCheckGeoIP '74.119.76.100', @RtnCountry output
SELECT @RtnCountry RtnCountry

実行結果

RtnCountry
----------
US

このように接続IPの国を確認することができます。
活用例としては日本のみ接続を許可することもできますし、
特定の国のみ遮断することもできます。

ですが、ウェブサイトの場合、Google-bot, Bing-botなどの検索エンジンまで遮断する場合がありますので、
少し工夫をして開発する必要があります。

コメント