misc.log

日常茶飯事とお仕事と

SQL ServerでのLike検索でエスケープすべき文字

ちょっと理由をうまく説明できなかったのでメモ。

SQLでデータベースから情報を取得する際に「Like」で指定する場合、ワイルドカードとして使える文字があります。そういう「ワイルドカード文字」のような特殊用途文字自体を検索する場合、「これは特殊文字だけど、今回は普通の文字とみなしてね」という指示をするのですが、これを「エスケープ」と呼びます。

で、MicrosoftSQL Serverの場合、エスケープしなければならない文字は下記の3つ。

  • %(パーセント文字) …… Like文で主に用いるワイルドカード文字。たとえば「Like '%文字'」と指定すると、何か0文字以上の適当な文字のあと、最後に「文字」で終わるデータが該当します。
  • _(アンダースコア、下線)…… Like文では「何か1文字」を表すワイルドカード文字。たとえば「Like '%です_'」と指定すると、何か適当な文字のあと、「です」ともう1文字がつながるデータが該当します。
  • [(左大括弧、Opening Bracket) …… Like文では複数の文字を列挙したり範囲指定して該当する文字を検索条件にする場合、その範囲を大括弧で囲んで示します。その左側のカッコです。たとえば「Like ’渡[辺部邊邉]'」と指定すると、「渡辺」「渡部」「渡邉」「渡邊」のいずれかが該当します。ほかにも「[0-9]」とすれば0~9の数字文字どれか、というような範囲指定も可能です。

これらは通常、範囲指定や複数に該当する条件を表すための特殊文字ですが、中には「データに 50% という文字が入っているものを探したい」といった要望もあるかとおもいます。そうするとパーセントの記号を検索する必要がありますね。このような場合、対象文字を大括弧で囲めばOKです。たとえば、「where data like '%50[%]%'」とすれば、「文中に50%が入っている文字列」をdata列から探せます。このような特殊な文字の扱いをC#で行う場合、下記ブログで紹介されているようなコードでリプレースを掛ければOKです。

LIKE で検索するときの文字のエスケープ (SQL Server) / Do Design Space
https://sakapon.wordpress.com/2010/06/21/sqlserverlike/

たとえばこんな感じです。

string sql = Regex.Replace(originalSql, @"[%_\[]", "[$0]");

で、疑問が1つ……。

「なんで右大括弧はエスケープしなくてもいいのか?」

おそらく、SQL ServerSQL構文解析して処理する際に、左側のカッコが「範囲指定」の引き金になり、右側についてはケアしないことからエスケープしなくてもいいのではないかと推測したのですが、これについて理由が明記されているサイトなどは見つけられませんでした。Microsoftのリファレンス、Like句の説明にも下記のような記述があるのみです。

LIKE (Transact-SQL) / T-SQL Language Reference
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.

とりあえず、右大括弧(right bracket / closing bracket)、キャレット(^)、ハイフンはLike文の範囲指定で利用されますが、範囲指定の文脈以外で登場した場合はエスケープ不要、ということのようです。とりあえずここまで。

セキュアプログラミング―失敗から学ぶ設計・実装・運用・管理

セキュアプログラミング―失敗から学ぶ設計・実装・運用・管理

WRITING SECURE CODE 第2版 上 (マイクロソフト公式解説書)

WRITING SECURE CODE 第2版 上 (マイクロソフト公式解説書)