# pihole-FTL sqlite3 error

**URL:** <https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549>\
**Category:** Help\
**Created:** [February 13, 2022, 9:53am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549 "2022-02-13T09:53:24Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![jpgpi250](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jpgpi250/32/286_2.png) [@jpgpi250](https://discourse.pi-hole.net/u/jpgpi250)\
**Post date:** [February 13, 2022, 9:53am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/1 "2022-02-13T09:53:24Z")

</div>

**sqlite3** is no longer installed, thus need to use **pihole-FTL sqlite3**"..."

following script works with **sqlite3**

```auto
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:

```auto
Error: in prepare, no such column: (\.|^)eulerian\.net$ (1)

```

what am I doing wrong?

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 13, 2022, 12:43pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/2 "2022-02-13T12:43:49Z")

</div>

```auto
pi@ph5b:~ $ domain="eulerian.net"
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ echo "$domain"
eulerian.net

```

```auto
pi@ph5b:~ $ regex="(\\.|^)${domain%.*}\\.${domain##*.}$"
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ echo $regex
(\.|^)eulerian\.net$

```

```auto
pi@ph5b:~ $ query="insert or ignore into domainlist (type, domain, enabled, comment) values (3, '$regex', 1, 'NextDNS CNAME list');"
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ echo $query
insert or ignore into domainlist (type, domain, enabled, comment) values (3, '(\.|^)eulerian\.net$', 1, 'NextDNS CNAME list');

```

```auto
pi@ph5b:~ $ sudo -u pihole cp /etc/pihole/gravity.db /etc/pihole/gravity.copy.db
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ sudo -u pihole pihole-FTL /etc/pihole/gravity.copy.db "$query"
pi@ph5b:~ $

```

```auto
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

```

---

<div class="post-metadata">

**Author:** ![yubiuser](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/yubiuser/32/12100_2.png) [@yubiuser](https://discourse.pi-hole.net/u/yubiuser)\
**Post date:** [February 13, 2022, 12:51pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/3 "2022-02-13T12:51:47Z")

</div>

Use single quotes.

```auto
sudo pihole-FTL sqlite3 "/etc/pihole/gravity.db" "insert or ignore into domainlist (type, domain, enabled, comment) values (3, '$regex', 1, 'NextDNS CNAME list');"

```

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 13, 2022, 12:53pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/4 "2022-02-13T12:53:15Z")

</div>

There was more wrong.  
One slash too many:

> [@jpgpi250](#):
>
> `regex=(\\.\|^)${domain%.*}\\.${domain##*.}$`

EDIT: and you dont run `pihole-FTL sqlite3` but instead `sudo pihole-FTL` without the `sqlite3`.  
EDIT2: Though it seems to work as well:

```auto
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`😉

---

<div class="post-metadata">

**Author:** ![DanSchaper](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/danschaper/32/91_2.png) [@DanSchaper](https://discourse.pi-hole.net/u/DanSchaper)\
**Post date:** [February 13, 2022, 7:19pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/5 "2022-02-13T19:19:46Z")

</div>

> [@deHakkelaar](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 13, 2022, 9:51pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/6 "2022-02-13T21:51:00Z")

</div>

Or an alias in `~/.bashrc`:

```auto
pi@ph5b:~ $ alias
[..]
alias sqlite3='pihole-FTL'

```

```auto
pi@ph5b:~ $ sqlite3 -vv
[..]
Version: pi-hole-2.87test4-18

```

---

<div class="post-metadata">

**Author:** ![DanSchaper](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/danschaper/32/91_2.png) [@DanSchaper](https://discourse.pi-hole.net/u/DanSchaper)\
**Post date:** [February 14, 2022, 6:47pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/7 "2022-02-14T18:47:54Z")

</div>

True, but an alias only affects the environment of that user whereas symlinks are the whole system and work in cronjobs.

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 15, 2022, 3:36pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/8 "2022-02-15T15:36:20Z")

</div>

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`:

```auto
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

```

```auto
pi@ph5b:~ $ alias sqlite3='pihole-FTL'
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ sqlite3 --version
v5.13

```

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

```

---

<div class="post-metadata">

**Author:** ![MichaIng](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/michaing/32/13351_2.png) [@MichaIng](https://discourse.pi-hole.net/u/MichaIng)\
**Post date:** [February 16, 2022, 2:14am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/9 "2022-02-16T02:14:37Z")

</div>

Without breaking SQLite but being effective in non-interactive shells (scripts/cron/systemd/...):

```sh
ln -s /usr/bin/pihole-FTL /usr/local/bin/sqlite3

```

---

<div class="post-metadata">

**Author:** ![yubiuser](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/yubiuser/32/12100_2.png) [@yubiuser](https://discourse.pi-hole.net/u/yubiuser)\
**Post date:** [February 16, 2022, 10:02am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/10 "2022-02-16T10:02:10Z")

</div>

> [@deHakkelaar](#):
>
> ```auto
> pi@ph5b:~ $ alias sqlite3='pihole-FTL'
> pi@ph5b:~ $
> 
> ```

Isn't the right thing?

```auto
pi@ph5b:~ $ alias sqlite3='pihole-FTL sqlite3'
pi@ph5b:~ $

```

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 2:38pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/11 "2022-02-16T14:38:46Z")

</div>

> [@MichaIng](#):
>
> `ln -s /usr/bin/pihole-FTL /usr/local/bin/sqlite3`

I tried that but doesnt seem to work???

```auto
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

```

```auto
pi@ph5b:~ $ sudo ln -s /usr/bin/pihole-FTL /usr/local/bin/sqlite3
pi@ph5b:~ $

```

```auto
pi@ph5b:~ $ readlink -f /usr/local/bin/sqlite3
/usr/bin/pihole-FTL

```

```auto
pi@ph5b:~ $ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/games:/usr/games

```

```auto
pi@ph5b:~ $ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

```

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 2:43pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/12 "2022-02-16T14:43:53Z")

</div>

> [@yubiuser](#):
>
> Isn't the right thing?
> 
> ```auto
> pi@ph5b:~ $ alias sqlite3='pihole-FTL sqlite3'
> pi@ph5b:~ $
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![MichaIng](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/michaing/32/13351_2.png) [@MichaIng](https://discourse.pi-hole.net/u/MichaIng)\
**Post date:** [February 16, 2022, 3:00pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/13 "2022-02-16T15:00:52Z")

</div>

> [@deHakkelaar](#):
>
> I tried that but doesnt seem to work???

Probably you need to clear the cache:

```sh
hash # This lists the cached executable locations.
hash -r # This clears the cache.

```

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 3:04pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/14 "2022-02-16T15:04:41Z")

</div>

If I start a new session, cache is clean am I right?

```auto
pi@ph5b:~ $ hash
hits command
   1 /usr/bin/tput

```

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

```

---

<div class="post-metadata">

**Author:** ![MichaIng](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/michaing/32/13351_2.png) [@MichaIng](https://discourse.pi-hole.net/u/MichaIng)\
**Post date:** [February 16, 2022, 3:45pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/15 "2022-02-16T15:45:26Z")

</div>

🤔, what does the following say?

```sh
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:

```sh
stat -c '%a' /usr/local
stat -c '%a' /usr/local/bin
stat -c '%a' /usr/local/bin/sqlite3

```

> [@deHakkelaar](#):
>
> If the script invokes `sqlite3` with other arguments first before the actual dbase file, my proposal wont work.

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:

```bash
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?

```bash
cat << '_EOF_' > /usr/local/bin/sqlite3
#!/bin/sh
exec /usr/bin/pihole-FTL sqlite3 "$@"
_EOF_
chmod +x /usr/local/bin/sqlite3

```

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 3:55pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/16 "2022-02-16T15:55:18Z")

</div>

Stock Pi-OS Bullseye with dedicated Pi-hole + Unbound and nothing else:

```auto
pi@ph5b:~ $ which sqlite3
/usr/local/bin/sqlite3

```

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

```

```auto
pi@ph5b:~ $ stat -c '%a' /usr/local /usr/local/bin /usr/local/bin/sqlite3
755
755
777

```

> [@MichaIng](#):
>
> 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.

I've done wrappers before to condition [advmame](https://www.advancemame.it/).  
Things complicate quickly 😉

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 4:03pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/17 "2022-02-16T16:03:50Z")

</div>

> [@MichaIng](#):
>
> But it becomes inconsistent then. Ah but this should work, since it doesn't depend on `.db` ending, right?

I think it needs `.db` at the end if omit the `sqlite3` argument for `pihole-FTL`:

```auto
pi@ph5b:~ $ pihole-FTL /etc/pihole/gravity.db ".databases"
main: /etc/pihole/gravity.db r/o

```

```auto
pi@ph5b:~ $ sudo -u pihole cp /etc/pihole/gravity.db /etc/pihole/gravity.dbbbbb
pi@ph5b:~ $

```

```auto
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

```

---

<div class="post-metadata">

**Author:** ![MichaIng](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/michaing/32/13351_2.png) [@MichaIng](https://discourse.pi-hole.net/u/MichaIng)\
**Post date:** [February 16, 2022, 4:17pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/18 "2022-02-16T16:17:04Z")

</div>

> [@deHakkelaar](#):
>
> I think it needs `.db` at the end if omit the `sqlite3` argument for `pihole-FTL` :

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.

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 16, 2022, 4:18pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/19 "2022-02-16T16:18:08Z")

</div>

Ow it seems even with the `sqlite3` argument included, it still needs `.db` :

```auto
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

```

---

<div class="post-metadata">

**Author:** ![MichaIng](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/michaing/32/13351_2.png) [@MichaIng](https://discourse.pi-hole.net/u/MichaIng)\
**Post date:** [February 16, 2022, 4:18pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549/20 "2022-02-16T16:18:51Z")

</div>

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

[Next page](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-error/53549.md?page=2)