More than 3 years have passed since last update.

「世界最悪のログイン処理コード」をセキュアに実装してみる

Last updated at 2020-01-08Posted at 2019-10-23

しばらく前に話題になった「世界最悪のログイン処理コード」なるものがあります。セキュリティホール満載のログイン処理として有名なコードなのですが、ご存知無い方は是非以下を読んでみて下さい。

いかがですか？　話題としては古いかも知れませんが、少しでも WEB アプリケーションのプログラミングに関心のある方なら大きな衝撃を受けたと思います。さまざまな問題点が指摘されていますが、何と言ってもすごいのは以下の部分でしょう。

var accounts = apiService.sql(
  "SELECT * FROM users"
);

apiService なるオブジェクトの仕様や実装は分かりませんが、どうやらこれはクライアント側で SQL を発行し、その結果セットを API サーバーから取得するという仕組みのようです。この斬新な実装に多くの人が驚きを禁じ得ませんでした。イントラネット内でのシステムとの事ですが、インターネット上に公開したらCVSS で10.0も狙えると思います。

このように大問題を抱えている「世界最悪のログイン処理コード」ですが、クライアントから自由に SQL を発行できるというのは確かに便利かも知れません。SQL が発行可能なら ORマッパー等、色々な処理がクライアント側で可能です。サーバーサイドでいちいち API を用意する必要も無くなるかも知れません。これをセキュアに実現する事はできないものでしょうか？　

そんな事を考え、プロトタイプを作って検証してみました。その結果を報告したいと思います。

結論

まずは結論から。WEBアプリケーションのクライアントサイドからサーバーサイドの RDBMS に自由に SQL を発行する仕組みをセキュアに実装する事は、データベース層の権限管理機能を利用すればたぶん可能です。ただし色々な制約や不便さのために、実用的とは思えませんでした。一方、環境さえ整えば利用価値があり、新しいセキュリティソリューションになるのではないかという将来性を感じる部分もありました。

名前を付けよう

記事を書く上でも不便なので、

「世界最悪のログイン処理コード」に見られる、多数のユーザーを対象とした、アプリケーションのクライアントサイドからサーバーサイドの RDBMS に自由に SQL を発行する仕組み

に、名前を付けたいと思います。DCBAR (Direct connection between Application and RDBMS) というのはいかがでしょうか？　異論は色々あるかも知れませんが、この記事ではそう呼ぶ事にしたいと思います。発音は「でぃーしーばー」としましょう。またセキュアに実装された DCBAR を SDCBAR (Secured DCBAR / えすでぃーしーばー)、SDCBAR を作ろうと思って失敗している状態を「DCBAR 脆弱性」と呼びたいと思います。

なお「世界最悪のログイン処理コード」ではログイン処理に DCBAR を使っていますが、私の実装ではログイン処理自体は DCBAR ではありません。追々詳しく説明したいと思いますが、予めご了承下さい。

そもそも何がいけないのか？

クライアントから SQL を発行されると何がいけないのでしょうか？　それはデータベース内の情報を制限なく自由に読めるようになってしまい、重要な情報が漏洩してしまうからです。例えば users テーブルにユーザーが秘密にしておきたい電話番号や住所等の情報が記録されているのだとしたら、それらの情報は不特定多数に公開されてはなりません。また「世界最悪のログイン処理コード」では SELECT 文の発行しか確認できませんが、もし UPDATE 文や INSERT 文も実行可能だとしたら被害はより大きくなります。

ではなぜ SQL を発行されただけで、重要な情報が読み書きできてしまうのでしょうか。それはサーバーサイドのビジネスロジック層では強い権限を持つアカウントでデーベースに接続しているからです。データベースのアカウントは、多くの製品で非常に細かく権限の設定ができるようになっていますが、WEB アプリケーションでは強い権限のアカウントでデータベースにアクセスしている事が多いのではないでしょうか。本来ならAさんがアクセスした時はAさんだけが自分の個人情報に、Bさんがアクセスした時にはBさんだけが自分の個人情報にアクセスできるようにデータベース層で隔離されているのが理想です。しかし多くの実装ではその隔離はビジネスロジック層のプログラムコードで行われていると思います。だからこそ、ビジネスロジック層の制御が及ばなくなる DCBAR は許されないアンチパターンなのです。

