Hidden data

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Hidden data

bitwyse
Hello

Many of you may already know that FireFox (and other Mozilla family
navigators like SeaMonkey) store their bookmarks in an Sqlite file
("places.sqlite").
Bookmarks may include
 - the name of the page
 - the URL
 - chosen labels
 - keywords
 - the description

The last 2 are extracted from the page header.

You _can_ edit the description - some programmers just use the title and
others the first line(s) of the text (I've even seen about half of the
page!) - but it's not much use - you can't search in it.
So I don't regret that it is no longer visible nor editable in the
latest versions of the bookmark manager. However the data REMAINS in the
file, although it isn't displayed in a basic viewer (the FireFox Sqlite
manager extension or DB sqlite viewer).
There is a field "bookmarkProperties/description" in the
"moz_anno_attributes" table but I can't find the corresponding data.

Could it be hidden in a Blob?
What program (command?) would diplay it and allow deleting it?
(VACUUMing doesn't remove it.)

(I have got rid of it by exporting the bookmarks to an HTML file,
deleting all the <dd> tag lines and then re-importing it - but that's
laborious and I would like to be able to do it in one operation.)

Regards
Christophe

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

Simon Slavin-3
On 4 Aug 2019, at 5:33pm, bitwyse <[hidden email]> wrote:

> Could it be hidden in a Blob?
> What program (command?) would diplay it and allow deleting it?
> (VACUUMing doesn't remove it.)

There's only one program guaranteed to get at every element of a SQLite database file: the sqlite3 shell tool written and supported by the team which developed SQLite.  You can download it from the SQLite web site ("precompiled binaries") and see documentation here:

<https://sqlite.org/cli.html>

Have a poke around in the database file using it.  I'd probably start with the '.schema' command and work from there.

Once you've found what you're looking for you might be able to switch to some other program for everyday access.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

Thomas Kurz
In reply to this post by bitwyse
Have you tried dumping the database ("sqlite3 places.sqlite .dump") and then searching for some known data in the resulting SQL file?


----- Original Message -----
From: bitwyse <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Sunday, August 4, 2019, 18:33:29
Subject: [sqlite] Hidden data

Hello

Many of you may already know that FireFox (and other Mozilla family
navigators like SeaMonkey) store their bookmarks in an Sqlite file
("places.sqlite").
Bookmarks may include
 - the name of the page
 - the URL
 - chosen labels
 - keywords
 - the description

The last 2 are extracted from the page header.

You _can_ edit the description - some programmers just use the title and
others the first line(s) of the text (I've even seen about half of the
page!) - but it's not much use - you can't search in it.
So I don't regret that it is no longer visible nor editable in the
latest versions of the bookmark manager. However the data REMAINS in the
file, although it isn't displayed in a basic viewer (the FireFox Sqlite
manager extension or DB sqlite viewer).
There is a field "bookmarkProperties/description" in the
"moz_anno_attributes" table but I can't find the corresponding data.

Could it be hidden in a Blob?
What program (command?) would diplay it and allow deleting it?
(VACUUMing doesn't remove it.)

(I have got rid of it by exporting the bookmarks to an HTML file,
deleting all the <dd> tag lines and then re-importing it - but that's
laborious and I would like to be able to do it in one operation.)

Regards
Christophe

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

bitwyse
In reply to this post by Simon Slavin-3
Thanks to Simon and Thomas for their suggestions - I'll work on it!

Regards
Christophe

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

Keith Medcalf
In reply to this post by bitwyse

You mistakenly assumed that the page description is an attribute of the bookmark rather than of the page ... The list of all pages ever visited is stored in the moz_places table and one of the attributes (columns) in that table is the description of the page ...

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of bitwyse
>Sent: Sunday, 4 August, 2019 10:33
>To: [hidden email]
>Subject: [sqlite] Hidden data
>
>Hello
>
>Many of you may already know that FireFox (and other Mozilla family
>navigators like SeaMonkey) store their bookmarks in an Sqlite file
>("places.sqlite").
>Bookmarks may include
> - the name of the page
> - the URL
> - chosen labels
> - keywords
> - the description
>
>The last 2 are extracted from the page header.
>
>You _can_ edit the description - some programmers just use the title
>and
>others the first line(s) of the text (I've even seen about half of
>the
>page!) - but it's not much use - you can't search in it.
>So I don't regret that it is no longer visible nor editable in the
>latest versions of the bookmark manager. However the data REMAINS in
>the
>file, although it isn't displayed in a basic viewer (the FireFox
>Sqlite
>manager extension or DB sqlite viewer).
>There is a field "bookmarkProperties/description" in the
>"moz_anno_attributes" table but I can't find the corresponding data.
>
>Could it be hidden in a Blob?
>What program (command?) would diplay it and allow deleting it?
>(VACUUMing doesn't remove it.)
>
>(I have got rid of it by exporting the bookmarks to an HTML file,
>deleting all the <dd> tag lines and then re-importing it - but that's
>laborious and I would like to be able to do it in one operation.)
>
>Regards
>Christophe
>
>--
>bitwyse   [PGP KeyID 0x18EB38C4]
>Les conseils - c'est ce qu'on demande
>quand on connaît déjà la réponse
>mais aurait préféré ne pas la savoir.
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

bitwyse
Keith Medcalf a écrit :
>
> You mistakenly assumed that the page description is an attribute of the bookmark rather than of the page ... The list of all pages ever visited is stored in the moz_places table and one of the attributes (columns) in that table is the description of the page ...

The bookmarks are stored in places.sqlite together with their associated
descriptions, independently of the navigation history.
https://bugzilla.mozilla.org/show_bug.cgi?id=1402890

No description column is visible in moz_places in a normal
(non-forensic) viewer.

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

Keith Medcalf

Using an ordinary client to access the database ... like the one you can download from www.sqlite.org

The only "description" field is an attribute (column) with the moz_places table.  There is no "description" in the moz_bookmarks table.  This is Firefox 68.0.1.  Perhaps two years ago there was an attribute (column) in the moz_bookmarks table called description but there is no more.

>sqlite places.sqlite
SQLite version 3.30.0 2019-08-04 01:51:00
Enter ".help" for usage hints.
sqlite> .schema --indent
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 ,
  description TEXT,
  preview_image_url TEXT,
  origin_id INTEGER REFERENCES moz_origins(id)
);
CREATE TABLE moz_historyvisits(
  id INTEGER PRIMARY KEY,
  from_visit INTEGER,
  place_id INTEGER,
  visit_date INTEGER,
  visit_type INTEGER,
  session INTEGER
);
CREATE TABLE moz_inputhistory(
  place_id INTEGER NOT NULL,
  input LONGVARCHAR NOT NULL,
  use_count INTEGER,
  PRIMARY KEY(place_id, input)
);
CREATE TABLE moz_hosts(
  id INTEGER PRIMARY KEY,
  host TEXT NOT NULL UNIQUE,
  frecency INTEGER,
  typed INTEGER NOT NULL DEFAULT 0,
  prefix TEXT
);
CREATE TABLE moz_bookmarks(
  id INTEGER PRIMARY KEY,
  type INTEGER,
  fk INTEGER DEFAULT NULL,
  parent INTEGER,
  position INTEGER,
  title LONGVARCHAR,
  keyword_id INTEGER,
  folder_type TEXT,
  dateAdded INTEGER,
  lastModified INTEGER,
  guid TEXT,
  syncStatus INTEGER DEFAULT 0 NOT NULL,
  syncChangeCounter INTEGER DEFAULT 1 NOT NULL
);
CREATE TABLE moz_keywords(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  keyword TEXT UNIQUE,
  place_id INTEGER,
  post_data TEXT
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE moz_anno_attributes(
  id INTEGER PRIMARY KEY,
  name VARCHAR(32) UNIQUE NOT NULL
);
CREATE TABLE moz_annos(
  id INTEGER PRIMARY KEY,
  place_id INTEGER NOT NULL,
  anno_attribute_id INTEGER,
  mime_type VARCHAR(32) DEFAULT NULL,
  content LONGVARCHAR,
  flags INTEGER DEFAULT 0,
  expiration INTEGER DEFAULT 0,
  type INTEGER DEFAULT 0,
  dateAdded INTEGER DEFAULT 0,
  lastModified INTEGER DEFAULT 0
);
CREATE TABLE moz_items_annos(
  id INTEGER PRIMARY KEY,
  item_id INTEGER NOT NULL,
  anno_attribute_id INTEGER,
  mime_type VARCHAR(32) DEFAULT NULL,
  content LONGVARCHAR,
  flags INTEGER DEFAULT 0,
  expiration INTEGER DEFAULT 0,
  type INTEGER DEFAULT 0,
  dateAdded INTEGER DEFAULT 0,
  lastModified INTEGER DEFAULT 0
);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE moz_bookmarks_deleted(
  guid TEXT PRIMARY KEY,
  dateRemoved INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE moz_meta(key TEXT PRIMARY KEY, value NOT NULL) WITHOUT ROWID;
CREATE TABLE moz_origins(
  id INTEGER PRIMARY KEY,
  prefix TEXT NOT NULL,
  host TEXT NOT NULL,
  frecency INTEGER NOT NULL,
  UNIQUE(prefix, host)
);
CREATE INDEX moz_places_url_hashindex ON moz_places(url_hash);
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_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);
CREATE INDEX moz_bookmarks_itemindex ON moz_bookmarks(fk, type);
CREATE INDEX moz_bookmarks_parentindex ON moz_bookmarks(parent, position);
CREATE INDEX moz_bookmarks_itemlastmodifiedindex ON moz_bookmarks(
  fk,
  lastModified
);
CREATE UNIQUE INDEX moz_bookmarks_guid_uniqueindex ON moz_bookmarks(guid);
CREATE UNIQUE INDEX moz_keywords_placepostdata_uniqueindex ON moz_keywords(
  place_id,
  post_data
);
CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos(
  place_id,
  anno_attribute_id
);
CREATE UNIQUE INDEX moz_items_annos_itemattributeindex ON moz_items_annos(
  item_id,
  anno_attribute_id
);
CREATE INDEX moz_bookmarks_dateaddedindex ON moz_bookmarks(dateAdded);
CREATE INDEX moz_places_originidindex ON moz_places(origin_id);
sqlite>

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of bitwyse
>Sent: Sunday, 4 August, 2019 12:55
>To: [hidden email]
>Subject: Re: [sqlite] Hidden data
>
>Keith Medcalf a écrit :
>>
>> You mistakenly assumed that the page description is an attribute of
>the bookmark rather than of the page ... The list of all pages ever
>visited is stored in the moz_places table and one of the attributes
>(columns) in that table is the description of the page ...
>
>The bookmarks are stored in places.sqlite together with their
>associated
>descriptions, independently of the navigation history.
>https://bugzilla.mozilla.org/show_bug.cgi?id=1402890
>
>No description column is visible in moz_places in a normal
>(non-forensic) viewer.
>
>--
>bitwyse   [PGP KeyID 0x18EB38C4]
>Les conseils - c'est ce qu'on demande
>quand on connaît déjà la réponse
>mais aurait préféré ne pas la savoir.
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

bitwyse
Keith Medcalf a écrit :
>
> The only "description" field is an attribute (column) with the moz_places table.  There is no "description" in the moz_bookmarks table.

There is NO description column in the moz_places table in my "places.sqlite"

sqlite> .schema --indent
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
);

The last 3 columns you saw are only created by new versions.
You overlooked the fact that I clearly stated that the data remains in
files created by earlier releases; in which the descriptions are
attached to the bookmarks and not to the history.

(I use the latest release of SeaMonkey (2.49.4): "places.sqlite" is
totally compatible with FireFox.)


In any case your reply is not relevant to the problem I described.

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

Adrian Ho
In reply to this post by bitwyse
On 5/8/19 12:33 AM, bitwyse wrote:
> There is a field "bookmarkProperties/description" in the
> "moz_anno_attributes" table but I can't find the corresponding data.

I suspect that's the key right there. I don't have any descriptions
stored in my places.sqlite, but this may surface something for you:

$ sqlite3 places.sqlite
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.

sqlite> .schema moz_annos
CREATE TABLE moz_annos (id INTEGER PRIMARY KEY,place_id INTEGER NOT
NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT
NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER
DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT
0,lastModified INTEGER DEFAULT 0);
CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos
(place_id, anno_attribute_id);

sqlite> .schema moz_anno_attributes
CREATE TABLE moz_anno_attributes (id INTEGER PRIMARY KEY,name
VARCHAR(32) UNIQUE NOT NULL);

The field names suggest a "foreign key" relationship of sorts between
moz_annos:anno_attribute_id and moz_anno_attribute:id, so...

sqlite> select * from moz_anno_attributes;
3|bookmarkProperties/description
4|URIProperties/characterSet
17|downloads/destinationFileURI
19|downloads/metaData
20|mobile/bookmarksRoot
22|Places/SmartBookmark

sqlite> select content from moz_annos where anno_attribute_id=3;

--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

bitwyse
Thank you, Adrian, that's really helpful.

Regards
Christophe

--
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Hidden data

J Decker
In reply to this post by Simon Slavin-3
On Sun, Aug 4, 2019 at 9:40 AM Simon Slavin <[hidden email]> wrote:

> On 4 Aug 2019, at 5:33pm, bitwyse <[hidden email]> wrote:
>
> > Could it be hidden in a Blob?
> > What program (command?) would diplay it and allow deleting it?
> > (VACUUMing doesn't remove it.)
>
> There's only one program guaranteed to get at every element of a SQLite
> database file: the sqlite3 shell tool written and supported by the team
> which developed SQLite.  You can download it from the SQLite web site
> ("precompiled binaries") and see documentation here:
>
Except the end of those that have a \0 in them.


>
> <https://sqlite.org/cli.html>
>
> Have a poke around in the database file using it.  I'd probably start with
> the '.schema' command and work from there.
>
> Once you've found what you're looking for you might be able to switch to
> some other program for everyday access.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users