29 December 2009

(Perl) DBIでSQLiteを使う場合の文字エスケープ

PerlでSQLクエリを実行する場合、実際にちゃんと特殊文字がエスケープ(クオート)されているか心配になって、調べてみた。

Perlのサンプルコード

#!/usr/bin/perl use strict; # 変数の宣言を強制する use DBI; # DBI モジュールを利用する my $dbh = undef; my $sth = undef; my $strQuery = ""; my $strTmp = ""; eval{ # DBに接続 $dbh = DBI->connect($strDsn, "", "", {PrintError => 1, AutoCommit => 1}); if(!$dbh) { die();} $dbh->trace(2,"./dbi_trace.txt"); # デバッグトレースをファイル出力 print("input new data string to add : "); $strTmp = <STDIN>; $strTmp =~ s/(\xa|\xd)//eg; # 行末の改行を取る # データを書き込む $strQuery = "INSERT INTO test_tbl (data) VALUES (?)"; $sth = $dbh->prepare($strQuery); if($sth){ $sth->execute($strTmp);} if($sth){ $sth->finish();} # DBを閉じる $dbh->disconnect(); }; if($@){ # evalによるDBエラートラップ:エラー時の処理 $dbh->disconnect(); print("DBI error : ".$@."\n"); } exit(0);

$DBI::prepare が特殊文字を適切にクオートするらしいが、本当にクオートされているのか…

『#データを書き込む』 の部分を次のように書き換えて、試してみる。

# データを書き込む $strQuery = "INSERT INTO test_tbl (data) VALUES ('".$strTmp."')"; $dbh->do($strQuery);


データがどのように格納され、SELECT命令でちゃんと読み出すことが出来るか試してみた。

■ 実験1
ASCII記号を片っ端から入力してみる。
入力文字列: 『 !_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ 』

(1) $DBI::prepare 関数を用いた場合

INSERT命令は成功
SELECT命令出力: 『 !_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ 』

SQLiteデータファイルのバイナリダンプ
20091229-sqlitequote-prepare01.png

トレースログに次のように記録されている。

-> prepare for DBD::SQLite::db (DBI::db=HASH(0x82cfe70)~0x82cfe28 'INSERT INTO test_tbl (data) VALUES (?)') sqlite trace: prepare statement: INSERT INTO test_tbl (data) VALUES (?) at dbdimp.c line 284 <- prepare= DBI::st=HASH(0x81ffeec) at test_dbi_quote.pl line 61 -> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x82d0074)~INNER) <- DESTROY= undef at test_dbi_quote.pl line 62 -> execute for DBD::SQLite::st (DBI::st=HASH(0x81ffeec)~0x82cfef4 '!_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@') <- execute= 1 at test_dbi_quote.pl line 62

(2) $DBI::do 関数をいきなり用いた場合

INSERT命令が失敗
トレースログに次のように記録されている。

-> do in DBD::_::db for DBD::SQLite::db (DBI::db=HASH(0x82cf67c)~0x82cf634 'INSERT INTO test_tbl (data) VALUES ('!_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@')') sqlite trace: prepare statement: INSERT INTO test_tbl (data) VALUES ('!_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@') at dbdimp.c line 284 !! ERROR: 1 'near "_": syntax error' (err#0) <- do= undef at test_dbi_quote.pl line 67

■ 実験2
SQL命令が誤動作するか試してみる
書き込みに用いた文字列は、たとえば 『 TEST '); INSERT INTO test_tbl (data) VALUES ('EXAMPLE 』

(1) $DBI::prepare 関数を用いた場合

INSERT命令が成功し、入力した文字列行がすべて1つのデータとして格納された。
SELECT命令出力: 『 TEST '); INSERT INTO test_tbl (data) VALUES ('EXAMPLE 』 と読み出された。

(2) $DBI::do 関数をいきなり用いた場合

INSERT命令は成功。 (したように見える)
SELECT命令で読み出されるデータは 『 TEST 』のみ。(登録されたデータも1つのみ)

つまり、TESTの後ろの 『 ' 』(シングルクオーテーション) は適切にクオート(エスケープ)処理されなかったようである。 1つの do 関数では、2つのSQL命令が実行できないという感じだ。(この例では…)

■ 実験3
実際にどのようにエスケープされているのか、確認してみる。

$strTmp = <STDIN>; $strTmp =~ s/(\xa|\xd)//eg; # 行末の改行を取る print("quoted sql str = ".$dbh->quote($strTmp)."\n");

入力 『 !_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ 』 に対して、
出力は 『 '!_"_#_$_%_&_''_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@' 』 となった。

シングルクオーテーション 『 ’ 』 のみがエスケープされている。

■ さらに安全のために
$DBI::prepare を使えば、一応問題なく特殊文字のエスケープ処理がされるようだ。 さらに安全性を高めるためには、やはり手動で『危険そうな』文字を消去したりクオートしたりする方が 『枕を高くして寝れる』。

URI::Escape ライブラリの uri_escape を使ってエスケープ処理する方法

$strTmp = uri_escape($strTmp, "'%"); # シングルクオーテーションのみエスケープ処理する # 入力:!_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ # 出力:!_"_#_$_%25_&_%27_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ $strTmp = uri_escape($strTmp); # デフォルトのエスケープ処理をする # 入力:!_"_#_$_%_&_'_(_)_=_~_|_\_^_-_;_:_]_+_*_}_[_{_,_._/_`_@ # 出力:!_%22_%23_%24_%25_%26_'_(_)_%3D_~_%7C_%5C_%5E_-_%3B_%3A_%5D_%2B_*_%7D_%5B_%7B_%2C_._%2F_%60_%40 # 入力:ソフトウエア # 出力:%83%5C%83t%83g%83E%83G%83A

もちろん、データを取り出したあとは uri_unescape で復号化するのを忘れずに…

□ 特殊文字を削除してしまう方法
最も過激だが、最も信頼性が高い方法。特殊文字が必要ないログファイルの記録などに…

# バイナリや特殊記号を全て削除してしまう $strTmp =~ tr/(\x0-\x1f|\x21-\x27)//; $strTmp =~ s/(\x2c|\x5c|\x3b|<|>|\x3f|\x60)//g;

この特殊記号を削除してしまう方法では、ShiftJISの日本語で 0x5c を持つ文字が文字化けする。『 ¥ 』 (ルート記号)が問題ないならば、この文字(0x5c)は消去しないという選択肢も考えられる。


■ 参考資料
・@IT 今夜分かるSQLインジェクション対策
・SmartSmart Perl講座 第2章 DBI クイックリファレンス
DBI/DBDの使い方

ShiftJIS 文字コード表
ASCII文字コード表