データベース層の権限管理機能を利用するために必要な事

ではなぜ WEB アプリケーションは強い権限でデータベースにアクセスしなければならないのでしょうか。主な理由は以下のような物だと思います。

ユーザーアカウントとデータベースアカウントが分離してしまっている
- データベース層の権限管理機能を利用するためにはデータベースアカウントとユーザーアカウントが1対1に対応していなければならない。しかし時には数万、数億のオーダーを想定しなければならないユーザーアカウントに対し、ひとつずつデータベースアカウントを発行するのは現実的ではない
行レベルの隔離が扱いにくい
- 仮にユーザーごとにデータベースアカウントを用意したとしても、それぞれのアカウントの ACL を制御し行レベルの隔離を実現していくのはさらに手間がかかる

他にも色々あると思いますが、特に重要な点は以上のような物だと思います。しかし、これを克服する事は本当に無理でしょうか。ログイン前の匿名アカウントにはごく弱い権限を持たせ、サイトのログインにはデータベースアカウントでログインさせます。ログイン後はきめ細かくチューニングされた ACL を適用したテーブルだけにアクセスできるようにし、Aさんの個人情報はどのような SQL を叩いても、Aさん以外には閲覧も更新もできないようにするのです。テーブルに対する ACL 設定だけでは難しい場合は、ストアドプロシージャーも活用しましょう。

このような事をひとつずつ実現していけば、SDCBAR は不可能ではないようにも思えます。

そしてやってみる

実を言うと「世界最悪のログイン処理コード」を見る前から、漠然とクライアントから SQL を発行するような実装には興味がありました。同じような事を考えた事のある人は結構居るのではないでしょうか？　しかし、ちょっと考えると色々無理がある部分が見えてくるので実装には至りませんでした。そこで今回、検証用に作ってみる事にしました。目的はクライアントからの SQL 発行を許可しつつ、アクセス権に基づくリソースの隔離が正しく行えるアプリケーションを作る事です。すなわち SDCBAR を実現する事です。

実際に作る物ですが、簡単でそれなりに権限管理が必要な物としたいと思います。以下のようなコミュニティサイトのデモを作る事にしました。

会員登録ができる事
会員はログインIDとパスワードでログインできる事
会員は自分のパスワード、メールアドレス、プロフィール、プロフィールの公開範囲を設定できる事
会員のメールアドレスは非ログインユーザーや他の会員からは閲覧できない事
会員は他の会員を自分の「フレンド」にしたり、解除したりできる事
会員、非ログインユーザーともに会員の一覧を閲覧できる事。ただし各会員のフレンド一覧は会員同士でだけ閲覧できる事
プロフィールの公開範囲は「非公開」「フレンドのみに公開」「会員にのみ公開」「非ログインユーザーにも公開」から選べる事
ユーザーが入力した SQL を実行し、結果を出力するページを利用できる事（テスト用）

開発環境・実行環境は以下の通りです。

項目	内容
OS	CentOS 7
データベース	MariaDB 5.5
サーバーサイド言語	PHP 7.2
サーバーサイドフレームワーク	無し。素のPHP
クライアントサイドフレームワーク	VueCLI 3.11
CSS フレームワーク	bootstrapvue 2.0

ソースファイル・デモサイト

gitgub にソース一式を置きました。

https://github.com/matsumoto-sp/sdcbar_sample

また、Amazon Lightsail でデモサイトを公開しています。直接 SQL の発行もできるようになっていますのでよかったら触ってみて下さい。そのうち止めると思いますが、2019年中はオープンしておこうと思います（2020/01/09: デモサイトを終了しました。検証して下さった皆さん、ありがとうございます）。

