これで脱初心者！初心者がハマりやすい『SQLアンチパターン』解説

こんにちは、まさきです。

本日はデータベース関連の技術書であるSQLパターンを読んだのでご紹介します。

リレーショナルデータベースを使用するシステム開発では、陥りやすい失敗（アンチパターン）があります。
カテゴリとしてはデータベース論理設計、物理設計といった設計の部分や、
クエリの記述、アプリケーション開発といった開発時のものがあります。

本書はDB設計やSQL記述時のそれぞれのカテゴリにおける避けるべき事柄（アンチパターン）を紹介し、失敗を避けるようなより良い方法を紹介しています。

今回はその中で初心者が知っておきたい事象を3つ、わかりやすく要約してご紹介します！

SQLアンチパターンとは

まず『SQLアンチパターン』と言う言葉が聞き慣れないと思うのでご説明します。
本書ではこのように定義しています。

アンチパターンとは、問題の解決を意図しながらも。しばしば他の問題を生じさせてしまうような技法を指します。アンチパターンは様々な形で現れますが、これらには共通点があります。
アンチパターンにつながる発想の源も、開発者が自分で思いついたもの、同僚からのアドバイス、書籍や雑誌・ウェブの記事など多岐に渡ります。

つまりアンチパターンとは、実際の開発者がシステムに機能の追加や要望の対応するときにやりがちなよくないパターンを指します。

開発者はアンチパターンがなぜよくないのかを理解し、アンチパターンを避ける別の方法を知っている必要があります。

対象読者

SQLを使う必要があるソフトウェア開発者、データベース管理者全般ではありますが、
その中でSQLの経験が2,3年ぐらいある方が一番読みやすいと思います。

特にSQLが本書にもたくさん記載されているのでそういったSQLを理解して、
アンチパターン25個全て理解したいのであれば少しSQLを触っていて経験している必要があります。

しかし初心者の方でも読んでいてわからない部分も出てくると思いますが、それでも初心者のうちから知っておきたいと思った内容も半分ぐらいありました。
今回は初心者のうちから知っておきたい内容を3つご紹介するので、気になった方はぜひ次に進んでください！

アンチパターン

本書では25個挙げられておりどれも知っておきたい内容ではありますが、
1つ1つが大事な内容だったので今回は3つに絞ってご紹介します。

なおアンチパターンにはユーモラスで刺激的なタイトルが付けられることが多いため、少し変わった言い回しになっています。

(例)ジェイウォーク（信号無視）、サーティワンフレーバー、インデックスショットガン

そのおかげで個人的には覚えやすい名前でした。

SQLインジェクション

1つ目にご紹介するのはアプリケーションのセキュリティの脆弱性を突く攻撃の1つでSQLインジェクションです。

SQLインジェクションとは

アプリケーションにおいてSQLを使う場合、クエリ内容をデータによって変更したいときに変数を使い文字列を埋め込むことを動的SQLと呼びます。

SQLインジェクションとは、その動的SQLの変数に悪意のある文字列を埋め込むことで、実際の使い方と違った意図しない挙動をさせることです。

例えば、データベースからデータを抜き取ったり、データベースのデータを勝手に更新することができます。

なお、IPAのサイトでも紹介されるほど有名な攻撃手段です。

https://www.ipa.go.jp/security/vuln/websecurity-HTML-1_1.html

アンチパターンになってしまう理由

SQLインジェクションは画面から入力された値をそのままSQL文に当てはめることで、元のSQLに続けて別のSQL文が実行されることが生じます。

例えば以下のSQL文があります。
$userIdは変数です。

SELECT * FROM USERS WHERE user_id = $userId;

このとき変数$userIdに
123;DELETE FROM USERS
が入ってたとすると変数と組み合わせて以下のSQLが実行されます。

SELECT * FROM USERS WHERE user_id = 123;DELETE FROM USERS;

このSQLではデータを取得していただけなのに、データが全て削除されてしまいます。

これは開発者の意図しない結果になっています。

アンチパターンが起こす弊害

これは上記でも上げましたが、SQLが不当に実行されることにより、
データの削除、更新、取得など開発者が意図しない挙動をします。

