SQLのエスケープ方法についてまとめてます。
SQLのエスケープは、LIKEのESCAPE指定をおこなう
引用符内で特別な意味を持つ文字（ワイルドカードなど）を、通常文字として扱うにはエスケープをおこなう必要があります。例えば、likeで使用するワイルドカード「%」を通常の文字として使用するには、以下のようにします。
USE tempdb;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2;
GO
USE tempdb;
GO
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 30-50% off'), ('Discount is .30-.50 off');
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%30-50!% off%' ESCAPE '!';
GO
【関連記事】
▶SQLのescapeはエスケープ文字を指定 エスケープ指定は各DBで異なる
上記はMySQLの例です。
!をエスケープ文字として指定し、!に続く文字を通常文字として扱うんですね。「!%」の部分が、通常文字の「%」として置き換えられます。
ワイルドカード文字である%や_（アンダースコア）をデータ内に混入させると、SQLでの扱いが面倒になります。データ設計の際に注意すべきでしょう。
SQLインジェクション対策のエスケープ処理
SQLインジェクションを防ぐためのアプリ側での処理をおこなう場合にも「入力文字列をエスケープする」という言い方をする場合があります。正確には、サニタイジングという処理です。
ウェブの検索欄のように自由に入力可能なテキストにワイルドカード文字やシングルクォート文字が入力された場合、SQL実行時に誤動作を起こす可能性があるんですね。そのため、入力文字列をアプリ側で特殊文字をエスケープ処理して対策をおこないます。
単純な例では、検索窓に「’ OR 1=1」を指定するだけで、SQLのwhere条件を全て真とした全件データの取得ができてしまいます。
関連）SQLインジェクションでデータベースを攻撃(!)してみよう (解答編) | 丸ノ内テックブログ
Oracleのエスケープシーケンス
Oracleでは、テキスト演算子や予約記号を含む文字は、中括弧や、バックスラッシュでエスケープします。中括弧は囲んだ文字列全てがエスケープされます。バックスラッシュは、続く1文字のみがエスケープされます。
'high\-voltage'
'{high-voltage}'
'XY\&Z'
'{XY&Z}'
oracleでは、シングルクォーテーション中のハイフンなど、予約語・演算子全てにエスケープが必要になるんですね。一見、どの部分のために中括弧のエスケープシーケンスを使用しているのかわかりづらいですね。
Oracleでは、他のデータベースでは通常文字として扱える文字や文字列もエスケープする必要があるため、SQLのエスケープ部分を一見して「この部分は何をやっているんだろう？」と、可読性に問題を感じてしまうケースも出てきます。
ただし、エスケープ対象ワードを明確にするために’high-voltage’を’high{-}voltage’としてハイフンを中括弧で囲むと、ハイフンの左右に空白が挿入され、’high – voltage’という文字列として扱われてしまい、意図しない動作の原因となるので注意しましょう。
また、like中のescape指定をおこなうことも可能です。
SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\' ORDER BY last_name;
SQL Serverのエスケープシーケンス
SQL Serverでのescapeは中括弧で囲む必要があります。
SELECT Name FROM Customers WHERE Name LIKE '\%AAA%' {escape '\'}
参考）LIKE 述語エスケープ文字 – SQL Server | Microsoft Docs
中括弧で囲むのは、他のデータベースと比べて独特ですよね。
上記の例では、バックスラッシュをエスケープ文字に指定することで、バックスラッシュに続く%（ワイルドカード）を通常文字として扱い、バックスラッシュなしの%文字をワイルドカードとして扱います。
なお、シングルクォーテーション「’」に対しては、escapeを使ってもエスケープできません。
select productName from ForgeRock where productName like 'a!'b' {escape '!'};
上記を実行すると、シングルクォーテーションの終端が見つからないというエラーになります。
Invalid SQL statement or JDBC escape, terminating ''' not found.
この問題に対策するためには、シングルクォーテーションを２つ重ねる必要があります。
select productName from ForgeRock where productName like 'a''b';
PostgreSQLのエスケープシーケンス
PostgreSQLでは、likeのescapeにてエスケープ文字を指定可能です。
string LIKE pattern [ESCAPE escape-character]
なお、ESCAPEで指定する文字は、シングルクォーテーションで囲む必要があります。ESCAPE ”（シングルクォーテーションを２つ重ねて記述）と指定すると、エスケープ文字を無効にすることができ、パターン内のアンダースコアやパーセント文字などの特殊文字のエスケープができなくなります。
PostgreSQLでは、シングルクォーテーション（単一引用符）のエスケープは、2つ続けて単一引用符を記述することで可能です。 例えば、’Dianne”s horse’のような形です。 PostgreSQLでは、単一引用符をバックスラッシュでエスケープすること（\’）も可能。 しかし、今後のPostgreSQLのバージョンではできなくなる予定なのだそう。
エスケープ文字を指定する方式に変更する必要があります。
まとめ
- like内の特殊文字列をエスケープするにはバックスラッシュを使う
- シングルクォーテーションをエスケープするには、”のようにシングルクォーテーションを2つ続けて記述
- likeの後ろにescape句を記述することでエスケープ文字を指定可能。文法はデータベースにより異なる
