安全なSQLの呼び出し方

安全なSQLの呼び出し方の自分用のまとめです。

リテラルとSQLインジェクション

SQL文の構造

SELECT a,b,c FROM atable WHERE name='YAMADA' and age>=20

　SQL 文を構成する要素には、キーワード、演算子、識別子、リテラルなどがある。

要素	例
キーワード（予約語）	SELECT FROM WHERE AND
演算子など	= >= ,
識別子	a b c atable name age
リテラル	'YAMADA' 20

リテラルとは

　SQL で、列employee_id（社員番号）が052312である社員を検索するには、以下のような SQL 文を実行する。

SELECT name, age FROM employee WHERE employee_id = '052312'

　SQL 文中の「'052312'」のような定数のことをリテラルと呼び、文字列としてのリテラルのことを文字列リテラルと呼ぶ。リテラルには文字列リテラルの他、数値リテラル、論理値リテラル、日時リテラルなどがある。

SQLインジェクションの原因

　SQL をアプリケーションから利用する場合、SQL 文のリテラル部分をパラメータ化することが一般的である。パラメータ化された部分を実際の値に展開するとき、リテラルとして文法的に正しく文を生成しないと、パラメータに与えられた値がリテラルの外にはみ出した状態になり、リテラルの後ろに続く文として解釈されることになる。この現象が SQL インジェクションである。

文字列リテラルに対するSQLインジェクション

SQL の id 列は文字列型を想定しており、$id は Perl の変数で、外部から与えられるものとする。

$q = "SELECT * FROM atable WHERE id='$id'";

　ここで、$id に以下の値を与える場合、

';DELETE FROM atable--

　パラメータを展開した後の SQL 文は以下のようになる。

SELECT * FROM atable WHERE id='';DELETE FROM atable--'

　SELECT 文の後ろに DELETE 文が追加され、データベースの内容がすべて削除される結果となる。(SQLでは--以降はコメントとして無視される)
　このように、SQL 文の文字列リテラルをパラメータ化しているときに、そこに別の SQL 文の断片を含ませることで、元の SQL 文の意味を変更できる場合がある。これが SQL インジェクションの脆弱性である。

数値リテラルに対するSQLインジェクション

　数値リテラルについては、PerlやPHPなど、変数に型のない言語を使用している場合に注意が必要である。アプリケーション開発者は変数に数値が入っているつもりでも、数値以外の文字が入力された場合、変数に型のない言語では、それを文字列として扱ってしまう。

$q = "SELECT * FROM atable WHERE id=$id";

　このような SQL 呼び出しがあった際に、$id として以下の値を与える場合、

0;DELETE FROM atable

　パラメータを展開した後の SQL 文は以下のようになる。

SELECT * FROM atable WHERE id=0;DELETE FROM atable

　SELECT 文の後ろに DELETE 文が追加され、データベースの内容がすべて削除される結果となる。

安全なSQL呼び出しの要件

• 文字列リテラルに対しては、エスケープすべき文字をエスケープすること
• 数値リテラルに対しては、数値以外の文字を混入させないこと

SQLの呼び出し方

　アプリケーションから SQL を呼び出す際には、検索条件などをパラメータ化することが一般的である。その実現方法は、まず大きく分けて次の 2 つの方法に分類できる。

• 文字列連結による SQL 文の組み立て
• プレースホルダによる SQL 文の組み立て

文字列連結によるSQL分の組み立て

　文字列連結による組み立てとは、アプリケーションのプログラミング言語で、SQL 文を生成するとき、パラメータ部分への値の埋め込みを文字列連結によって実現する方法である。以下は、CGI パラメータnameに指定された社員を検索する SQL 文を、PHP で組み立てる処理の例である。
※ このプログラムには SQL インジェクションの脆弱性がある。

$name = $_POST['name'];
//...
$sql = "SELECT * FROM employee WHERE name='" . $name . "'";