これにより、ユーザIDとパスワードが盗まれてしまうことや、大事な顧客データが削除されることが想定されます。

そのため個人情報の流出など引き起こされる可能性があります。

そういったSQLインジェクション攻撃に対策するため、以下のいくつかの方法があります。

１つ目はSQLのクエリで意図しない文字列がSQLに解釈するのを避ける一番簡単な方法として引用符をエスケープして、SQLが途中で終了することを阻止する方法です。

SQLの標準では引用文字を２つ重ねることで１つの引用文字を表せます。

ただしこの方法は文字列以外のデータに用いることはできません。

２つ目ににSQLインジェクションの汎用的に使用されるのが、プリペアドステートメントです。

プリペアドステートメントはSQL文字列内に動的に値を挿入する代わりに、クエリの前処理（プリペア）時にパラメータの代わりに仮の値を挿入します。
そしてSQL実行時に仮の値にパラメータを渡します。

この方法ではやっていることは同じように見えますが、エスケープが不要なためエスケープを忘れる心配がありません。

プリペアドステートメントはSQLインジェクションに対して最も強力な防御手段です。
※もちろんあらゆる状況に対応できるわけではありません。

プリペアドステートメントが対応できない状況は以下です。
・値のリストを１つのパラメータにする場合
・テーブル識別子をパラメータとして扱う場合
・SQL予約語を使用する場合

３つ目にSQLインジェクションを避ける場合にストアドプロシージャを利用する方法もあります。

これらいくつかの対策がありますが、ここで一番伝えたいのはどの方法も万能ではないということです。
SQLコードの安全性を保障する唯一絶対の方法は存在しません。

アンチパターンを避ける方法

もう一度言いますがSQLコードの安全性を保障する絶対と言える方法は存在しないため、以下の方法を知り、状況に応じて適切に使い分ける必要があります。

入力のフィルタリング

ユーザからの入力に危険な文字列が含まれていないかを探るよりも、その入力から無効な文字列を初めから取り除くべきです。

エスケープもこれになります。

他には正規表現を用いて安全な文字列を判別し、無効な内容をフィルタリングするなどがあります。

動的値のパラメータ化

動的な部分がシンプルであるならSQLから分離させるためにプリペアドステートメントを使用できます。

SQLから分離するとは、例えば変数に悪意のある文字列が渡ってきたとします。

SELECT * FROM USERS WHERE user_id = 123;DELETE FROM USERS;

これを全てSQLとして解釈するのではなく分離することで、

SELECT * FROM USERS WHERE user_id = '123;DELETE FROM USERS';

といった感じに入力値を全て文字列として置き換えることで悪意のあるSQLをそのまま実行しないようにするということです。

この方法をプリペアドステートメントと言います。

ユーザの入力をコードから隔離する

エスケープやプリペアドステートメントはほとんどの場合に有効ですが、テーブル識別子、列識別子、SQL予約語には適用できません。

そういった場合はユーザの入力を直接SQLの変数に置き換えるのではなく、コードで制御します。

例えばユーザーの操作でソートのASCかDESCを変更したい場合、
「入力値とASCが等しかったらASCを代入」といったように好きな文字を入れさせず、決められた値だけがSQL文に使えるようにします。

ユーザには値の入力を許可してもコードの入力は許可しないようにしましょう！

フィア・オブ・ジ・アンノウン（恐怖のunknown）

フィア・オブ・ジ・アンノウンとは

データベースに値が状況ができることがあります。

そういった値がない(null)、不明(unknown)、適用不能(inapplicable)のときにSQLでは予約語でNULLを使用します。

SQLではNULLはゼロやFALSE、空文字とは別の特殊な扱いになっているのです。

アンチパターンになってしまう理由

このときユーザのファーストネーム、ミドルネーム、ラストネームの文字列を連結して画面に表示したいときに、以下のSQL文を書くとします。

SELECT first_name || ' ' || middle_name || ' ' || last_name FROM USERS;

しかしこのSQLではmiddle_nameがない(NULLの)場合なにも表示されなくなります。

