SQL
SQLアンチパターン

リーダブルパスワード

More than 3 years have passed since last update.


「いますぐパスワードが必要なんです。以前務めていた会社では、サポートスタッフはパスワードを忘れたらすぐに調べてくれました。なぜ口頭で教えてもらえないのでしょうか? どうしても駄目というのなら、話をするのであなたの上司につないでください」

「彼? 営業部門には Pat Johnson という人がいるけど、その人は女性だよ。騙されたんじゃない?」


だめだめ


目的:パスワードのリカバリーとリセットを行う

最近のアプリケーションの多くは、電子メールを通じてパスワードの再通知やリセットを行うことができます。再通知やリセットは、アプリケーションのユーザープロファイルに関連付けられた電子メールアドレスを、パスワードを忘れたユーザーが使用できることを前提としています。


アンチパターン:パスワードを平文で格納する


この種のパスワードリカバリー手法でよくある間違いは、平文のパスワードが含まれた電子メールをユーザーがリクエストできるようにしてしまうことです。


↑のリスクを以下で紹介していきますよ。


パスワードの格納

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');

パスワードを平文で保存。

だめだめ

パスワードを盗むチャンスの例


  • アプリケーションクライアントからデータベースサーバーに送信された SQL 文のネットワークパケットを傍受することは、見かけほど難しいことではありません。傍受を可能にする Wireshark などのフリーソフトウェアもあります。


    • か○こさんとかよくやってます



  • データベースサーバー上の SQL クエリログを探す方法もあります。データベースサーバーに侵入した攻撃者によって、データベースが実行した SQL文の記録を含むログファイルにアクセスされてしまう可能性があります。

  • データベースのバックアップファイル、またはバックアップメディアからもデータを読み取れます。バックアップメディアが盗まれたり、バックアップメディアのリサイクルや破棄の前にデータを完全に消去していないと、パスワードが盗まれてしまうことがあります。


パスワードの認証

パスワードが平文で格納されていると、ログインの度に以下のようなSQLでパスワードが流れてしまう。

SELECT CASE WHEN password = 'opensesame' THEN 1 ELSE 0 END AS password_matches

FROM Accounts
WHERE account_id = 123;


2つの条件をひとまとめにしない

SELECT * FROM Accounts

WHERE account_name = 'bill' AND password = 'opensesame';

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

同一アカウントに対して複数回認証が失敗したら、一時的にロックするなどの対応をとれない。

CASE文とか使うのがいい。

ただし、ログインエラーメッセージとして「ユーザーが存在しない」のか「ユーザーは存在するが、パスワードが 間違っている」のかは表示すべきではありません。攻撃者にヒントを与えてしまうからです。


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

パスワードを再通知する電子メールの例


From: daemon

To: bill@example.com

Subject: パスワードのリクエスト

アカウント「bill」のパスワードの再通知リクエストに回答します。 パスワードは「xyzzy」です。

アカウントにログインするには、以下のリンクをクリックしてください。

http://www.example.com/login


平文のパスワードを電子メールで送信するのは、非常に深刻なセキュリティリスクです。攻撃者は様々な方法で、電子メールの傍受、記録、保存を行えます。メールを取得する際にセキュアなプロトコルを使用したり、メールの送受信サーバーが信頼できるシステム管理者によって管理されていても、十分な対策にはなりません。電子メールはインターネットの様々なサーバーを経由するため、途中で傍受される可能性があります。電子メール向けのセキュアなプロトコルも、誰もが使用しているとは限りませんし、開発者が完全にコントロールできるものでもありません。

メール受信側が常にセキュアなクライアントを使用しているとは限らないので、サービス側としてはメールは傍受可能なものとして設計するべき。

http://tyoro-ge.hatenablog.com/entry/email-plaintext-password

https://cleartext.azurewebsites.net/


アンチパターンのみつけ方

パスワードのリカバリーを行ってユーザーへ送信できるアプリケーションは、パスワードを平文または復元可能な暗号化によって格納しています。それこそが「リーダブルパスワード(読み取り可能パスワード)」 アンチパターンです。アプリケーションが正当な目的でパスワードを読み取れるということは、攻撃者が不当にパスワードを読み取れることも意味するのです。

パスワード忘れたから教えて!といわれて答えられるような状態ならOUT!


アンチパターンを用いてもよい場合

社内のみで使用するアプリケーションの場合は、セキュリティにそこまでコストをかたくない場合もある。

しかしながら、そこがセキュリティホールになる可能性が今後の改修によって発生しかねないので慎重に検討する。

小さなイントラネットアプリケーションを社内のファイヤーウォールの外でも使えるようにする前に、優秀なセキュリティ専門家(CERT)による評価を行うべき。


  • 本人識別(Identification)


    • 自分が誰であるかを申告すること



  • 認証(Authentication)


    • 自らが名乗った人物であることを証明することです。パスワードは、認証を行う最も一般的な方法です。

    • パスワードが漏れると、本人識別しかできていない状態になる。なりすまし可能。



  • 認可(Authorization)


    • 認証済みの利用者に対して、何らかのサービスの利用やリソースへのアクセスなどに対する権限を与えたりすることを指します。



認証・認可

http://www.itmedia.co.jp/enterprise/articles/0804/22/news044.html


解決策:ソルトを付けてパスワードハッシュを格納する


  • 不可逆暗号化で格納しても問題ない


    • 暗号化の文字列でWHERE句にかく



  • 暗号化されていても、脆弱な暗号化方式を使っているとセキュリティレベルが低くなるよ


