「SQLアンチパターン」を避けるためのチェックリスト④（アプリケーション設計編）

　「SQLアンチパターン」について、自分なりのチェックポイント言語化シリーズ第４弾です。下記の記事の続きとなります。

「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log4ketancho
「SQLアンチパターン」を避けるためのチェックリスト②（DB物理設計編） - log4ketancho
「SQLアンチパターン」を避けるためのチェックリスト③（SQLクエリ設計編） - log4ketancho
アプリケーション開発のアンチパターン【本記事】

今回の内容は、DB 設計のみではなく、DB を含めたシステム設計全体のお作法に関する話になります。

作者: Bill Karwin,和田卓人,和田省二,児島修
出版社/メーカー: オライリージャパン
発売日: 2013/01/26
メディア: 大型本
購入: 9人クリック: 698回
この商品を含むブログ (46件) を見る

SQLクエリ設計をする際のチェックポイント

□ パスワードを平文でテーブルに格納していないか
□ 動的に SQL 文を作成するときに SQL インジェクションへの対策をしているか
□ 連番主キーの欠番を頑張って埋めようとしていないか
□ DB接続している部分で例外処理しないプログラムはないか
□ バージョン管理されてない DB 関連リソースはないか
□ モデルがアクティブレコード化していないか
□ 発生しうる問題やそれに対する対応はまとまっているか

19. リーダブルパスワード（読み取り可能パスワード）：パスワードを平文でテーブルに格納していないか

アンチパターン例

　パスワードを平文で格納している場合、SQL 文でパスワードの突き合わせをする電文上でパスワードが晒されることになります。また、クエリログやバックアップファイルにも平文のパスワードが表示／格納されてしまい、個人情報流出時の被害が大きくなります。まれによく、設定したパスワードをメールで送信してくる Web サイトがありますが、あの手のサイトは裏側で平文でパスワードを格納しているのだと思います。ダメですね。

グッドパターン例

　二つの方式が推奨されていました。

ハッシュ化（平文パスワード＋ソルト）

　パスワードにソルトを加え、ハッシュ化して格納すべきです。ハッシュ化するだけでは、何らかの理由でDBに接続されてしまった際に、辞書攻撃をされる可能性があるので、ユーザーごとにソルトを与えた上でハッシュ化することが推奨されていました。

CREATE TABLE Accounts ( 
    account_id SERIAL PRIMARY KEY, 
    account_name VARCHAR( 20), 
    email VARCHAR( 100) NOT NULL, 
    password_hash CHAR( 64) NOT NULL, 
    salt BINARY( 20) NOT NULL 
);

SQLクエリでもハッシュ後パスワードを利用する

　また、SQLクエリでも平文パスワードを使わず、アプリケーションサイドでハッシュ化した上で、クエリを投げることも推奨されていました。

まずはユーザIDで検索し、返ってきたソルトを用いてアプリケーション側で入力された平文パスワードをハッシュ化し、クエリを投げる。
↑の方式だと、ソルトは電文でさらされてしまうので HTTPS 化することを推奨。

20. SQL インジェクション：動的に SQL 文を作成するときに SQL インジェクションへの対策をしているか

アンチパターン例

　下記のような未検証の入力を使って SQL 文を組み立てるアプリケーションコードを書いてしまっていないでしょうか？

$sql = "SELECT * FROM Accounts WHERE account_id = $account_id";
$stmt = $pdo->query($sql);

$account_id はユーザからの 123 というような入力を想定していますが、 123; DELETE FROM Accounts という入力をされてしまうと Accounts テーブルの全てのレコードが削除されてしまいます。

グッドパターン例

　本の中では SQL インジェクションをひとつの方法で完璧に防ぐのは難しく、下記の方法を理解し適切に使い分けるべきと書かれています。

フロントエンド、バックエンドで適切にバリデーション処理を行い、不正な入力値をフィルタリングするようにします。
プリペアドステートメントを用いて、入力値を SQL から分離します。先に SQL 文としての解析を済ませ、変数部分にしか代入ができないため、上のような DELETE を含む入力をされても DELETE 文が発行されることはありません。
プリペアドステートメントを使った場合、インデックスを含む列へのクエリの最適化がおかしくなる場合があるそうです。その場合、（性能要件で）どうしてもプリペアドステートメントを使えない場合、下記のように引用符で囲む方法も紹介されていました。

$quoted_account_id = $pdo->quote($_REQUEST["account_id"]);
$sql = "SELECT * FROM Accounts WHERE account_id = $quoted_account_id";
$stmt = $pdo->query($sql);

また、ソート順などをユーザの入力によって変更する場合は、リクエスト値を直接使うのではなく、リクエスト値として想定している値の配列を用意し、その中から選択する形で SQL 文を動的に組み立ててリスクを減らす方法も紹介されていました。
チームメンバーにコードレビューしてもらうことを徹底する。

21. シュードキー・ニートフリーク（疑似キー潔癖症）：連番主キーの欠番を頑張って埋めようとしていないか

アンチパターン例

　下記のような状態のデータを見たときに、「2, 3, 5 はどこにいったの？」ムズムズしますでしょうか？

user_id(PK)	email	subject	created_at
1	alice@xxx.xxx	English	2018-03-01
4	bob@yyy.yyy	Mathematics	2018-03-01
6	carol@zzz.zzz	English	2018-03-05

　ムズムズするのはいいのですが、これを

