sqlite3 is no longer installed, thus need to use pihole-FTL sqlite3 "..."
following script works with sqlite3
domain="eulerian.net"
echo "$domain"
regex=(\\.\|^)${domain%.*}\\.${domain##*.}$
echo "$regex"
sudo sqlite3 "/etc/pihole/gravity.db" "insert or ignore into domainlist (type, domain, enabled, comment) values (3, \"$regex\", 1, 'NextDNS CNAME list');"
but when replacing sudo sqlite3 with pihole-FTL sqlite3 I get an error:
Error: in prepare, no such column: (\.|^)eulerian\.net$ (1)
what am I doing wrong?
pi@ph5b:~ $ domain="eulerian.net"
pi@ph5b:~ $
pi@ph5b:~ $ echo "$domain"
eulerian.net
pi@ph5b:~ $ regex="(\\.|^)${domain%.*}\\.${domain##*.}$"
pi@ph5b:~ $
pi@ph5b:~ $ echo $regex
(\.|^)eulerian\.net$
pi@ph5b:~ $ query="insert or ignore into domainlist (type, domain, enabled, comment) values (3, '$regex', 1, 'NextDNS CNAME list');"
pi@ph5b:~ $
pi@ph5b:~ $ echo $query
insert or ignore into domainlist (type, domain, enabled, comment) values (3, '(\.|^)eulerian\.net$', 1, 'NextDNS CNAME list');
pi@ph5b:~ $ sudo -u pihole cp /etc/pihole/gravity.db /etc/pihole/gravity.copy.db
pi@ph5b:~ $
pi@ph5b:~ $ sudo -u pihole pihole-FTL /etc/pihole/gravity.copy.db "$query"
pi@ph5b:~ $
pi@ph5b:~ $ pihole-FTL /etc/pihole/gravity.copy.db --header --column "SELECT * FROM domainlist"
id type domain enabled date_added date_modified comment
-- ---- ------------------------------ ------- ---------- ------------- ------------------
[..]
5 3 (\.|^)eulerian\.net$ 1 1644755933 1644755933 NextDNS CNAME list
Use single quotes.
sudo pihole-FTL sqlite3 "/etc/pihole/gravity.db" "insert or ignore into domainlist (type, domain, enabled, comment) values (3, '$regex', 1, 'NextDNS CNAME list');"
2 Likes
There was more wrong.
One slash too many:
EDIT: and you dont run pihole-FTL sqlite3 but instead sudo pihole-FTL without the sqlite3.
EDIT2: Though it seems to work as well:
pi@ph5b:~ $ pihole-FTL sqlite3 /etc/pihole/gravity.copy.db ".tables"
adlist domainlist_by_group vw_gravity
adlist_by_group gravity vw_regex_blacklist
client group vw_regex_whitelist
client_by_group info vw_whitelist
domain_audit vw_adlist
domainlist vw_blacklist
But still seems a bit redundant as long as the database is the first argument and the filename ends in .db 
Or symlink pihole-FTL to sqlite3 and call sqlite3 like you always did.
1 Like
Or an alias in ~/.bashrc:
pi@ph5b:~ $ alias
[..]
alias sqlite3='pihole-FTL'
pi@ph5b:~ $ sqlite3 -vv
[..]
Version: pi-hole-2.87test4-18
True, but an alias only affects the environment of that user whereas symlinks are the whole system and work in cronjobs.
The right tool for the right job.
Suppose you want to fool a script in using pihole-FTL instead of sqlite3, while sqlite3 is already installed (like in my case), symlinking would be hard without breaking sqlite3:
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
pi@ph5b:~ $ alias sqlite3='pihole-FTL'
pi@ph5b:~ $
pi@ph5b:~ $ sqlite3 --version
v5.13
pi@ph5b:~ $ /usr/bin/sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
Without breaking SQLite but being effective in non-interactive shells (scripts/cron/systemd/...):
ln -s /usr/bin/pihole-FTL /usr/local/bin/sqlite3
Isn't the right thing?
pi@ph5b:~ $ alias sqlite3='pihole-FTL sqlite3'
pi@ph5b:~ $
1 Like
I tried that but doesnt seem to work???
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
pi@ph5b:~ $ sudo ln -s /usr/bin/pihole-FTL /usr/local/bin/sqlite3
pi@ph5b:~ $
pi@ph5b:~ $ readlink -f /usr/local/bin/sqlite3
/usr/bin/pihole-FTL
pi@ph5b:~ $ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/games:/usr/games
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
Yeah your right.
If the script invokes sqlite3 with other arguments first before the actual dbase file, my proposal wont work.
Also another thing to consider, if the script invokes sqlite3 with sudo in front, a new environment is started that doesnt include the alias.
Probably you need to clear the cache:
hash # This lists the cached executable locations.
hash -r # This clears the cache.
If I start a new session, cache is clean am I right?
pi@ph5b:~ $ hash
hits command
1 /usr/bin/tput
pi@ph5b:~ $ sqlite3 --version
3.37.1 2021-12-30 15:30:28 378629bf2ea546f73eee84063c5358439a12f7300e433f18c9e1bddd948dea62
, what does the following say?
which sqlite3
command -v sqlite3
The PATH definitely gives /usr/local/bin a higher priority (as of the order). The pi user has read and execute permissions for all path elements and the symlink, doesn't it? Not sure whether, if not, bash picks the next PATH element automaticaly:
stat -c '%a' /usr/local
stat -c '%a' /usr/local/bin
stat -c '%a' /usr/local/bin/sqlite3
True. To cover this, a wrapper would be required to either call /usr/bin/sqlite3 or /usr/bin/pihole-FTL based on whether the first argument ends with .db or not:
cat << '_EOF_' > /usr/local/bin/sqlite3
#!/bin/sh
case "$1" in
*.db) exec /usr/bin/pihole-FTL "$@"
*) exec /usr/bin/sqlite3 "$@"
esac
_EOF_
chmod +x /usr/local/bin/sqlite3
But it becomes inconsistent then. Ah but this should work, since it doesn't depend on .db ending, right?
cat << '_EOF_' > /usr/local/bin/sqlite3
#!/bin/sh
exec /usr/bin/pihole-FTL sqlite3 "$@"
_EOF_
chmod +x /usr/local/bin/sqlite3
Stock Pi-OS Bullseye with dedicated Pi-hole + Unbound and nothing else:
pi@ph5b:~ $ which sqlite3
/usr/local/bin/sqlite3
pi@ph5b:~ $ command -v sqlite3
/usr/local/bin/sqlite3
pi@ph5b:~ $ stat -c '%a' /usr/local /usr/local/bin /usr/local/bin/sqlite3
755
755
777
I've done wrappers before to condition advmame.
Things complicate quickly
I think it needs .db at the end if omit the sqlite3 argument for pihole-FTL:
pi@ph5b:~ $ pihole-FTL /etc/pihole/gravity.db ".databases"
main: /etc/pihole/gravity.db r/o
pi@ph5b:~ $ sudo -u pihole cp /etc/pihole/gravity.db /etc/pihole/gravity.dbbbbb
pi@ph5b:~ $
pi@ph5b:~ $ pihole-FTL /etc/pihole/gravity.dbbbbb ".databases"
pihole-FTL: invalid option -- '/etc/pihole/gravity.dbbbbb'
Command: 'pihole-FTL /etc/pihole/gravity.dbbbbb.databases'
Try 'pihole-FTL --help' for more information
Jep, hence the second wrapper proposal which calls pihole-FTL sqlite3 explicitly 
. But strange that the override PATH is not used in your case. Actually shells explicitly follow/derive commands as reported by command -v <command>, so since this reports /usr/local/bin/sqlite3 this is what the shell must pick. Pretty confused when it does not.
Ah, for the wrappers I missed execute permissions (symlink have it OOTB), adding them above.
Ow it seems even with the sqlite3 argument included, it still needs .db :
pi@ph5b:~ $ pihole-FTL sqlite /etc/pihole/gravity.dbbbbb ".databases"
pihole-FTL: invalid option -- 'sqlite'
Command: 'pihole-FTL sqlite /etc/pihole/gravity.dbbbbb .databases'
Try 'pihole-FTL --help' for more information
Isn't it pihole-FTL sqlite3 instead of pihole-FTL sqlite?
1 Like