URL	BASIC認証アカウント	BASIC認証パスワード
デモサイトは終了しました	`test`	`test`

初期状態で利用可能なアカウントは以下の通りです。

ログイン名	パスワード	備考
admin	ahThip9ivoh0	管理者アカウント
member_1	member_1_pw	プロフィール公開設定 'none', member_5 をフレンド設定してある
member_2	member_2_pw	プロフィール公開設定 'friends', member_5 をフレンド設定してある
member_3	member_3_pw	プロフィール公開設定 'member', member_5 をフレンド設定してある
member_4	member_4_pw	プロフィール公開設定 'open', member_5 をフレンド設定してある
member_5	member_5_pw
member_6	member_6_pw
member_7	member_7_pw
member_8	member_8_pw
member_9	member_9_pw

デモサイト利用方法

上記のアカウントでログインしてもらっても構いませんし、新たにアカウントを作ってもらっても構いません。なお、データベースは毎時00分、20分、40分に初期化していますので登録した内容はすべて20分毎にクリアされます。到達性確認は行ってませんので、必ず架空のメールアドレスで登録して下さい。ただしメールアドレスとしての有効性だけは確認しているため user1@example.com 等を利用する事をお勧めします。

非ログイン状態の時も含め、メニューには "SQL" という項目があります。ここから発行された SQL は直接サーバー内の MySQL で実行され、その結果を返します。いかなる SQL を発行されても、サイトの機密情報を漏洩させない事がこのデモサイトの目的です。色々な SQL を発行し、もし権限昇格を確認したり、他のユーザーの機密情報（メールアドレスや非公開のプロフィール）を取得可能でしたら、是非お知らせ下さい。

実装の概要

サイトにはデーベースアカウントでログインし、それぞれのアカウントでは見えてはいけない物を見えないようにするというのが基本的な戦略です。そのために GRANT 文相当の処理を行い、各テーブルやビューへのアクセス制限を設けています。これを実現するために登録時にCREATE USER文相当の処理を行い、データベースアカウントを作成した後に権限の設定を行っています。

しかし、少しでも複雑な処理になると、GRANT文による制御では対応できなくなります。そのような部分はストアドプロシージャーやストアドファンクションを利用しました。

詳細な解説を別の記事に用意しましたので興味がありましたらご覧下さい。

感想とまとめ

総評としてはですね、たぶんこれを実務で利用する事は無いと思います。SQL をクライアントから発行する事で確かに API の作成は簡略化できるのですが、それ以上にストアドプロシージャーの開発が大変です。昨今の WEB アプリケーション開発環境では色々なツールやライブラリ、フレームワークが揃っていますが、そう言った物が全然ない所が辛いです。たとえば今回はアカウント登録時にメールの到達性確認等を省略していますが、これをストアドプロシージャーで認証コードを作成し、メール送信するのは大変です。他にも PDF出力、CSV出力、HTMLメール、画像加工等々、今どきのサーバーサイドでやるような当たり前の事がいちいち大変です。また、PL/SQL という言語自体も大規模な開発にはあまり向いていないと思います。

それとこれがセキュアな実装であるかどうかですが、今の所大きな問題は確認できていません。SQL の発行自体に制限はありませんが操作可能なテーブル、レコード、ストアドプロシージャーが大幅に制限されているため、おそらくできる事は限られていると思います。これは好ましい事であり、SDCBAR ではなく、通常の形態のアプリケーションにうまく取り込めれば WEB アプリケーションのセキュリティは飛躍的に向上する可能性を感じます。つまりデータベース層では権限管理のみ行い、ビジネルロジックは従来通りサーバーサイドのプログラミング言語で開発する形態です。これにより SQL インジェクション等による脅威は、大幅に減らせるはずです。最大の課題はやはりデータベースアカウントとアプリケーションのアカウントを紐づける事でしょう。今回はやっていないのですが、パフォーマンスやリソース消費量等も含めた検証が必要かも知れません。

