Adobe AIR2.0でのSQLite使用について質問です。


DBのテーブル上を検索する際にLIKEを使ったSELECT文を作ろうと思いましたが、parametersプロパティを使用した場合の「あいまいな検索」のコーディングの仕方が判りません。

例えば、任意のカラムを対象に、「Google」と「Yahoo」というレコードがあったとします。普通検索エンジンで「oo」と打つと、両方ヒットすると思います。

これを、決め打ちでやるのであれば、

SELECT * FROM tableName WHERE columnName LIKE “%oo%”

となりますが、

SELECT * FROM tableName WHERE columnName LIKE “%?%”

として、パラメータを

SQLStatement.parameters[0] = "oo";

のようにparametersを使用すると、インデックスだったら「?の近くが違う」とか、名前での引数だったら「:paramの近くが違う」とか、エラーが出てしまいます。

SELECT * FROM tableName WHERE columnName LIKE “%” + searchWord.text + “%”

という風にステートメント文を動的に構築してやれば大丈夫なのですが、これはセキュリティ的にいかがなものかと・・・。

もしご存知の方がいらっしゃればご教授いただきたいと思います。よろしくお願いします。

回答の条件
  • 1人3回まで
  • 13歳以上
  • 登録:2010/08/07 11:49:32
  • 終了:2010/08/14 11:50:02

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492010/08/07 16:48:07

ポイント35pt

%も一緒に送るようにしてやると良いでしょう

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE ?";
sqlStatement.parameters[0] = "%oo%";

いくつものパラメータが存在する場合や将来のメンテナンスや再利用を考慮して、何がしか命名しておくほうが良いかもしれません

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE :searchStr";
sqlStatement.parameters[":searchStr"] = "%oo%";
id:Hiiragi3

ふむふむ、ステートメント上で必ずしも%を付ける必要はなく、むしろparametersの方でつければ大丈夫ということですね。なるほど。

なんとなく見えてきました。今までステートメントに何が何でも%を入れて、parametersの中には必ず「入力された文字」のみを入れなければならないと思い込んでいましたが、そういうことはないのですね。言われてみれば確かにその通りかー・・・。

となると、この方法なら、自前エスケープ関数は必要ないんですかね。そのためのparametersプロパティですし。

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE ?";

sqlStatement.parameters[0] = "%" + searchWord + "%";

っていう感じで。

2010/08/07 17:07:28

その他の回答(1件)

id:yamaneroom No.1

yamaneroom回答回数1040ベストアンサー獲得回数612010/08/07 12:11:53

ポイント35pt

>これはセキュリティ的にいかがなものかと・・・

入力キーワードをきちんとエスケープしてやれば問題ない。

http://rubyist.g.hatena.ne.jp/edvakf/20090504/1241385507

id:Hiiragi3

なるほど・・・。

SELECT * FROM tableName WHERE columnName LIKE ? ESCAPE '$'

SQLStatement.parameters[0] = "%" + escapeFunc(searchWord.text) + "%";

っていう感じにして、escapeFuncの中で「%」と「_」を指定したエスケープ文字でエスケープさせて、ステートメントに合体させるという感じでしょうか。

function escapeFunc(originalString: String): String

{

return originalString.replace(/([%|_])/g, "$$$1");

}

これで無事できました。ありがとうございます。他にも案がありましたら、是非教えていただきたいと思いますのでよろしくお願いします。

2010/08/07 14:14:21
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492010/08/07 16:48:07ここでベストアンサー

ポイント35pt

%も一緒に送るようにしてやると良いでしょう

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE ?";
sqlStatement.parameters[0] = "%oo%";

いくつものパラメータが存在する場合や将来のメンテナンスや再利用を考慮して、何がしか命名しておくほうが良いかもしれません

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE :searchStr";
sqlStatement.parameters[":searchStr"] = "%oo%";
id:Hiiragi3

ふむふむ、ステートメント上で必ずしも%を付ける必要はなく、むしろparametersの方でつければ大丈夫ということですね。なるほど。

なんとなく見えてきました。今までステートメントに何が何でも%を入れて、parametersの中には必ず「入力された文字」のみを入れなければならないと思い込んでいましたが、そういうことはないのですね。言われてみれば確かにその通りかー・・・。

となると、この方法なら、自前エスケープ関数は必要ないんですかね。そのためのparametersプロパティですし。

sqlStatement.text = "SELECT * FROM tableName WHERE columnName LIKE ?";

sqlStatement.parameters[0] = "%" + searchWord + "%";

っていう感じで。

2010/08/07 17:07:28
  • id:windofjuly
    うぃんど 2010/08/07 18:01:32
    >自前エスケープ関数は必要ないんですかね
    検索文字列の中に%を含みたい場合には必要な措置となりますが、そうでなければ不要です
     
    少し整理して原因を見直して見ましょう
    【1】処理の流れ
    データベースは sqlStatement.text をまずは受け取ってSQLの構文解析と実行プラン作成を行います
    実行プランが完成した後に、パラメータを受け取ってプランを実行し、結果を返します
    【2】エラーの原因
    構文解析の段階では?や:searchStrと書かれた部分はまだ受け取っていないため、
    ”%?%”は”% 後から受け取るパラメータ %”の3つに分かれていると解釈されてしまいエラーとなります
    (その部分だけは実行段階で結合するといった都合のいい解釈までは行ってくれません)
    【3】対策
    先に回答しましたようにパラメータに%なども含んでしまうということになります
    【4】セキュリティ
    データベースに書き込まれている内容に関しては話が長くなりますので割愛しますが、
    SQLインジェクションに関してはprepareを用いればおおよそOK(未知のバグなどもあるかもしれないので完全にOKとは言えない)です
    動的に生成するとなれば、それはまたいろいろと問題がでてきますのでやめておいたほうが無難ですね
     
    以下、質問とは外れますが、回答1は意味を理解して回答しているとは到底思えないものです
    SQLite側の問題なのでrubyからの利用でもそれは別にかまわないかもしれませんが、リンク先のページに載っているエスケープ処理は%という文字を検索したい場合の逃げ道としての処理なので、セキュリティの観点からのものではまったくもってありません
    セキュリティ、動作コストなど複数の利点があるためparametersのようなものが用意されているのであって、その意味でも回答1は質問を理解しているとは到底思えないということです
     
    ご対応は下記参照ください
    http://hatena.g.hatena.ne.jp/hatenaquestion/20100212/1265942197
  • id:Hiiragi3
    わざわざこんなに丁寧にありがとうございます!

    かゆい所に手が届いた感じです。なるほど、僕が作った文は、処理の順番に即してなかったわけですね。納得です。

    実はSQLiteのことはよくわからないので、prepareに関してもピンとこない感じですが、ちょっと余裕があるときに勉強してみようかと思います。このままの知識で使っていくのはちょっと危険ですね・・・。

    >回答1
    まぁ確かにAIRのお話ではないですし、windofjulyさんの話を聞くとちょっと違うなとは思いましたが・・・。不正にポイントを取得しようとしたものではないと思いますし、知識の幅も少し広がったので、結果論ですがそれはそれでよかったのかな、と。なので、この件に関しては対応は致しません。ただ、もしこの質問を他の方が参考にするときに混乱があってはいけないので、この質問が終わるときに、ちょこっとだけ返信部分にそこらへんを書き加えるだけにします。わざわざご考慮頂き、ありがとうございます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません