Fish out any URL from the places.sqlite DB with sqlite3

Business software, financial software, etc.
Post Reply
Message
Author
musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

Fish out any URL from the places.sqlite DB with sqlite3

#1 Post by musher0 »

Hi people.

As the title says: "Fish out any URL from the places.sqlite DB with
sqlite3 and awk in MZ browsers."


This is a break-through for me. Before, sqlite DB's had always intimidated
me. This time, I took the bull by the horns.

So here we go:

-- First, the doc: https://sqlite.org/cli.html. It explains how to fish
out info from a sqlite DB at
9. CSV Export
and
15. Using sqlite3 in a shell script
.
-- Puppy has the sqlite3 utility in /usr/bin.

-- Here is the script:

Code: Select all

#!/bin/sh
# ./sqlite3-places.sh
# (c) musher0, Aug. 7 2017.GPL3
#
# Usage : Drill down with "cd" to the directory where the places.sqlite DB 
# is located:
# ~/.mozilla/NameOfYourMZBrowser/RandomDirName.default
# and issue this command:
# sqlite3-places.sh $1 # for a specific search
# or this one
# sqlite3-places.sh # to get a dump of all URLs
####
A="$1"
if [ -z $A ];then # We output the whole URL field

sqlite3 places.sqlite 'select * from moz_places' | awk -F"|" '{ print $2 }' > URLs.output

else # We output only the specified sub-set.

sqlite3 places.sqlite 'select * from moz_places' | awk -F"|" '$2 ~ /'$1'/ { print $2 }' > $1.output # This file is named after your search word.

fi
As I said, this is a break-through. After this, it is a matter of learning
sqlite3's dot commands in more depth and of getting more familiar
with the places.sqlite DB. Or any other sqlite3 DB.

A example of the text output this script produces is attached.
I used the command

Code: Select all

./sqlite3-places.sh murga
I hope this helps a few of you.

BFN.
Attachments
murga.output.zip
(1.21 KiB) Downloaded 569 times
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
Galbi
Posts: 1098
Joined: Wed 21 Sep 2011, 22:32
Location: Bs.As. - Argentina.

#2 Post by Galbi »

What I don't fish is, what fish are you expecting to fish with this fishing technique.
:D
Remember: [b][i]"pecunia pecuniam parere non potest"[/i][/b]

User avatar
souleau
Posts: 148
Joined: Sun 23 Oct 2016, 15:24

#3 Post by souleau »

This really hammers home for me the need to regularly delete my places.sqlite and cookies.sqlite.

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#4 Post by musher0 »

Galbi wrote:What I don't fish is, what fish are you expecting to fish with this fishing technique.
:D
Trout. :wink: :lol:
Last edited by musher0 on Mon 07 Aug 2017, 18:47, edited 1 time in total.
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#5 Post by musher0 »

souleau wrote:This really hammers home for me the need to regularly delete my places.sqlite and cookies.sqlite.
Shucks. :wink: :lol:
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
souleau
Posts: 148
Joined: Sun 23 Oct 2016, 15:24

#6 Post by souleau »

Here's a tip. Make it even easier by installing this browser add-on:

https://github.com/lazierthanthou/sqlite-manager

Here's another tip: Don't! :)

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#7 Post by musher0 »

I meant my "Shucks!" as a joke, souleau!

The places.sqlite DB is only mentioned as an example. Sorry if I accidentally
offended you.

Some people may like the idea of having access to their own browsing
history -- or even a need for it (older people affected by partial memory loss,
for example). Viewing your own data is not snooping, you know.

About the SQLiteManager extension, it's a good app. If one is interested in
databasing, it can complement the sqlite3 utility Puppies have. Or the other
way around.

BFN.
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
souleau
Posts: 148
Joined: Sun 23 Oct 2016, 15:24

#8 Post by souleau »

musher0 wrote:I meant my "Shucks!" as a joke, souleau!

The places.sqlite DB is only mentioned as an example. Sorry if I accidentally
offended you.
No harm done. If I was that easily offended I shouldn't frequent online forums.
musher0 wrote:About the SQLiteManager extension, it's a good app. If one is interested in
databasing, it can complement the sqlite3 utility Puppies have. Or the other
way around.
Maybe it is. All I know is that I installed it, and then cache entries from Digital Forensics popped up.
Perhaps that means nothing, but it spooked me plenty.

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#9 Post by musher0 »

