php+postgresqlでクエリ生成時の過ち
phpでユーザーからの入力をレコードを取得する際の条件に代入しようとした時に遭遇したミス。
当時のシチュエーション ・クエリを生成する際の条件文にユーザーの入力を代入しようとした為、記号が入力される可能性がある時 ・今回は いくつかの記号で発生した。発生した記号は以下の通り %(パーセント), _(アンダースコア), '(シングルクォート)
調査してみる
実際に生成されたクエリをコマンドラインから実行してみたらエラーを確認。
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE '%'%'; [/sql]
これを実行すると
見たいな状態に... この状態の時は入力を受け付けるのだけどいっこうに抜け出す事が出来ない
[sql] TEST'#nukedasenai!; TEST'#nannde?; TEST'#'''; ERROR: operator is not unique: unknown % unknown LINE 1: .. FROM test WHERE test.text LIKE '%'%'; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. [/sql]
どうやら ''';のようにシングルクォートを続けて3個連続で入力した状態でセミコロンを入れ実行するとようやくクエリとして認識してくれる模様。 おそらく%に挟まれたシングルクォートの解釈がおかしいために発生している予感がするためsqlでのシングルクォートの扱いを調査してみる。
sqlでのシングルクォートについて 簡単に言うとクエリの中で文字列として扱う際に囲むモノで 文字列として扱いたい対象をシングルクォートで囲むと文字列として扱われる。 シングルクォート以外にもダブルクォート(")で囲っても同じである。 ただし、一部の記号は文字列としては扱われない。 % _ はその代表で LIKE等で使う部分一致させるために使用される為特殊な解釈をされる。 またその様な特殊な解釈をする文字を純粋な文字列として扱う場合は特殊な文字の直前にバックスラッシュ(\) か エンマーク(￥) を置く事で文字列として扱う事が出来る。 これをエスケープという。
エスケープだ！これをシングルクォートにすれば解決じゃないかな？と思ったが...
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE '%\'%'; TEST'# onazikekkani...; [/sql]
同じ結果に... シングルクォートのエスケープは特別なのかと思って今度はシングルクォートのエスケープについて調べるとビンゴ!
シングルクォートのエスケープについて シングルクォートをエスケープして文字列として扱うには二種類ある。 ・ダブルクォートで囲む この方法はまんまその通り。
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE "%'%"; [/sql]
ってするだけ。逆にダブルクォートをエスケープして文字列として扱いたい場合はシングルで囲むだけ。
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE '%"%'; [/sql]
・シングルクォートを2個連続で置く この方法もまんまその通り。
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE '%''%'; [/sql]
ってするだけ。こちらもダブルクォートバージョンがある。
[sql] TEST=# SELECT * FROM test WHERE test.text LIKE "%""%"; [/sql]
このことからクォート系はこうやって文字列として扱える模様。
まとめ ・クォート系をエスケープして文字列として扱う場合はバックスラッシュではなく 同じクォートを2個置く シングルクォートを文字列としたいならダブルクォートで囲む。(逆も可)
