MS-SQL / SQL Server
MaxMind GEOIP Function for Microsoft SQL SERVER
GeoIPを利用してIPアドレスの国情報を取得することができます。
GeoIPのデータは世界のIPの国情報があるデータで、毎月上旬に更新されます。
ダウンロードは下記のリンクを参考してください。
GeoIPCountryWhois.csv download :
上記のページで「GeoLite Country」のCSV/ZIPをダウンロードしてください。
では、「GeoIPCountryWhois.csv」データをDBテーブルに保存する為に下記のテーブルを作成しましょう。
-- #### テーブル作成
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GeoIP](
[StartIp] [varchar](39) NOT NULL,
[EndIp] [varchar](39) NOT NULL,
[StartNo] [numeric](18, 0) NOT NULL,
[EndNo] [numeric](18, 0) NOT NULL,
[NationCode] [varchar](5) NOT NULL,
[NationName] [varchar](80) NULL
) ON [PRIMARY]
-- #### INDEX作成
CREATE NONCLUSTERED INDEX [IX_GeoIP_StartNo_EndNo] ON [dbo].[GeoIP]
(
[StartNo] ASC,
[EndNo] ASC
)
GO
ダウンロードした「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 FUNCTION [dbo].[fnGetGeoIPNationCode] (
@IpAddr varchar(39)
)
RETURNS VARCHAR(5) AS
BEGIN
DECLARE @RtnVal varchar(5)
SET @RtnVal = ''
-- #### 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 @RtnVal = NationCode
FROM dbo.GeoIP WITH (NOLOCK)
WHERE StartNo <= @IpNum AND EndNo >= @IpNum
RETURN @RtnVal
END
IP Addressを渡せば国のコードがリータンされます。
実際に下記の例文で実行して結果を確認してみましょう。
SELECT dbo.fnGetGeoIPNationCode('74.119.76.100')
このように接続IPの国を確認することができます。
活用例としては日本のみ接続を許可することもできますし、
特定の国のみ遮断することもできます。
ですが、ウェブサイトの場合、Google-bot, Bing-botなどの検索エンジンまで遮断する場合がありますので、
少し工夫をして開発する必要があります。