Hi souleau.

Do you have AdBlocker or Ghostery installed in your Firefox?

Also your /etc/hosts file should be populated. There's a jwm menu item
that will do it for you, under "Network", named "Puppy AdBlocker", IIRC.

~~~~~~~~

@All:

I've been chugging along and came up with a "short list" version of the
above script. I'm sharing it with you for what it's worth.

What it does is it limits the places.sqlite output to 100 (real) URLs, sorts it
by time, then keeps only the name of the main site and "uniq's" it.

The uniq utility is better here because it doesn't alter the time sort. When
uniq sees two identical entries in a list on top of one another, it removes
one. It skims rather than sort.

You'll find more details of how the script works in the comments. It creates
too many work files, remove them as needed. And it's designed to be run
from terminal.

An example of the results I got is attached. As you can see, I was left
with a list of less than 20 recent sites.

Have fun trying it out!

You may be surprised at the results: "Memory is a capacity that forgets,"
as we say in French.

BFN

~~~~~~~~~~~~~~~~~

Code: Select all

#!/bin/sh
# ./sqlite3-places-2.sh # For terminal use.
#
# (c) musher0, Aug. 8 2017. GPL3
# https://opensource.org/licenses/GPL-3.0
#
# Usage : Drill down with "cd" to the directory where the places.sqlite DB is located:
# ~/.mozilla/NameOfYourMZBrowser/RandomDirName.default
#
# and issue this command:
#
# sqlite3-places.sh $1 # -=» for a specific search, # Replace "$1" with your search word.
#
# or this one
#
# sqlite3-places.sh # -=» to get a very short list (+/- 17) of URLs you last visited.
#### set -xe
A="$1"
if [ -z $A ];then # If there is no positional parm., we work the whole URL field.

	sqlite3 places.sqlite 'select url,last_visit_date from moz_places' | head -n 129 | awk /http/ > Last_100.output
	sort -t\| -nk 2 Last_100.output | awk -F"\|" '$1 !~ /seamonkey|firefox|mozilla|mozdev|google/ { print $1 }' | sort -u > Last_100.tri3

>breve.lst
while read line;do
	if [ "${line:0:11}" = "http://www." ];then
		echo "${line:11:${#line}}" >> breve.lst
	elif [ "${line:0:12}" = "https://www." ];then
		echo "${line:12:${#line}}" >> breve.lst
	elif [ "${line:0:7}" = "http://" ];then
		echo "${line:7:${#line}}" >> breve.lst
	elif [ "${line:0:8}" = "https://" ];then
		echo "${line:8:${#line}}" >> breve.lst
	else
		echo "${line}" >> breve.lst
	fi
done < Last_100.tri3

# awk -F"\/" '{ print $1 }' breve.lst | sort | uniq -u > breve2.lst # Cancels the previous time sort.

awk -F"\/" '{ print $1 }' breve.lst | uniq -u > breve2.lst # Better alternative. # Some repeats, but
#  previous time sort is respected.

more breve2.lst # This shows only the main sites, not the pages you visited within them.

else # If there is, we output only the specified sub-set.

sqlite3 places.sqlite 'select * from moz_places' | awk -F"|" '$2 ~ /'$1'/ { print $2 $9 }' > $1.output
# This file is named after your search word.

fi # set +xe
Attachments
short-list.jpg
(120.1 KiB) Downloaded 484 times
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
souleau
Posts: 148
Joined: Sun 23 Oct 2016, 15:24

#10 Post by souleau »

musher0 wrote:Do you have AdBlocker or Ghostery installed in your Firefox?
No, but I do have NoScript, which, as some people quite rightly pointed out in this forum, may be a liability in combination with other add-ons.
But it could be innocent. For all I know the Sqlite Manager add-on is simply being developed by Digital Forensics and published under another name. I just don't know.

It happened a while ago, and what I took away from it was not to use other add-ons with NoScript.

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#11 Post by musher0 »

Hi Souleau.

