SQLite3でSafariの履歴を読み出す
技術情報2Safari
/ History
SQLite3でSafariの履歴を読み出す 
はじめに
WebブラウザのSafariの履歴をCSVで取り出す．監査目的．
フォルダへの移動
Safariの履歴はSQLite3データベース形式で保存されており，各ユーザのLibraryディレクトリ配下に設置されている．
このディレクトリは，macOS X 10.15(Mojave)以降はコマンドで操作できないので，作業お葉ディレクトリにコピーした上で，実行する．
まずは，Finder上で，フォルダに移動する．
チルダで監査対象ユーザのユーザディレクトリを指定する．(/Libraryではない点に注意)
Finderでファイルをコピーする．
ターミナルから，確認する．
MBA2013:SafariHistory ujpadmin$ ls -la🆑 total 12616 drwxr-xr-x 6 ujpadmin staff 192 5 25 16:50 . drwxr-xr-x 7 ujpadmin staff 224 5 25 16:54 .. -rw-r--r--@ 1 ujpadmin staff 126976 5 25 13:29 History.db -rw-r--r--@ 1 ujpadmin staff 0 4 16 12:31 History.db-lock -rw-r--r--@ 1 ujpadmin staff 32768 5 25 13:29 History.db-shm -rw-r--r--@ 1 ujpadmin staff 5524952 5 25 16:40 History.db-wal MBA2013:SafariHistory ujpadmin$
コピー完了．
SQLite3に接続する
macOS XにはデフォルトでSQLite3が導入されいるので，これを開く．
MBA2013:SafariHistory ujpadmin$ sqlite3 History.db🆑 SQLite version 3.24.0 2018-06-04 14:10:15 Enter ".help" for usage hints. sqlite>
保管されているテーブルの一覧を確認する．
sqlite> .tables🆑 history_client_versions history_items history_tombstones history_event_listeners history_items_to_tags history_visits history_events history_tags metadata sqlite>
SQLite3のコマンドモニタの表示を行モードにする．
sqlite> .mode line🆑 sqlite>
今回利用するテールブの構造を確認する．
sqlite> select * from sqlite_master where name = 'history_items';🆑
type = table
name = history_items
tbl_name = history_items
rootpage = 2
sql = CREATE TABLE history_items (id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT NOT NULL UNIQUE,domain_expansion TEXT NULL,
visit_count INTEGER NOT NULL,daily_visit_counts BLOB NOT NULL,weekly_visit_counts
BLOB NULL,autocomplete_triggers BLOB NULL,should_recompute_derived_visit_counts
INTEGER NOT NULL,visit_count_score INTEGER NOT NULL)
sqlite> ２つめ．
sqlite> select * from sqlite_master where name = 'history_visits';🆑
type = table
name = history_visits
tbl_name = history_visits
rootpage = 5
sql = CREATE TABLE history_visits (id INTEGER PRIMARY KEY AUTOINCREMENT,
history_item INTEGER NOT NULL REFERENCES history_items(id) ON DELETE CASCADE,
visit_time REAL NOT NULL,title TEXT NULL,load_successful BOOLEAN NOT NULL DEFAULT 1,
http_non_get BOOLEAN NOT NULL DEFAULT 0,synthesized BOOLEAN NOT NULL DEFAULT 0,
redirect_source INTEGER NULL UNIQUE REFERENCES history_visits(id) ON DELETE CASCADE,
redirect_destination INTEGER NULL UNIQUE REFERENCES history_visits(id) ON DELETE CASCADE,
origin INTEGER NOT NULL DEFAULT 0,generation INTEGER NOT NULL DEFAULT 0,
attributes INTEGER NOT NULL DEFAULT 0,score INTEGER NOT NULL DEFAULT 0)
sqlite>
テーブルの定義は確認できた．
Safariの履歴をCSV形式で書き出す
表示モードをCSV形式に設定する．
sqlite> .mode csv🆑 sqlite>
出力結果を，CSVファイルに出力する設定とする．
sqlite> .output SafariHistory.csv🆑 sqlite>
一行目に項目のヘッダを書き出す．
sqlite> .headers on🆑 sqlite>
次のSQL文を実行する．
sqlite> select datetime(v.visit_time + 978307200, 'unixepoch', 'localtime') as date, i.domain_expansion, v.title, i.url from history_items i left join history_visits v on i.id = v.history_item order by date desc;🆑 sqlite>
このSQLにある978307200は，1970年1月1日0時から2001年1月1日0時までの差．2001年1月1日0時というのは，Mac Absoulte timeという，この瞬間から刻んでいる絶対時間．macOSやiOSなどだけに流用できる．
実行した後は，Control+DでSQLite3のモニターモードを抜ける．
sqlite> ^D MBA2013:SafariHistory ujpadmin$
取り出したCSVファイルを確認．
MBA2013:SafariHistory ujpadmin$ ls -la SafariHistory.csv -rw-r--r-- 1 ujpadmin staff 36552 5 25 16:59 SafariHistory.csv MBA2013:SafariHistory ujpadmin$
CSVファイルに，Safariの履歴が保存されていることが確認できる．
Counter: 2187,
today: 1,
yesterday: 0
最終更新: 2020-05-26 (火) 00:59:50 (JST) (1880d) by nobuaki
