技術の犬小屋

Webプログラミングを餌に生きる犬のメモ帳

Posts in the PostgreSQL category

SQLインジェクションについて,改めて勉強したので忘れないようにメモしておく。
 

SQLインジェクションとは

データベースと連動したWebサイトで,データベースへの問い合わせや操作を行うプログラムにパラメータとしてSQL文の断片を与えることにより,データベースを改ざんしたり不正に情報を入手する攻撃。また,そのような攻撃を許してしまうプログラムの脆弱性のこと。
 

具体例

例えば,ユーザによって入力されたユーザID,パスワードをそれぞれ {$userId} , {$passwd} に代入し,上記SQLにてその存在が確認できればログインできるWEBページがあるとする。
 
そのWEBページで使われているSQLは以下のようになっている。
 

SELECT * FROM ユーザマスタ WHERE ユーザID = '{$userId}' AND パスワード = '{$passwd}'

 
悪意のあるユーザがユーザID「dog」としてパスワードを入力せずにログインを試みるようなパラメータを入力した際には,以下のようなSQLとなる。
 

SELECT * FROM ユーザマスタ WHERE ユーザID = 'dog' AND パスワード = '' OR 'A' = 'A'

 
$passwd に対して ‘ OR ‘A’ = ‘A’ というパラメータを渡している。このように OR で A は A である,というような条件式を追加することで不正にログインすることが出来てしまう。
 

SQLインジェクション脆弱性への対策方法

SQLインジェクション脆弱性への対策としては,プレースホルダを使用する。ここで,パラメータ部分を示す記号「?」のことをプレースホルダと呼び,そこへ実際の値を割り当てることを「バインドする」と呼ぶ。
 
プレースホルダには,以下の2つがある。

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

 
以下,これらについて説明する。
 

静的プレースホルダ

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

動的プレースホルダ

動的プレースホルダとは,プレースホルダを利用するものの,パラメータのバインド処理をデータベースエンジン側で行うのではなく,アプリケーション側のライブラリ内で実行する方式である。動的プレースホルダは静的プレースホルダとは異なり,バインド処理を実現するライブラリによっては,SQL構文を変化させるようなSQLインジェクションを許してしまう,脆弱な実装のものが存在する可能性を否定できない。
よって,SQLインジェクション脆弱性対策には,なるべく静的プレースホルダを使用することを推奨する。
 

プログラミング言語とデータベースの組み合わせによって使用されるプレースホルダの種類が分かれる

プログラミング言語とデータベースの組み合わせで,デフォルトで使用されるプレースホルダが,静的プレースホルダか,動的プレースホルダか,に分かれる。
 
以下にプログラミング言語とデータベースの組み合わせでどちらのプレースホルダが使われるかを示す。
 

Java + Oracle

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

Java + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にuseServerPrepStmts=trueというパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

PHP + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にPDO::setAttribute(ATTR_EMULATE_PREPARES, false)というパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

PHP + PostgreSQL

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

Perl + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にmysql_server_prepare=1というパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

ASP.NET + SQL Server

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

まとめ

  • SQLインジェクション脆弱性への対策にはプレースホルダを使う
  • 動的プレースホルダよりも,なるべく静的プレースホルダを使うことが望ましい

 
 
以上
 
 
参考
SQLインジェクション
安全なSQLの呼び出し方
PDOでATTR_EMULATE_PREPARESを適切に設定してないとSQLインジェクションの原因になるかも(MySQL編)
プリペアド・ステートメント(プレースホルダ)入門
PDOにおける一応の安全宣言と残る問題点 | 徳丸浩の日記
SQLインジェクションのまとめ – No Programming, No Life

RDBのインデックスについて勉強したのでメモ。
 

インデックスとは

インデックス(索引)とはテーブルに格納されているデータを高速に取り出す為の仕組みを意味する。インデックスを適切に使用することによってSQL文の応答時間が劇的に改善される可能性がある。
 
一口にインデックスと言っても、いくつかの種類があり、DBMSによっても使用できる種類に差がある。しかし、実は頻繁に利用するインデックスは1種類だけなので、それだけ覚えれば十分である。そのインデックスは、B-treeインデックスである。B-treeは非常にポピュラーであるため、通常、DBMSにおいてデフォルトで使用される。
 

B-treeインデックスはどの列に作れば良いか

B-treeインデックスは以下の指針に沿って作成する。

  1. 大規模なテーブルに対して作成する。
  2. カーディナリティの高い列に作成する。
  3. SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。

 

