職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している(いた)組織の意見ではない。

医療情報サイト WELQ(ウェルク)への自分自身のアクセス記録を調べる

医療情報のキュレーションサイト「WELQ」(ウェルク)について、適切でない情報が多く掲載され、かつ強力なSEO施策が実行されていた結果として検索結果の上位に食い込んでいた、とされています。
本日飛び込んできたニュースの中に、当該サイト、および、運営会社であるDeNAが他に運用している多くのキュレーションサイトが閉鎖された、というものがありました。
掲載されていたコンテンツにどれほど適切でない情報が掲載されていたのか、今となっては調べるのも面倒臭いですが、医療にそれほど詳しくない我々がそれらのサイトの情報を知らないうちに見ていて、知らないうちに鵜呑みにしている可能性も十分に考えられます。
そのような情報が脳内に存在していたとしても直ちに自分が死ぬ訳ではありませんが、どれほど自分がアクセスしていたのか、知っておいて損することは無いでしょう。

自分が問題のあるサイトにどれほどアクセスしていたのかは、ブラウザの履歴を辿って行けばわかります。しかし、全てを人間の目でチェックすることは極めて困難です。ならば、機械的に調べてみようではありませんか。
この記事では、例として、MacFirefoxの履歴を調査する方法をご紹介します。

免責

ここで紹介する操作は、ブラウザやOSのバージョンなどが異なれば動作しないこともあります。また、ちょっとした誤操作によってブラウザ等のデータを破壊する危険性もあります。他の記事も同様ですが、この記事を参考にして操作をした結果、何らかの損害が発生したとしても筆者は一切責任を負いません。試す際は十分注意してください。


環境の準備

ここで紹介する操作は、Macの「ターミナル」の操作が必要です。俗に言う「黒い画面」です。
また、MacFirefoxでは、履歴などのデータは SQLite のファイルに記録されています。ここから先の操作では SQLite のツールが必要です。SQLiteのコマンドがインストールされていることを確認して下さい。

$ which sqlite3
/usr/bin/sqlite3

筆者は明示的にSQLiteのツールをインストールした覚えはありませんので、OSに最初から入っているか、それともXcodeとかのツールと一緒に入ってきたか、そんな感じだと思います。
もしもお手元の環境にインストールされていないようであれば、他のサイトで調べてみて下さい。

履歴などの情報の格納場所を知る

Firefoxの公式のヘルプページ によれば、

  • 履歴などの情報は places.sqlite というファイルに記録されている
  • このようなファイルが置かれている場所は、次の手順で知ることができる
    • ウィンドウ右上のハンバーガーメニューの中で「?」アイコンをクリックし、
    • 出てきたメニューの中から「トラブルシューティング情報...」を選択して、
    • 出てきたページの中に「プロファイルフォルダ」という項目があるので、
    • そこで「Finder に表示」ボタンを押す

ということらしいです。
最終的にFinderのウィンドウの中で places.sqlite ファイルを見つけることができるでしょう。
このファイルを直接見ても良いのですが、誤操作して壊すとアレなので、このファイルを別のフォルダにコピーしましょう。
そして、そのコピー先のフォルダに、ターミナルで移動しましょう。

sqlite ファイル内のテーブル設置状況を知る

ターミナルの中で、 sqlite3 コマンドを使って places.sqlite ファイルを開いて、操作してみましょう。
ここまでの手順をきちんと踏んでいると、sqlite3 ./places.sqlite コマンドの実行によって、次のような結果が得られます。

$ sqlite3 ./places.sqlite
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite>

ここから先はbash(環境による)ではなくて、sqliteのコマンドを実行することになります。
主要なものを紹介しておきましょう。

  • .tables : テーブル一覧を得る
  • .schema : テーブルやインデックスの一覧を得るんだけども、CREATE 文の形式
  • .schema {tablename} : 指定したテーブルの定義を CREATE 文の形式で得る
  • .quit : sqlite 対話モードを終了してbash(環境による)に戻る。筆者は初めて使った時は、mysqlのときのクセで exit とか、psql(PostgreSQL)のクセで \q とか打ちましたが、いずれも怒られました。。
  • {SQL文}; : SQL文の実行

