pihole-FTL sqlite3 error

[deHakkelaar]

@MichaIng

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 what the shell must pick. Pretty confused when it does not.

Yeah indead, somthing doesnt look right.

[deHakkelaar]

@MichaIng

Isn't it pihole-FTL sqlite3 instead of pihole-FTL sqlite ?

Your right, typo

pi@ph5b:~ $ pihole-FTL sqlite3 /etc/pihole/gravity.dbbbbb ".databases"
main: /etc/pihole/gravity.dbbbbb r/o

[MichaIng]

Okay, so this seems to work. Probably the command override works with the wrapper:

rm /usr/local/bin/sqlite3
cat << '_EOF_' > /usr/local/bin/sqlite3
#!/bin/sh
exec /usr/bin/pihole-FTL sqlite3 "$@"
_EOF_
chmod +x /usr/local/bin/sqlite3
hash -r
sqlite3 --version

[deHakkelaar]

@MichaIng

hash -d

Havent tried it yet but noticed before below not working:

pi@ph5b:~ $ hash -d
-bash: hash: -d: option requires an argument

[MichaIng]

@deHakkelaar

-bash: hash: -d: option requires an argument

Ah whoops, it's either (delete a specific command form cache):

hash -d sqlite3

or (delete all commands from cache):

hash -r

[deHakkelaar]

root@ph5b:~# rm /usr/local/bin/sqlite3
root@ph5b:~#

root@ph5b:~# cat << '_EOF_' > /usr/local/bin/sqlite3
#!/bin/sh
exec /usr/bin/pihole-FTL sqlite3 "$@"
_EOF_

root@ph5b:~# chmod +x /usr/local/bin/sqlite3
root@ph5b:~#

root@ph5b:~# hash -r
root@ph5b:~#

root@ph5b:~# sqlite3 --version
3.37.1 2021-12-30 15:30:28 378629bf2ea546f73eee84063c5358439a12f7300e433f18c9e1bddd948dea62

I also rebooted to be sure but still get the actual sqlite3 responding instead of pihole-FTL:

pi@ph5b:~ $ command -v sqlite3
/usr/local/bin/sqlite3

pi@ph5b:~ $ cat /usr/local/bin/sqlite3
#!/bin/sh
exec /usr/bin/pihole-FTL sqlite3 "$@"

pi@ph5b:~ $ sqlite3 --version
3.37.1 2021-12-30 15:30:28 378629bf2ea546f73eee84063c5358439a12f7300e433f18c9e1bddd948dea62

[deHakkelaar]

Hold on, i made an observation mistake.
Give me a sec!

[deHakkelaar]

Yeah your last sugestion is working.
Version for the actual sqlite3:

pi@ph5b:~ $ /usr/bin/sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

Version for sqlite3 embedded in pihole-FTL:

pi@ph5b:~ $ sqlite3 --version
3.37.1 2021-12-30 15:30:28 378629bf2ea546f73eee84063c5358439a12f7300e433f18c9e1bddd948dea62

Cheers!

[MichaIng]

Ahh, I was expecting an FTL version, not an SQLite version. Actually nice that the argument is passed through by FTL to the embedded SQLite .

[deHakkelaar]

Yeah I made the same mistake expecting the pihole-FTL version 5. somthing

[MichaIng]

Great, so we have a system-wide working option. Helpful when consistency (and/or the benefit of the newer embedded SQLite version) is wanted while e.g. the sqlite3 APT package needs to stay as dependency.

In theory the Pi-hole installer could even ship it. But is bears the risk of breaking custom SQLite compiling where /usr/local/bin is used by default as executable install directory. Better to not touch this.

[DanSchaper]

pihole-FTL will only transparently invoke sqlite3 mode when it can determine that the command line arguments being passed are for sqlite3.

[jpgpi250]

Very interesting conversation above on how to ensure the sqlite3 command can be used, even if the package is not installed.

This doesn't change the fact that queries, like the one in my initial post, don't work anymore with "pihole-FTL sqlite3". They used to work when the sqlite3 package was installed.

For me, the best solution is to simply install the sqlite3 package. All my scripts work again, I will check / change the scripts over time, until they all use the embedded sqlite3.

[DL6ER]

@jpgpi250

This doesn't change the fact that queries, like the one in my initial post, don't work anymore with "pihole-FTL sqlite3". They used to work when the sqlite3 package was installed.

I just want to mention that this isn't caused by FTL itself (or a defect within) but solely due to the fact that you are now using the most-recent SQLite3 engine available which deprecated support for string-delimitation using double-quotes.

The reason for why accepting double-quotes for string literals is bad and has changed is given here, excerpt:

This misfeature means that a misspelled double-quoted identifier will be interpreted as a string literal, rather than generating an error. It also lures developers who are new to the SQL language into the bad habit of using double-quoted string literals when they really need to learn to use the correct single-quoted string literal form.

In hindsight, we should not have tried to make SQLite accept MySQL 3.x syntax, and should have never allowed double-quoted string literals.

Their recommendation

Application developers are encouraged to compile using -DSQLITE_DQS=0 in order to disable the double-quoted string literal misfeature by default.

is what causes the behavior you see with the SQLite3 engine offered by FTL. This recommendation is there since version 3.29.0 (2019-07-10) so I assume your system provided one is older.

For completeness: The full set of compile-time options for the current version can be found here. This follows the recommendations by the SQLite3 maintainers. Reasoning for each and every set parameter can be found therein.