Re: [sqlite] Hidden data
On Sun, Aug 4, 2019 at 9:40 AM Simon Slavin wrote:
> On 4 Aug 2019, at 5:33pm, bitwyse 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.
>
>
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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- >boun...@mailinglists.sqlite.org] On Behalf Of bitwyse >Sent: Sunday, 4 August, 2019 12:55 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Hidden data > >Keith Medcalf a écrit : >> >> You mistakenl
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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-
>boun...@mailinglists.sqlite.org] On Behalf Of bitwyse
>Sent: Sunday, 4 August, 2019 10:33
>To: sqlite-users@mailinglists.sqlite.org
>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 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
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
To: sqlite-users@mailinglists.sqlite.org
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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hidden data
On 4 Aug 2019, at 5:33pm, bitwyse 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:
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