これだけの情報があれば、勘のいい方なら、この記事の続きは見る必要は無いかもしれません。

履歴が格納されているテーブル

筆者の環境でテーブル一覧を見ると、次のような結果が得られました。

sqlite> .tables
moz_anno_attributes  moz_historyvisits    moz_keywords       
moz_annos            moz_hosts            moz_places         
moz_bookmarks        moz_inputhistory   
moz_favicons         moz_items_annos    

この中で履歴情報を持ってそうなテーブルと言えば moz_historyvisits でしょうか。それでは、このテーブルの定義を見てみましょう。

sqlite> .schema moz_historyvisits
CREATE TABLE moz_historyvisits (  id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER);
CREATE INDEX moz_historyvisits_placedateindex ON moz_historyvisits (place_id, visit_date);
CREATE INDEX moz_historyvisits_fromindex ON moz_historyvisits (from_visit);
CREATE INDEX moz_historyvisits_dateindex ON moz_historyvisits (visit_date);

履歴情報っぽくはあるのですが、データ型がどれも整数ですね。URLは格納されていません。カラム名から推測するに、place_idが怪しいです。この参照先のテーブルは moz_places でしょうか。定義を見てみましょう。

sqlite> .schema moz_places
CREATE TABLE moz_places (
 id INTEGER PRIMARY KEY,
 url LONGVARCHAR,
 title LONGVARCHAR,
 rev_host LONGVARCHAR,
 visit_count INTEGER DEFAULT 0,
 hidden INTEGER DEFAULT 0 NOT NULL,
 typed INTEGER DEFAULT 0 NOT NULL,
 favicon_id INTEGER,
 frecency INTEGER DEFAULT -1 NOT NULL,
 last_visit_date INTEGER ,
 guid TEXT,
 foreign_count INTEGER DEFAULT 0 NOT NULL,
 url_hash INTEGER DEFAULT 0 NOT NULL
);
CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
CREATE INDEX moz_places_visitcount ON moz_places (visit_count);
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
CREATE UNIQUE INDEX moz_places_guid_uniqueindex ON moz_places (guid);
CREATE INDEX moz_places_url_hashindex ON moz_places (url_hash);

※注:カラム定義が長すぎたので途中で改行しています。

おおっ、このテーブルには url が含まれますね。そして、他のカラムの構成から、このテーブルには1つのページにアクセスすると1レコードだけ挿入され、同じページに複数回アクセスしてもレコードは増えずにupdateするという使い方をしているのだと推測されます。
なので、「自分がWELQのサイトにアクセスしたことがあるのか?」という問いは、この moz_places テーブルに、url列の値に「welq」を含むレコードが居るか居ないかで判断することが可能そうです。

筆者の環境では、次のような結果が得られました。

sqlite> select url, title from moz_places where url like '%welq%';
https://welq.jp/3087|この季節、赤ちゃんも大人も要注意。発疹やかゆみは風疹のサインかも|WELQ [ウェルク]

筆者は数ヶ月ほど前に、蕁麻疹(じんましん)で苦しんでいたことがありました。おそらくそのときに「発疹」などのキーワードで検索したのだと思います。
あー、やっぱりアクセスしていたかw


環境情報

筆者は、次のような環境で実験を行いました。

  • macOS Sierra 10.12.1
  • Firefox 50.0.2 (余談ですが、これ以前のバージョンで脆弱性が見つかってるようなので、古いのをお使いの皆さんはお早めのアップデートをお勧めします)

参考図書

Using SQLite: Small. Fast. Reliable. Choose Any Three.

Using SQLite: Small. Fast. Reliable. Choose Any Three.


さいごに

このように、機械的な操作でブラウザの履歴を調べるのは、今回の WELQ のようなサイトが出てきたときだけでなく、いろんな場面で応用させることができるでしょう。みなさんも有効活用してみて下さい!
また、他のブラウザでも似たような感じで、機械的に履歴データを取ってこれることもあるかと思います。お暇な方は他のブラウザの状況を調べると楽しいかもしれません。