The answer to that is a loud no, because SQLiteManager is being developed
publicly at https://github.com/lazierthanthou/sqlite-manager. Before it is
compiled, any application is simply "specially formatted text", to put it in
layman's terms, and anyone can read it. It's boring as boring can be, as
texts go, but being openly developed at that public site, anyone can read it.

If that Digital Forecsics company had tampered with the code, somebody
would have caught it.

Among others, the inventor of Puppy, BarryK. He made a 64-bit compilation
of SQLiteManager for his new Quirky a few days ago, and he would certainly
have noticed any alteration in the source code. (Ref.: here and here.)

IHTH
Last edited by musher0 on Tue 08 Aug 2017, 21:13, edited 1 time in total.
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#12 Post by musher0 »

Hello again, people.

More "chugging". (hehe) ;)

This one can handle two or more MZ browsers on your Pup,
and a search term. You can run it from any directory, too.

Do not enjoy, "do not pass go, do not collect $200."
(Remember the Monopoly game?)

BFN.

Code: Select all

#!/bin/sh
# ./sqlite3-places-3.sh # For terminal use.
#
# (c) musher0, Aug. 8 2017. GPL3 # https://opensource.org/licenses/GPL-3.0
#
# Usage :
# sqlite3-places-3.sh $1 # -=» for a specific search, # Replace "$1" with your search word.
# or
# sqlite3-places-3.sh # -=» to get a very short list (+/- 17) of URLs you last visited.
#
#### set -xe

A="$1"

SkimURLs () { sort -t\| -nk 2 Last_100.output | awk -F"\|" '$1 !~ /seamonkey|firefox|mozilla|mozdev|sex|porn|lesarch|google/ { print $1 }' | sort -u > Last_100.tri3;
while read url;do
	if [ "${url:0:11}" = "http://www." ];then echo "${url:11:${#url}}" >> $dir/breve.lst
	elif [ "${lurl:0:12}" = "https://www." ];then echo "${url:12:${#url}}" >> $dir/breve.lst
	elif [ "${url:0:7}" = "http://" ];then echo "${url:7:${#url}}" >> $dir/breve.lst
	elif [ "${url:0:8}" = "https://" ];then echo "${url:8:${#url}}" >> $dir/breve.lst
	else echo "${url}" >> $dir/breve.lst
	fi
done < Last_100.tri3

# awk -F"\/" '{ print $1 }' breve.lst | sort | uniq -u > breve2.lst # Cancels the previous time sort.
awk -F"\/" '{ print $1 }' breve.lst | uniq -u > breve2.lst # Much better. # Some repeats,
# ............................................................................................. but previous time sort is respected.

echo >> breve2.lst
more breve2.lst >> /tmp/MZurls # Shows only the main sites, not the pages visited within them.
rm Last*;rm breve.lst; }

