SQLアンチパターンをまとめました。
この本では、

の4つのパートに分けてそれぞれのアンチパターンを解説しており、
この記事では「4. アプリケーション開発のアンチパターン」について記述します。

第19章-Readable Passwords（読み取り可能パスワード）
第20章-SQL Injection（SQLインジェクション）
第21章-Pseudokey Neat-Freak（疑似キー潔癖症）
第22章-See No Evil（臭いものに蓋）
第23章-Diplomatic Immunity（外交特権）
第24章-Magic Beans（魔法の豆）
第25章-Sandcastle（砂の城）

第19章-Readable Passwords（読み取り可能パスワード）

パスワードを平文で格納してしまうアンチパターンです。
例：ユーザー情報管理のためのAccountsテーブル

CREATE TABLE Accounts (
  account_id   SERIAL PRIMARY KEY,
  account_name VARCHAR(20) NOT NULL,
  email        VARCHAR(100) NOT NULL,
  password     VARCHAR(30) NOT NULL
);

デメリット：重大なセキュリティ欠陥が存在する

①パスワードの格納時のセキュリティ欠陥

新規アカウントの発行時に、以下のクエリでパスワードを平文で保存するとします。

INSERT INTO Accounts (account_id, account_name, email, password)
  VALUES (123, 'billkarwin', 'bill@example.com', 'xyzzy');

パスワード挿入（or 変更）のクエリを攻撃者に読み取られた場合に、パスワードが簡単に分かってしまいます。
読み取られる代表的な例を以下に3つ紹介します。

アプリケーションクライアントから、データベースサーバーに送信されたクエリのネットワークパケットを傍受される
データベースサーバーに侵入した攻撃者によって、データベースが実行したクエリの記録を含むログファイルにアクセスされる
データベースが実行したクエリの記録を含むログファイルにアクセスされる
データベースのバックアップファイルやバックアップメディアを盗み、データを読み取られる

②パスワードの認証時のセキュリティ欠陥

パスワードが平文で格納している場合、ユーザーのログイン時には、以下のようなクエリが実行されます。

-- DB登録のパスワードと一致で1、不一致で0
SELECT CASE WHEN password = 'opensesame' THEN 1 ELSE 0 END
  AS password_matches
FROM Accounts
WHERE account_id = 123;

パスワードの格納と同様に、攻撃者にパスワードを晒してしまっています。

③2つの条件をひとまとめにしてしまい、対策ができない

認証クエリは名前とパスワードの2つの条件が指定されているケースが多いです。

SELECT * FROM Accounts
WHERE account_name = 'bill' AND password = 'opensesame';

このクエリだと、アカウント名が存在しないのか、パスワードが間違っているのかが判別できません。

例えば、ログインが複数回連続で失敗を検出した場合に攻撃者による可能性があるため、アカウントを一時的にロックしたいとします。
しかし、名前とパスワードの2つの条件を指定しまっていると、どちらが原因で失敗しているのかわからないため、一時ロックの対応をとれません。

④パスワードを電子メールで送信する

パスワードを忘れてしまった場合などは、アプリケーション側でユーザーのメールアドレスに情報を送信する場合を考えます。

SELECT account_name, email, password 
FROM Accounts
WHERE account_name = '123';

パスワードを平文で格納している場合には、メールの内容は以下のようになります。

From: daemon
To: bill@example.com
Subject: パスワードのリクエスト
Content: アカウント「bill」のパスワードの再通知リクエストに回答します。
パスワードは「xyzzy」です。アカウントにログインするには、以下のリンクをクリックしてください。
http://www.example.com/login

平文のパスワードを電子メールで送信してしまうことは、非常に深刻なセキュリティリスクになります。
攻撃者は様々な方法で、電子メールの傍受、記録、保存を行えるためです。

解決策：ソルトを付けてパスワードハッシュを格納する

解読可能でなくても、ユーザーの入力内容を使用したパスワード認証は行うことができます。

①ハッシュ関数の使用

パスワードを一方向性の暗号学的ハッシュ関数を用いて、暗号化します。
ハッシュには「不可逆である」という特性があるため、ハッシュ値から入力文字列を復元できません。
ハッシュ化には、「SHA-1」や「MD5」などは暗号化強度が十分ではないため、「SHA-256」などの強度があるものを使用するべきです。
また、「SHA-256」を用いることで64文字固定長になるので、パスワードの長さも漏洩する心配がなくなります。

CREATE TABLE Accounts (
  account_id    SERIAL PRIMARY KEY,
  account_name  VARCHAR(20),
  email         VARCHAR(100) NOT NULL,
  -- 64文字固定長を保存できるように設定
  password_hash CHAR(64) NOT NULL
);

②ハッシュにソルトを加える

解決策①を行っても、同じパスワードが同じハッシュ値になってしまうと、脆弱性に繋がってしまいます。
攻撃者にデータベースへのアクセスを許してしまった場合に、解析されてしまうと同じパスワードを使用しているユーザーのパスワードが分かってしまうためです。
このような解析手法は、以下のようにSQLを使用しても行うことができます（辞書攻撃と呼ばれています）。

CREATE TABLE DictionaryHashes ( 
  password      VARCHAR(100),
  password_hash CHAR(64)
);

SELECT a.account_name, h.password
FROM Accounts AS a INNER JOIN DictionaryHashes AS h
  ON a.password_hash = h.password_hash;

この辞書攻撃を防ぐために、暗号化前のパスワードへ「ソルト」を付与する方法があります。
「ソルト」とは無意味な文字列のことです。
暗号化前に実際のパスワードと一緒に「ソルト」を渡して連結することで、同じ文字列でも異なるハッシュ値にすることができます。
ソルトの設定や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
);

INSERT INTO Accounts (account_id, account_name, email, password_hash, salt)
VALUES (123, 'billkarwin', 'bill@example.com', SHA2('xyzzy' || 'G0y6cf3$.ydLVkx4I/50', 256), 'G0y6cf3$.ydLVkx4I/50');

SELECT (password_hash = SHA2('xyzzy' || salt, 256)) AS password_matches FROM Accounts
WHERE account_id = 123;

③SQLからパスワードを隠す

解決策①と②を行うことで、辞書攻撃への対策を行うことができました。
しかし、SQLデータベース内では、パスワードは平文として保存されています。
そのため、以下のケースなどではパスワードを読み取られてしまいます。

攻撃者によってネットワークパケットが傍受された場合
SQLクエリが記録されたログファイルが攻撃者の手に渡ってしまった場合

このケースの対策としては、SQLクエリでパスワードを平文として使用しないことです。
以下のようにアプリ側でハッシュを計算するようにして、SQLクエリではハッシュ値のみを扱うようにします（PHPの場合）。

<?php
$password = 'xyzzy';

