アプリケーション開発のアンチパターン
はいはいメリクリメリクリ。
O'Reilly Japan - SQLアンチパターン を、真夏の車中に放置された果汁グミくらいの柔らかさに噛み砕くシリーズ、最終回。
「Ⅳ部 アプリケーション開発のアンチパターン」です。
19. Readable Passwords
目的
パスワードのリカバリとリセットを行う
アンチパターン
パスワードを平文で格納する
せやな感。認証時に通信傍受されるとアウト。データベース・サーバに侵入されてSQLクエリログみられたらアウト。バックアップファイルをみられたらアウト。あとは、パスワードをメールで送るやつ。ダメ、ゼッタイ。
解決策
ソルトをつけてパスワードハッシュを格納する
まずはハッシュ化。ハッシュは不可逆、固定長な暗号化です。
select SHA2('love_gakky', 256); --> 66561cbb0b879c570c4f97b52dd98526c8b17d261c78e4cfff53dd3ab4a105ed
ハッシュ関数は各データベース製品のハッシュ拡張を使います。上の例はMySQL。
ハッシュ化するだけでは、辞書攻撃ができてしまいます。そこで、生のパスワードにソルトという適当な文字列を加えて、それをハッシュ化します。
select SHA2('love_gakky' || 'daoJf239j$4n.p30/12', 256); --> 5feceb66ffc86f38d952786c6d696c79c2dbc239dd4e91b46729d73a27fb57e9
パスワードごとに異なるソルトを付加しましょう。
これで辞書攻撃に対してはだいぶ強くなりましたが、SQLには平文パスワードが入っているので、ネットワークパケットを傍受されると試合終了です。
アプリケーションでハッシュ化し、SQLクエリではハッシュのみを用いるようにします。
20. SQL Injection
目的
動的SQLを記述する
アプリケーションコードでSQLクエリを文字列として作成するやつ。複雑なクエリを書かなくて良かったり、楽ですね。
アンチパターン
未検証の入力をコードとして実行する
SQLインジェクションについて特に説明する必要もないと思うんですが、例えば、 変数account_idの文字列が1234; delete from Accounts だった場合、
select * from Accounts where account_id = 1234; delete from Accounts
というクエリが発行されて死ぬ可能性があります。他には、URLのパラメータからも攻撃は可能です。http://example.com/setpass?password=xyzzy?userid=123 or true みたいな。
解決策
誰も信用してはならない
それ言っちゃお終いよ。でもそうらしいです。 対処法としては、値のエスケープ、プリペアドステートメント、ストアドプロシージャ、データアクセスフレームワークなどを駆使しましょう。
21. Pseudokey Neat-Freak
目的
欠番を詰める
以下のように連番になっていないテーブルで、隙間を埋めるのか？というお話です。
| person_id | name |
|---|---|
| 1 | ガッキー |
| 2 | 星野源 |
| 4 | かばお |
アンチパターン
隙間を埋める
欠番を割り当てる
新しい行を挿入するときに、 空いているperson_id = 3 を使います。
| person_id | name |
|---|---|
| 1 | ガッキー |
| 3 | 石田ゆり子 |
| 2 | 星野源 |
| 4 | かばお |
アプリケーションにとっては必要ないはずの、開いてる行を探すクエリを発行せなあかんし、別のトランザクションで同時に同じ値を取得してしまうこともあるで。
既存行に番号を振り直す
番号を繰り上げます。
| person_id | name |
|---|---|
| 1 | ガッキー |
| 2 | 星野源 |
| 3 | かばお |
どのタイミングで更新するのかやキーの競合が起きると言った問題があるし、これまで参照できていたデータが参照できなくなるみたいなリスクもあるで。
解決策
疑似キーの欠番は埋めない
行番号と主キーは違う。行番号は結果セットにおける行の順序番号。主キーはテーブルに置ける行の識別子。主キーが連番である必要性はナッシングなのです。主キーにおいては「値が重複しない」を満たせればいいので、GUIDを使ったりしてもおk。ちょっと容量食うけど。
22. See No Evil
目的
簡潔なコードを書く
できるだけ少なくシンプルなコードで、やりたいことを実現できたらモテます。
アンチパターン
肝心な部分を見逃す
ちゃんと何が起こっているかを判断しましょう。APIをつかったときにエラーが返ってきたら、その原因は何なのか。動的SQLの場合、実際に発行されるクエリが構文的に正しいか、パラメータは正しいか、みてみること。
解決策
エラーから優雅に回復する
エラーは適切にハンドリングしましょう。もはやSQL関係ないですね。
23. Diplomatic Immunity
目的
ベストプラクティスを採用する
アプリケーションコードと同じくデータベースについても、バージョン管理・テスト・ドキュメント化をしよう！というお話。
アンチパターン
SQLを特別扱いする
この世には、ソフトウェアエンジニアとデータベースエンジニアが独立している世界があるらしいです。弊社ではないですね。とはいえ、アプリケーションコードとSQLはその本質的な違いから、特別扱い感はありますね。
解決策
包括的に品質問題に取り組む
文書化
ER図を書こう！テーブルの説明、関係性もドキュメント化しよう！
バージョン管理
データベースそのもののバージョン管理はできない（それはすなわちバックアップ）ため、上記のドキュメントやテーブル定義のスクリプト、トリガなどもまとめてGitへGo！！！
テスティング
テーブル、列、ビューの存在、各種制約、トリガやストアドプロシージャの挙動について、アプリケーションのユニットテストフレームワークを利用するなどしてテストしよう！
24. Magic Beans
目的
MVCのMを単純化する
MVCっていうのは、アプリケーションをモデルとビューとコントローラの3つの関心事に分けるアーキテクチャ技法なんですけど、そのあたりはググってください。Fat ModelといわれるようにModelは責務が多く処理が複雑になりがちです。これを一般化してデータベースとの結合をシンプルにしたいみたいな欲求があります。
アンチパターン
モデルがアクティブレコードそのもの
アクティブレコードっていうのは、モデルのフィールドをテーブルの列とマッピングして、オブジェクトに対するCRUD操作がすなわちデータベースのリレーションに対する操作になるようなデザインパターンです。パターンとしてはシンプルなのですが、以下の問題があります。
- データベースとアプリケーションの結合が密になりすぎます。例えば、データベースのあるテーブルに列を追加すると、対応するモデル及びそのモデルを扱うコードをすべて修正する必要があります。
- データベースに対するCRUD操作をアプリケーションコードに対して公開してしまいます。
- 意図せずとも「モデル＝データベーススキーマ」みたいなつくりになっていって、ドメインモデル貧血症になり、だいたいコントローラが太ります。
- モデルのテストがデータベース依存になりがちです。
解決策
モデルがアクティブレコードを「持つ」ようにする
モデルを理解する
モデルとデータベースを分離する際に、以下のガイドラインがあります。
- 情報エキスパート：操作の責任を持つオブジェクトは、その操作に必要なすべてのデータを持つべき。
- 生成者：モデルがデータを扱う方法は、非公開であるべき。
- 疎結合性：論理的に独立しているコードは、分離するべき。
- 高凝集性：ドメインモデルクラスのインタフェースは、物理的な操作ではなく意図を示すべき。
ドメインに基づいて設計しましょう。データベースの構造ではなく、アプリケーションの概念に基づいてモデルを設計するといいかんじです。
25. 砂の城
訳者のあとがきてきなものなので割愛します。 サービス安定稼働という観点において、だいたいの問題は「想定不足」。理想的には、テストや例外処理、バックアップ、高可用性構成から、災害対策まで、あらゆる障害を想定して対策を立てておきたい、というお話です。障害時の予行練習などいいかもってさ。耳が痛い。
Ⅳ部のまとめ
データベースと関わる部分で意識すべきところがまとめられており、クエリやデータベースの構造についてはあまり出てきませんでした。本書の主眼ではないので表面的な説明にとどめている感があり、本格的に理解するには参考文献を読む必要がありそうです。
将来生まれてくるかもしれない自分のこどもには「 xxx'); drop table students; --」という名前をつけようと思います。
全体のまとめ
ウェッブの各所で、「この本の功績は各アンチパターンに名前をつけたことだ」と言われているっぽいです。自分の環境だとこの本のアンチパターン名が共通言語になる未来はあまり見えないですが、たぶん常識的なことばかりだと思うので、トンチンカンなことを言わないために役立ったかなと思います。逆に言うと、いまは周りに優秀な方々がいて、自分が間違ったことをやろうとしていてもフルボッコにしてくれるのですが、そうじゃない環境にいつなるかわからないので、自分が適切な知識を身につける必要さも感じました。
ちなみに本をまとめなおすことは今回はじめてです。自分は本を読むのが苦手で5分以上集中が続かず、読んでもだいたい5秒以内に忘れています。自分の言葉でまとめなおすことで、かなり頭には入った気がします。ただ読むのに比べてかなり時間がかかってしまって、効率がいいのかは怪しいです。でも、今後もたぶん（ブログを更新するために）やります。
