Batch replace http links with https in places.sqlite?

[MuffinMan123]

I was hoping the places file has a text editor readable structure so that I could just open the file in a text editor and replace all "http://" strings with "https://", but the file format is not that straightforward as I had hoped.

What's the easiest way to accomplish what I want to do?

[morat]

You could edit the places.sqlite file with the sqlite3 utility.

http://superuser.com/questions/740083#1161667
http://kb.mozillazine.org/Places.sqlite
http://www.sqlite.org/

I think the release build is using sqlite version 3.26.0.

http://dxr.mozilla.org/mozilla-release/ ... ersion.txt
http://dxr.mozilla.org/mozilla-release/ ... TE_VERSION

Code: Select all

sqlite3 places.sqlite .dump > places.txt
notepad places.txt

Code: Select all

sqlite3 places.sqlite
select url from moz_places where url like 'http:%';
update moz_places set url=replace(url, 'http:', 'https:') where url like 'http:%';
select url from moz_places where url like 'http:%';
.exit

The places.sqlite file includes a number of tables. The moz_places table is for sites visited.

Remember to make a backup of the places.sqlite file.

[Benjamin Markson]

What's the easiest way to accomplish what I want to do?

I've not done this for a while but in my notes I have:

Code: Select all

UPDATE OR IGNORE moz_places SET url="https" || SUBSTR(url, 5), url_hash = hash(url) WHERE url LIKE "http://www.example.com/%";

I'm pretty sure it's important to update the url_hash column (as a hash of the url) otherwise things don't work properly. A little research suggests that the hash function is not a common SQLite fuction, so, now the ironic part. You can do this using Firefox's SQLite Manager which, of course, you'll only get to work using a much earlier (i.e. more capable) version of Firefox. I'd recommend FF ESR 52.9.0 - everyone who wants to use more powerful add-on tools for occasional use should keep a copy of this browser version.

As Morat points out make a backup first. The places.sqlite can be easily restored if you break it.

Ben.

[Brummelchen]

about how many bookmarks you speak of?
100, 1000, 10.000?
not all servers are on ssl yet.

for me and my second profil i changed all 10.000++ by search and hand. i wont let work any other program on my places, to much value in it.

[morat]

I didn't know about the url_hash column. What a pain...

More info
http://superuser.com/questions/1290337
http://github.com/bencaradocdavies/sqli ... a-url-hash
http://gist.github.com/boppreh/a9737acb ... 77b40efe71

[jscher2000]

Maybe it would be easier to:

* Create a JSON backup of your bookmarks, storing one copy safely in case of emergency (https://support.mozilla.org/en-US/kb/re ... -move-them)
* Open the JSON file in a text editor and use Find and Replace for http:// to https://
* Use Firefox's Restore feature to replace your current bookmarks with the edited ones
* Discover that many of the URLs do not work
* Restore the backup you made in the first step and install HTTPS Everywhere instead

[Benjamin Markson]

Create a JSON backup of your bookmarks...

The moz_places table carries history. I thought we were talking about history. The reason I've done this kind of update in the past is that when a site switches from http to https you will lose all of your visited highlighting. Which can be really annoying.

But you may be right!

Ben.

[MuffinMan123]

Yeah you are right, export and import json is way faster than editing sqlite. Did it in less than a minute without installing anything.

[jscher2000]

Create a JSON backup of your bookmarks...

The moz_places table carries history. I thought we were talking about history. The reason I've done this kind of update in the past is that when a site switches from http to https you will lose all of your visited highlighting. Which can be really annoying.

I thought bookmarks were more likely, but I didn't think of your use case.

[costark]

With the intended mod being some trial & error, and maybe preferable, How Close does the Extension - HTTPS Everywhere - come to doing the Same?
It "reads" like it's the same ( forces use of Https where supported when Default is Http ) but I'm Not that technical.

https://www.eff.org/https-everywhere

[jscher2000]

It seems that HTTPS Everywhere checks whether the server is listening on port 443 and if it is, it modifies the URL to use HTTPS. This sometimes causes problems when Firefox applies a stricter standard to the connection than the extension does.