$stmt = $pdo->query(
  "SELECT salt
  FROM Accounts
  WHERE account_name = 'bill'");

$row = $stmt->fetch();
$salt = $row[0];

$hash = hash('sha256', $password . $salt);

$stmt = $pdo->query("
  SELECT (password_hash = '$hash') AS password_matches
  FROM Accounts AS a
  WHERE a.account_name = 'bill'");

$row = $stmt->fetch();
if ($row === false) {
  // アカウント 'bill' が存在しない場合の処理
} else {
  $password_matches = $row[0];
  if (!$password_matches) {
    // パスワードが間違っている場合の処理
  }
}

この方法ではブラウザからサーバーへの情報（ユーザーがフォームに情報を入力して送信するなど）は、平文で送信されてしまいます。
そのため、ブラウザからサーバーへパスワードを送信する際は、セキュアHTTP（HTTPS）がよく使用されています。

④パスワードをリカバリーするのではなく、リセットする

パスワードの安全な格納方法は解決策①〜③で解決しましたが、パスワードを忘れた場合のリカバリー方法はどのようにするかという問題が残っています。
データベースには、パスワードのハッシュ値を格納しているため、リカバリーはできません。
そのため、パスワードを忘れたユーザーに対しては、主に以下の2つ方法をとります。

一時パスワードをメールで送信する
リクエストをデータベースに記録してトークンを割り当てる

「 一時パスワードをメールで送信する 」方法は以下のようなメールを送信して、ユーザーに再度パスワードを再設定してもらいます。

From: daemon
To: bill@example.com
Subject: パスワードのリセット
Content: アカウントのパスワードリセット依頼に回答します。
一時パスワードは「p0trz3b1e」です。このパスワードは1時間後に無効化され、アクセスできなくなります。
以下のリンクをクリックして、アカウントにログインし、新しいパスワードを設定してください。
http://www.example.com/login

一時パスワードを短時間で無効にすることで、セキュリティを強化することができます。
また、ユーザーの初回ログイン時には、パスワード変更を強制するように設計すべきです。

次に「 リクエストをデータベースに記録してトークンを割り当てる 」方法についてです。
トークンのテーブルは以下のように設計します。

REATE TABLE PasswordResetRequest (
  token       CHAR(32) PRIMARY KEY,
  account_id  BIGINT UNSIGNED NOT NULL,
  expiration  TIMESTAMP NOT NULL,
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

SET @token = MD5('billkarwin' || CURRENT_TIMESTAMP || RAND());

INSERT INTO PasswordResetRequest (token, account_id, expiration) 
VALUES (@token, 123, CURRENT_TIMESTAMP + INTERVAL 1 HOUR);

作成したtoken付きのURLをメールで以下のように送信します。

From: daemon
To: bill@example.com
Subject: パスワードのリセット
Content: アカウントのパスワードリセット依頼に回答します。
1時間以内に以下のリンクをクリックしてパスワードを変更してください。
1時間が経過するとリンク先のページにはアクセスできなくなり、パスワードも変更できません。
http://www.example.com/reset_password?token=f5cabff22532bd0025118905bdea50da

アプリケーションがパスワードリセットページへのリクエストを受信した時、tokenパラメータの値はPasswordResetRequestテーブルと一致している必要があります。
加えて、トークン失効の期限は未来の時刻である必要があります。
また、account_idを参照していることで、トークンは1つのアカウントのパスワードしかリセットできないように制限されています。

さらにセキュリティを強化するためには、以下の方法があります。

PBKDF2
- 広く普及している暗号化標準で、鍵強化(keystrengthening)に使用されている
- http://tools.ietf.org/html/rfc2898
Bcrypt
- アダプティブハッシュ(adaptivehashing)関数の実装
- http://bcrypt.sourceforge.net/

第20章-SQL Injection（SQLインジェクション）

動的なSQLを使用した際に、未検証の入力がコードとして実行されてしまうアンチパターンです。
動的なSQLとは、以下のようなSQLクエリを文字列として作成し、文字列内にアプリケーション変数を挿入する方法のことを言います。

<?php
$sql = "SELECT * FROM Bugs WHERE bug_id = $bug_id";
$stmt = $pdo->query($sql);

上の例（PHPの場合）では、\$bug_idの値が整数であり、データベースがクエリを受け付ける場合に、$bug_idの値がクエリの一部となります。
このように動的なSQLを利用することで、データベースのクエリの内容をアプリ側で変化させることができます。

デメリット：脆弱性が潜んでいる

そもそもSQLインジェクションとは、SQLクエリ文字列に動的に挿入された文字列が、開発者の意図していない方法でクエリ構文を変えられることで起こる攻撃のことです。

例えば、挿入された値によって、元のSQLに続けて別のSQL文も実行されることです。
変数$bug_idに「1234; DELETE FROM Bugs」が入ってくる場合、対策されていないと以下のように2つの文が実行されてしまい、Bugsテーブルが削除されてしまいます。

SELECT * FROM Bugs WHERE bug_id = 1234; 
DELETE FROM Bugs

2つ目の例として、以下のように、変数$project_nameにプロジェクトの名前を指定して、データを取得する場面があるとします。

<?php
$project_name = $_REQUEST["name"];
$sql = "SELECT * FROM Projects WHERE project_name = '$project_name'";

文字列は最初に検出された引用符文字で終了してしまうため、「O'Hara」などの「'」が使用されていると、構文エラーを引き起こしてしまいます。

3つ目の例として、例えば、ユーザーのパスワード変更の処理を以下のようなプログラムで行っているとします。

<?php
$password = $_REQUEST["password"];
$userid = $_REQUEST["userid"];
$sql = "UPDATE Accounts SET password_hash = SHA2('$password', 256)
  WHERE account_id = $userid";

攻撃者はリクエストパラメータがSQLステートメントでどのように使用されるかを推測して、悪用するための以下のような文字列を送信します。

http://bugs.example.com/setpass?password=xyzzy&usreid=123 OR TRUE

useridパラメータからSQL式へ挿入された文字列によって構文が以下のように改変されてしまいます。

UPDATE Accounts SET password_hash = SHA2('xyzzy', 256)
WHERE account_id = 123 OR TRUE;

結果として、データベースの全てのアカウントのパスワードが「xyzzy」に変更されてしまいます。

このようにSQLインジェクションは、SQL ステートメントが解析される前に構文を改変します。
そのため、SQLステートメントの解析前に挿入される動的な値を扱う限りは、SQLインジェクションで攻撃される恐れがあります。
SQLインジェクションに対しての様々な対策がありますが、それぞれデメリットが存在します。

①引用符のエスケープのデメリット

引用符文字の不一致に対しての対策としては、引用符のエスケープがあります。
データベースでの文字のエスケープには、バックスラッシュ（\）が用いられます。
以下のようにエスケープされると、構文エラーを回避することができます。

SELECT * FROM Projects WHERE project_name = '$O\Hara'";

これはSQL文字列内へ挿入する前に、アプリケーションがデータを変更する方法です。
PHPのPDO拡張モジュールではquote関数を用いて、入力文字列を引用符文字で囲みつつ、入力文字列内の全ての引用符文字リテラルをエスケープすることができます。

<?php
$project_name = $pdo->quote($_REQUEST["name"]);
$sql = "SELECT * FROM Projects WHERE project_name = '$project_name'";

しかし、 文字列以外のデータには引用符文字リテラルをエスケープを使用できません。
そのため、プリペアドステートメントが使用されます。

②プリペアドステートメントのデメリット

プリペアドステートメントとは、SQL文字列内に動的に値を挿入する代わりに、クエリの前処理時にパラメーターのプレースホルダーを文字列内に残します。
そして、準備したクエリを実行する前にパラメータとして値を渡します。

<?php
$stmt = $pdo->prepare("SELECT * FROM Projects WHERE project_name = ?");
$stmt->bindValue(1, $_REQUEST["name"], PDO::RAPAM_STR);
$stmt->execute();

このようにすることで、動的コンテンツのエスケープが不要となります。
ただし、プリペアドステートメントには以下のような欠点があります。

パラメータの値が常に1つのリテラル値と解釈される
テーブル識別子もパラメータとして扱えない
列名もパラメータとして扱えない
SQL予約後もパラメータにならない

「パラメータの値が常に1つのリテラル値と解釈される」

MySQLではカンマ区切りの最初の数字のみが文字列パラメータとして渡されたかのように処理されてしまうため、複数の整数値を渡したときと同じような振る舞いにはなりません。

<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id IN (?)");
$stmt = bindValue(1, "1234,3456,5678", PDO::PARAM_STR);
$stmt->execute();
// SELECT * FROM Bugs WHERE bug_id IN ('1234') と処理される

「テーブル識別子もパラメータとして扱えない」
この場合、テーブル名の代わりに文字列リテラルを入力したかのように処理されるため、構文エラーになります。

<?php
$stmt = $pdo->prepare("SELECT * FROM ? WHERE bug_id = 1234");
$stmt = bindValue(1, "Bugs", PDO::PARAM_STR);
$stmt->execute();
// SELECT * FROM 'Bugs' WHERE bug_id = 1234 と処理される

「列名もパラメータとして扱えない」
この場合、列が文字列定数と解釈されてその値はすべての行で同じであるため、ソートしても何も起こらなくなってしまいます。

<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs ORDER BY ?");
$stmt = bindValue(1, "date_reported", PDO::PARAM_STR);
$stmt->execute();
// SELECT * FROM Bugs BY 'date_reported'; と処理される

「SQL予約後もパラメータにならない」
パラメータはSQL予約語ではなく、リテラル文字列と解釈されるため、構文エラーとなってしまいます。

<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs ORDER BY date_reported ?");
$stmt = bindValue(1, "DESC", PDO::PARAM_STR);
$stmt->execute();
// SELECT * FROM Bugs ORDER BY date_reported 'DESC' と処理される

③ストアドプロシージャのデメリット

一般的なストアドプロシージャには、定義時に解析された静的なSQLステートメントが記述されます。
しかし、ストアドプロシージャにも安全性の低い動的なSQLを使用できてしまいます。
例えば、input_userid変数は動的にSQLクエリ内に挿入されます（安全でない）。

CREATE PROCEDURE UpdatePassword(input_password VARCHAR(20),
input_userid VARCHAR(20))
BEGIN
  SET @sql = CONCAT('UPDATE Accounts SET password_hash = SHA2(', QUOTE(input_password), ', 256)
    WHRER account_id = ', input_userid);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END

input_userid変数に危険な文字列を含めてしまうことができるため、次のような安全性の低いSQLステートメントになる可能性があります。

UPDATE Accounts SET password_hash = SHA2('xyzzy', 256)
WHERE account_id = 123 OR TRUE;

④データベースフレームワークのデメリット

データベースフレームワークやオブジェクトリレーショナルマッピング（ORM）フレームワークは、安全なSQLコードを保証するフレームワークがありません。
そのため、利用者がSQLステートメントを文字列として直接記述できるフレームワークは安全性が低いと言えます。
フレームワークは便利な機能を提供してくれますが、その機能を使わずに一般的な文字列操作でSQKステートメントを容易に構築できてしまいます。
その結果、リスクが生じてしまいます。

解決策：誰も信用しないこと

SQLコードの安全性を保証するための唯一絶対の方法は存在しません。
以下の方法を状況に応じて使い分ける必要があります。

①入力のフィルタリング

その入力によって無効な文字を全て取り除くようにします。
例えば、整数が必要な場合は、入力内容の整数で構成される部分のみを使用するようにします。
PHPの場合は、以下のようにfilter拡張を用います。

<?php
// 数字、プラス記号、マイナス記号 以外のすべての文字を取り除く
$bug_id = filter_input(INPUT_GET, "bug_id", FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM Bugs WHERE bug_id = $bug_id";
$stmt = $pdo->query($sql);

数値などの単純なケースでは、型キャストの関数intval関数を用いて以下のようにできます。

<?php
$bug_id = intval($_GET["bug_id"]);
$sql = "SELECT * FROM Bugs WHERE bug_id = $bug_id";
$stmt = $pdo->query($sql);

正規表現を使用して部分文字列を取得するフィルタリング方法もあります。

<?php
$sortorder = "date_reported";

if(preg_match("/([_[:alnum:]]+)/", GET["order"], $matches)) {
  $sortorder = $matches[1];
}

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

②動的値のパラメータ化

動的な部分がシンプルな値から構成されているときは、SQLから分離させるためにプリペアドステートメントを用います。

<?php
$sql = "UPDATE Accounts
  SET password_hash = SHA2(?, 256)
  WHERE account_id = ?";
$stmt = $pdo->prepare($sql);
$stmt = bindValue(1, $_REQUEST["password"], PDO::PARAM_STR);
$stmt = bindValue(2, intval($_REQUEST["userid"]), PDO::PARAM_INT);
$stmt->execute();

デメリットに記述したように、パラメータには単一の値しか用いることができません。
攻撃者が「123 OR TRUE」のようなパラメータを渡しても、RDBMSがSQLステートメントを解析した後に渡すため、以下のようにパラメータを値と解釈します。

UPDATE Accounts SET password_hash = SHA2(?, 256)
WHERE account_id = '123 OR TRUE';

アプリケーション変数をリテラル値としてSQL文字列と連結する必要がある場合、プリペアドステートメントを使用するべきです。

③動的値を引用符で囲む

SQLインジェクション対策にはほとんどの場合、プリペアドステートメントが最善策となります。
しかし、パラメータプレースホルダーを持つクエリのインデックスに対して、クエリオプティマイザーがおかしな判断をする場合があります。

例えば、Accountsテーブルのaccount_status列に「ACTIVE」か「BANNED」の2つの内どちらかの値が格納されているとします。そして、99%の行で「ACTIVE」が使用されているとします。
この場合、「account_status = 'ACTIVE'」を求めるクエリの場合、インデックスを読みに行くと非効率となってしまいます。

しかし、プリペアドステートメントで「account_status = ?」という式を使う場合、オプティマイザーは作成済みのクエリを実行する場合にどちらの値が来るか判断できません。
このため、不適切な最適化を行ってしまう場合があります。
このようなケースでは、SQLステートメント中に直接値を挿入したほうが良いです。

<?php
$quoted_status = $pdo->quote($_REQUEST["status"]);
$sql = "SELECT * FROM Accounts WHERE account_status = $quoted_status";
$stmt = $pdo->query($sql);

こうした複雑なケースでは、セキュリティを完全に理解するまで使用しないことです。

④ユーザーの入力をコードから隔離する

プリペアドステートメントやエスケープによる解決法は、SQL式内へのリテラル値の組み込みに対して効果的ですが、その他（テーブル識別子や列識別子、SQLを予約語など）には適用できません。

例えば、ユーザーがステータスや作成日などの条件でバグをソートする場合を考えます。

-- ステータスの昇順
SELECT * FROM Bugs ORDER BY status ASC;
-- 作成日の降順
SELECT * FROM Bugs ORDER BY date_reported DESC;

<?php
$sortorder = $_REQUEST["order"];
$direction = $_REQUEST["dir"];
$sql = "SELECT * FROM Bugs ORDER BY $sortorder $direction";
$stmt = $pdo->query($sql);

この例では「order」に列名、「dir」にASCかDESCが入ることが想定されています。
しかし、ユーザーはリクエストでどのようなパラメータの値でも送ることができるため、この方法は安全ではありません。

この解決法としては、リクエストパラメータの値を用いて定義済みの値を参照し、SQLでは定義済みの値を使う方法をとります。

$sortorders = array("status" => "status", "date" => "date_reported");
$directions = array("up" => "ASC", "down" => "DESC");

// ユーザーの入力値が配列内に存在しない場合のデフォルト値としての変数
$sortorder = "bug_id";
$direction = "ASC";

//ユーザーの入力値が宣言した配列キーと一致する場合、対応する値を使用
if (array_key_exists($_REQUEST["order"], $sortorders)) {
  $sortorder = $sortorders[$_REQUEST["order"]];
}
if (array_key_exists($_REQUEST["dir"], $directions)) {
  $direction = $directions[$_REQUEST["dir"]];
}

// 最後に取得
$sql = "SELECT * FROM Bugs ORDER BY $sortorder $direction";
$stmt = $pdo->query($sql);

この技法は以下のメリットが存在します。

ユーザーの入力内容とSQLクエリの連結が行なわれないため、SQLインジェクションのリスクが減る
SQLステートメントのどの部分でも使用可能
ユーザーの入力値の妥当性確認を簡単に行える
ユーザーインターフェースからデータベースクエリ内部を分離できる

⑤他の開発者にコードレビューをしてもらう

SQLインジェクション対策のコードレビューのガイドラインは、以下になります。

アプリケーション変数や文字列連結、文字列置換によって構築されているSQLステートメントを特定
SQLステートメントで使われている、全ての動的コンテンツの起点を辿り、外部ソースから来る全てのデータを特定（ユーザー入力、外部ファイル、環境周り、外部のウェブサービス、サードパーティのコード、データベースから取得した文字列など）
これらの外部コンテンツには全て潜在的なリスクがあると想定し、フィルター、バリデーター、マッピング配列などを用いて、これら信用度の低いコンテンツを変換
プリペアドステートメントまたはエスケープ関数を用いて、SQLステートメントと外部データを組み合わせる
他にもストアドプロシージャなどの動的SQLステートメントが隠れている可能性がある場所をチェック

第21章-Pseudokey Neat-Freak（疑似キー潔癖症）

物理削除などでIDに欠番が出てしまい、その欠番を埋めようアンチパターンです。

例：バグ管理のためのBugsテーブル（bug_id = 3が欠番）

bug_id	status	product_name
1	OPEN	OPEN RoundFile
2	FIXED	ReConsider
4	OPEN	ReConsider

デメリット

①欠番の値の特定が必要

欠番を割り当てるために、新しい行を挿入する時に欠番で最も小さい番号を割り当てる方法があります。
実行することで欠番が埋まっていきますが、
最も小さい欠番の値を特定するためには、以下のような独自のクエリを実行する必要が出てきます。

SELECT b1.bug_id + 1 AS max_bug_id
FROM Bugs b1
LEFT OUTER JOIN Bugs AS b2 ON b1.bug_id + 1 = b2.bug_id
WHERE b2.bug_id IS NULL
ORDER BY b1.bug_id LIMIT 1;

②都度更新しなければならない

すぐに欠番を埋めるために、全ての値が連続するように既存行のキー値を更新する方法もあります。
今回の例の場合だと、以下のクエリを実行します。

UPDATE Bugs SET bug_id = 3 WHERE bug_id = 4;

しかし、こちらも欠番を割り当てる方法と同様に欠番の値を特定して、主キーの値を更新する必要があります。
そのため、競合状態を引き起こす可能性が生じてきます。
加えて、外部キー参照が設定されていてカスケード更新を宣言していなかった場合、手作業で子レコードの更新を行う必要があるため、手間がかかってしまいます。
さらに擬似キージェネレーターが作成するのは、テーブルの最大値ではなく 「最後に作成した値より1つ大きな値（単調増加）」 のため、番号を振り直して欠番を一時的に埋めても、新たに行が挿入された場合に、欠番が出てきてしまいます。

③データの不一致の元になる

欠番は正当な理由による行の削除やロールバックの結果であるため、主キー値を再割り当てを行ってしまうと問題が生じる可能性があります。
例えば、有害なメールを他の全てのユーザー宛に送信したことでアカウント削除されたID = 700のユーザーがいるとします。
そこに新たなユーザーで欠番を埋めてしまうと、新たなユーザーが有害なメールを送信したID = 700のユーザーと認識されてしまう可能性があります。

解決策：疑似キーの欠番は埋めない

主キーの値は、一意で非NULLの値でなければなりません。
しかし、ルールはそれだけで連続する必要はありません。

①行のナンバリング

疑似キージェネレーターによって生成された値は単調増加するため、行番号と同等に見えますが、それぞれ以下の役割があり異なります。

主キー：一意に特定するための値
行番号：結果セットのにおける行の順序番号

クエリの結果セット内の行番号は、主キー値と必ずしも一致するわけではありません。
（特にJOINやGROUP BY、ORDER BYなどを用いたクエリで起こります。）

また、行番号はページネーションなどの行のサブセットを取得などで使用します。
サブセットを選択するにはクエリの形式に関わらず、単調増加で連続した行番号を使用する必要があります。
SQL:2003では、ROW_NUMBER関数（クエリの結果セットの固有の連番を返す関数）が定義されており、以下のようにクエリの結果を一定範囲の行に限定する場合に使用します。

SELECT t1.* FROM
  (SELECT a.account_name, b.bug_id, b.summary,
    RAW_NUMBER() OVER (ORDER BY a.account_name, b.date_reported) AS rn
  FROM Accounts a INNER JOIN Bugs b ON a.account_id = b.reported_by) AS t1
WHERE t1.rn BETWEEN 51 AND 100;

②グローバル一意識別子（Globally Unique IDentifier:GUID）の使用

GUIDは128ビットの擬似乱数で、同じ数を複数回使用しないために、ランダムな疑似キーを生成します。
（同じ機別子が生成される可能性が極めて低いので、事実上一意な値と見なされる）
Microsoft SQL Server2005でのGUIDの生成方法は以下になります。

CREATE TABLE Bugs (
  bug_id UNIQUEIDENTIFIER DEFAULT NEWID(),
  --以下に他の列を定義
);

GUIDのメリット・デメリットは以下になります。

メリット
- 複数のデータベースサーバー間で重複した値を生成することなく、並行して疑似キーを生成できる
- 値がランダムになるため、欠番に関して気にならなくなる
デメリット
- 値が長いため、タイプしづらい
- 値がランダムなため、パターンの推測や大小からの順番の推測ができなくなる
- よりも多くのスペースが必要で、実行時間も長くなる
- GUIDの格納には16バイト必要
- 一般的な疑似キーは4バイト整数

③「疑似キーの欠番をうめろ」の断り方

技術による説明
- 欠番がある良い感じはしませんが、欠番は無害です
- 行のスキップやロールバック、削除ができることは以上ではありません
- 新しい番号を割り当てることで、処理が高速になりエラーも削減できます
コストの見積もりの提示
- 新しい値の計算、重複値を処理するためのコードの記述とテスト
- データベース全体への変更の反映
- 他のシステムへの影響調査
- ユーザーや管理者への新しい手順の説明
自然キー（ナチュラルキー）の使用
- 疑似キーを導入せずに識別しやすい意味を表す文字列や数値を使用する

第22章-See No Evil（臭いものに蓋）

簡潔なコードを書くことを優先してしまい、データベースAPIの戻り値を無視したり、アプリケーションコード内に点在するSQLしか読まなかったりするアンチパターンです。

デメリット

①ユーザー側の画面にはブランクページや例外メッセージが表示されてしまう

以下のようなデータベースから特定の値を取得する処理があるとします。

<?php
$pdo = new PDO("mysql:dbname=test;host=db.example.com",
"dbuser", "dbpassword"); // 処理①
$sql = "SELECT bug_id, summary, date_reported FROM Bugs
  WHERE assigned_to = ? AND status = ?";
$stmt = $pdo->prepare($sql); // 処理②
$stmt->execute(array(1, "OPEN")); // 処理③
$bug = $stmt->fetch(); // 処理④

それぞれの箇所で生じるエラーについてまとめると、以下になります。

処理	どういう時にエラーが起こるか	例	エラー時の処理
例①	データベース接続時	・データベース名やサーバーのホスト名、ユーザー名、パスワードなどの間違え・データベースサーバーのネットワーク障害	PDOコネクションのインスタンス化を行う際に問題があると例外が検出されて、前述したサンプルスクリプトは終了する
例②	単純な構文エラー	・タイプミス・括弧の不一致・列名のスペルミスなど	prepareメソッドが呼び出し時にfalseが返される ③executeメソッドで以下の致命的エラーが発生「PHP Fatal error: Call to a member function execute() on a non-object」
例③	executeメソッドの呼び出し失敗	・SQLステートメントが制約に違反・SQLステートメントがアクセス権限に違反	executeメソッドの呼び出し時にfalseを返す
例④	fetchメソッドの呼び出し失敗	・データベースの接続失敗	fetchメソッドの呼び出し時にfalseを返す

PHPで致命的エラーが生じると、ユーザー側の画面にはブランクページや例外メッセージが表示されてしまいます。

②バグ発生時に解決に時間がかかってしまう

特定のバグを取得する条件付きクエリを記述するとします。

<?php
$sql = "SELECT * FROM Bugs";
if ($bug_id) {
  $sql .= "WHERE bug_id = " . intval($bug_id);
}
$stmt = $pdo->prepare($sql);

実行すると、エラーになってしまいました。
「Bugs」と「WHERE」の間のスペースがないことは、連結後の$sql文字列を確認すると、すぐに判明します。

SELECT * FROM BugsWHERE bug_id = 1234

しかし、構築されたSQLそのものを調べていると、余計に時間がかかってしまいます。

解決策：エラーから優雅に回復する

重要なのは、いかにミスから回復するかです。
そのためには、ミスの原因を気づきやすくする工夫を行うべきです。

①データベースAPI呼び出しの戻り値と例外のチェック

エラーを起こす可能性があるメソッド呼び出し後に、ステータスをチェックするコードを記述するようにします。

<?php
try {
  $pdo = new PDO("mysql:dbname=test;host=localhost",
"dbuser", "dbpassword");
} catch (PDOException $e) {
  report_error($e->getMessage());
  return;
}

$sql = "SELECT bug_id, summary, date_reported FROM Bugs
  WHERE assigned_to = ? AND status = ?";

if ($stmt->execute(array(1, "OPEN")) === false) {
  $error = $pdo->errorInfo();
  report_error($error[2]);
  return;
}

if (($bug = $stmt->fetch()) === false) {
  $error = $pdo->errorInfo();
  report_error($error[2]);
  return;
}

②ステップをたどり直す

デバックにはSQLクエリを構築するコードだけでなく、実際に構築されたSQLクエリを使用することも重要です。

データベースAPIの引数の渡す時に直接SQLを組み立てず、はじめに一時変数を使用してSQLクエリを構築してからAPIに渡すようにする
ログファイルやデバックコンソールなどでアプリケーションとは別の出力先にSQLを出力するようにする
アプリケーション出力のHTMLコメント内にSQLクエリを表示させないようにする

第23章-Diplomatic Immunity（外交特権）

SQLのベストプラクティスを実施しないために、技術的な負債を抱えてしまうアンチパターンです。

ベストプラクティスには、不要な仕事や繰り返し作業を減らすメリットが存在し、怠るとプロジェクトの失敗に向かってしまいます。
SQLの他に、以下のようなソフトウェアエンジニアリングのベストプラクティスがあります。

SubversionやGitなどによる、ソースコードのバージョン管理
ユニットテストや機能テストの自動化や実行
ドキュメント、仕様書、コードコメントを書き、アプリケーションの要件や実相戦略を記録

開発者は「アプリケーション開発のルールは、データベース開発には当てはまらない」とSQLを特別扱いする傾向があります。
そのように考える主な原因としては、以下の4つが挙げられます。

ソフトウェアエンジニアとデータベース管理者の役割が区別されているため
- データベース管理者はいくつかの開発チームを掛け持ちすることがあり、「お客さん」のように扱われてしまう
- 「他のメンバーと同じようなソフトウェアエンジニアの責任に従わなくてよい」という暗黙の了解が生まれてしまっている
SQL言語は従来型のプログラミング言語と性質が異なるため
- SQL言語がアプリケーションコード内で特殊な言語として扱われるため、データベース管理者が「お客さん」のように扱われてしまう
データベース開発向けの高度なIDEツールがないため
- アプリケーションコードを記述するときに使える高度なIDEツールはソースコードの編集、テスティング、バージョン管理を素早く行うことができる
- アプリケーションコードと勝手が違うため、ベストプラクティスをうまく適用できない
データベースに関する知識や運用権限が1人のデータベース管理者に集中してしまう傾向にあるため
- データベース管理者が、データベース・サーバーへのアクセス権限を唯一持っている

解決策：包括的に品質管理に取り組む

ソフトウェア開発者の多くが行っているテストは 品質管理（Quality Control:QC） に過ぎず、一部でしかありません。
ソフトウェアエンジニアリングのライフサイクルは、 品質保証（Quality Assurance:QA） を伴い、これらは以下の3つで構成されます。

プロジェクト要件の明確な定義・文書化
要件に対する解決策の設計・構築
解決策が要件を満たしていることの確認・テスト

適切な品質保証のためには、これら3つを全て行う必要があります（順番は自由）。
データベース開発における品質保証は 「①文書化」 、 「②バージョン管理」 、 「③テスティング」 です。

①文書化

コードの分析・調査を行うことでコードの意味を理解することはできますが、かなりの労力が必要です。
そのため、データベースの要件と実装はアプリケーションコードと同じように文書化すべきです。

データベース文書化のチェックリスト

項目	文書化したほうが良い情報	備考
ER図	・テーブルとその関連（リレーションシップ）を表す図・詳細に描くためには、「列」、「キー」、「インデックス」なども記述する	非常に多くのテーブルを持つ複雑なデータベースの場合、複数のER図に分割すること
テーブル、列、ビュー	・テーブル：説明する対象は「参照テーブル」、「交差テーブル」、「従属テーブル」各テーブルで想定している行数や、テーブルに対して実行されるクエリ、テーブルを構築するインデックスも記述する・列：名前やデータ型と「その列にとって妥当な値は何か」を記述する・ビュー：テーブル間の複雑な関連（リレーションシップ）を要約する意図があるか、特権アクセスのないユーザーが特権的なテーブルの行や列のサブセットを参照できるようにするか、ビューは更新可能かなどを記述	・列：定量的な値を格納する列で用いる単位、NULLの許容するか否か、一意性制約があるかも記述する・ビュー：1つ以上のテーブルに対して頻繁に使われるクエリが格納されている
関連（リレーションシップ）		参照整合性制約はテーブルの依存関係を実現しているが、制約のモデリングについて開発者の意図を全て表しているとは限らないため、この項目も必要
トリガー	トリガーに実装しているビジネスルールなどを記述する	データのバリデーションや変換、データベース変更のロギングなどが役割
ストアドプロシージャ	「どのような問題を解決するものか」、「データに対する変更を行うか」、「入出力パラメータのデータ型と意味」、「パフォーマンスボトルネックを回避するために特定のクエリを置換することを意図しているか」、「特権テーブルの特権アクセスのないユーザーのアクセスを許可することを意図しているか」などを記述	APIの文書化と同様
SQLセキュリティ	データベースユーザーの定義、各ユーザーのアクセス権、提供するロールと対象ユーザー、バックアップやレポートなどの特定のタスクの実行を許可されているユーザーの有無、システムレベルのセキュリティ対策、不正アクセスの検出・防御のための処置、SQLインジェクション脆弱性対策のためのコードレビューの有無などを記述
データベースインフラストラクチャ	データベースの種類とバージョン、データベースサーバーのホスト名、データベースサーバーの冗長化（レプリケーション、クラスタリング、プロキシなど）、データベースサーバーで使用するネットワーク構成とポート番号、クライアントアプリケーションが使用すべき接続オプション、データベースユーザーのパスワード、データベースのバックアップポリシーなどを記述	主にインフラ技術者とデータベース管理者のためのもの
オブジェクトリレーショナルマッピング（ORM）	アプリケーションコードに実装されるビジネスルール、データの妥当性確認、データ変換、ロギング、キャシュの取り扱い、プロファイルの取り方などを記述	ORMライブラリを使用したクラス群を通じて、データベース操作のロジックを実装する可能性があるため

②バージョン管理

アプリケーションコードと同様に、データベースを扱うコードに対してもバージョン管理を用いるべきです。

データベースサーバーが故障によって駄目になってしまった場合、どのようにデータベースを再構築するのか
変更を取りやめたい場合、どのように元に戻すのか

などの問題が起こっても、バージョン管理を導入することで解決することができます。

以下のようなデータベース開発関連のファイルを、バージョン管理下に入れておく必要があります。

項目	説明
データ定義スクリプト	「CREATE TABLE」などのステートメントでデータベースオブジェクトを定義するSQLスクリプト群
トリガーとプロシージャ	データベースに格納したトリガーやプロシージャでアプリケーションコードを補完していることが多いため
ブートストラップデータ	シードデータ
ER図とドキュメント	コードではないが、コードと密接に結びついているため
データベース管理スクリプト	アプリケーション外部で実行されるデータ処理のジョブ。ジョブにはインポート/エクスポート、同期、レポート、バックアップ、バリデーション、テスティングなどのタスクが含まれ、SQLスクリプトとして書かれる場合がある

③テスティング（QC）

テスティングの重要な原則の1つは、独立(isolation)です。
テストごとにシステムの１部分のみを検証することで、欠陥が存在する場合に可能な限り正確に、欠陥の箇所を絞り込むことができます。
また、アイソレーションテストはデータベースにも適用でき、データベースの構造と振る舞いの妥当性確認をアプリケーションコードとは独立させて行うことができます。

データベースの妥当性を検証するテストのためのチェックリスト

項目	テストで確認すること
テーブル、列、ビュー	・データベースに存在するべきテーブルやビューなどが実際に存在することを確認・データベースを拡張する場合はオブジェクトが存在していることを確認・削除したテーブルや列が存在しないことを確認（否定テスト）
制約	・制約に違反するようなステートメントを実行し、エラーになることを確認（NOT NULL制約、一意性制約、外部キー制約）
トリガー	・トリガーが発動するステートメントを実行し、トリガーが意図した処理を行ったかを確認
ストアドプロシージャ	・入力およびデータの条件に応じた処理の確認
ブートストラップデータ	・初期データ確認のクエリを実行して確認
クエリ	・クエリを実行し、結果に適切な列名とデータ型が含まれていることを確認
ORMを使用したクラス	・バリデーション、変換モニタリングなどのテストを行い、これらのクラスが入力に対して予期された振る舞いをすることや無効な入力を拒絶することを確認

第24章-Magic Beans（魔法の豆）

モデル・ビュー・コントローラー（MVC）アーキテクチャで、モデルを過度に単純化するアンチパターンです。
MVCアーキテクチャの役割と関係性は以下のようになっています。

それぞれの役割

モデル
- 「ビュー」と「コントローラー」以外の全てを担う
- 入り口のバリデーション、ビジネスロジック、データベースとのやり取りなども含む
ビュー
- ユーザーインターフェースに情報を表示
コントローラー
- ユーザーの入力を受け入れ、入力に対してアプリケーションが何を返すかを定義し、関連するモデルに仕事を委譲し、結果をビューに送る
- 「ユーザー」と「モデル」と「ビュー」の橋渡し役

関係性

シンプルなアプリケーションでは、モデルに多くのカスタムロジックは不要となります。
モデルオブジェクトのフィールドをテーブルの列と対応づけて、オブジェクトに必要なCRUD操作（作成、読み込み、更新、削除）で行うようにすることです（「 アクティブレコード 」と呼ばれるデザインパターンの一種です）。

まず、データベースのテーブルやビューに対応するクラスを定義します。
クラスメソッドfindを呼び出すと、そのテーブルやビューに対応したインスタンスが返されます。
また、クラスのコンストラクタを用いて新しい行を作成できます。
オブジェクトのsaveメソッドを呼ぶ事で、行の挿入や既存行の更新も行えます。

<?php
$bugsTable = Doctrine_Core::getTable('Bugs');
$bugsTable->find(1234);

$bug = new Bug();
$bug->summary = '保存時にクラッシュが発生';
$bug->save();

アクティブレコードは単一テーブルの各行に対するシンプルなインターフェースを提供してくれるデザインパターンで、
現在は多くのWeb開発フレームワークがアクティブレコードを事実上のデータアクセスオブジェクト（DAO）設計として採用しています。
問題となるのは、MVCアプリケーションの全てのモデルクラスがアクティブレコードの基底クラスを継承してしまう（モデルがアクティブレコードそのものになってしまう）ことです。

デメリット

①モデルをデータベーススキーマに強く依存させてしまう

アクティブレコードはクラスは1つのテーブルもしくはビューを表現するため、テーブルやビューの数の分、モデルの定義が必要なります。
よって、新たなデータ構造を表す場合は、モデルクラスだけでなく、そのモデルクラスを扱うアプリケーションのコードを変更する必要があります。
また、アプリケーションで新たな画面を処理するコントローラーを加える場合、モデルとのやり取りを行うために既存のコードによく似たコードをコントローラーに描く必要が出てきます。

②CRUD機能を公開してしまう

意図した用法を無視して、CRUD操作メソッドを通じて直接データの更新を行ってしまう可能性があります。
例えば、Bugテーブルのassigned_toが登録されたら電子メールを送る。というビジネスロジックを用意していたとします。

<?php
class CustomBugs extends BaseBugs
{
  public function assignUser(Accounts $a)
  {
    $this->assigned_to = $a->account_id;
    $this->save();
    mail($a->email, "バグ担当に任命されました",
      "あなたはバグ #{$this->bug_id} の修正担当に任命されました。");
  }
}

しかしこのメソッドを迂回して、電子メールを送信せずにバグ担当者を割り当てるコードを記述してしまう可能性があります。
もしも、機能要件に電子メールの送信処理が入っていた場合、要件を満たさないことになってしまいます。

<?php
$bugsTable = Doctrine_Core::getTable('Bugs');
$bugsTable->find(1234);
$bug->assigned_to = $user->account_id;
$bug->save();

③ドメインモデル貧血症をもたらす

モデルが基本的なCRUD以外の振舞いを持たないという問題が生じてしまいます。
開発者の多くは、モデルがすべき仕事を表現する新たなメソッドを追加せずにアクティブレコードの基底クラスを単に継承します。
モデルをシンプルなデータアクセスオブジェクトとして扱うと、モデル外部でビジネスロジックのコーディンが必要になってしまいます。
結果として、複数のコントローラーにロジックが書かれてしまい、モデルの振舞いの凝集度が低下してしまいます（ドメインモデル貧血症）。

④MVCの各レイヤのユニットテストが困難

アーキテクチャ	テストが困難な理由
モデル	モデルをアクティブレコードと同じクラスにしているため、データベースアクセスから分離してテストを行うことができないモデルをテストするために、本物のデータベースに対してクエリを実行しなければならない
ビュー	ビューをHTMLとしてレンダリングした結果を解析し、モデルが関係する動的なHTML要素が出力されていることをテストする特定の解析のために、フレームワークは複雑で時間のかかるコードを実行する必要がある
コントローラー	複数のコントローラーにおけるコードの重複したものも含めて、全てテストする必要がある。テストの為に偽のHTTPリクエストを用意するなどの準備が必要になる

解決策：Modelがアクティブレコードを[持つ]ようにする

①モデルを理解する

モデルをデータアクセスオブジェクトから分離する際の設計判断において、以下のガイドラインを参考にします。

情報エキスパート
- アクティブレコードのようなDAOとモデルの関係は、is-a（継承）ではなく、has-a（集約）であるべきです。
- ほとんどのフレームワークで、is-a型を想定していますが、モデルがDAOクラスからの継承ではなくDAOを使う関係にある場合、対象となるドメインの全てのデータとコードを含むようなモデルを設計できます。
- また、モデルはドメインを表現するために、複数テーブルを参照することもできます。
生成者
- モデルがデータベース内のデータは、外部に公開しないようにします。
- DAOを集約するドメインモデルが、これらのデータオブジェクトを生成する責任を持つべきです。
- コントローラーとビューはドメインモデルのインターフェースを使用するべきです。
- このように設計することで、1箇所の変更だけで、後からでもデータベースクエリの変更を容易に行えるようにできます。
疎結合性
- 論理的に独立しているコードは、分離して疎結合化を行うことが重要です。
- コードの利用者に影響を与えずに、クラスの実装を柔軟に変更できるようにします。
高凝集性
- ドメインモデルクラスのインターフェースは、物理的なデータベース構造やCRUD操作ではなく、意図を示すべきです。
- アクティブレコードの一般的なメソッドのfind、first、insert、saveなどは、どのようにしてアプリケーションの要件を満たせばよいのかが伝わりません。
- assignUserみたいにすることで、コントローラーのコードも理解しやすくできます。
- モデルクラスをモデルが使用するDAOから分離すると、同じDAOを使う複数のモデルクラスを設計できるようになります。

②ドメインモデルの使用

MVC本来の意味合いにおけるモデルとは、ビジネスロジックを実装する場所のことを示します。
データベースとのやりとりは、モデルの内部的な実装の詳細なのです。
アプリケーションの概念に基づいてモデルを設計することで、データベースの操作をモデルクラスに完全に隠蔽して実装できるようになります（カプセル化）。

③プレーンなオブジェクトのテスト

理想は、本物のデータベースに接続することなくモデルをテストできることです。
モデルとコントローラの切り離し、モデルとデータアクセス機能の分離すると、全てのクラスのユニットテストをしっかりと分離した上でシンプルに実行できるようになります。

第25章-Sandcastle（砂の城）

天災やアクセス過多などによって起こりうる影響や事故に関して、事前に対策を行なっていないアンチパターンです。

サービスを安定稼働させるためには、トラブルは当然起きるものとして想定しておく必要があります。
想定するだけでは不十分で、実際にトラブルが起こった時にどのような対応を取るかの検討も必要になってきます。

解決策：どのようなトラブルが起こりうるかを可能な限り想定しておく

サービスの運用を始めるにあたって実施・想定しておくべき代表的な対策を紹介します。

①ベンチマーク

大きなトラフィックが予想されるシステムでは、事前にどの程度まで処理が可能をベンチマークしておいた方が良いです。
運用を続けていくうちにトラフィックやデータ量の増大によって、性能の問題に突き当たるシステムが多く存在します。
重要なことは限界が来る前に対策をとることです。
ベンチマークによって、どの程度のトラフィックやデータ量まで耐えられるのかを知っておくことで事前に対策が立てられます。
ベンチマークするにあたって重要な点が、以下の3つです。

現実に即したシナリオを用いる
- どの程度の負荷まで耐えられるかを調べるために行うので、現実に即したシナリオでないと意味のある数字が出ないため
データサイズを実際のアプリケーションで用いられるものと同程度にする
- データが大きくなると性能が急激に低下するケースがあるため
実際のシステムと同じハードウェアやOSを使う
- ベンチマークの結果は使用するシステムによって大きく変わってしまうため
- CPUの動作周波数が1.5倍異なると、ベンチマークも1.5倍異なるとはならない

②テスト環境の構築

テスト環境を用意しておくことでデバックを楽に行うことができます。
特に問題の切り分け（アプリケーションの問題かミドルウェアやデータベースの問題か）や、本番環境では採取できない詳細なデータの取得、本番環境に対する更新作業のリハーサルなどの用途には必須です。
テスト環境のシステムは、本番環境で利用しているものと同じものを1セット用意するのが理想です（ベンチマークのテストにも使用できるようになります）。

③例外処理

データベース管理システムを用いたアプリでは、適切な例外処理を実装することが必須となります。
トランザクション実行中にはどのようなエラーが発生するのか知っておく必要があります。

トランザクションのデッドロックやロック待ちのタイムアウトエラー
- トランザクションをリトライする例外処理が必要
データベースサーバーへの接続が切れた場合の対処や、データベース製品固有の一時的なエラーの対処
- 製品固有のエラーはその製品に精通している必要がある
構文エラーのように明らかなバグやSQLインジェクションに対する脆弱性の可能性を示すような致命的エラーが発生
- 即座に関係者へ連絡が行くような仕組みを作るべき

④バックアップ

サービスの最後の生命線です。
ディスクの冗長化は、データが論理的に破壊されてしまうようなケースに対応できていないため、バックアップになりません。
バックアップの運用は万全に行う必要があります。

⑤高可用性

どれだけ高価なマシンを使おうと、マシンそのものの故障から完全に逃れることはできません。
停止時間をできる限り短くするためには、マシンを冗長化する仕組みを考えておく必要があります。
データベース製品によってはアクティブ/アクティブ構成やレプリケーションをサポートしているものもあるため、そういった機能の活用も重要となってきます。
最終的には、コストと停止時間を天秤にかけて、最適な構成を選択する必要があります。

『SQLアンチパターン』まとめその4（19〜25章）

目次

第19章-Readable Passwords（読み取り可能パスワード）

デメリット：重大なセキュリティ欠陥が存在する

①パスワードの格納時のセキュリティ欠陥

②パスワードの認証時のセキュリティ欠陥

③2つの条件をひとまとめにしてしまい、対策ができない

④パスワードを電子メールで送信する

解決策：ソルトを付けてパスワードハッシュを格納する

①ハッシュ関数の使用

②ハッシュにソルトを加える

③SQLからパスワードを隠す

④パスワードをリカバリーするのではなく、リセットする

第20章-SQL Injection（SQLインジェクション）

デメリット：脆弱性が潜んでいる

①引用符のエスケープのデメリット

②プリペアドステートメントのデメリット

③ストアドプロシージャのデメリット

④データベースフレームワークのデメリット

解決策：誰も信用しないこと

①入力のフィルタリング

②動的値のパラメータ化

③動的値を引用符で囲む

④ユーザーの入力をコードから隔離する

⑤他の開発者にコードレビューをしてもらう

第21章-Pseudokey Neat-Freak（疑似キー潔癖症）

デメリット

①欠番の値の特定が必要

②都度更新しなければならない

③データの不一致の元になる

解決策：疑似キーの欠番は埋めない

①行のナンバリング

②グローバル一意識別子（Globally Unique IDentifier:GUID）の使用

③「疑似キーの欠番をうめろ」の断り方

第22章-See No Evil（臭いものに蓋）

デメリット

①ユーザー側の画面にはブランクページや例外メッセージが表示されてしまう

②バグ発生時に解決に時間がかかってしまう

解決策：エラーから優雅に回復する

①データベースAPI呼び出しの戻り値と例外のチェック

②ステップをたどり直す

第23章-Diplomatic Immunity（外交特権）

解決策：包括的に品質管理に取り組む

①文書化

②バージョン管理

③テスティング（QC）

第24章-Magic Beans（魔法の豆）

デメリット

①モデルをデータベーススキーマに強く依存させてしまう

②CRUD機能を公開してしまう

③ドメインモデル貧血症をもたらす

④MVCの各レイヤのユニットテストが困難

解決策：Modelがアクティブレコードを[持つ]ようにする

①モデルを理解する

②ドメインモデルの使用

③プレーンなオブジェクトのテスト

第25章-Sandcastle（砂の城）

解決策：どのようなトラブルが起こりうるかを可能な限り想定しておく

①ベンチマーク

②テスト環境の構築

③例外処理

④バックアップ

⑤高可用性