この事象を知らずに初心者のころバグを出してしまった方は少なくないと思います。

私もNULLの扱いに悩んでいたことがありました・・・

アンチパターンが起こす弊害

式でNULLを扱う

開発者が困惑する例として、NULLを含むカラムを使った計算があります。

例えば以下のようなSQLを使用するときにhoursがNULLだったとします。

SELECT hours + 10 FROM Bugs;

NULLは0にはなりませんので、NULL+10の結果としてNULLが返されます。

またSQL標準では、文字列とNULLを連結するとNULLが返されます。

他にはNULLはFALSEとも異なっているため、AND、NOTを用いる場合にも注意が必要です。

NULLを許容する列の検索

検索時でもNULLは困惑させてきます。

例えば担当者がfooのデータを一覧で検索します。

SELECT * FROM Bugs WHERE manager = 'foo'; 

では逆にそれ以外のデータを全て取得したい場合に以下を実行しますが、
これでは担当者がNULLのデータは取得ができません。

SELECT * FROM Bugs WHERE NOT( manager = 'foo'); 

NULLを用いた比較はTRUE,FALSEではなく,すべて不明(unknown)を返します。
NULLの否定はNULLを返します。

またNULLのデータを扱いたい場合に以下を実行してしまう場合もあります。

SELECT * FROM Bugs WHERE manager = NULL; 
SELECT * FROM Bugs WHERE manager <> NULL;

しかしこれは想定した結果を返しません。
NULLを扱う場合はこちらを使用します。

SELECT * FROM Bugs WHERE manager IS NULL; 
SELECT * FROM Bugs WHERE manager IS NOT NULL;

プリペアドステートメントでNULLを扱う

プリペアドステートメントでパラメータ化したSQLでNULLを検索する場合なども難しい問題です。

SELECT * FROM Bugs WHERE manager = ?; 

この場合構文上NULLを検索することができませんし、NULLリテラルはパラメータには使えないのです。

NULLを別の値で代替する

上記の問題を避けるためNOTNULL制約を定義し、NULLを一切使わない設計をしたとします。
そしてNULLの代わりに他の値をNULLの意味として使用します。

例えば、文字列ではNULLを’NULL’で代替したり、数値ではNULLを-1で代替することです。

この方法では代わりに使用した値を特別なものとして扱うためにアプリケーションのコードを修正する必要があり、またSQL使用時にも混乱を起こさせます。

-1を代わりに使った場合SUMやAVGなどの関数が予期しない挙動を起こすからです。

アンチパターンを避ける方法

NULLの使い方を見直す必要があります。

スカラー式でのNULL

NULLの問題のほとんどは真偽値で使用する場合にNULLはどのような挙動をするか知らないことです。

SQLでは3値論理のため、プログラミング言語での2値論理に慣れているプログラマーは間違えやすいということです。

以下の式では予想した結果と実際の結果での差異です。

論理式でのNULL

上記に続き論理式でも間違えやすくなっています。
特に先ほどより予想がつきにくいものが多く、慣れが必要です。

特にNOT(NULL)はNULLを返すことは引っかかりやすいことでしょう。

NULLの検索

アンチパターンが起こす弊害でも例に挙げましたが、
NULLを検索するときは等式(=)では取得できないためIS NULLを使う必要があります。

SELECT * FROM Bugs WHERE manager IS NULL; 
SELECT * FROM Bugs WHERE manager IS NOT NULL;

また別の方法としてIS DISTINCT FROMがあります。
※現在PostpreSQL,IBM DB2, Firebirdがサポートしている

これは一般的な非等価演算子(＜＞)と同様な動きであり、さらに対象データがNULLの場合でも常にTRUEかFALSEを返します。
IS DISTINCT FROM を使うことでIS NULLの記載を省略することができます。

使う方としては以下の2つのSQLは同等な動きをします。

SELECT * FROM Bugs WHERE manager IS NULL OR manager <> 'foo';
SELECT * FROM Bugs WHERE manager IS DISTINCT FROM 'foo'; 

IS DISTINCT FROM はプリペアドステートメントでも使うことができます。