大規模なテーブルに対して作成する。

データ量が少ないテーブルではインデックスを使うよりもフルスキャンを使った方が検索性能が良かったり、インデックスを作ってもフルスキャンを使うのと検索性能がほとんど変わらない場合が多い。では、「データ量が少ないテーブル」とはどの程度が閾値なのか。これは、マシンスペックなど環境によって左右されるので、固定的な値は存在しないが、目安としてはレコード数が1万件を超えた場合は、インデックスを作成する意味はあると考えて良い。
 

カーディナリティの高い列に作成する。

カーディナリティとは、カラムの値の種類の絶対数をあらわす。例えば、人間の性別なら「男・女」の2種類なので、カーディナリティは2となる。もう一つ例を挙げると、1年間の日付なら「1~365」なのでカーディナリティは365である。
B-treeインデックスを作るときは、カーディナリティの高い列を選ぶことが基本である。この場合の目安は、特定のカラム値を指定した時に、全体の5%程度に絞り込めるだけのカーディナリティがあることである。例えば、365日のうちの1日を指定するSELECT文を考えるとすれば、0.3%に絞り込めるため、B-treeインデックスを作る意味はある、と判断できる。
 

SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。

これは当たり前な話だが、SQLの選択条件や結合条件に使用されない列にいくらインデックスを作っても無意味である。
 
ただし、インデックスが使用されるためには、SQLの記述方法として注意点があるので、代表的なものを挙げる。
 

  1. インデックス列に演算を行っている
  2. 索引列に対してSQL関数を適用している
  3. IS NULL述語を使っている
  4. 非定形を用いている
  5. ORを用いている
  6. 後方一致、または中間一致のLIKE述語を用いている
  7. 暗黙の型変換を行っている

 
上記の各パターンについて解説する。下記ではcol_1カラムに対してインデックスを作成している。
 
 

1. インデックス列に演算を行っている

インデックスを作成したカラムはSQLにおいて「裸」でいるのが原則である。
カラムに対して演算を行うとインデックスを利用することができない。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

 
インデックスが利用される例

SELECT * FROM SomeTable WHERE col_1 > 100/1.1

 
 

2. 索引列に対してSQL関数を適用している

理由は1.と同じである。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

 
 

3. IS NULL述語を使っている

B-treeインデックスは一般的にNULLについてはデータの値とは見なさず、保持していない。したがって、IS NULL、またはIS NOT NULL述語に対しては有効ではない。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 IS NULL;

 
 

4. 非定形を用いている

非定型はインデックスを利用できない。理由は、検索したとしても検索範囲が広過ぎて役に立たないからである。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 <> 100;

 
 

5. ORを用いている

ORを用いた場合はインデックスを利用できない。これはINで書き換えることで回避することができる。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 = 99 OR col_1 = 100;

 
インデックスが利用される例

SELECT * FROM SomeTable WHERE col_1 IN (99, 100);

 
 

6. 後方一致、または中間一致のLIKE述語を用いている

LIKE述語を使用するときは、前方一致検索の場合のみ索引が使用される。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 LIKE '%a';

 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';

 
インデックスが利用される例

SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

 
 

7. 暗黙の型変換を行っている

データ型の異なるカラム値をSQLにおいて選択条件または結合条件として利用する場合、数値型⇔文字列型、文字列型⇔日付型のように、型変換を行って型を統一する必要がある。
列とデータ型の異なる値を条件に指定した場合、DBMSは内部的に暗黙の型変換を行う。しかし、その場合はインデックスが使用されなくなる。これを回避するためには明示的に条件に使用する値のデータ型をカラムのデータ型に合わせてやる必要がある。
 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 = 10;

 
インデックスが利用されない例

SELECT * FROM SomeTable WHERE col_1 = '10';

 
インデックスが利用される例

SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

 
 

主キーおよび一意制約の列には作成不要

DBMSは主キー制約や一意制約を作成する際、内部的にはB-treeインデックスを作成している。B-treeインデックスがデータをソートして保持するため、重複値チェックにこれを利用している。そのため、主キーや一意制約が存在するカラムに、二重にインデックスを作成する必要はない。
 

B-treeインデックスは検索性能を劣化させる

インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れて性能が劣化していく。そのため、運用において定期的なメンテナンスを行う、具体的にはインデックスの再構築を行うことが、性能を維持するためには望ましい方策である。
 
 
以上
 
 
参考
書籍 達人に学ぶDB設計徹底指南書