SQLiteはファイルベースのオープンソースRDBMSです。オープンソースとしては珍しいパブリックドメインライセンスを採用しています。SQLiteはファイルベースなのでデータベースサーバーが必要なく手軽に利用できます。SQLiteは組み込みデバイスで広く利用され、Android/iOSなどでは標準的なデータベースとして利用されています。モバイルデバイス以外での利用も広がっており例えば、Drupal8はSQLite3に対応しています。
普通のRDBMSのようにSQLクエリが利用できるのでとても便利ですが、SQLiteの仕様は他のRDBMSと異なるので注意が必要です。
追記：論理的・体系的セキュリティを構築していれば、ここに書かれているようなセキュリティ上問題となる事を自身で分析／対応できるようになります。
SQLiteの仕様
最も特徴的なSQLiteの仕様は「型にはまらないカラム」です。
SQLite3は以下のデータ型を持っており、次のような形式で保存されます。
- ヌル型(NULL)：NULL値
- 整数型(INTEGER)：符号付 1バイト、2バイト、4バイト、8バイト、自動選択
- 浮動小数点型(REAL)：8バイト IEEE
- テキスト(TEXT)：UTF-8, UTF-16BEまたはUTF-16LE
- ラージオブジェクト(BLOB)：バイトをそのまま保存
SQLite3にはデータ型がありますが、カラムは型にはまらない”コンテナ”を持っています。SQLite3のカラムはデータ型を持っているのではなく、型親和性を持っています。
型親和性（Type Affinity）
元々SQLiteはデータ型はないデータベースでしたが、SQLite3でデータ型が強化されました。データ型が強化された、とは言っても一般のデータベースのようにカラムのデータ型が静的に決まりません。他のデータベースとの互換性を高めるため、SQLite3のカラムは型親和性という概念を持っています。
- TEXT – NULL, TEXTまたはBLOBデータ型で保存。数値型データはテキストに変換して保存
- NUMERIC – 5つ全ての型親和性を利用
- INTEGER – 5つ全ての型親和性を利用。キャストの動作がNUMERICと異なる
- REAL – NUMERICとほぼ同じ。整数型を浮動小数点型に変換する
- NONE – 他のストレージクラスへの変換を強要しない
簡単に説明すると、SQLiteはカラムにデータ型を指定しても、そんなモノは無視して文字列型のデータとして何でも保存できてしまいます。
データ型名と型親和性
| データ型名 | SQLiteの型親和性 |
|---|---|
| INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER |
| CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB | TEXT |
| BLOB no datatype specified | NONE |
| REAL DOUBLE DOUBLE PRECISION FLOAT | REAL |
| NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME | NUMERIC |
結局どういう動作なのか？
SQLite2は「型なし」でカラムデータをテキストとして保存していました。他のデータベースとの互換性のためだけにデータ型は存在し、実際にはデータ型は存在せずどんなテキストデータでも保存できます。
SQLite3は「型親和性」の概念が導入され、データをネイティブの整数、浮動小数点として保存できるようになりました。しかし、すでに解説した型親和性から分かるようにSQLite3のSQLite2と同じように、スキーマ定義のデータ型とは関係なくどのようなデータでも保存でき、そのまま出力されます。例えば、
数値型のカラムにテキストを保存し、テキストを取り出せる
これが可能であるのがSQLiteの仕様です。
SQLite3のカラムで「例外」は整数型プライマリーキーのみです。整数型プライマリーキーは常に8バイト整数として保存されます。
セキュリティ上の問題
データベースのカラムが数値型や日付型だから絶対に任意文字列は保存されないから安全、と勘違いしていると問題になります。
数値カラムに文字列が入ることはセキュリティ上の脅威になります。例えば
$user_id = (int) [データベースに文字列として保存されたユーザーID];
のようなコードがある場合、$user_id は “0”（整数）になります。このような動作がセキュリティ上の問題となるアプリケーションは少くありません。
プリペアードクエリを利用しているだけではこの問題には対処できません。確実な入力バリデーションは知らずにこのような問題を作ってしまった場合に非常に有効なセキュリティ策として機能します。
まとめ
SQLite3を利用する場合、整数型のカラムの結果は整数、浮動小数点型は浮動小数点、日付型は日付など、のデータであることを仮定できません。SQLiteを利用する場合、カラムに保存するデータの種類を保証するにはバリデーションが必要です。アプリレベルでの入力バリデーションに合わせて、SQLiteに出力する直前にバリデーションすると完璧です。最近のMVCモデルのWebフレームワークならモデルでのバリデーションも簡単です。
詳しくはSQLiteのマニュアル参照してください。
追記：
セキュリティ上の問題も書いていたつもりで書いていなかったので追記しました。
Leave a Comment