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="""/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR=""\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などの検索エンジンまで遮断する場合がありますので、
少し工夫をして開発する必要があります。