発端としては。
Slimを色々いじってるなかで「まぁちょっとしたツールくらい欲しいよねぇ」になり、その過程の一つとして「ほんのりしたModelクラス欲しいなぁ」がありまして。
でまぁ
$r = モデルクラス::insert(データのハッシュ配列);
とかって書式でいけると楽だよねぇ、が、発端。
ほんのりと処理を書いてすぐに気づいたのですが「やだぁSQL-Injectionやりやすい！！」。
いやまぁ値はプリペアドすればよいのですが、テーブル名とカラム名が、ねぇ。
テーブル名はともかく、カラム名は、書き方によっては「hashのkeyにちょいと大嘘つっこんだら、気を付けないとあっという間にクラック可能」でございます。
まぁPDO使ってるから何かエスケープ的メソッドあったよねぇ……ほいほいあったquote( http://php.net/manual/ja/pdo.quote.php )。
ってなわけでサクっと実装してUnitTest……こける。なんで？
確認してみたら、こいつ「シングルクォートを前後につける」だけでやんの orz*1
標準SQL(含むPostgreSQL)では、SQL識別子の前後はダブルクォーテーション。MySQLは基本的にはバックスラッシュ……なんだけど「SET sql_mode='ANSI_QUOTES';」ってのでモードが違うと、標準SQLよろしく「ダブルクォーテーション」になる。………めんどい orz
どっちにしても「quote」が使えないぽいので、escapeメソッドを探す………ない orz
このあたりから、茨の道がstartでございます。
まずネットをググってみますが………エスケープの文脈は大概「値の入れ方」のところで、つまりそれは「プリペアドステートメント使えば足りるぢゃん」って箇所ばかりなので、あんまり参考になりません orz
それでもいくつかヒットはしたのですが………
https://d.nekoruri.jp/entry/20131211/no_escape
3. RDBMSの管理ツールなどの開発をしている場合は頑張ってエスケープする
RDBMSの管理ツールなどをあなたが実装しているのであれば、ユーザがうっかり作ってしまった記号まみれのテーブル名に対してもアクセスできないといけないかもしれません。
おめでとうございます！
ここまできて初めてエスケープが必要となります。
おめでたくないです orz
んで
個人的には、RDBMSのエスケープAPIや既存のライブラリを用いて「プレースホルダに値をエスケープして埋め込む」という関数をきっちり作り込み、そこに集約させるのが良いのでは無いかな、と思います。
決して、エスケープ処理自身を一から自分で書こうだなんて恐ろしいことを考えないでください。
その必要があるのは、あなたがRDBMS自身やそのライブラリの開発者の場合だけです。
その「提供された関数(メソッド)」が、現状、ないのでございます orz orz orz
https://blog.tokumaru.org/2013/12/sql_27.html
SQL識別子に関しては「もっと良い方法」がある
そもそも、SQL識別子をエスケープ処理しなければならない局面は以下であると考えられます。
・データベース管理ツールを作成していて､識別子はユーザ入力である（局面１）
・アプリケーション内でテーブル名や列名をジェネレートしており、これらを構成する文字として引用符が使われる可能性がある（局面2）
局面1の典型例は、phpMyAdminやMySQL Workbenchを作成する場合ですが、これは識別子のエスケープ処理は必須ですね。しかし、この種のツールを作る人であれば、当然識別子のエスケープ処理くらいは知っているだろう…と思っていただけに、MySQL Workbenchの識別子のエスケープもれがあったことは驚きでした。しかし、前述のようのように、幸い重大な脆弱性とまでは言えません。データベース管理ツールを作る開発者はまれだと思われるので、この件は本稿ではこれ以上触れません。
「データベース管理ツールを作る開発者はまれだと思われるので、この件は本稿ではこれ以上触れません」触れてもらえなかった orz orz orz
いや厳密には「データベース管理ツール」ではないんだけど………方向性として「かなり似ている」ので orz
大体にしてから。
「MySQL Workbenchの識別子のエスケープもれがあったことは驚きでした( https://blog.tokumaru.org/2013/12/sql_26.html )」を、ほんのりとはいえ、覚えているので。「しっかりした抜け漏れのないエスケープ処理」とか、さすがに全く自信がないのですよ orz
だから、他人様の知恵を、ググってお借りしたかった orz
……うん、一応おいちゃんもオリジナルのフレームワークは持っているのですが(Githubで公開もしてますし、そこそこ実務実績もあります)。
うちの、世間さまでModelとか呼ばれるものに相当するdata_clumpっていうクラスは「カラム名もテーブル名も(第一種)ホワイトリスト」なので、エスケープとか、あんまり深刻ではなかったのですだよ orz
まぁそこ嘆いても始まらんのですが。
さて……どうエスケープすっかなぁ？？？ なのですが。
PostgreSQLだと「pg_escape_identifier()」ってのがあるのですが……PDOで接続していると「それとは別にもう１connection」なので、ちょっと、なぁ。
MySQLだと「real_escape_string()」になりそう……なんだけど、そもそもこれ「値用」なので、識別子用じゃない orz
いやなんていうか本気で「公式関数での方向がふさがれている」のは、地味にツライのです orz
とりあえず「エスケープ自体の仕様」を、軽く確認。
https://blog.ohgaki.net/sql-identifier-escape
SQLリテラルのシングルクォートをシングルクォートでエスケープするように、SQL識別子ではダブルクォートをダブルクォートでエスケープします。
https://blog.ohgaki.net/mysql-postgresql-sqlite-identifier-escape#MySQL
通常モードの場合、識別子は｀（バッククオート）2で囲みます。また、識別子に利用できる文字は次の通りです。
(中略)
ANSI QUOTESモードの場合はPostgreSQLと同様に”（ダブルクオート）で囲み”（ダブルクオート）でエスケープします。文字リテラルもPostgreSQLと同様に’（シングルクオート）で囲み’（シングルクオート）でエスケープになります。
あとは識別子の長さもあるっぽいのですが(63バイトとか)、一旦ドン無視。
識別子自体の仕様は
https://hidekatsu-izuno.hatenablog.com/entry/2015/12/07/233618
MySQL
英数で始まる 英数（A-Z0-9）、アンダースコア（_）、ドル（$）からなる文字列
64バイト以内
大文字/小文字の区別なし（ただし、一部の環境では混在不可）
バッククォート（`識別子`）にてエスケープ可能、後者は ANSI_QUOTES 有効時はダブルクォート（"識別子"） も可
PostgreSQL
英字あるいはアンダースコアで始まる英数（A-Z0-9）、アンダースコア（_）、ドル（$）からなる文字列
64バイト以内（設定で変更可）
大文字/小文字の区別なし（小文字に正規化）
ダブルクォート（"識別子"）、ユニコードクォート（U&"識\5225+005B50"）にてエスケープ可能
結構色々あるんだよなぁ………。
……あれ？ MySQL、日本語も確かいけたはずだぞ？？
調査し直し。
https://dev.mysql.com/doc/refman/5.6/ja/identifiers.html
識別子は内部で Unicode に変換されます。以下の文字を含めることができます。
引用符で囲まれていない識別子で許可される文字。
ASCII: [0-9,a-z,A-Z$_] (基本的なラテン文字、0-9 の数字、ドル、下線)
拡張: U+0080 ..U+FFFF
引用符で囲まれている識別子で許可される文字には、U+0000 を除き、完全な Unicode Basic Multilingual Plane (BMP) が含まれます。
ASCII: U+0001 ..U+007F
拡張: U+0080 ..U+FFFF
ASCII NUL (U+0000) と補助文字 (U+10000 以上) は、引用符で囲まれた識別子または引用符で囲まれていない識別子では許可されません。
識別子は数字で始めることができますが、引用符で囲まれていないかぎり、数字のみで構成することはできません。
データベース名、テーブル名、およびカラム名は、空白文字で終えることはできません。
うわぁ思ったより面倒 orz
まぁ、やっぱり原典を当たるべきだね、うん。改めてしみじみ。
ついで、PostgreSQL。
https://www.postgresql.jp/document/9.6/html/sql-syntax-lexical.html#sql-syntax-identifiers
「デフォルトではNAMEDATALENは64なので、識別子は最長で63バイトです。 この制限が問題になる場合は、src/include/pg_config_manual.h内のNAMEDATALEN定数の値を変更して増やすことができます。 」すげぇなおい。
おいといて。
SQL識別子とキーワードは、文字（a?zおよび発音区別符号付き文字と非Latin文字)、アンダースコア（_）で始まらなければいけません。 識別子またはキーワードの中で続く文字は、文字、アンダースコア、数字（0?9）あるいはドル記号($)を使用することができます。 標準SQLの記述に従うと、ドル記号は識別子内では使用できないことに注意してください。 ですから、これを使用するとアプリケーションの移植性は低くなる可能性があります。 標準SQLでは、数字を含む、あるいはアンダースコアで始まったり終わったりするキーワードは定義されていません。 したがって、この形式の識別子は標準の今後の拡張と競合する可能性がないという意味で安全と言えます。
ふむり。
引用符付き識別子は、コード0の文字以外であればどのような文字でも使えます （二重引用符を含めたい場合は、二重引用符を2つ入力します）。 これにより、空白やアンパサンド（&）を含むテーブル名や列名など、この方法がなければ作れないような名前のものを作ることが可能になります。 この場合においても長さの制限は適用されます。
こっちがおっかない。
……これ、DB毎にチェックする元気と気力と工数と体力、ないぞ？？
ただ、幾分興味深いのが。
・だいたい、英数とアンダースコア
・ハイフンはNGっぽい(なんとなく、いけそうにおもってた)
・アンダースコア以外の記号はまちまち(なんだけど、厳しいところだと割とダメぽ)
あたりが多いんだよね。「PostgreSQLは小文字のみ」とかあるんだけど、その辺は一旦踏みつぶし倒し。
短絡的に「ダブルクォートとかバックスラッシュとかが文字列にあったら二重にした上で全体をダブルクォートとかバックスラッシュとかで囲む」って考えてもよいのですが、その程度で片付くんなら誰も困りゃしません、ってお話でございます。
で……そこから「抜け漏れ」とか考え出すと、なんていうか、割と、沼。
で……現実的に(とりあえず直近で使いたい)プロジェクトも併せて考えると。あんまり時間も大量にはないので。一端ですが
・テーブル名は「クラスによる選択」で、外部から一切入ってこないので、あんまり気にしない
を前提にして(まぁなんかエスケープはしたいが)。
カラム名を、割とがっつり「縛る」と、よさげかしらん？ 的な。
ちと「想定している実装」を考えながら考察を重ねてみませう。
insert(とかupdateとか)の値って、ある程度まとめて「formからごそっと取ってきたい」んだよね。
サンプルコード的には
$data = $request->getSpecifiedParams(['col_1', 'col_2', 'col_3', ..... ]); $r = モデルクラス::insert($data);
こんな感じ。
………うんまぁ多分なんとなく、都度、列挙書くのが面倒だったりするから
$data = $request->getSpecifiedParams(モデルクラス::getParamNames()); $r = モデルクラス::insertFromRequest($request);
とか
$r = モデルクラス::insertFromRequest($request);
とかってなりそうな気もするんだけど(とはいえ結局、列挙、書くの面倒なんだよなぁ……またバッチ作るかなぁ。MySQLのはあるから、PostgreSQLの、簡易バッチ、くらい)。
その辺考えると「事実上ホワイトリストっぽくなる」ような気もする……んだけど、Githubに持ち上げて公開もするつもりだから、一応「もうちょっとModel基底クラス単体でもガード」しておきたい、んだよねぇ。
雑に考えると。
テーブル名はまぁ「よしなに」していただくとして(そこに外部起因が入るとはあんまり思ってない)。
・カラム名は「半角英数とアンダースコア」のみ、に絞る(それ以外があったらエラー)：文字長はどうするかなぁ？
→「ほかの記号」とか「unicode」とか、一旦、踏みつぶしますｗ
→テーブル名も「同じチェックを一端する」でよいかしらん？
・PostgreSQLならダブルクォーテーション、MySQLならバックスラッシュで囲う
→ 「囲う」文字列が元文字列にあったら、重ねる(「二重引用符を含めたい場合は、二重引用符を2つ入力します」的な処理)：まぁ上述のvalidateやってたら入り込まないが
ってやると、とりあえず「自由度は下がるけど、ある程度の硬さは保持できる」んじゃないかなぁ？ と思っていた。
後は「プリペアドステートメント」で使ってる「名前付きプレースホルダ」の名前、か。
……一旦は「半角英数とアンダースコアのみ」なら、そのままでよいかなぁ？
後で改修したほうがいいんだろうけど……この手のライブラリでやるんなら、名前のつかない、？のプレースホルダのほうが楽なのかしらん？？
ちと思案してみませう。
とりあえず思考整理を兼ねて、一旦、記述。
後で読み直してみますが、なんか突っ込みどころなどあったら、突っ込みをいただけるとありがたいです。
*1:いやまぁ「値にシングルクォートとかあったらよしなに対応してくれるんだろう」とは思うんですが、その辺面倒なんで未実験。どのみち「シングルクォート」だと駄目だし
