以前のエントリでSET NAMES問題に対して「MYSQLをやめる」ことを結論として書いた。
しかし、識者の人たちが「SET NAMESは禁止」とか「PDOは禁止」のような表現をしている事が、
この問題の理解をややこしくしているのではないかと思い直し、より実践的なまとめを書く事にした。
この記事を書き終える寸前にいいサイトを見つけてしまった。
http://d.hatena.ne.jp/jrofbyr/20081228/p1
ここの内容がほぼ結論じゃないかと思う。
というわけで、シチュエーション別にベストと思われる解決策を考えたつもりだが、以下は蛇足。
「PDOは禁止」と言われて困っている人がPDOとmysqliを比較するためのネタとして使ってくれれば大変光栄です。
データベースエンジン選定前
mysqlを使うかどうかも確定していない時は、mysqlを導入すべきかを検討しなおすと良い。
WebサーバとDBサーバを分けたい、堅牢性を求めたい時は、postgresqlが良い選択肢になる。
レプリケーションをしたい時は、Tokyo|Kyoto Tyrantが選択肢に入るかも知れない。
単純な速度比較でもpostgresqlは劣っていないし、手動vacuumも過去のものであるから、
選択可能な状況なら、迷わずpostgresqlを選択したほうが良い。
データベースエンジン選定後導入前
mysqlを使う事に決定してしまった。
phpもmysqlも「最新の安定版」を入れる事に決まったが具体的なバージョンには言及されていない。
あなたはバージョン決定に意見する事が出来るか、具体的なインストール作業を任されている。
この時あなたがするべきことは、php 5.2の出来る限り最新版を入れることだ。5.3が許されるなら尚良い。
そしてmysqlは5.1系（現在の最新安定版）を選ぶべきだ。
そして、php.iniとmy.cnfを編集し、SET NAMESを使わなくても文字化けしないような状態に設定すれば良い。
データベースエンジン導入後
phpもmysqlもバージョンが既に決まっており、インストールが済んでいる。
たとえば既存サイト上に新しいアプリを追加する時がこれに当てはまる。
アップグレードや既存システムに影響しそうな設定の変更は認められない。
しかし、スクリプト自体はこれから書く。
まず、mysqld < 5.0.7の場合は、SET NAMES問題を解決する方法は今のところ無い。
SET NAMES対象の文字コード以外がSQL文字列に一切含まれないように対策を取る必要がある。
もしもmysqld < 4.1.3なら、サーバサイドプリペアードステートメントすら使えないので、状況は絶望的である。
mysqld >= 5.0.7かつphp >= 5.1なら、PDOとサーバサイドプリペアードステートメントを使う手がある。
ただしこの時にSET NAMESと非プリペアードなSQLを併用する場合は、上記と同様の対策が必要になる。
さもなくば、PDOを使ってmy.cnfを読み込ませる手があるらしいが、正しい対処法なのかはテストしていない。
$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password, array(
PDO::MYSQL_ATTR_READ_DEFAULT_FILE => '/var/www/my.cnf',
));
※MYSQL_ATTR_READ_DEFAULT_FILEは、Windows版だとうまく指定できないらしい？
[client]
default-character-set=sjis
mysqld >= 5.0.7かつphp >= 5.2.3なら、mysql拡張でmysql_set_charsetを使える。
この場合は逆にプリペアードステートメントが使えなくなる。
mysqld >= 5.0.7かつphp >= 5.0.5なら、mysqli拡張でmysqli_set_charsetを使える。
この場合はプリペアードステートメントも使えてSET NAMES問題も起こらない。
ただし最大の弱点として、利用者が少ないため、ノウハウの共有が困難かも知れない。
たとえば、レンタルサーバとかではmysqliを導入していない所が結構多いと思う。
開発自体は継続されているので、charset指定出来ないからPDO全否定、みたいな人はベターなのかも。
http://www.php.net/manual/ja/mysqli.overview.php
PEAR::DBを使うメリットは何も無い。
エミュレートプリペアードステートメントはSET NAMES問題の特効薬にはならないからだ。
もちろん、ある種のライブラリやラッパーがこの問題を解決する機能を提供してくれるかも知れない。
そういう観点では、PEAR::MDB2も同じだ。
しかし、MDB2には、mysqliが使えて、これを使いこなすための機能も整っているという利点がある。
「PDOをやめてmysqli(とMDB2)を使いましょう」と言われた場合に抵抗するための良い方法は思いつかない。
mysqliのラッパークラスをPDOに似せてしまうというのは如何か。
少なくとも、PDOベースのライブラリを捨ててまで、マイナーなMDB2を新規に導入する意義は無いと思う。
スクリプト実装後
既存システムのセキュリティ脆弱性を解決したい、という場合。
PDOを使っているなら、PDO::ATTR_EMULATE_PREPARES を false にする。
mysql < 5.1ではサーバサイドプリペアードステートメントを使うとクエリキャッシュが効かなくなるらしいので要注意。
PDOとSET NAMESを使っているなら、my.cnfを読み込ませる方法を確認するのが最善手かも知れない。
ORMなどを使っており、SQLを直書きで実行したい時に呼び出すメソッドが統一されているのであれば、
それが直接呼び出された時だけmysql関数を使う、という実装を考えたが、けっこう不毛な気もする。
コネクションを2本はる事になるのが良いことかどうかも悩ましい。まあアイデアの一つとして。
既存システムの改修をする際に、せめて改修対象の部分だけでもまともにしておこう、と思うならば、
とりあえずプリペアードステートメントを使うように心がけておくのが一般論としては最善手だと思う。
改善を待つべきか？
PDO_MYSQLがcharsetを適切に取り扱えるようになった時点で、PDOが最善手になる。
MDB2やmysqliを選択する事のつらさがそこにはある。php >= 5.3以降はPDOが積極的に開発される。
しかし、PDO_MYSQLには改善の兆しが無い。PHP 5.3.3RC3のソースコードを見ても対応されていない。
ひょっとするとDSNでcharsetを指定させたくないポリシーでもあるのかも知れない。
「APIの仕事を増やすなんてけしからん。pgsqlみたいに SET CLIENT_ENCODING したら動くようにしろ」って思っているとか。だとしたら反論の余地は無い。個人的にもmysqlが修正されるのが最善の方法だと思う。
（まあ、本当にそう思ってるなら、emulate_prepareも0にしたまえよ、と思うけど）
「mysql >= x.xを使えばセキュリティもパフォーマンスもサロゲートペアも全部マシになりますよ」という答えのほうが必要である。mysqlの今後の開発は、あまり期待できる状況では無いのだが・・・。
というわけで一万歩譲って「mysql関数で対応してるんだからPDOも対応しろ」という主張をすると仮定しても、
年内リリースと噂されるRHEL 6には搭載が間に合わない事が予測される。
RHEL 6のbeta2に搭載されたPHPのバージョンは5.3.2（最新版）。RHEL 5が5.1.6で、RHEL 4が4.3.9である。
最悪のケースを「mysqliの無いRHEL5」と仮定すると、
打てそうな手は結局 MYSQL_ATTR_READ_DEFAULT_FILE しか無い。
まとめ
PDOで良い。
$dbh = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password,
array(
PDO::MYSQL_ATTR_READ_DEFAULT_FILE => '/etc/my.cnf',
PDO::MYSQL_ATTR_READ_DEFAULT_GROUP => 'pdo',
PDO::MYSQL_ATTR_DIRECT_QUERY => true
));
[pdo]
default-character-set=utf8
接続時に PDO::MYSQL_ATTR_DIRECT_QUERY を true にしていれば、
わざわざ後付けで PDO::ATTR_EMULATE_PREPARES を false にする必要は無い。
2010/07/20追記
[php-5.3.3RC3/ext/pdo_mysql/mysql_driver.c]
static int pdo_mysql_set_attribute(pdo_dbh_t *dbh, long attr, zval *val TSRMLS_DC)
中略
case PDO_MYSQL_ATTR_DIRECT_QUERY:
case PDO_ATTR_EMULATE_PREPARES:
((pdo_mysql_db_handle *)dbh->driver_data)->emulate_prepare = Z_BVAL_P(val);
って書いてあるけど、これ本当にPDO::MYSQL_ATTR_DIRECT_QUERYにtrueを指定して正しく動くのか？
static int pdo_mysql_get_attribute(pdo_dbh_t *dbh, long attr, zval *return_value TSRMLS_DC)
中略
case PDO_MYSQL_ATTR_DIRECT_QUERY:
ZVAL_LONG(return_value, H->emulate_prepare);
break;
static int pdo_mysql_handle_factory(pdo_dbh_t *dbh, zval *driver_options TSRMLS_DC) /* {{{ */
中略
H->emulate_prepare = pdo_attr_lval(driver_options,
PDO_MYSQL_ATTR_DIRECT_QUERY, H->emulate_prepare TSRMLS_CC);
H->emulate_prepare = pdo_attr_lval(driver_options,
PDO_ATTR_EMULATE_PREPARES, H->emulate_prepare TSRMLS_CC);
なんか非常に不安なので、
PDO::MYSQL_ATTR_DIRECT_QUERY をいじるのはやめて、
PDO::ATTR_EMULATE_PREPARES を false にする方法を採用しようかと思う。
実際に動作確認を綿密にしたほうがいいな・・・。
大垣さんの3年も前のエントリが元になっている議論ですが・・・。
http://blog.ohgaki.net/set_namesa_mcb_asc
意外にもネットで正しい結論が書かれていない（≒誤った理解が増えている）ので、まとめてみました。
最近どっかの本の著者がPEARを勧めて、それを鵜呑みにした人がPDOをPEAR::DBに乗り換えようとしているらしい？ いや、それもう推奨されてないんですけど・・・。
変なPHPerのせいでPHPが叩かれる現象は永遠に繰り返されるのだろうか。
問題の発生条件
以下のすべて を満たすこと。
- データベースエンジンが mysql >= 4.1.3 である。
- プリペアードステートメントを使っていない。
- フォームから入力された値を使うSQL文が存在する。
- スクリプト中で SET NAMES を使って文字コードを変えている。
- PHP >= 5.2.3 にもかかわらずスクリプト中で mysql_set_charset を使っていない。
正しい考察手順
- mysql をやめる。
- 最初にこの問題が mysql の不具合であることを認識するべきである。
- ゆえに mysql を使わないのが最も正しい解決方法となる。どうせこの問題が無くてもmysqlは正しく4バイトのUTF-8を取り扱えないのだ。SET NAMES binary も正当な解決策ではないのだ。
- たとえば postgresql >= 7.4であれば、この問題は起こらない。
- mysql < 4.1.3 にはプリペアードステートメントが無いのでそもそも論外。
- プリペアードステートメントを使う。
- PDOを使ってないのが理由なら、PDOを使うように変更しましょう。
- PDO::ATTR_EMULATE_PREPARES は false にする（PHP >= 5.2.1のPDO_MYSQLではデフォルトtrueらしい。ショックだ）。
- プ リペアードステートメントをエミュレーションするPHPライブラリには注意する。特にPHP4互換を謳うモダンフレームワークには要注意。
- likeは WHERE name LIKE '%' || :name || '%' とか書くと安全だよ！
- 当 たり前だけど、SQL文字列に連結する変数が全くエスケープされていないなら、SET NAMES 問題以前にセキュリティに大問題があります。
- バリデータを真面目に書く。
- そもそも日本語文字列の自由記入が出来る箇所は限られている。
- 結局プリペアードステートメントをまともに使っているかどうかの問題に帰結する。
- SET NAMES を使わないで済むようにmysqlクライアントとサーバの設定をする。
- スクリプトとmysqlクライアントとmysqlサーバのエンコードを統一すれば済む問題です。
- たとえそれがShift_JISであっても、 mysql_real_escape_string()を使えば済む問題です。
- Shift_JISの文字化けを回避するためだけに、たとえばモバイル用サイトで「PHPとDBはEUCだけど表示はSJIS」のような構築をするのは本末転倒です。過去の亡霊にとらわれすぎ。
- でも、UnicodeやEUC-JPが選択可能なシステムでわざわざShift_JISを選んでる人のことは「ブヲ」（侮辱の侮に半角のヲ）と呼んであげま しょう。
- UnicodeとEUC-JPの選択で後者を選ぶ人は、サロゲートペアか文字列長か「波ダッシュ全角チルダ問題」のい ずれかで被害を受けて精神的に病んでしまった方です。いたわりの心をもって接しましょう。マジレスすると、PC専用サイトでEUC-JPに統一するのは個 人的にはアリだと思う。
- 以上すべての対策を満たせないときに、はじめて mysql_set_charset()を考える。
- つまり「mysqlを使わないといけなくて」「PDOやプリペアードステートメントが使えなくて」「mysqlの設定もいじれない」状況。
- 安物レンタルサーバで無料サンプルやら前任者の糞スクリプトを動かすような鬱になる仕事がこれに該当する。
- mysql_query("SET NAMES 'utf8'") を mysql_set_charset('utf8') に変えるだけの簡単なお仕事です。
誤った考察
- PHPをやめればいいと考えている。
- ジョークや煽りならともかく真に受けてる人がいそうで恐ろしい。
- あらゆる面でPHPを上回る代替手段を追い求めるのはロマンだから否定しない。
- 古いmysqlを使っている。
- mysql < 5.1ではプリペアードステートメントを使うとクエリキャッシュが効かなくなるらしい。
- で、それを理由にプリペアードステートメント自体をやめてしまうらしい。バージョンあげろよ・・・。
- そもそもPDOを使っていない事に疑問を抱いていない。
- mysql関数のプリペアードステートメントは貧弱です。?しか使えません。それでいいの？ いいならいいけど。
- PHP4を使っていいのは小学生までだよねー！
- そもそもPHP < 5.2.3には対策手段が無い。RHEL5とかでPHP 5.1.6を使ってる人はその時点で解決不能なのです。そんな環境でPHPを実行している事自体を疑問視すべき。
- PDOは mysql_set_charset() が無いからセキュリティに不備があると考えている。
- 「SET NAMES問題の直接的な解決策は mysql_set_charset() が最適である」という主張を見て、「mysql_set_charset()を使わない環境はすべてSET NAMES問題が起こる」と短絡しちゃった人が、こういう誤った結論に陥る。
- どうしてもいくつかプリペアードステートメントを使えないSQL文字列を実行せざるを得ないのであれば、そのSQLだけ mysql_set_charset() を使って mysql_query() すれば良いのでは？
- そういうSQLをなくす事のほうが重要だと思いませんか？
- PDOよりPEAR::DB や PEAR::MDB2 のほうが良いソリューションであると考えている。
- PEAR::DBは mysql_set_charset() に対応していません。最新版 1.7.13 (stable) was released on 2007-09-21を調べました。
- PEAR::MDB2の最新版 2.4.1 (stable) was released on 2007-05-03 は mysql_set_charset() が実装されるPHP 5.2.3 (01-Jun-2007)よりも前にリリースされました。
- なのでそもそも問題解決手段としては全く不適切。
結論
セキュリティ対策のために真面目にやらなければならないこと。
- 最新安定版のPHP本体とライブラリを使うこと。
- まともなデータベースエンジンを使うこと。もしくはデータベースエンジンを使わないこと。
- 自分でインストールしたソフトウェアを使うか、信頼出来る人に依頼すること。
- 誤った判断や選択をしないこと。自分の誤りを訂正できるようにしておくこと。
- ケチらんでプロに頼んだほうがトータルで考えてお得だよ！
「セキュリティ過敏症」に掛かっている人ほど、セキュリティに関するリテラシーが低いと思う。
個人情報を実際に入力するお客様が不満を言っているならともかく、技術力の低いプログラマーが「なんちゃってセキュリティ対策」をやっちゃっても、決して誰も幸せにはならないのだ。
JOINをどうするか。
まず、RDBMSで自分が設計するテーブルの種類を洗い出してみた。
- ハッシュ（設定ファイル的な用途）
- 一対多のリレーション
- 多対多のリレーション
- 人間様向けに視認できるようにするための表（view）
- なんとかマスタ
- 取引の記録
こんだけと言えば、こんだけである。
もうちょっとだけ掘り下げておく。有料サイトのアクセス権を管理するシステムを想定する。
- customersマスタ
- sitesマスタ
- purchase_history(誰がいつ何を買ったかの記録 一対多)
- permission(誰がどこに入れる権限を有するかの状態 多対多)
- お客様がどのサイトにアクセス出来るかの一覧（ビュー）
リレーションをPHPで書くために最低限必要なことは、関連するすべてのテーブルをreadしないといけない。
それが嫌なら、リレーションの結果として取得したいデータを、あらかじめテーブルに埋めておくしかない。
SQL的に言うなら「パフォーマンスを稼ぐために正規化をあえて崩す部分を作る」ということになる。
「お客様がどのサイトにアクセス出来るかの一覧」を得るための最小限のコードは、次のようになる。
SELECT sites.name
FROM permission
INNER JOIN sites ON permission.sites_id = sites.id
WHERE customers.id = 1
これに対して、あらかじめpermissionテーブルの中にsites.nameを格納しましょう、というアプローチはだいぶナンセンスだ。なので仕方なくこれを分けることにする。
$sites_ids =
SELECT sites_id
FROM permission
WHERE customers_id = 1
$site_names =
SELECT name
FROM sites
WHERE id IN ( 【join(',', $site_ids)】 )
【】の中身はSQLではなくPHPです。
ちなみに、TT用語では、INのことを TDBQCNUMOREQ というらしい。
これを拡大して、「サイトの価格を表示したい」「合計金額も計算したい」となった場合は、合計金額の表示はPHPで計算することになる。カテゴリ別に分類したり、カテゴリごとの合計金額を出すのもPHPの仕事だ。でもそれは元々多次元配列で実現するだろうから、あまり労力は変わらないんじゃなかろうか。
この$site_namesを「購入順」に並べたい。となると、話は少しややこしくなる。
ひとつの方法は、PHPで配列をマージした上でsortしてしまう手である。さもなくば、新しい配列を作って並べ直す。
// $site_idsは ORDER BY dt ASCされているものとする。
// $site_namesは site_id を キーにしているものとする。
$sorted = array();
foreach ($site_ids as $site_id) {
$sorted[] = $site_names[$site_id];
}
ちょっと苦しいけど、まあ出来る。値渡しにしたらメモリ効率は良くないだろうな。
なんだかこの作業って、ORMの内側にある泥臭い実装とあんまり変わらない気がするのだ。
どうせmysqldにはhtmlspecialchars関数は無いので、ビューで受け取った値をさらに加工しているのが実態である。
だったら、いままでSQLに頼ってきた処理も全部PHPで用意しとけば便利じゃね？ と思ったりする。
それだけじゃ実現不可能なケースや、実現コストが現実的ではないケースを考えようとしてみたけど、
とりあえずはあんまり浮かばなかった。あとは色々試してみるしかないな。
NoSQLという挑発的な名称と、ハッシュしか扱えないという貧弱ぶりが嫌いで、
「どうせこんなもんただのバズワードだろう。memcachedに顧客情報を記録するのは馬鹿のすることだ」と思っていたが、
NoSQLをNot Only SQLと読ませようという活動や、Tokyo Cabinetがテーブルモデルに対応している事を知り、
もともとmysqlがあまり好きではない身としては、かなり惹かれるものがあるのではないかと思い直した。
mysqlとTokyo Tyrant(TT)を比較して、前者の優位点を列挙してみる。
- JOINが使える。つまり、リレーションする。
- サブクエリーが使える。
- GROUP BY句が使える。
- SQL関数が使える。
- 1つのサービスで複数のデータベースとテーブルを扱える。
つまり、「考えることが出来る」というのが、RDBMSの相対的な利点だと思う。
しかし、そんな利点は本当に必要なのだろうか？
ビューやストアドプロシージャががんがん回るDBサーバがあって、フロントエンドの負荷を軽減しているというなら良いが、普通そんな仕事はmysqlにはやらせない。PostgreSQLの出番だ。
それに、レプリケーション済みmysqldなんてものは、データの読み込みにしか使わないのだ。どうせ読んでくる内容は決まっているというのに、そのためにデータベース接続を随時発生させるなど愚の骨頂である。データはローカルのメモリにおいておけばいいし、それがデータベースの体裁を保っている必要など全くないのだ。
その要件においては、NoSQLを使うと冗長性と耐負荷性能はあがるので、一概にmysqlのほうが堅牢安全であるとは言い難い。
そして、たいていはmysqlを使ったアプリでも、複雑なSQLは発行しない。そしてそれが必要の無いような設計をする。
ORMが糞みたいなSQLをはき出して仕方ないからそこだけ手でチューニングしたとか、その程度である。
しかし、パフォーマンスならTTのほうがmysqlより数段良いのだ。mysqlにしがみつく理由にはならない。
そして何より我々はPHPerである。ロジックはすべてPHPで書いてしまえばいいのだ。
- サブクエリーの目的は何だ？ WHERE句の条件指定のために内側のクエリを書くくらいなら、そのクエリだけを実行して条件を作ってしまえばいい。2回クエリを投げるとデータベース接続コストがかさむって？ TTならその心配は無い！
- GROUP BY句とSQL関数はPHP関数で代替してしまえ。DBサーバのかわりにWebサーバが計算コストを支払えば良いだけだ。WebサーバとDBサーバが同居しているなら、尚更DBサーバにやらせる意味は薄い。
- あとはJOINだけなんとかすればいいんだー！
というわけで、最終的にこんな事が出来ないかと夢想している。
before
1号機 Webサーバ #1
2号機 Webサーバ #2
3号機 mysqld マスタ(読み書き)
4号機 mysqld スレーブ #1(読み込み)
5号機 mysqld スレーブ #2(読み込み)
もしくは、上記構成よりパフォーマンスが発揮できるmysqlの導入事例として
1号機 mysqldマスタ(読み書き)
2号機 Webサーバ #1 mysqld スレーブ #1(読み込み)
3号機 Webサーバ #2 mysqld スレーブ #2(読み込み)
after
1号機 Webサーバ #1 TTマルチマスタ #1(読み書き)
2号機 Webサーバ #2 TTマルチマスタ #2(読み書き)
3号機 PostgreSQL(読み書き)
長々と書いて自分が導き出したい結論が、mysqldレプリケーションへの否定だという事に今気がついた。
つづく。
