Kazuho@Cybozu Labs

mysql> show create table numbers;
+---------+----------------------------------------------------------------------------------------+
| Table   | Create Table                                                                           |
+---------+----------------------------------------------------------------------------------------+
| numbers | CREATE TABLE `numbers` (
  `n` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+---------+----------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> select count(*) from numbers where n%'4'='0';
+----------+
| count(*) |
+----------+
|   400000 | 
+----------+
1 row in set (0.36 sec)

mysql> select count(*) from numbers where n%4=0;
+----------+
| count(*) |
+----------+
|   400000 | 
+----------+
1 row in set (0.24 sec)

　Perl の場合、DBI::execute を呼んでいると、プリペアードステートメントのパラメータは必ずクォートされてしまいます。集約処理等、重たいSQLを実行するにあたっては、DBI::bind_param で数値型であることを明示すべき、と言えるでしょう。

# 変更前 (DBI::execute を使用)
use DBI;
...
$sth->execute(@params)
    or die $dbh->errstr;

# 変更後 (DBI::bind_param を使用)
use DBI qw(:sql_types);
...
for (my $i = 0; $i < @params; $i++) {
    if ($params[$i] =~ /^-?[0-9]+$/) {
        $sth->bind_param($i + 1, $params[$i], SQL_INTEGER);
    } else {
        $sth->bind_param($i, $params[$i]);
    }
}

ところで、実数の場合はどうすればいいんでしょうね。

2007年21日追記: ２点追記します。

　まず、上の例についてですが、たとえばクエリのパラメータとして文字列 "0" を渡した場合にも、数値 0 への変換が行われます。ゼロは空文字列と等価ですので、文字列 "0" を検索したかった場合には不具合が発生することになります。言うまでもないことかもしれませんが、値をみてエスケープの有無を判断するのではなく、パラメータの文脈 (どのように使用したいのか、という点) からエスケープする／しないを判断するのが王道です。

　次に、実数のバインドについて。気になってコードを追っていたのですが、現時点で最新のドライバである DBD::mysql-4.005 には浮動小数点型として bind_param された変数の扱いにバグがあるようです。具体的には、指数表現が無視されます (parse_number 関数が e 以降を読み捨てているっぽい)。よって、現時点では、実数をバインドする際には、型を指定しないか (あるいは SQL_VARCHAR を指定する) すべきだと思います。以下が、確認用のコードです。

my $dbh = DBI->connect(...);

my $sth = $dbh->prepare('select ?') or die $dbh->errstr;
$sth->bind_param(1, 1e16, SQL_FLOAT) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;

print $sth->fetchrow_arrayref()->[0], "\n";  # should print 1e16, but shows "1"

暗黙の型変換をしているか否かの差ですね。