NOT NULL制約を宣言する

場合によりますが、NULLを一切使わないと言い切れる場合はNOT NULL制約を宣言するのがよいです。
アプリケーションで管理するのではなく、データベースで一貫した制約を強制する方がよい方法だと言えます。

またDEFAULTを使うことでINSERT時に値が指定されていない場合はNULLではなくデフォルト値が設定されることになります。

しかしそのカラムに適切なデフォルト値がない場合は、NOT NULL制約を付けてもデフォルト値がうまく定義できないため効率的に使える場合とそうでない場合があります。

動的なデフォルト

そもそもSQLでデータ取得時にNULLの制御をいれることで、NULLを文字列に連結したせいでデータが表示されないことを防ぐ方法です。

これにはCOALESCE関数が有効です。

COALESCE関数はNULLが返ってきた場合に別の文字列に置換します。
第1引数がNULLじゃない場合第1引数を、NULLの場合は第2引数を返します。

使い方としては、冒頭の以下のSQLを使うとこのようになります。

SELECT first_name || ' ' || middle_name || ' ' || last_name FROM USERS;
SELECT first_name || COALESCE(' ' || middle_name || ' ',' ') || last_name FROM USERS;

こうすることでNULLが返ってくるのが防げます。

NULLは正しく使いましょう！

ラウンディングエラー（丸め誤差）

ラウンディングエラーとは

テーブルに小数点を扱うカラムをFLOAT型で追加するとします。

このときに小数値の計算で誤差が発生してしまうことです。

例を挙げると、1/3の結果である0.333…といった循環小数は一定値で切り捨てられてテーブルに格納されます。

つまり、
1/3+1/3+1/3は1となりますが、これを少数で表そうとすると
0.333+0.333+0.333=0.999となります。

こういったデータに少数に置き換えたとき、表せないとき場合に発生する誤差を丸め誤差といいます。

アンチパターンになってしまう理由

このアンチパターンになってしまう理由は明確で、FLOAT型を使用してしまうことです。

多くのプログラマーは10進数で表している数値全てを2進数として格納できるわけがないという浮動小数点の特性を知りません。
FLOAT型は10進数を2進数にエンコードして格納するのです。

なのでプログラミング言語で聞き覚えがあるからといって、FLOAT型やDOUBLE型を選ばない方がよいですね。

アンチパターンが起こす弊害

1つ目は浮動小数点の誤差累積です。

小数点の桁数が大きいなら誤差は小さくてすみます。
誤差が0.1と0.0000000001では後者の方が小さく、場合によっては気にならないかもしれません。

しかし、これを掛け算に使用すると誤差が多大な影響を生み出します。
誤差がある数値に大きな値を掛け算すると誤差もその分大きくなります。
例えばお金の計算で、0.01×1億円だとすると誤差は100万円になってしまいます。

これは重大なバグになりますよね。

2つ目はSQLでのふるまいです。

59.95という数値は2進数で表す場合、循環小数が必要になります。
その結果2進数でエンコードしてテーブルに格納後、テーブルから取得すると59.950000762939が得られます。

これを意識してSELECT文でWHERE句の条件で59.95を指定しても取得することはできません。
これは実際には59.95ではなく、59.950000762939が格納されているからです。

アンチパターンを避ける方法

これに関しては1つでFLOATなどのデータ型の代わりにNUMERICまたはDECIMALを選ぶことで固定精度の小数点を表すようにします。

NUMERICまたはDECIMALを使用するとき、引数に精度とスケールを指定する必要があります。

ALTER TABLE COSTS ADD COLUMN col1 NUMERIC(9,2);

これにより小数点は指定したスケールまでの小数点以下の桁数までしか格納できなくなりますが、有理数を丸めることなく格納できるメリットがあります。

これにより小数点における誤差が発生しないため、正確な値が格納されていることが信頼できるのです。

ただしこれらを使用しても3分の1のような無限小数は格納できません。
しかし、適切な精度とスケールを選択することで、概算値を使って計算しても気にならない範囲の誤差で結果を得ることができます。

できる限りFLOAT型は使わないことにしましょう！