Searching in firefox history with any number of clue words

Rosika · August 14, 2023, 3:28pm

Hi all,

just want to let you know I´ve come up with the solution of a “problem” regarding the entries in firefox history.
Perhaps it might also be of interest to someone else.

Well, firefox provides the means for importing and exporting bookmarks. That´s a pretty well-known fact, I suppose.

I was looking for something similar for the history of already visited sites, too.
But that´s not supported by firefox out-of-the-box. Perhaps there´s a third-party add-on available for that, I don´t know.

Yet it can easily be done this way:

install sqlite3 (in case it´s not already installed on your system)

[In the following you´ll have to deal with an SQLite database file, and using a regular text editor won’t provide a meaningful view of its contents.
In order to explore the data stored in the SQLite database, you’ll need an SQLite database management tool]
close all running firefox instances
copy firefox´s places.sqlite file to some other location (it´s better to work with a copy of it since incorrect queries or modifications can potentially cause issues)

The file can be found in your firefox profile folder (normally in your home directory): .mozilla/firefox/[...].default .
From within the folder where you copied the places.squlite file to: issue the terminal command

  sqlite3 places.sqlite "SELECT url FROM moz_places;" > history_urls.txt

Now you have a text file you can work with. It lists all sites you visited in in chronological order.
You can use the sort command to get it in alphabetical order, if you want to.

Now, here comes a real advantage over accessing history from within the firefox browser itself:

Say you want to see the entries using more than just one search word, like two for example. Firefox itself would encounter difficulties performing such a task.

Here´s how I do it:
(this is a personal example, paths do vary in your systems, of course)

here´s my path to work with:

/media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/DATEN-PARTITION/Dokumente/Sicherungen/Sicherungen_für_jammy/history_urls.txt

I want to list the pages I visited looking for the Debian OS on the ventoy site
now I issue the following command within a terminal:

cat /media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/DATEN-PARTITION/Dokumente/Sicherungen/Sicherungen_für_jammy/history_urls.txt | grep -i ventoy | grep -i debian

and get the following result:

https://www.ventoy.net/en/distro_iso/debian.html

This procedure will work even more satisfactorily when using fish as default shell as you´d just have to enter some meaningful clue-word (or part of it) and then “arrow up”
and there´s the command again. Like “_urls”, then “arrow up”.

I suppose there are not many people around who have to watch their data consumption, but I do and for me it´s a convenient way to get the desired result in a jiffy without having to do some extended research over and over again.

Many greetings from Rosika

kovacslt · August 14, 2023, 6:35pm

Great idea @Rosika !!!

May be we can advance it a bit?
You can put conditions right in the sql query, so the resulting text file will already contain what you look for?
Something like:

sqlite3 places.sqlite "SELECT url FROM moz_places; WHERE url LIKE "%ventoy%" OR url LIKE "%bian%"; > history_urls.txt

Some hints about LIKE: SQLite LIKE - Querying Data Based On Pattern Matching

I did not try this myself, and I wrote actual SQL code somewhat 20 years ago, so the syntax may be wrong. I just wanted to give the idea, to filter the search result right with sqlite, rather than with grep and such in post…

Rosika · August 15, 2023, 11:37am

Hi László,

Thanks for the praise and for your suggestion.

Also: thanks for providing the link.

I did a bit of research on sql syntax and came up with the following improvenments.

It seems the correct way to structure the query would be to place the WHERE clause before the semicolon
I´d have to use “AND” instead of “OR” in the command, otherwise I would get all results that match either condition (ventoy, debian)

So it seems this is the right command for achieving my goal:

sqlite3 places.sqlite "SELECT url FROM moz_places WHERE url LIKE '%ventoy%' AND url LIKE '%debian%';" > history_urls.txt

I tried it and it comes up with the result I was looking for (just one entry):

https://www.ventoy.net/en/distro_iso/debian.html

Thanks a lot for your suggestion and improvement of the command, László.

Many greetings from Rosika

silvain · August 17, 2023, 9:23am

Interesing suggestion
To search or examine sqlite files, I am using DB Browser for sqLite (on Ubuntu)
https://sqlitebrowser.org/