PHP の式$nameの値が山田`の場合、実行後は以下の SQL 文が生成される。

SELECT * FROM employee WHERE name='山田'

SQL インジェクションの脆弱性をなくすには、式$nameをクォートして文字列連結する際に、$nameの値に対するエスケープ処理が必要である。

プレースホルダによるSQL分の組み立て

　プレースホルダによる組み立てとは、パラメータ部分を?などの記号で示しておき、後に、そこへ実際の値を機械的な処理で割り当てる方法である。以下は Java におけるプレースホルダによる組み立ての例である。

PreparedStatement prep = conn.prepareStatement("SELECT * FROM employee WHERE name=?");
prep.setString(1, "山田");

　ここで、パラメータ部分を示す記号?のことをプレースホルダと呼び、そこへ実際の値を割り当てることを「バインドする」と呼ぶ。
　プレースホルダによる組み立ては、バインドをいつ行うのかによって2種類に分けることができる。

• 静的プレースホルダ
• 動的プレースホルダ

静的プレースホルダ

　静的プレースホルダは、JIS/ISO の規格では「準備された文(Prepared Statement)」と規定されている。これは、プレースホルダのままの SQL 文をデータベースエンジン側にあらかじめ送信して、実行前に、SQL 文の構文解析などの準備をしておく方式である。
　セキュリティの観点で、最も安全である。静的プレースホルダでは、SQL を準備する段階で SQL 文の構文が確定し、後から SQL 構文が変化することがないため、パラメータの値がリテラルの外にはみ出す現象が起きない。その結果として、SQL インジェクションの脆弱性は生じない。

動的プレースホルダ

　動的プレースホルダは準備された文(Prepared Statement)とは異なり、プレースホルダを利用するものの、パラメータのバインド処理をデータベースエンジン側で行うのではなく、アプリケーション側のライブラリ内で実行する方式である。
　セキュリティの観点では、プレースホルダを用いたバインド処理によってパラメータの値の埋め込みがライブラリで機械的に処理されることから、文字列連結による組み立てに比べてアプリケーション開発者のミスによるエスケープ漏れを防止できると期待される。
　ただし、動的プレースホルダは静的プレースホルダとは異なり、バインド処理を実現するライブラリによっては、SQL 構文を変化させるような SQL インジェクションを許してしまう、脆弱な実装のものが存在する可能性は否定できない。

データベースと連動したSQL文生成

文字列連結にquoteメソッドを使う

　文字列連結による SQL 文の組み立てで安全な SQL 呼び出しを実現するには、以下の要件を満たさなければなければならない。

• 文字列リテラルに対しては、エスケープすべき文字をエスケープすること
• 数値リテラルに対しては、数値以外の文字を混入させないこと

　Perl の DBI、PHP の Pear::MDB2、PDO (PHP Data Objects) などで用意されている quote メソッドは、SQL のリテラルを生成する処理を抽象化したメソッドであり、アプリケーション開発者がデータベースエンジンの種類や設定を意識することなく、正しいエスケープ処理を実現するのに利用できる。
　PHP の Pear::MDB2 で、様々なデータを quote メソッドに与えた場合の例を以下に示す。

データ	型指定	戻り値
abc	'text'	'abc' （PHP の文字列型の値、クォートを含む）
O'Reilly	'text'	'O''Reilly' （PHP の文字列型の値、クォートを含む）
-123	'decimal'	-123 （PHP の文字列型の値）
123abc	'decimal'	123 （PHP の文字列型の値）
-123	'integer'	-123 （PHP の整数型の値）
123abc	'integer'	123 （PHP の整数型の値）

データベースと連動した動的プレースホルダ

　動的プレースホルダを実現している例として、Perl 言語の DBI モジュールから呼び出されるDBD::mysql がある。DBD::mysql の提供するプレースホルダ機能は、データベース接続時のオプションにより、動的プレースホルダを使用するか静的プレースホルダを使用するかを、明示的に指定することができる。

$sth->bind_param(2, '山本', SQL_VARCHAR);
my $rt = $sth->execute();
$sth->bind_param(1, 27, SQL_INTEGER);
my $sql = "SELECT * FROM test3 where age >= ? and name = ?";
my $sth = $db->prepare($sql);
my $db = DBI->connect("DBI:mysql:$dbname:$host;mysql_server_prepare=0",
$user, $pwd) || die $DBI::errstr;

　この例では、データベース接続時のオプション「mysql_server_prepare=0」によって、動的プレースホル
ダを指定している。
　このプログラムによって生成される SQL 文は以下のようになる。

SELECT * FROM test3 where age >= 27 and name = '山本'

同じプログラムで、name 列に O'reillyおよび\100を与えた場合、生成される SQL 文はそれぞれ以下のようになる。

SELECT * FROM test3 where age >= 27 and name = 'O\'reilly'
SELECT * FROM test3 where age >= 27 and name = '\\100'

MySQL のデフォルト設定では、'と\をエスケープする必要があるため、このような挙動となる。しかし、MySQL に「NO_BACKSLASH_ESCAPES」オプションを指定した場合、JIS/ISO の規格通り'のみをエスケープする設定となり、生成される SQL 文は以下のようになる。

SELECT * FROM test3 where age >= 27 and name = 'O''reilly'
SELECT * FROM test3 where age >= 27 and name = '\100'

DBMS製品の実態調査

調査内容

　ウェブアプリケーション開発で実際に使用されることの多い DBMS とプログラミング言語の組み合わせを対象に、以下の点について実態を調査した。

• プレースホルダの実装は静的プレースホルダ（準備された文）か、動的プレースホルダか
• 動的プレースホルダのエスケープ処理は正しく処理されるか
• quote メソッドは正しく処理されるか
• 文字エンコーディングの扱い

結果のまとめ

	Java + Oracle	PHP + MDB2 + PostgreSQL	Perl + MySQL	Java + MySQL	ASP.NET + SQL Server
プレースホルダの実装	静的のみ	静的のみ	静的または動的	静的または動的	静的のみ
動的プレースホルダの処理	－	－	○	△	－
quote メソッドの処理（文字列）	－	○	○	－	－
quote メソッドの処理（数値）	－	○	×	－	－
文字エンコーディング	UTF-8 が使われる	指定可能	UTF-8 を明示可能	指定可能	UTF-16 が 使われる