ハッシュ関数を理解する


  • SHA-1, MD5暗号化強度が十分ではない

  • SHA-256とか使う

SHA2('xyzzy', 256) = '184858a00fd7971f810848266ebcecee5e8b69972c5ffaed622f5ee078671aed'


SQL でのハッシュの使用

SHA-256を用いると64文字固定長になる。なのでパスワードの長さが漏洩することもない。

CREATE TABLE Accounts (

account_id SERIAL PRIMARY KEY, account_name VARCHAR(20),
email VARCHAR(100) NOT NULL, password_hash CHAR(64) NOT NULL
);

SSL サポートを有効にした MySQL では、SHA2 関数が使えます(大体使えません)。

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

SELECT CASE WHEN password_hash = SHA2('xyzzy', 256) THEN 1 ELSE 0 END AS password_matches
FROM Accounts
WHERE account_id = 123;


パスワードハッシュの値をハッシュ関数が返せない文字列に変更することで、アカウントを簡単に ロックできます。例えば、文字列 noaccess は 16 進数ではない値を含んでいます。


別にlock用のfieldをもつ方がいい。lockされたアカウントのリストを取得するときとか。


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

同じパスワードが同じハッシュ値になると脆弱になる。


サイバー犯罪者、一般的に使用されているパスワードについて、ハッシュテーブルを事前に計算しておき (また、十分なディスク容量があれば、特定の長さのすべてのパスワードについてハッシュを計算し)、リストにあるパスワードであれば1 回のデータベースルックアップでクラックできてしまうのです。


http://www.sophos.com/ja-jp/press-office/press-releases/2013/11/ns-serious-security-how-to-store-your-users-passwords-safely.aspx

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;


ソルトと呼ばれる理由は、ハッシュ出力を「味付けする」ものだからです。

ソルトは、nonce とも呼ばれる場合があります。nonce とは、「number used once (一度だけ使用される数)」という意味です。

簡単に言えば、実際のパスワードと一緒にハッシュ計算するランダムなバイト文字列を生成します。


SHA2('password', 256)

= '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8'
SHA2('password' || 'G0y6cf3$.ydLVkx4I/50', 256)
= '9cb669bbba0bfd55189f7b58c1d85014ec4438e815e2993847a289bb41c46de8'

同じ文字列でも異なるハッシュになる。

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;

レインボーテーブル対策を行うには、ソルトとパスワードをあわせた長さが最低でも20文字は必要です。また各パスワードごとに、異なるソルトを付加するべきです。また、前述のサンプルではソルトの文字列に 印刷可能な値が含まれていますが、ソルトにはランダムな、印刷不可能な文字を用いることもできます。

(ソルトについては、徳丸本よんでね)

あと「ハッシュのストレッチング処理をする」とかあるけど、難しすぎた…


SQL からパスワードを隠す

攻撃者によってネットワークパケットが傍受された場合 や、SQL クエリが記録されたログファイルが攻撃者の手に渡ってしまった場合には、パスワードを読み取 られてしまう

アプリケーション内で、メモリにあるユーザ入力パスワードをハッシュ化して、SQLとしてはハッシュ暗号化後の文字列のみが流れるようにする。

<?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) {
// パスワードが間違っている
}
}

最近はpassword_hashを使うらしい

http://blog.tokumaru.org/2014/12/phpphp.html

my $pwd = 'xyzzy';

my ($salt)= $dbh->selectrow_array(<<SQL, undef, 'bill');
SELECT salt
FROM Accounts
WHERE account_name = ?
SQL

unless ($salt) {
// アカウント 'bill' は存在しない
}

use Digest::SHA 'hmac_sha256_hex'
my $hash = hmac_sha256_hex($pwd, $salt);

my $account = $dbh->selectrow_hashref(<<SQL, undef, $hash);
SELECT account_id, account_name, email
FROM Accounts
WHERE password_hash = ?
SQL

unless ($account) {
// パスワードが間違っている
}


サンプルコードの hash 関数は、16 進数のみを返すことが保証されています。このため、SQL インジェ クションの危険はありません(20 章「SQL インジェクション」を参照)。


…とはいっても、あきらかによろしくないので普通にprepared statement使うよろし。

ブラウザ→サーバ間は、HTTPSを使わないと平文で送られてしまうよ。

(HTTPSだとしても野良証明書だと認証が不十分だよ)


パスワードをリカバリーするのではなく、リセットする

パスワード忘れた人をどうやって救済する?


一時パスワードをメールで送る


  • 一時パスワードは短時間で無効にする

  • 初回ログイン時にパスワード変更を強制する


トークンを仕込んだパスワード変更URLをメールで送る

よくあるやつ

CREATE 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をメールやSMSで送る


From: daemon

To: bill@example.com

件名 : パスワードのリセット

アカウントのパスワードリセット依頼に回答します。

1 時間以内に以下のリンクをクリックしてパスワードを変更してください。

1 時間が経過するとリンク先のページにはアクセスできなくなり、パスワードも変更できません。

http://www.example.com/reset_password?token=f5cabff22532bd0025118905bdea50da


my $account = $dbh->selectrow_hashref(<<SQL, undef, $token);

SELECT account_id, expiration
FROM PasswordResetRequest
WHERE token = ?
SQL

unless ($account) {
// 無効なtoken
}

if ($account->{expiration} < time) {
// 有効期限切れ
}

// パスワード再設定画面を表示

あと一度使われたtokenは削除しないとね。


あなたが読み取れるものは、攻撃者にも読み取れます。(キリッ