前回の(1)はこちら から。
DBIの使い方
(2)では、Perlのデータベースプログラミングにおける基本的なモジュールであるDBIについて解説します。
なお、本稿のコードは、執筆時点の最新であるDBI
のバージョン1.632、DBD::mysql
のバージョン4.028を前提に記述しています。
DBIとは何か
DBI
は、データベースへアクセスするための抽象的なインタフェースを提供するモジュールです。DBIを利用することにより、Perlではデータベースの種類をほとんど気にせずにコードを記述できます[2] 。Perlにおいてデータベースを扱うほとんどのモジュールは、DBI
を利用することを前提にしています。
DBI
は各データベース用のドライバモジュールであるDBD
系モジュールと組み合わせて利用します。たとえば、DBI
でMySQLを扱う場合はDBD::mysql
と組み合わせて利用します。同様にPostgreSQLではDBD::Pg
、SQLiteではDBD::SQLite
を利用します。このようにDBI
では、データベースへの実際のアクセスをドライバモジュールに任せることによって、統一されたインタフェースであらゆるデータベースへアクセスすることを可能にしています。
この性質は、データベースを扱うモジュールの実装を容易にします。実際、CPANにはDBIx
というDBI
のAPIを利用したモジュールを登録するネームスペースが存在します。そして、DBIx
ネームスペースにはなんと、執筆時点(2014年11月2日)で920個ものモジュールが存在します。これらのモジュールのほとんどが、データベースの差異を意識せずに利用できるようになっています。
libmysqlclientのインストール
今回はMySQLを用いるため、DBD::mysql
を利用します。DBD::mysql
のインストールにはlibmysqlclient
が必要です。次のようにインストールします。
Homebrewを使う場合(OS X)
apt-getを使う場合(Ubuntu、Debian GNU/Linuxなど)
$ sudo -H apt-get install libmysqlclient-dev
※ OSによってはパッケージ名が異なる場合があります
DBI、DBD::mysqlのインストール
続いて、DBI
とDBD::mysql
をcpanm でインストールします。
モジュールがロードできればインストールに成功しています。
$ perl -MDBI -E 'say $DBI::VERSION'
$ perl -MDBD::mysql -E 'say $DBD::mysql::VERSION'
基本的な使い方
DBI
は、データベースへの接続(connect
メソッド) 、SQLの準備(prepare
メソッド) 、SQLの実行(execute
メソッド) 、結果の取得(fetch
メソッド)というフローで利用します。以降でこれらのメソッドの具体的な使い方を解説します。
データベースへの接続――connect
データベースへ接続するためにはconnect
メソッドを使います。connect
メソッドは「データベースハンドラ」と呼ばれるデータベースとの接続を示すオブジェクトを返します。次のようなコードでデータベースに接続できます。
my $dsn = "dbi:mysql:database=myappdb
;host=myapp-mysql.local;port=3306";
my $user = "myapp";
my $pass = q{fQ$aH'dKd#YxC};
my $dbh = DBI->connect($dsn, $user, $pass, {
AutoCommit => 1,
PrintError => 0,
RaiseError => 1,
ShowErrorStatement => 1,
AutoInactiveDestroy => 1
});
接続にはDSN(Data Source Name )というODBC(Open Database Connectivity )由来の記法を使います。DSNは次のようなフォーマットの文字列です。各項目の名前は定義されていないため、ここでは筆者が独自の名前を定義しています。
"dbi:ドライバモジュール名: 属性名1= 属性値1; 属性名2= 属性値2"
ドライバモジュール名には、ドライバモジュールのネームスペース(DBD
)を除いた名前を記述します。たとえばMySQLの場合はDBD::mysql
なので、ドライバモジュール名はmysql
になります。
属性には、サーバへの接続に必要な設定、細かい挙動を制御する設定などを記述します。ドライバモジュールごとにどのような属性を渡すかは異なります。たとえばDBD::mysql
の場合は、MySQLサーバのホスト名などを指定する必要があります。DBD::mysql
の代表的な属性は表1 のとおりです。つまり(1) のDSNを指定すると、DBD::mysql
を利用し、myapp-mysql.local:3306
のMySQLのmyappdb
データベースに接続します。
表1 DBD::mysqlで利用できる代表的な属性
属性名 属性値 属性値の例
database 利用するMySQLのデータベース myapp
host 接続先のMySQLサーバのホスト名 myapp-mysql.local
port 接続先のMySQLサーバのポート番号 3306
mysql_enable_utf8 MySQL上のデータがUTF-8で保存されることを前提に、utf8フラグの管理を自動で行わせる 1
※ ほかにもさまざまな属性を利用できる。詳しくはドキュメントを参照(表2も同様)
また、ドライバモジュールと同様に、DBI
にも(2) のように細かい挙動を変更できる属性を記述できます。代表的な属性は表2 のとおりです。特に重要な属性としてRaiseError
を覚えておいてください。DBI
の各メソッドの失敗時の挙動はRaiseError
によって異なります。詳しくは後述します。
表2 DBIで利用できる代表的な属性
属性名 属性値 属性値の例
AutoCommit ステートメント単位でコミットする。トランザクションはbegin_work、commit、rollbackにより明示する 1
PrintError DBIのエラーを標準エラー出力にprintする 1
RaiseError DBIのエラーが起きたときにdieする 1
ShowErrorStatement DBIのエラーに原因となったSQL文を含める 1
InactiveDestroy DBIにはデストラクタで接続を切断する機能があるが、それを無効にする 0
AutoInactiveDestroy fork時に自動的に子プロセスでInactiveDestroyを有効にする 1
なおDBI
の属性については、理解が浅いうちは上記サンプルコードと同様の設定にすることをお勧めします。いろいろな設定をオン/オフして挙動を観察するのもおもしろいので、ぜひ試してみてください。
SQLの準備と実行――prepare、execute
prepare
メソッドはSQLを準備し、ステートメントハンドラを生成します。そしてステートメントハンドラのexecuteメソッドを利用してSQLを実行します。execute
メソッドはSQLの実行に成功すれば真値を返します。失敗時の挙動はRaiseError
属性に準じます。
my $sth = $dbh->prepare(
'INSERT chat (room, user, msg) VALUES (?, ?, ?)'
);
$sth->execute("room1", "karupanerura", "hello");
$sth->finish;
ステートメントハンドラは再利用できます。再利用しない場合は明示的にfinish
メソッドを呼び出しましょう。
my $sth = $dbh->prepare(
'INSERT chat (room, user, msg) VALUES (?, ?, ?)'
);
$sth->execute("room$_", "bot", "hello") for 1..10;
$sth->finish;
上記のように、DBI
でSQLに値を埋め込むにはプレースホルダというsprintf
とよく似たしくみを利用します。値を埋め込みたい個所を?
としてSQLに記述すると、execute
メソッドの引数の値が対応する順序で埋め込まれます。
sprintf
と違うのは、自動的にサニタイズ処理が行われる点です。単純な文字列結合やsprintf
では、次のように攻撃者が任意のSQLを実行できてしまうことがあります[3] 。
# 悪意のある入力
my $room = "room1";
my $user = "evil";
my $msg = "');
UPDATE chat SET msg = 'What a weak system!'; --";
# プレースホルダを利用せずに文字列結合で値を埋め込む
my $sth = $dbh->prepare(
"INSERT chat (room, user, msg)
VALUES ('$room', '$user', '$msg')"
);
$sth->execute();
このケースでは次のようなSQLが実行されます。
INSERT chat (room, user, msg)
VALUES ('room1', 'evil', '');
UPDATE chat SET msg = 'What a weak system!'; --')
意図していない悪意のあるUPDATE
が出現しています。もとのSQLの一部はコメントアウトされ、構文エラーにはなりません。サニタイズ処理が適切に行われておらず、複数のSQLが同時に実行可能である場合、このような攻撃も可能になってしまいます。
プレースホルダを利用して値を埋め込めばサニタイズが行われるため、もとのSQLが破壊されることはありません。もちろん、DBI
やドライバモジュールにバグがないことが前提になりますが、DBI
もDBD::mysql
も枯れているモジュールなので安全性は高いです。少なくとも、自前でサニタイズ処理を実装したり、安全な入力かどうかをチェックする処理を実装するよりははるかに安全でしょう。よって、SQLに値を埋め込む際は必ずプレースホルダを使いましょう。
結果の取得――fetch系
ステートメントハンドラを利用してSQLの結果を得るには、fetch
系メソッドを利用します。ここではfetchall_arrayref
メソッドを例に紹介します。fetchall_arrayref
は結果のすべての値を配列リファレンスとして返します。
my $sth = $dbh->prepare(
'SELECT user, msg FROM chat WHERE room = ?'
);
$sth->execute('room1');
my $rows = $sth->fetchall_arrayref();
# $rows => [
# ["karupanerura", "hello"]
# ["kfly8", "hello"]
# ]
第1引数にリファレンスを指定することにより、単一行をデータとしてどのように表現するかを変えることができます。たとえば単一行をハッシュリファレンスとして扱いたい場合は、次のようにします。
my $rows = $sth->fetchall_arrayref(+{});
# $rows => [
# { user => "karupanerura", msg => "hello" },
# { user => "kfly8", msg => "hello" },
# ]
ほかにも、TMTOWTDI(There's More Then One WayTo Do It )( 注4 )をスローガンに掲げるPerlならではか、値を得るだけでも多くのメソッドが提供されています。詳しくはDBI
のドキュメントを参照してください。
任意のSQLの実行――do
単純にSQLを実行するだけであればdo
メソッドが便利です。これはprepare、execute
のショートカットです。SQLの実行に成功すれば真値を返します。失敗時の挙動はRaiseError
属性に準じます。第3引数以降にプレースホルダに埋め込む値を渡すことができます。
my $room = "room1";
my $user = "karupanerura";
my $msg = "hello";
$dbh->do(
'INSERT chat (room, user, msg) VALUES (?, ?, ?)', {},
$room, $user, $msg
);
トランザクション処理── begin_work、commit、rollback
DBI
でトランザクションを扱うにはbegin_work、commit、rollback
メソッドを利用します。基本的にSQLを書く代わりに同様の名前のメソッドを呼ぶイメージですが、AutoCommit
属性の状態によって正しい使い方が異なります。
$dbh->begin_work;
$dbh->do(...);
$dbh->do(...);
$dbh->commit;
AutoCommit
属性が偽値である場合は、常にbegin_work
されているような状態になります。そのため、begin_work
を明示的に呼び出すとAlready in atransaction
エラーが発生します[5] 。また、暗黙的にトランザクション内にいるため、commit
かrollback
を明示的に呼び出す必要があります。
AutoCommit
属性が真値である場合は、明示的にbegin_work
を呼び出さない限りトランザクションとして処理されません。こちらのほうがコンソールから直接MySQLを操作する感覚に近いでしょう。
Webアプリケーションの場合など、大きなアプリケーションではトランザクションの状態管理が難しくなってしまうため、基本的にはAutoCommit
属性は真値に設定しておくほうが無難です。
エラーハンドリング
エラーハンドリングの方法はRaiseError
の値によって異なります。
RaiseError
が偽値である場合は、失敗時にundef
などの偽値を返します。エラーは$DBI::errstr
に格納されます。
my $ret = $dbh->do(...);
unless ($ret) {
# エラー処理
my $err = $DBI::errstr;
...
}
RaiseError
が真値である場合は、失敗時に$DBI::errstr
でdie
します。失敗時にリカバリ処理を行いたい場合、eval
で例外を拾うとよいでしょう。
eval {
$dbh->do(...);
};
if (my $err = $@) {
# エラー処理
...
}
ちょっとしたスクリプトなどで失敗しても処理を継続したいことが多い場合は、RaiseError
に偽値を設定したほうが見通しが良くなることがあるかもしれません。しかし、エラーのハンドリング漏れが起きても気づきにくいうえ、エラーが起きても続きの処理が実行できてしまうため、バグを作ってしまいがちです。基本的にはRaiseError
は真値に設定しましょう。
<続きの(3)はこちら 。>