SQLのLIKE節では「%」と「_」の2文字をワイルドカードとして利用します。「%」「_ 」を通常の文字として使いたい場合、以下のようにエスケープを行います

% => like 'foobar\%%'
_ => like 'foobar\__'

ただし、デフォルトでbackslashにてエスケープが行われるのは、少し調べたところMySQLとPostgreSQLあたりで、SQLiteやOracleではエスケープされません。別途エスケープ文字を指定する必要があります。それがLIKE .. ESCAPEです

LIKE 'foobar\%%' ESCAPE '\'
LIKE 'foobar$%%' ESCAPE '$'

上のように、エスケープに利用する文字を指定できます。もし、エスケープをしないなら空文字にすれば良いそうです

LIKE '_foobar%' ESCAPE ''

あまりないとは思いますが、ユーザからの入力をLIKE節に使う際に、適切にエスケープしないと、前方一致だったのが、部分一致になってしまいインデックスが利用されずにDBに負荷がかかることが考えられます。その場合、

my $ESCAPE_CHAR = '\\';
my $param = $req->param('search');
$param =~ s![_%]!$ESCAPE_CHAR$&!g;
my $sth = $dbh->prepare("SELECT * FROM mytable WHERE subject like ? ESCAPE ?");
$sth->execute( $param . "%", $ESCAPE_CHAR );

のように書けば安全だと思われます。

kazuhoさんのDBIx::Printfだとこのエスケープ処理を自動でやってくれます。LIKE .. ESCAPEに対応してませんでしたが、patch書いて 0.08 で対応しました。

use DBIx::Printf;
my $sth = $dbh->prepare(
    $dbh->printf("SELECT * FROM mytable WHERE subject like %like(%s%%) ESCAPE '\$'", $req->param('search'))
);
$sth->execute();

%like(..) のところが適切にエスケープされた文字に変換されます。

さらに、named printfに対応したDBIx::Printf::Namedを今日リリースしました。DBIx::Printfのplaceholderを名前で指定できるようになります。

use DBIx::Printf;
my $sth = $dbh->prepare(
    $dbh->printf("SELECT * FROM mytable WHERE category = %(category)d AND subject like %like(%(search)s%%) ESCAPE '\$'",
        {
            category => $req->param('category'),
            search => $req->param('search')
        }
    )
);
$sth->execute();

%like(%(search)s%%) はキモイ。けど、便利になるかもしれないこともないかもしれない

このブログ記事について

このページは、Masahiro Naganoが2010年12月16日 00:00に書いたブログ記事です。

ひとつ前のブログ記事は「JavaScript::Value::Escapeの変更点について」です。

次のブログ記事は「memcachedにおけるキャッシュシステムの Thundering Herd 問題への対策案」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

ウェブページ

OpenID対応しています OpenIDについて
Powered by Movable Type 4.27-ja