Rosika · August 17, 2023, 12:59pm

Hi Silvain,

thanks for mentioning that.

Yes, I also have also sqlitebrowser installed on my system.

I guess sqlitebrowser is a GUI application which is primarily designed for visualizing and managing SQLite databases.
It surely is a great tool for browsing and editing SQLite databases but it may not be suitable for directly exporting the entire browsing history in the format I described.

So sqlite3 seems to be better suited for getting the results I need.

Thanks again and many greetings from Rosika

Rosika · September 5, 2023, 2:00pm

Hi all,

Update:

After using commands like

cat /media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/DATEN-PARTITION/Dokumente/Sicherungen/Sicherungen_für_jammy/history_urls.txt | grep -i ventoy | grep -i debian

for a while - with the need to modify the 2 search parameters each time - I was looking for a more convenient way to use it on a daily basis.

I came up with the following:

I wrote this tiny script:

batcat script_firefox-history_mit_sqlite3.sh

File: script_firefox-history_mit_sqlite3.sh
───────┼────────────────────────────────────────────────────────────────────────
   1   │ #!/bin/bash
   2   │ 
   3   │ echo "ffhi is looking for a combination of $1 and $2"
   4   │ sleep 2
   5   │ cd /media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/DATEN-PARTITION/D
       │ okumente/Sicherungen/Sicherungen_für_jammy
   6   │ sqlite3 places.sqlite "SELECT url FROM moz_places WHERE url LIKE '%$1%'
       │  AND url LIKE '%$2%'"

and put it the directory

/media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/LINK-FARM/Scripte/Scripte_für_jammy/

The important part of the scripting philosophy is:

A script can adopt parameters during launch time. The parameters are located at
runtime in the variables number $1 to $9 and can be used to influence the script in its work

(Bash-Skripting-Guide für Anfänger › Shell › Wiki › ubuntuusers.de # original in German)

This leaves me with this example-command (I was looking for “debian” AND “ventoy”):

/media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/LINK-FARM/Scripte/Scripte_für_jammy/script_firefox-history_mit_sqlite3.sh debian ventoy

Better now, but still a bit clumsy.

As a second step I defined an alias (ffhi) for the command.

Here´s the fish syntax for it (use an alternative method for e.g. bash):

alias ffhi="/media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/LINK-FARM/Scripte/Scripte_für_jammy/script_firefox-history_mit_sqlite3.sh"

then:

funcsave ffhi

Now the alias (or function in fish) is defined and all I have to do is punch the following into the terminal:

ffhi [search term 1] [search term 2]

e.g.:

ffhi debian ventoy

ffhi is looking for a combination of debian and ventoy
https://www.ventoy.net/en/distro_iso/debian.html

Just one hit there, as was to be expected.

I hope looking for a combination of 2 search terms in firefox´s history should be pretty convenient that way.

Many greetings to all

Rosika

Rosika · September 6, 2023, 2:41pm

Hi again,

as far as my script is concerned (post #6) I just added some improvement to it, as I
found out that it’s considered to be a good practice to check whether the user has provided the required parameters ($1 and $2) and handle errors gracefully.
For instance, you can use an if statement to check if the number of arguments is correct.

So here´s the improved version of it:

batcat script_firefox-history_mit_sqlite3.sh

───────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: script_firefox-history_mit_sqlite3.sh
───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ #!/bin/bash
   2   │ 
   3   │ # Check if both search terms are provided
   4   │ if [ $# -ne 2 ]; then
   5   │     echo "Usage: $0 search_term1 search_term2"
   6   │     exit 1
   7   │ fi
   8   │ sleep 1
   9   │ echo "ffhi is looking for a combination of $1 and $2"
  10   │ sleep 2
  11   │ cd /media/rosika/f14a27c2-0b49-4607-94ea-2e56bbf76fe1/DATEN-PARTITION/Dokumente/Sicherungen/Sicherungen_für_jammy
  12   │ sqlite3 places.sqlite "SELECT url FROM moz_places WHERE url LIKE '%$1%' AND url LIKE '%$2%'"

Many greetings from Rosika