以下にこれまで触れてこなかった事も含め、メリット・デメリットをまとめたいと思います。

SDCBAR とは？

WEB アプリケーションのクライアントサイドから、直接 SQL を発行するシステム形態です。構成するために以下が必要になります。

アプリケーションのユーザーアカウントとデータベースのアカウントを紐づける
それぞれのアカウントが許された範囲で活動できるように、テーブルやレコード、カラムの権限を適切に設定する

メリット

どのような SQL を発行されても問題がないシステムは、なかなか他の構成で構築しようと思っても難しい。それが達成できる事が、まず何よりメリットである
クライアント側では権限の事を意識せずにプログラミングが可能になる
SQL インジェクションの脅威を大幅に減らせる。と言うより SQLインジェクションなんて存在しない。もしくはすべてが SQL インジェクションである
各プロシージャーや関数は個別にアスセス権を指定できる。このセキュリティ的な価値は大きい
平文パスワードや可逆的な暗号化パスワードをサーバー内に保存する必要が無い（ただしセッション情報のテンポラリファイルには保存されている）
従来的なサーバーサイドプログラミングの役割はかなり小さくなる。ミドルウェア化もおそらく可能

デメリットや注意点

PL/SQL は言語構造もライブラリも物足りない部分が多い（他の言語が利用可能な処理系もある）
多数のデータベースアカウントで接続される事になるので、持続的接続は実質的に使えない
入力文字列の中には ' 等が含まる事が当然あるので、クライアントサイドにおけるエスケープ処理は当然必要になる（クライアントサイドで動作する ORM 等があればもっと使い勝手は良くなるかも知れない）
CREATE USER文や GRANT 文ではユーザー名等に変数を利用できなかったために、mysql.user テーブルや mysql.tables_priv テーブル等を直接操作し、FLUSH PRIVILEGES する必要があった。他の RDBMS では当然やり方が異なるため互換性に乏しい。（PREPARE 文の利用は除外）
ユーザーごとに mysql.user テーブルのレコードが必要になる。権限関係では mysql.tables_prev テーブル、mysql.procs_prevテーブル等にも複数のレコードが必要。基本的にユーザー数には制限が無いはずだが、消費するリソースが大きすぎるかも知れない（MySQL 8.0 になるとロールが利用できるので、この問題は大きく緩和されるかも知れない）
PL/SQL をステップ実行、コードカバレッジ計測、静的コード解析等を行う方法が見つけられなかった（Oracle や MS SQL-Server ならツールも多い）
今のままでは複数の SQL をワントランザクションで実行する術が無いので、やりたかったら工夫が必要
どのような SQL を実行されても良いとは言っても、パラメーターのバリデーションチェックが不要になるわけではない。例えば膨大なレコード数のあるテーブルなら1ページ表示数の最大値をストアドプロシージャーやビューで設定する必要がある。この辺の事情は一般的なアプリケーションと同様
SQL インジェクションの脅威は大幅に減らせると思うが、DOS 攻撃や CSRF 等、その他の脅威への耐性は変わらない。一般的な対策が別途必要になる
RDBMS の諸機能（特に権限管理や SQL パーサ）に絶対的な信頼が要求される（もっともプロジェクトごとに作るアプリケーションのコードより信頼性は遥かに高いと思う）
SHOW VARIABLES 等、SQL として利用可能なコマンドのいくつかはデフォルトで利用可能になっている。必要に応じてそれらの権限を無効にしなくてはならない
2層アーキティクチャへの逆行的な側面を持っている
本格的にやろうと思うなら、生産性の高いフレームワークの設計から始める必要があるかも知れない

以上で「世界最悪のログイン処理コード」をセキュアに実装してみる実験の結果報告は終了です。実用性には疑問がありますが、前々から POC してみたいと思っていた事ができてスッキリしました（笑）。

長い記事に最後までお付き合い頂きましてありがとうございます！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up