データベース基礎 #4 - 信頼性とセキュリティ

このブログ記事では、信頼性とセキュリティを確保するための複数の機能を紹介します。

前回の2つの記事では、CRUD操作を実行するための基本的なSQL文について説明しました。 DBMSは、それらに加え、信頼性とセキュリティを維持しながら操作を実行するための機能を実装しています。したがって、この記事では、それらの機能とその使用方法のいくつかを紹介します。

トランザクション

一般的に、一緒に実行されなければならない操作（通常は書き込み）が存在します。例えば、あるユーザーが別のユーザーにお金を送金する場合、両方のユーザーの残高を同時に更新する必要があります。プロセス中にサーバーがクラッシュすると、送金者の口座からお金が消えたり、または順序によっては無からお金が生成されたりする可能性があり、どちらも重大な問題です。これらの問題を防ぐために、DBMSはこれらの操作を1つの原子単位にまとめることができる、トランザクションを提供しています。

BEGIN;
UPDATE users SET balance = balance - 250.00 WHERE name = 'A';
UPDATE users SET balance = balance + 250.00 WHERE name = 'B';
COMMIT;

上は、PostgreSQLでのAからBへの250ドルの送金のための書き込み操作をまとめるトランザクションの例です。 BEGIN文はトランザクションを開始し、COMMIT文が表示されるまで後続の文が即座に処理されるのを防ぎ、それらの間の文をまとめます。トランザクション内の文の処理中に障害が発生した場合、DBMSは行われたすべての変更をロールバックし、原子性と一貫性を確保します。

DBMSはトランザクションを、他のトランザクションに見えないように実装し、同時の行われたコミットが順に行われたかのように動作するように、分離性を保証します。さらに、DBMSはトランザクションがコミットされると、それらが不揮発性メモリに記録されることを保証し、耐久性を保証します。原子性、一貫性、分離性、耐久性の特性は、総称してACID特性として知られており、信頼性を優先する場合、データベースシステムはこの特性を持つことが望ましいです（一部のシステムでは一貫性よりも可用性を優先する場合があります）。

トリガー

場合によっては、列の値が他の列に依存することがあります。例えば、製品ごとの収益は、製品の価格と販売数量に依存します。毎回の更新ごとに手動でこれらの値を掛け合わせて収益を追跡するのはエラーが発生しやすいため、代わりに各更新の前に自動的に収益を計算するトリガーを使用して、データの整合性を確保できます。この場合CREATE TRIGGER revenue_update BEFORE UPDATE ON Product FOR EACH ROW SET NEW.revenue = price * quantity;のようなトリガーを設定できます。

また、新しい行を挿入する際に自動的に収益を計算するトリガーをCREATE TRIGGER revenue_insert BEFORE INSERT ON Product FOR EACH ROW SET NEW.revenue = NEW.price * NEW.quantity;のように設定することもできます。ここでは、トリガーが自動的に処理するため、収益列にNULL値を挿入できます。会社の総収益を追跡する場合、 CREATE TRIGGER revenue_total_update AFTER INSERT ON Product FOR EACH ROW UPDATE Company SET revenue_total = revenue_total + (NEW.revenue - OLD.revenue) WHERE id == NEW.id;のようなトリガーを使用できます。

SQLインジェクション

データベースシステムにおいてセキュリティは大きな懸念事項であり、SQLインジェクションは重大な脆弱性です。一般的な例として、悪意のある攻撃者がパスワード入力フィールドに ' OR '1'='1 と入力すると、 SELECT * FROM Customer WHERE name='admin' AND password='' OR '1'='1'; というSQLステートメントが生成され、これは常にtrueと評価されてデータが露出してしまいます。攻撃者は admin' -- と入力してクエリからパスワードチェックをコメントアウトすることもできます。

これらの例は比較的単純に防止できますが（入力の検証やサニタイズ、例えば = や -- のチェックなど）、手動で対処するのが難しい、より複雑なSQLインジェクションも存在します。そのため、幅広いSQLインジェクション攻撃から守るために複数の対策を実装することが重要です。正規表現を使用した入力の検証やサニタイズの他に、SQLコマンドとユーザー入力を明確に分離するパラメータ化クエリを使用できます。これはストアドプロシージャやプリペアドステートメントで実装できます。

