DBD::mysql for perl の mysql_server_prepare による 動的/静的プレースホルダ設定

sqlのプレースホルダに 動的or静的があることを初めて知りましたし、プレースホルダを利用したsqlでも脆弱性があったことには更に驚きました。
http://jvn.jp/jp/JVN59748723/
http://gihyo.jp/admin/serial/01/charcode/0008
( JVN59748723は Shift_JIS+0x5C にも関連するので「この脆弱性を発見するなんてスゴイ」という感想もありますが )

で、今回は、DBD::mysql for perl の mysql_server_prepare=0 or 1 を試してみます

SQLプレースホルダの動的/静的とは?

my $sql = "select * from test_tbl where id=?";
my $sth = $DBH->prepare($sql);
my $arg_val = 'A';
unless( $sth->execute($arg_val) ){
   print STDERR $sth->errstr,"\n";
}

のようにプレースホルダ付きSQLを実行する場合、SQL文と値のbindが、動的=アプリ側(perl) , 静的=サーバ側(mysql)のように異なるらしく、DBD::mysql ver.4.032 のドキュメントには次のように記載されています。

Prepared statement support (server side prepare)
As of 3.0002_1, server side prepare statements were on by default (if your server was >= 4.1.3). As of 3.0009, they were off by default again due to issues with the prepared statement API (all other mysql connectors are set this way until C API issues are resolved). The requirement to use prepared statements still remains that you have a server >= 4.1.3

To use server side prepared statements, all you need to do is set the variable mysql_server_prepare in the connect:

$dbh = DBI->connect( "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1", "", "", { RaiseError => 1, AutoCommit => 1 } );

* Note: delimiter for this param is ';'

There are many benefits to using server side prepare statements, mostly if you are performing many inserts because of that fact that a single statement is prepared to accept multiple insert values.

To make sure that the 'make test' step tests whether server prepare works, you just need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_SERVER_PREPARE=1

http://search.cpan.org/perldoc?DBD%3A%3Amysql

先程の JVN59748723 を考えると、「mysql_server_prepare=1」の方が良いように思えますが、defaultがonになったり、offになったり...、DBD::mysql のドキュメントだけでは server side prepare の有効性を理解できませんが、とりあえず図示してみましょう。

動的プレースホルダ

      <<<<perlアプリ>>>>                      <<<<DBサーバ>>>>
┌───────────────┐
│select * from tbl where id=?  │
└┬──────────────┘
  ↓(PREPARE?)
┌(BIND)────────────┐    ┌───────────────┐
│select * from tbl where id='A'├─→│SQL文 COMPILE                 │
└───────────────┘    └┬──────────────┘
                                        ↓
┌───────────────┐    ┌───────────────┐
│実行結果                      │←─┤SQL実行                       │
└───────────────┘    └───────────────┘

静的プレースホルダ

      <<<<perlアプリ>>>>                      <<<<DBサーバ>>>>
┌───────────────┐    ┌(SQL文 COMPILE) ───────┐
│select * from tbl where id=?  ├─→│select * from tbl where id=?  │
└───────────────┘    └┬──────────────┘
  					↓
┌───────────────┐    ┌(BIND)────────────┐
│$arg_val='A'                  ├─→│select * from tbl where id='A'│
└───────────────┘    └┬──────────────┘
                                        ↓
┌───────────────┐    ┌───────────────┐
│実行結果                      │←─┤SQL実行                       │
└───────────────┘    └───────────────┘

テストスクリプト - mysql_server_prepare= 1 or 0 の結論出せず

mysql_server_prepare= 1 or 0 の実行速度を比較する為、次のscriptを書いてみました。

結果は mysql_server_prepare=1(静的プレースホルダ)の方が遅い印象を受けましたし、
mysql_server_prepare=0がデフォルトのようですので、動的プレースホルダのままにするかと思います。

#!/usr/local/bin/perl
use strict;
use warnings;
use utf8;
use DBI;
use Encode;
use Time::HiRes qw/gettimeofday tv_interval/;
use Data::Dumper;

my $DB_CONF =
    {host =>'localhost',
     port => '3306',
     db_name=>'xing',
     db_user=>'root',
     db_pass=>'',
     db_opt=>{
#              mysql_server_prepare=>1, ## SERVER SIDE PREPARE
              AutoCommit=>0,
              PrintError=>0,
              RaiseError=>1,
              ShowErrorStatement=>1,
              AutoInactiveDestroy=>1,
              mysql_enable_utf8=>1,
              mysql_auto_reconnect=>0} };
main();

sub main {

    my $start_time = [ Time::HiRes::gettimeofday ];

    my $dbh = connect_db();
    print "MYSQL_SERVER_PREPARE = $dbh->{mysql_server_prepare}\n";

    my $sth = $dbh->prepare("select * from test_tbl where name=?");
    my $i = 0;

    while( $i++ < 10000 ){
        unless( $sth->execute("テストの名前") ){
            print STDERR $sth->errstr, "\n";
        }

        my $result_row = $sth->fetchrow_hashref();
        my $result_str = "$i\t$result_row->{id} $result_row->{name}";
#        print Encode::encode('utf8',$result_str),"\n";
    }
    $sth->finish();
    $dbh->disconnect();


    print "EXEC TIME= ", Time::HiRes::tv_interval($start_time), " sec\n";
}

sub connect_db {
    my ($class,$db_conf) = @_;

    my $db = join(';',
                  "DBI:mysql:database=$DB_CONF->{db_name}",
                  "host=$DB_CONF->{host}",
                  "portt=$DB_CONF->{port}");
    my $dbh = DBI->connect($db,
                           $DB_CONF->{db_user},
                           $DB_CONF->{db_pass},
                           $DB_CONF->{db_opt});
    return $dbh;
}