UJP - 技術情報1

SQLite3でSafariの履歴を読み出す

更新履歴

初版　2020/05/26

はじめに

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の履歴が保存されていることが確認できる．