sqlite3 のSECURE DELETE

始めてFNG09参加させていただきました。いろいろと勉強になった。
このネタははてなに書くように！ と主催者の方から言われたので記しておきます。

で、勉強会の中でFirefoxのプラグインのSQLite Managerからデータを消すと、ゼロで上書きされるよねー。SECURE DELETE有効なのかナーという話があったのですが、sqliteのバージョンによって差があるようで、最近のものは基本はSECURE DELETEが有効になっているようです。

http://www.sqlite.org/changes.html#version_3_6_21

The SQLITE_SECURE_DELETE compile-time option fixed to make sure that
content is deleted even when the truncate optimization applies.

バージョン違いの差を以下に
手元のMacのsqlite3(3.6.12)だとこんな感じ。

mac% sqlite3 hoge.sqlite3 'create table hoge(aaa text, bbb text, ccc text);'
mac% sqlite3 hoge.sqlite3 'insert into hoge values("aaa","bbb","ccc");'
mac% hexdump -C hoge.sqlite3 
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  08 00 01 01 00 40 20 20  00 00 00 02 00 00 00 00  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 01  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000060  00 00 00 00 0d 00 00 00  01 07 bb 00 07 bb 00 00  |................|
00000070  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
000007b0  00 00 00 00 00 00 00 00  00 00 00 43 01 06 17 15  |...........C....|
000007c0  15 01 6b 74 61 62 6c 65  68 6f 67 65 68 6f 67 65  |..ktablehogehoge|
000007d0  02 43 52 45 41 54 45 20  54 41 42 4c 45 20 68 6f  |.CREATE TABLE ho|
000007e0  67 65 28 61 61 61 20 74  65 78 74 2c 20 62 62 62  |ge(aaa text, bbb|
000007f0  20 74 65 78 74 2c 20 63  63 63 20 74 65 78 74 29  | text, ccc text)|
00000800  0d 00 00 00 01 07 f1 00  07 f1 00 00 00 00 00 00  |................|
00000810  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000ff0  00 0d 01 04 13 13 13 61  61 61 62 62 62 63 63 63  |.......aaabbbccc|
00001000
mac% sqlite3 hoge.sqlite3 'delete from hoge;' 
mac% hexdump -C hoge.sqlite3 
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  08 00 01 01 00 40 20 20  00 00 00 03 00 00 00 00  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 01  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000060  00 00 00 00 0d 00 00 00  01 07 bb 00 07 bb 00 00  |................|
00000070  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
000007b0  00 00 00 00 00 00 00 00  00 00 00 43 01 06 17 15  |...........C....|
000007c0  15 01 6b 74 61 62 6c 65  68 6f 67 65 68 6f 67 65  |..ktablehogehoge|
000007d0  02 43 52 45 41 54 45 20  54 41 42 4c 45 20 68 6f  |.CREATE TABLE ho|
000007e0  67 65 28 61 61 61 20 74  65 78 74 2c 20 62 62 62  |ge(aaa text, bbb|
000007f0  20 74 65 78 74 2c 20 63  63 63 20 74 65 78 74 29  | text, ccc text)|
00000800  0d 00 00 00 00 08 00 00  07 f1 00 00 00 00 00 00  |................|
00000810  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000ff0  00 0d 01 04 13 13 13 61  61 61 62 62 62 63 63 63  |.......aaabbbccc|
00001000

新しめのsqlite3(3.6.21-2)での結果(Thanks yoggy)

yoggy@ne:~$ sqlite3 hoge.sqlite3 'create table hoge(aaa text, bbb text, ccc text);'
yoggy@ne:~$ sqlite3 hoge.sqlite3 'insert into hoge values("aaa","bbb","ccc");'
yoggy@ne:~$ hexdump -C hoge.sqlite3
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  04 00 01 01 00 40 20 20  00 00 00 02 00 00 00 00  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 01  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000060  00 00 00 00 0d 00 00 00  01 03 bb 00 03 bb 00 00  |................|
00000070  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
000003b0  00 00 00 00 00 00 00 00  00 00 00 43 01 06 17 15  |...........C....|
000003c0  15 01 6b 74 61 62 6c 65  68 6f 67 65 68 6f 67 65  |..ktablehogehoge|
000003d0  02 43 52 45 41 54 45 20  54 41 42 4c 45 20 68 6f  |.CREATE TABLE ho|
000003e0  67 65 28 61 61 61 20 74  65 78 74 2c 20 62 62 62  |ge(aaa text, bbb|
000003f0  20 74 65 78 74 2c 20 63  63 63 20 74 65 78 74 29  | text, ccc text)|
00000400  0d 00 00 00 01 03 f1 00  03 f1 00 00 00 00 00 00  |................|
00000410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
000007f0  00 0d 01 04 13 13 13 61  61 61 62 62 62 63 63 63  |.......aaabbbccc|
00000800
yoggy@ne:~$ sqlite3 hoge.sqlite3 'delete from hoge;'
yoggy@ne:~$ hexdump -C hoge.sqlite3
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  04 00 01 01 00 40 20 20  00 00 00 03 00 00 00 00  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 01  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000060  00 00 00 00 0d 00 00 00  01 03 bb 00 03 bb 00 00  |................|
00000070  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
000003b0  00 00 00 00 00 00 00 00  00 00 00 43 01 06 17 15  |...........C....|
000003c0  15 01 6b 74 61 62 6c 65  68 6f 67 65 68 6f 67 65  |..ktablehogehoge|
000003d0  02 43 52 45 41 54 45 20  54 41 42 4c 45 20 68 6f  |.CREATE TABLE ho|
000003e0  67 65 28 61 61 61 20 74  65 78 74 2c 20 62 62 62  |ge(aaa text, bbb|
000003f0  20 74 65 78 74 2c 20 63  63 63 20 74 65 78 74 29  | text, ccc text)|
00000400  0d 00 00 00 00 04 00 00  00 00 00 00 00 00 00 00  |................|
00000410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
 *
00000800

FirefoxあたりとかChromeあたりは新しいバージョンのモジュールを使っていて、勉強会で出ていたツールは古いバージョンで実装されているという差のようです。

• PupSQLite（https://www.eonet.ne.jp/~pup/）

↑勉強会で使っていたこちらのツールは↓の3.6.16のsqliteのようです。

• http://sqlite.phxsoftware.com/

Forensics的には今後厳しくなるのかなぁ(笑)
VACUUMでウィンドウごと移動したときの残骸とかだと残って解析しやすくなる？