firefox_history_clean.cmd

::
:: Windows batch script to clear Firefox history.
::
:: Requires:
::
::   SDelete - https://technet.microsoft.com/en-us/sysinternals/sdelete.aspx
::             If you do not have SDelete, replace instances of 'sdelete' in this script with 'del'
::
::   sqlite3 - https://www.sqlite.org/download.html
::
:: These programs above must be part of the user's PATH environment variable.
::

@echo off
setlocal

:: Set your Firefox profile directory here, do not use any trailing slash
set profile=c:\Users\xxxx\AppData\Roaming\Mozilla\Firefox\Profiles\xxxxxxxx.default

:: Set your WHERE conditions (SQL format) to delete items from history
:: '%' must be represented as '%%' in Windows batch files
set where=(url LIKE 'http%%://%%.facebook.com/%%' OR url LIKE 'http%%://facebook.com/%%' OR url LIKE 'http%%://mail.google.com/mail/%%' OR url LIKE 'http%%://google.com.au/%%q=%%' OR url LIKE 'http%%://www.google.com.au/%%q=%%')

:: This is where 'places.sqlite' lives, you shouldn't need to change this
set places=%profile%\places.sqlite

:: Determine if Firefox is running, or DB is locked, if it is, inform the user and quit
if exist "%places%-wal" (
	echo Firefox currently open or database 'places.sqlite' is locked.
	exit /b
)

:: Securely delete files in current working dir
echo Securely deleting (if exists) places.sqlite and places.sqlite.backup
sdelete -p 3 "places.sqlite" > NUL
sdelete -p 3 "places.sqlite.backup" > NUL

echo Making copy of 'places.sqlite' and creating backup...
copy "%places%" places.sqlite > NUL
copy "%places%" places.sqlite.backup > NUL

echo Clearing history...

:: Remove sensitive items from places.sqlite
sqlite3 places.sqlite "DELETE FROM moz_places WHERE %where% AND (id NOT IN (SELECT fk FROM moz_bookmarks WHERE fk NOT NULL))"
sqlite3 places.sqlite "DELETE FROM moz_bookmarks WHERE fk NOT IN (SELECT id FROM moz_places)"
sqlite3 places.sqlite "DELETE FROM moz_historyvisits WHERE place_id NOT IN (SELECT id FROM moz_places)"
sqlite3 places.sqlite "DELETE FROM moz_inputhistory WHERE place_id NOT IN (SELECT id FROM moz_places)"
sqlite3 places.sqlite "DELETE FROM moz_annos WHERE place_id NOT IN (SELECT id FROM moz_places)"
sqlite3 places.sqlite "DELETE FROM moz_anno_attributes WHERE id NOT IN (SELECT id FROM moz_annos)"
sqlite3 places.sqlite "DELETE FROM moz_items_annos WHERE anno_attribute_id NOT IN (SELECT id FROM moz_anno_attributes)"
sqlite3 places.sqlite "DELETE FROM moz_items_annos WHERE item_id NOT IN (SELECT id FROM moz_bookmarks)"
sqlite3 places.sqlite "DELETE FROM moz_keywords WHERE id NOT IN (SELECT id FROM moz_bookmarks)"

echo Now replacing current Firefox 'places.sqlite' file. You must confirm this below.
copy /-Y places.sqlite "%places%"

echo Remove local copy securely. Backup 'places.sqlite.backup' still remains.

sdelete -p 3 "places.sqlite" > NUL

echo Finished.

endlocal