bob を 2 に、carol を 3に変更する
これから新しく入ってきた先生に 2 やら 3 やらの欠番を割り当てる

といった形で埋めにいってしまうのはアンチパターンです。このような作業が複数のプロセスから行わると不具合の元になったり、正しく処理を作れないと外部から参照されていた場合にデータが見つからない or 誤ったデータを参照してしまう、ということになりかねません。　

グッドパターン例

　著者曰く、この「ムズムズ」は疑似キーを行番号と捉えてしまうことが背景になっているとのことです。ページングなどで行番号を利用したい場合は、エンジンごとに別の文法が提供されているので、そちらを利用しましょう。とにかく「欠番を埋めない」ことがグッドパターンです。

22. シー・ノー・エビル（臭いものに蓋）：DB接続している部分で例外処理しないプログラムはないか

アンチパターン例

　　アプリケーションプログラムでDB接続した際に、戻り値の確認や例外処理をしない部分があるのはアンチパターンです。幾つか確認ポイントが紹介されていたので、グッドパターンとして掲載します。

グッドパターン例

データベース接続時や、SQLクエリ実行の際に、戻り値の確認や例外処理を正しく行う。
デバッグ用に、プログラムではなく構築されたあとの SQL を見られる環境を整備する。例えば、実行したSQLをログなどに出力する方式にする。

23. ディプロマティック・イミュニティ（外交特権）：バージョン管理されてない DB 関連リソースはないか

アンチパターン例

　このご時世、アプリケーションプログラムをバージョン管理するのは当然なのに、DDL 文などの DB 関連のリソースが管理されていないことがある、という章です。DB 関連のナレッジがひとりの DBA に集中してしがちで、結果としてそのような状態が生まれてしまうとのことでした。同様の理由で、DB 関連の情報がドキュメント化されないこともよくあります。著者のおすすめな進め方の一部をグッドパターン例として掲載します。

グッドパターン例

DB 関連の情報を ER 図などでドキュメント化する。
SQL 文も必ずソースレビューの対象とする。特にセキュリティ観点のレビューを行うようにする。
DB 関連のリソースもバージョン管理の対象とする。ER 図などもここに含めること。アプリケーションコードとデータベースコードは同じリポジトリにすること。
DB 関連のテストコードも必ず書くこと（テーブルが存在すること、制約が正しく設定されていること、など）

24. マジックビーンズ（魔法の豆）：モデルがアクティブレコード化していないか？

アンチパターン例

　アクティブレコードパターンは、テーブルごとに対応するクラスを作り、その中でシンプルな CRUD 操作を定義するものです。このパターンはテーブルに対するシンプルな接続方法を提供してくれる良いパターンですが、モデルが常にアクティブレコード化する（全てのモデルクラスがアクティブレコードの基底クラスを継承する）場合、下記のような問題が発生するとのことです。

モデルをデータベーススキーマに強く依存させてしまう
CRUD 機能を公開してしまう
コントローラーとアクティブレコード間が複雑に密結合してしまう

グッドパターン例

　本章では、下記の設計をすることで、この問題を回避できると紹介されています。

モデルがアクティブレコードを持つようにする
コントローラーとアクティブレコードの間に「ドメインモデル」を挟むことで疎結合化する

25. 砂の城：発生しうる問題やそれに対する対応はまとまっているか？

アンチパターン例

　システム障害や性能問題が発生したときの想定をしておらず、問題が発生してからドギマギしてしまうのがアンチパターンです。どうしても目に見える機能の開発が優先され、このような「発生するかもしれないこと」への準備がおざなりになってしまうことがよくありますが、正しく「想定」しておきましょう、というのがこの章の内容です。私も改めて気を付けようと思います。

グッドパターン例

　トラブルは起きるものと捉えて、その想定をしておく。

事前に性能テストを行った上で、どの程度までのリクエスト量をさばけるかのベンチマークを取得しておく
本番同等の検証環境を１セット用意する
バックアップを適切に取る
システムの要件に合わせて高可用性な設計を行う
運用ポリシーを作成し、障害発生時のリカバリ手順などを準備しておく

まとめ

　第３弾から少し時間が経ってしまいましたが、第４弾についてまとめました。これまでの３つとは毛色が少し異なり、アプリケーションコーディングの際に意識したいポイントが中心でした。

　これまでの章についてはこちらをご覧ください。

第１弾「DB論理設計」編はこちらです。

www.ketancho.net

第２弾「DB物理設計」編はこちらです。

www.ketancho.net

第３弾「SQL クエリ」編はこちらです。

www.ketancho.net

　（別途まとめ記事を書くかもしれませんが、）今回の記事で、「SQLアンチパターン」の整理は完了です。非常に勉強になる本でした。この記事をまとめたことで、各章２回以上は読んだことになり（実は、３〜４回読んでいる章が大半ですｗ）、考え方が定着してきた気がします。今後 DB 設計をする際に、無意識的にグッドパターンを選択できるようになれればと思います。DB にデータを格納しないシステムはないといっても過言ではありません。そういう意味で、全てのエンジニアにとって必読な本だと思います。ぜひ、読んでみてはいかがでしょうか？

SQLアンチパターン

作者: Bill Karwin,和田卓人,和田省二,児島修
出版社/メーカー: オライリージャパン
発売日: 2013/01/26
メディア: 大型本
購入: 9人クリック: 698回
この商品を含むブログ (46件) を見る