ストアドプロシージャ

データベースシステムを設定する際、頻繁に行われるクエリについて事前に把握していることが多いです。例えば、Eコマースウェブサイトで商品を表示するための SELECT id, name, price FROM Product; のようなクエリが多く発生することが予想されます。また、クライアントが SELECT id, name, price, description, (AVG(score) FROM Review WHERE Review.product_id=<id>) FROM Product WHERE Product.id=<id>; や SELECT Customer.name, Review.score, Review.content FROM Review JOIN Customer ON Review.customer_id = Customer.id WHERE Review.product_id=<id>; のような一連のクエリを実行して商品の詳細やレビューを取得することも予想できます。

CREATE PROCEDURE product_details(pid integer) 
LANGUAGE SQL -- can be sql, c, internal, and others
BEGIN ATOMIC
    SELECT id, name, price, description, (AVG(score) FROM Review WHERE Review.product_id=pid) FROM Product WHERE Product.id=pid; -- product details and average score (could have been automatically computed with triggers)
    SELECT Customer.name, Review.score, Review.content FROM Review JOIN Customer ON Review.customer_id = Cutomer.id WHERE Review.product_id=pid; -- reviews
END;

このような場合、これらのクエリを事前にコンパイルしてサーバーに保存することで、複数のクライアントが毎回手動でクエリを書く代わりに、単にストアドプロシージャを呼び出すだけで済むようになります。これによりエラーの可能性が減少します。上記で示したように、商品の詳細やレビューを取得するための複雑なクエリに対して CREATE PROCEDURE を使用できます。クライアントは複雑なクエリを実行するためにCALL product_details(<id>); を使用するだけで済みます。ストアドプロシージャはパラメータ化クエリを定義してSQLインジェクションから保護するためにも使用できます（ただし、この特定のシナリオではそれほど関連性がないかもしれません）。また、どのクライアントがそれらを呼び出せるかを制限することで、セキュリティを強化することもできます。（これはクライアント-サーバーデータベースモデルを前提としているため、 SQLiteのような組み込みデータベースでよく使用される他のモデルのDBMSではサポートされていないことが多いです。）

プリペアドステートメント

プリペアドステートメントにより、クライアントはSQLインジェクションから保護するためのパラメータ化クエリを設定できます。例えば、クライアントが認証する際に、 PREPARE authenticate(text, text) AS SELECT * FROM Customer WHERE name=$1 AND password=$2; のようなプリペアドステートメントを設定し、その後 EXECUTE authenticate(<name>, <password>); で実行できます。一度データベースセッションでクライアントによって定義されると、同じプリペアドステートメントをそのセッション内で同じクライアントが異なるパラメータセットで再利用して実行できるため、クエリの複雑さとクエリの数によっては、クエリのパフォーマンスが向上する可能性があります。

プリペアドステートメントは再利用性により信頼性にも貢献します。ストアドプロシージャと似ていますが、プリペアドステートメントはサーバーではなくクライアントによって作成され、単一のデータベースセッションにのみ存在します。また、通常は複数のクエリをバンドルできず、ストアドプロシージャとは異なり、SQLiteのような組み込みデータベースでも利用可能です。プリペアドステートメント、ストアドプロシージャ、ユーザー入力の検証とサニタイズを組み合わせることで、SQLインジェクション攻撃に対するセキュリティを向上させることができます。

結論

この記事では、トランザクション、トリガー、ストアドプロシージャ、プリペアドステートメントについて説明しました。これらはすべて、SQLデータベースでの操作の信頼性とセキュリティに貢献するもので、ほぼすべてのユースケースで重要な側面です。これらの機能に関する詳細については、それぞれのDBMSの公式ドキュメントを確認することをお勧めします（PostgreSQLについては以下で引用されています）。

リソース

PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.