echo > /tmp/MZurls
ls -Algot /root/.*/*/*.default/places.sqlite | awk '{ print $NF }' > /tmp/MZplaces
while read place;do # for i in "`cat /tmp/MZplaces`";do
	dir="`dirname $place`"
	echo "$dir" | awk -F"\/" '{ print "-- "$4" --" }' >> /tmp/MZurls # Name of the browser
	cd $dir

	if [ -z $A ];then # If no positional parm., we work the whole URL field.
		sqlite3 places.sqlite 'select url,last_visit_date from moz_places' | head -n 129 | awk /http/ > Last_100.output
		SkimURLs

	else # If there is, we work only on the specified search word.
		sqlite3 places.sqlite 'select url,last_visit_date from moz_places' | awk '$1 ~ /'$A'/' > Last_100.output
		SkimURLs
		echo -e "$A\n" >> /tmp/MZurls # We add the search word in the list, to be clear.
	fi

done < /tmp/MZplaces

more /tmp/MZurls # set +xe
## 30 ##
Attachments
short-list-with-search-word.jpg
(101.07 KiB) Downloaded 465 times
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#13 Post by musher0 »

Hello all.

Some refinements:

Code: Select all

#!/bin/sh
# sqlite3-places-4.sh # For terminal use.
# (c) musher0, Aug. 8 2017. GPL3 # https://opensource.org/licenses/GPL-3.0
#
# Usage :
# sqlite3-places-3.sh $1 # -=» for a specific search. # Replace "$1" with your search word.
# or
# sqlite3-places-3.sh # -=» to get a very short list (+/- 17) of URLs you last visited.
#
#### set -xe
A="$1"

SkimURLs () { sort -t\| -nk 2 Last_100.output | awk -F"\|" '$1 !~ /seamonkey|firefox|mozilla|mozdev|sex|porn|lesarch|google/ { print $1 }' | sort -u > Last_100.tri3;
while read url;do
	if [ "${url:0:11}" = "http://www." ];then echo "${url:11:${#url}}" >> breve.lst
	elif [ "${lurl:0:12}" = "https://www." ];then echo "${url:12:${#url}}" >> /breve.lst
	elif [ "${url:0:7}" = "http://" ];then echo "${url:7:${#url}}" >> breve.lst
	elif [ "${url:0:8}" = "https://" ];then echo "${url:8:${#url}}" >> breve.lst
	else echo "${url}" >> breve.lst
	fi
done < Last_100.tri3
# awk -F"\/" '{ print $1 }' breve.lst | sort | uniq -u > breve2.lst # Cancels the previous time sort.
awk -F"\/" '{ print $1 }' breve.lst | uniq -u > breve2.lst # .............. Much better. Some repeats, but
# ................................................................................................................ previous time sort is respected.
echo >> breve2.lst
more breve2.lst >> /tmp/MZurls # Shows only the main sites, not the pages visited within them.
rm Last*;rm breve.lst; }

echo > /tmp/MZurls
ls -Algot /root/.*/*/*.default/places.sqlite | awk '{ print $NF }' > /tmp/MZplaces
while read place;do # for i in "`cat /tmp/MZplaces`";do
	dir="`dirname $place`"
	echo "$dir" | awk -F"\/" '{ print "-- "$4" --" }' >> /tmp/MZurls # Name of the browser
	cd $dir
	> breve.lst
	if [ -z $A ];then # If no positional parm., we work the whole URL field.
		sqlite3 places.sqlite 'select url,last_visit_date from moz_places' | head -n 129 | awk /http/ > Last_100.output
		SkimURLs

	else # If there is, we work only on the specified search word.
		sqlite3 places.sqlite 'select url,last_visit_date from moz_places' | awk '$1 ~ /'$A'/' | awk /http/ > Last_100.output
		if [ "`wc -l < Last_100.output`" -gt "2" ];then
			SkimURLs
			echo -e "entries for $A\n" >> /tmp/MZurls # We add the search word in the list, to be clear.
		else
			echo -e "no entry for $A\n" >> /tmp/MZurls
		fi
	fi
done < /tmp/MZplaces

more /tmp/MZurls # set +xe
## 30 ##
Attachments
refinements.jpg
(98.91 KiB) Downloaded 448 times
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
souleau
Posts: 148
Joined: Sun 23 Oct 2016, 15:24

#14 Post by souleau »

musher0 wrote:If that Digital Forecsics company had tampered with the code, somebody
would have caught it.
That's a very good point.

Not very reassuring for me though.

Oh well, I don't do online banking, my porn viewing habits aren't too kinky, my creations can't be monetized and my convictions are not a threat to society.

Not happy about it, but I'm not gonna go all paranoid over it either.

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#15 Post by musher0 »

Good morning, all.

My last take on this, hopefully.
I think it's good enough to go.

Enjoy!

~~~~~~~~~~

Code: Select all

#!/bin/ash
# sqlite3-places-5.sh # For use in terminal. # We gain half a second in execution time using
# ash rather than bash. Dash trips on some string manipulations; at line 26, for example.
# Overall time will vary depending on the size of the user's DB.
#
# (c) Christian L'Écuyer, Gatineau (Qc), Canada, Aug. 8-9 2017. GPL3
#  (Alias « musher0 » [forum Puppy].) # https://opensource.org/licenses/GPL-3.0
#
# Usage :
# sqlite3-places-3.sh $1 # -=» for a specific search. # Replace "$1" with your search word.
# or
# sqlite3-places-3.sh # -=» to get a very short list (+/- 17) of URLs that you visited.
# Timeframe: this month and the month before (but see at line 49) AND
# ..................... the history duration you have set in your browser.
#
#### set -xe
A="$1"

SQLitePlaces () { sqlite3 places.sqlite 'select url,last_visit_date from moz_places'; }

CleanUp () { rm -f Last*;rm -f breve.lst;
i="";for i in 2 3 4;do rm -f breve$i.lst;done; } # We keep breve5.lst as ref. in case of a bug.

SkimURLs () { >Last_100.output2;
while read total;do
	Long="`expr ${#total} - 6`" # # This simple math operation removes the milliseconds
	echo "${total:0:$Long}" >> Last_100.output2 # in the time field.
done < Last_100.output

sort -t\| -nk 2 Last_100.output2 | awk -F'\|' '$1 !~ /seamonkey|firefox|mozilla|mozdev|google/ { print $0 }' | sort -t '|' -nk 2 -u > Last_100.tri3
# Sort on time field, excluding some thematics we don't care about. The 
user can add to or edit these thematics.

while read url;do
	if [ "${url:0:11}" = "http://www." ];then echo "${url:11:${#url}}" >> breve.lst
	elif [ "${url:0:12}" = "https://www." ];then echo "${url:12:${#url}}" >> /breve.lst
	elif [ "${url:0:7}" = "http://" ];then echo "${url:7:${#url}}" >> breve.lst
	elif [ "${url:0:8}" = "https://" ];then echo "${url:8:${#url}}" >> breve.lst
	elif [ "${url:0:4}" = "www." ];then echo "${url:4:${#url}}" >> breve.lst
	else echo "${url}" >> breve.lst
	fi
done < Last_100.tri3

> breve2.lst
while read result;do
	Total1="${result%|*}"
	Total1="`echo ${Total1} | awk -F'\/' '{print $3"/"$4 }'`" # For the sake of brevity,
	# we show only the main sites, not the pages visited within them.
	 [ "${Total1:0:4}" = "www." ] && Total1="${Total1#*.}"

	Total2="`date -d @${result#*|} +'%x'`" # %R: This is a summary, we consider the date only.
	Mois="`date +'%m'`"
	Mois="`expr $Mois - 2`" # Change 2 to 3 if you want to view 3 months back.
	[ "${Mois}" -lt "10" ] && Mois="0$Mois" # This zero is needed from January to September.

	echo "$Total1 § $Total2" | awk -F'-' '$(NF-1) > '$Mois' { print $0 }' >> breve2.lst # We retrieve
	# the browsing history from 2 months (absolute) back. Or 3 months (possible at line 49).
done < Last_100.tri3

for i in 2 3 4;do
	j="`expr $i + 1`"
	uniq -u breve$i.lst > breve$j.lst # We skim 3 times. # A plain sort would cancel the
done # previous time sort. Uniq is much better in this case. Some entries are repeated,
# but they tell the user something because the time sort is respected.

echo >> breve5.lst
cat breve5.lst >> /tmp/MZurls

CleanUp; }

echo > /tmp/MZurls # Action
ls -Algot /root/.*/*/*.default/places.sqlite | awk '{ print $NF }' > /tmp/MZplaces
while read place;do
	dir="`dirname $place`"
	echo "$dir" | awk -F'\/' '{ print "-- "$4" --" }' >> /tmp/MZurls # Name of browser
	cd "$dir"

	> breve.lst
	if [ -z $A ];then # If no positional parm., we work the whole URL field.
		SQLitePlaces | tail -n 130 | awk /http/ > Last_100.output
		SkimURLs

	else # If yes, we search only for the specified search word.
		SQLitePlaces | awk '$1 ~ /'$A'/' | awk /http/ > Last_100.output
		Lign="`wc -l < Last_100.output`"
		if [ "$Lign" -gt "3" ];then
			SkimURLs
			echo -e "entries for $A\n" >> /tmp/MZurls
			# We add the search word at the bottom df the list, to be clear.

		else
			echo -e "no entry for $A\n" >> /tmp/MZurls
			CleanUp
		fi
	fi
done < /tmp/MZplaces

more /tmp/MZurls # We show the list. # set +xe
## 30 ##
Attachments
sqlite3-places-5.sh.zip
Download in a suitable &quot;bin&quot; directory, make executable and run.
(2 KiB) Downloaded 527 times
short-list-with-search-word(2).jpg
Execution time indicated at bottom is for a 10 Mb places.sqlite DB.
(89.52 KiB) Downloaded 422 times
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

Post Reply