プリペアドステートメントとエスケープの線引き おいちゃん変

微妙にあちこち賑わっている気がしたので、なんか必要になる前に、おいちゃんの見解を。

まず前提として「適切にもちいられていない」ケースは排除。
具体的には「＊＊をやる、というルールを作っても、守られない/中途半端なら意味がない」ってのはどこにいってもどこまで行っても当たり前のお話なので。
そーゆー基本は「出来ている」前提で。

おいちゃんのスタンスは以下の通り。
・基本はプリペアドステートメントを静的に
・SQLの識別子を動的にしたい場合は「ホワイトリスト」を用意して実装(プラスしてエスケープ、はアリだと思う)

ちなみに「通常の業務やゲーム範囲程度までで、phpMyAdmin的なものは作らない」前提。
一応念のため。

んで。
やはり基本は「静的プリペアドステートメント」のほうが、単純に安心。
エスケープ処理は、まぁ九分九厘ないとは思うのですがとはいえ「実装でミスったらアウト」なので、やはり静的プリペアドステートメントのほうが有難い。

性能について、複数の角度から言及を聞いた事があるんだけど…

まず「パケット数が増える」については、増えるんだろうにしても、それは「セキュリティとトレードオフに出来る関係なのだろうか？」っていう疑問が山盛り。
逆に言うと「真剣に考えた上で、トレードオフせざるを得ないほどのアクセス数(と収益のなさ)」があるんなら、考慮してもいいと思う。

「パケット数」の亜流で「プリペアドにすると速度が遅くなる」って言ってる人を聞いた事があるんだけど、それ、逆。
そもそもプリペアドステートメントは「速度を上げるために考案された」っていう基本背景をちゃんと踏まえてないと危ない。
…まぁ、その頃の基本背景、特にWeb系だと、恩恵にあずかりにくいのは事実なんだけどさ、それにしても。

次に「SQLの実行計画的に遅くなる」についても、概ね上述と一緒。
ちなみにこのケースは「可能性としてはあるんだろうなぁ」という予想の一方で「実際には見た事がない」ので、実際の状況を目にするまでは懐疑的。

そういや余談。
「プリペアドステートメントは軽量化のためであり、しかもPHPでは軽量化の意味もあまりなく、セキュリティ的には無関係です（｀・ω・´）キリッ」とかいう寝言をほざいていた、ガラケーなシステム会社擬さんがいらっしゃいましたねぇお元気してらっしゃいますですかしらん？
Blogネタ的には、 http://d.hatena.ne.jp/gallu/20130606/p1 この辺のエントリのネタとかやらかしてくれて、大変に有難い側面のある会社様でいらっしゃいましたが。

閑話休題

まぁ上述諸々から、静的プリペアドステートメントが「人為的ミスの介在の余地がなくなる」ので、割と心安らかだなぁ、とか思うわけです。
「性能とのトレードオフ」については、否定はしないけど「もうちょっと別の場所で、テーブル設計とかマシンパワーとかキャッシュとかロジックとか仕様とか」でカバー出来るんじゃねぇかなぁ？ って事象ばかり見ているので、可能性としては視野に入れつつも、懐疑的。

お次。
出てくるのが「動的な識別子」と、あと結構耳にするのが「IN」。

まず動的な識別子なのですが。「パラメタの値をエスケープする」よりは「初手からホワイトリスト用意しておく」ほうが、心安らかだと思うです。
なお「ホワイトリストを用意したうえでエスケープ通しておく」ってのは、おいちゃん的にはアリ。ホワイトリストに「ついうっかり」な何かが「ゼッタイに混入しない」とも限らないよねぇ、とは思うしねｗ

ん…この辺から、実装イメージを実際に。
一番ありがちなのは「どれか一つのカラムを対象に検索」的な何か。

<form action="./find.php">
<input type="radio" name="target_col" value="hoge">hoge
<input type="radio" name="target_col" value="foo">foo
<input type="radio" name="target_col" value="bar">bar <br>
<input name="value"><br>
<br>
<input type="submit">
</form>

すげぇ雑だけど。カラムhoge, foo, barがあって「どれか一つを選んで」検索する感じ。
多分、一部で言われているのは、こんなやり方。

  //
  $target_col = $_GET['target_col'];
  $val = $_GET['value'];
  //
  $e_target_col = DBが提供しているエスケープ関数($target_col);
  $e_val = DBが提供しているエスケープ関数($val);

  //
  $sql = "SELECT * FROM table WHERE `{$e_target_col}` = '{$e_val}';";

ん…valueはもしかするとプリペアドかもしれない。その辺は適宜脳内補完してくださいませ。

おいちゃんが推奨するのは、雑には、こんな感じ。

  // ホワイトリスト的な何か
  $target_col_list = array (
    'hoge' => 'hoge',
    'foo' => 'foo',
    'bar' => 'bar',
  );

  // XXX 微妙に雑なのは気にしない
  $target_col = @$target_col_list[$_GET['target_col']] . '';
  $val = $_GET['value'];

  //
  if ('' === $target_col) {
    // エラー処理的な何か
    return ;
  }

  // プリペアドステートメントの作成、だと思いねぇ
  $sql = "SELECT * FROM table WHERE `{$target_col}` = :val ;";

でまぁ、こんな風に丁寧なのは、アリだと思う。

  // ホワイトリスト的な何か
  $target_col_list = array (
    'hoge' => 'hoge',
    'foo' => 'foo',
    'bar' => 'bar',
  );

  // XXX 微妙に雑なのは気にしない
  $target_col = @$target_col_list[$_GET['target_col']] . '';
  $val = $_GET['value'];

  //
  if ('' === $target_col) {
    // エラー処理的な何か
    return ;
  }

  //
  $e_target_col = DBが提供しているエスケープ関数($target_col);

  // プリペアドステートメントの作成、だと思いねぇ
  $sql = "SELECT * FROM table WHERE `{$e_target_col}` = :val ;";

「ホワイトリストコードん中にべた書きすんな」とかについては「サンプルコードだからそうしたけど必要に応じて適宜外にはじき出してね」って感じ。
まぁ、こんな風に「ホワイトリスト」持っていたほうが、万が一の時に「動かねぇ」のほうで気づくので、テストで洗い出せるか、でなきゃお客様の怒声で気づけるので、便利。

ちなみにINの時は、単純にforなりforeachなりで採番した :val_番号 を埋め込んで、あとでどかどかっと値をbindすりゃいいと思う。
…いやもしかしてもっと妙手妙案があるかもしれないけど、あったら教えてくださいませ(笑

んで。
「数的に少ない、レア業務」まで想定するともうちょっと色々あるような気がするんだけど。
割とあちこちに転がってるくらいの業務ゲームだと、こんな指針で今のところ困らなかったので、こんなもんじゃないかなぁ、とか思うです。

…ふと最後まで読んで気づいた「エスケープについて書いてねぇじゃん」ｗ
ん…正直、あんまり使わないので。
おいちゃん的にはこんな感じ。
・識別子を動的にする時は、慎重の上にも慎重を重ねる意味合いで、ホワイトリストに「糅てて加えて」使うのは、あり
・「速度前提に」使うのは、よっぽどの考察によりけりだけど、基本、なし
・エスケープ処理が「どんな事をやっているか」を知るのは大切だけど、基本、自分での実装はせず、提供されている関数なりAPIなりクラスメソッドなりを使う

こんな所かなぁ…うんやっぱり、使わないですむんならあんまり積極的に使う事を考えていない。
いや単純に「プリペアド一本で片付く事」が多いので、あんまりお道具にバリエーションがあっても面倒なだけだなぁ、ってのも強くて(苦笑

以上、多分「いつかどこかで、授業で使うか引用するか」な時用の覚え書きでした(笑