Error "400: Bad Request" on all queries

[maia]

I'm failing to run any queries from the SQLite Web interface, everything returns "400: Bad Request", e.g. the queries listed here such as:

SELECT
  COUNT(*) AS cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
  states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY cnt DESC

The addon itself seems to be configured properly, there's nothing in the addon log that looks like an error to me and the web console shows it found /config/home-assistant_v2.db with 13 tables, 20 indexes and 2GB size.

But the HA Core logs show:

Logger: homeassistant.components.http.security_filter
Source: components/http/security_filter.py:66
Integration: HTTP (documentation, issues)
First occurred: 10:02:23 (3 occurrences)
Last logged: 10:04:34

Filtered a request with unsafe byte query string: /query/?sql=SELECT%0D%0A++COUNT%28*%29+AS+cnt%2C%0D%0A++COUNT%28*%29+*+100+%2F+%28SELECT+COUNT%28*%29+FROM+states%29+AS+cnt_pct%2C%0D%0A++states_meta.entity_id%0D%0AFROM+states%0D%0AINNER+JOIN+states_meta+ON+states.metadata_id%3Dstates_meta.metadata_id%0D%0AGROUP+BY+states_meta.entity_id%0D%0AORDER+BY+cnt+DESC

Log:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: SQLite Web
 Explore your SQLite database
-----------------------------------------------------------
 Add-on version: 3.10.0
 You are running the latest version of this add-on.
 System: Home Assistant OS 11.0  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2023.10.3
 Home Assistant Supervisor: 2023.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-sqlite-web: starting
s6-rc: info: service init-nginx: starting
s6-rc: info: service init-sqlite-web successfully started
s6-rc: info: service sqlite-web: starting
s6-rc: info: service sqlite-web successfully started
s6-rc: info: service init-nginx successfully started
s6-rc: info: service nginx: starting
s6-rc: info: service nginx successfully started
s6-rc: info: service legacy-services: starting
[09:50:48] INFO: Starting SQLite Web...
s6-rc: info: service legacy-services successfully started
 * Serving Flask app 'sqlite_web.sqlite_web'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:8080
Press CTRL+C to quit
[09:50:50] INFO: Starting NGINX...
127.0.0.1 - - [19/Oct/2023 09:50:54] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:00] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:22] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:27] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:02] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:14] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:37] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:47] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:28] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:34] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:36] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:37] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:19] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:33] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:04:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:05:00] "GET / HTTP/1.1" 200 -

Thanks!

[ziptbm]

I'm seeing this as well...thought it was something with my install or an update or something. Let me know what other data I can provide from my install to help here.

[ElPunto]

My queries all failed too. When I removed the Line Feeds and made it a single line statement the queries and updates worked again.

[BigThunderSR]

+1

Reverted to v3.9.2 from backup which is still working without any issues.

[maia]

@BigThunderSR Good to know, might help some people. Unfortunately I don't have any old versions in my backups I could restore, as 3.10.0 was my first install of the addon.

[smaknomercy]

Same problem

[BigThunderSR]

@BigThunderSR Good to know, might help some people. Unfortunately I don't have any old versions in my backups I could restore, as 3.10.0 was my first install of the addon.

@maia, see if this works for you to install (restore to) v3.9.2. You'll need to unzip it to get the "addon_a0d7b954_sqlite-web_3.9.2.tar" file which is what you need to use to restore this version in HA because GitHub doesn't let you upload .tar files.

addon_a0d7b954_sqlite-web_3.9.2.tar.zip

[maia]

@BigThunderSR Much appreciated. I'm quite new to HA yet, so I'll have to figure out where to place the tar file and how to let HA know to use it to install the addon. I assume there's a path it accepts as local repository.

[DanHaed]

@BigThunderSR Thank you for your service. Your Backup worked for me.

@maia restoring from Backup is pretty easy. First delete the installed Addon then go to Settings > System > Backup and choose Upload Backup in the top right menu and upload the tar.

[HansRemmerswaal]

It also worked for me. But don't forget to disable the "Auto update" as today I suddenly had 3.10.0 again...

[jphoke]

My queries all failed too. When I removed the Line Feeds and made it a single line statement the queries and updates worked again.

When I try that I get 404: Not Found error

[BradleyCampbell]

I am also receiving this error for 99% of queries. Running v3.10.0 of the add-on. Unfortunately no back to restore a previous version from.

Is there any eta on a fix?

[BigThunderSR]

I am also receiving this error for 99% of queries. Running v3.10.0 of the add-on. Unfortunately no back to restore a previous version from.

Is there any eta on a fix?

You can use the backup file I provided here until a fix is available: #281 (comment)

[wigster]

It seems that the queries have to be all on one line, then they work

[BradleyCampbell]

I am also receiving this error for 99% of queries. Running v3.10.0 of the add-on. Unfortunately no back to restore a previous version from.
Is there any eta on a fix?

You can use the backup file I provided here until a fix is available: #281 (comment)

Thank you very much! Works perfectly. Turned auto update off for the time being.

[wernerhp]

[sofa74surfer]

One line statement doesn't work for me - I get a Bad Request too. Perhaps my statement is too long, because it automatically wraps line.
select cast(round((max(s.state) - min(s.state)),0) as int) as state FROM statistics_meta sm inner join statistics s on s.metadata_id = sm.id where sm.statistic_id = 'sensor.heatpump_19_hz_power_consumption' and s.created_ts > (strftime('%s', 'now') - (1*60*60*24));
I reverted to 3.9.2, that works.

[wigster]

There seem to be two things. If I make a query like:

SELECT * FROM "statistics" where metadata_id=154;

This works if (a) it's on one line. (b) I actually click on the statistics database and then query, as opposed to typing it into the main query box. If I don't do (b), I get the 404 error. If I do (b) but not (a), I get a 400 error. I've managed to get some pretty long queries done in this way, but they are completely unreadable.

In any case, this is at best a bad workaround and clearly there is some sort of bug.

[Nico96R]

@wigster I could also fix my queries by writing them in one line. Using the Query box at the home screen also gives me the error 404.
Running Version 3.10.0

[bcutter]

I discovered that multi-line statements create the 400: Bad Request web page and create an HA log entry

Logger: homeassistant.components.http.security_filter
Source: components/http/security_filter.py:66
Integration: HTTP (documentation, issues)
First occurred: 14:43:21 (11 occurrences)
Last logged: 15:04:32

Filtered a request with unsafe byte query string: /api/hassio_ingress/XXXXXXXXXXXXXXXXXXXXXXXXXXXXX/states/query/?ordering=&export_ordering=&sql=SELECT+*+FROM+%22states%22+WHERE+metadata_id%3D%28SELECT+metadata_id+FROM+states_meta+WHERE+entity_id%3D%22sensor.statistics_entities_automations_disabled%22%29%0D%0AORDER+BY+created+DESC%3B

Using one-line statements instead works again. What an ugly bug... no matter when or why this has been introduced. Either HA Core (starting 2023.4 likely) or one of the latest addon updates.

Home screen query box not working at all, no matter if multi- or one-line statements are used. I tend to say the addon is partly broken.

[HansRemmerswaal]

@bcutter
You need to revert back to v3.9.2, a few replies back is described how to do this.

[bcutter]

@bcutter You need to revert back to v3.9.2, a few replies back is described how to do this.

Strongly disagree. That's a workaround (like using one-liner statements), at best. A solution would be the addon to provide a new version which downgrades it to a working state version.

[Linwood-F]

@bcutter You need to revert back to v3.9.2, a few replies back is described how to do this.

Strongly disagree. That's a workaround (like using one-liner statements), at best. A solution would be the addon to provide a new version which downgrades it to a working state version.

I think it was offered as a workaround to someone, not a final solution. I assume the author will at some point tackle this and fix it more completely. They may be off on a long ocean cruise or something. :)

[HansRemmerswaal]

Indeed it's currently the only working solution till the author is coming with a fix on 3.10.0
And to be honest, there is nothing wrong with 3.9.2.

So let the author enjoy that "long ocean cruise" and who knows it gets resolves somewhere in the future.

[bcutter]

OK another classic sit and wait situation. Luckily I don't use the addon to query stuff on a daily basis so my workaround of using one-lined statements is my way to go for now.

Clearly it must be one of these changes which has introduced these issues:

Looking forward to a new release fixing these.

[matthewhadley]

Note the recent version 4.0.0 of this Add-On does not fix this issue.

[Linwood-F]

That's a shame. It does appear that with current HA versions the old working one still works (I am a bit confused by the whole /config -> /homeassistant migration whether it would break anything, but the link from config still seems to work with older tools).

[thanasiskalos]

Note the recent version 4.0.0 of this Add-On does not fix this issue.

agree. I can understand the effort the developers put on , but a known bug exists for more than a month , new release is published and the bug still exists.

[Linwood-F]

agree. I can understand the effort the developers put on , but a known bug exists for more than a month , new release is published and the bug still exists.

Just from looking at the PR history the "owner" of this is someone who seems to own about half the addons out there, and the most recent changes were largely compatibility changes for ongoing HA work done by someone else. I.e. I think this addon is a tail wagged by a larger dog for the latest changes, not someone actually working on this add-on specifically.

I don't know any of the folks involved nor am I involved in HA generally, just remember these are all volunteers who keep this particular train running, so when it runs on time, be impressed, and a little forgiving if it's late.

[HansRemmerswaal]

agree. I can understand the effort the developers put on , but a known bug exists for more than a month , new release is published and the bug still exists.

Just from looking at the PR history the "owner" of this is someone who seems to own about half the addons out there, and the most recent changes were largely compatibility changes for ongoing HA work done by someone else. I.e. I think this addon is a tail wagged by a larger dog for the latest changes, not someone actually working on this add-on specifically.

I don't know any of the folks involved nor am I involved in HA generally, just remember these are all volunteers who keep this particular train running, so when it runs on time, be impressed, and a little forgiving if it's late.

I fully agree with you. I did a rollback to 3.9.2 and all is working fine for me. So even if it takes a year to fix the issue, I'm not blocked.

But I have to say that I was surprised to see that after 3.10.0 also 4.0.0 is having the issue. I assume that the person who released the latest version did some basic testing before releasing it, and for that person it then worked. So is there maybe something "else" causing the issue? A conflict with our configuration maybe?

[pearj]

I created an issue over at coleifer/sqlite-web#140, fingers crossed something can be done.

[Santanachia]

I found the issue in the upstream project that broke it, they switched SQL queries from POST to GET, which is tripping up the HA ingress security filter:

it seems that not everyone has GET?
#281 (comment)

[pearj]

it seems that not everyone has GET?

it’s still GET. What they reported is the URL the browser shows, mine is the same. It’s just running in a frame, so you can’t see the query in the URL. But if you right-click view frame source you can see the URL with the GET query.

[Santanachia]

it’s still GET. It’s just running in a frame...

you're right, I always opened in a new window, not in a frame and that's why I pointed it out
#281 (comment)

[coleifer]

Just jumping in here from coleifer/sqlite-web#140 -- here's the comment I posted:

---

I think this is a bug in the "ha" code, as it's perfectly fine for newlines and carriage returns to be passed in the GET as long as they're quoted (as far as I can tell):

>>> from urllib.parse import *
>>> unquote('q=foo%0D%0Abar')
'q=foo\r\nbar'
>>> parse_qs('q=foo%0D%0Abar')
{'q': ['foo\r\nbar']}

As you can see, Python has no problems with newlines. It's the janky middleware in the "ha" application that is being overly-strict, in my opinion.

---

Nonetheless, and because in the name of that holiest talisman "security" any nonsense is justifiable nowadays, I doubt the HA people will walk this back any time soon. So I've patched it on my side to return the form to a POST by default (though GET is still supported).

[pearj]

Thanks @coleifer, I really appreciate the quick turnaround even despite not fully agreeing with the underlying reasons.

[pearj]

If you're willing to "Live on the edge" (pun intended), you can install the Edge version of SQLite Web Addon at: https://addons.community/edge/

It includes the fixed version that was merged in #294. Otherwise, you can wait until the next release whenever that happens.

I just installed the Edge version, which fixes the problem of new lines in SQL queries.

I'm unsure if the edge version auto-update's though, so it might just a workaround until the next release.

[baylanger]

If you're willing to "Live on the edge" (pun intended), you can install the Edge version of SQLite Web Addon at: https://addons.community/edge/

It includes the fixed version that was merged in #294. Otherwise, you can wait until the next release whenever that happens.

I just installed the Edge version, which fixes the problem of new lines in SQL queries.

I'm unsure if the edge version auto-update's though, so it might just a workaround until the next release.

I added the edge repository but I'm unclear how to install the add-on from the edge repo. After adding the repo, I uninstalled SQLIteWeb but when trying to install, it still shows v4.1.0. From the HA EDGE page, there's no instructions :( Would you mind sharing how you've done it?

Thank you.

[pearj]

I think it takes quite a while for the edge repository to install. I reckon it might have been like 5 minutes until I could install things from it. I was impatient enough that I removed the repository and added it a few times until I went and did something else then I noticed I was getting 2 results in the add-on store for SQLite web. The edge version is bb44e95 for me instead of 4.1.0.

I hope that helps.

[baylanger]

I was impatient enough

Came back here ~5 hours later and read your reply. I was impatient as well, lol. I removed the stable release and now the add-on allows me to install from either stable or edge. Fantastic, thank you!

[pisolofin]

I tried to install the EDGE version, for me 99c6355, but i get 404: Not Found for the query select * from states

[pearj]

Hi @pisolofin,

turns out my SQLite Web auto-upgraded to 99c6355 but it works fine for me with and without spaces in the query.

[pisolofin]

I tried just now and yes, the query works fine, except for the main page
If I try to run the query from the main page, I get 404: Not Found

[pearj]

Oh you’re right. I hadn't even noticed that query spot on the main page, because I use HA on my phone most of the time.

I also noticed that if you do a multiline query it does a 400 bad request, so it looks like that still does a GET request there.

[pearj]

@pisolofin I’ve commented on the SQLite Web issue that's linked to this ticket and will see what happens.

[coleifer]

Yes my mistake, this is now fixed in 0.6.3 - the index page query form was still using GET and has now been switched to use POST.

[pearj]

@coleifer that fixes the bad request problem thanks.

@frenck Is it possible for the addon system to pass the base URL prefix through? I think that's why we're getting a 404 because SQLite Web doesn't know its base URL.

[pearj]

Oh, I can see a previous pull request that tried fixing some URLs in #260. Adding /query/ to sqlite-web/rootfs/etc/nginx/templates/ingress.gtpl will probably fix it, but it is not ideal.

sqlite-web supports providing a base URL with --url-prefix I noticed, and it looks like we could provide that here

https://github.com/hassio-addons/addon-sqlite-web/blob/main/sqlite-web/rootfs/etc/s6-overlay/s6-rc.d/sqlite-web/run#L17

Perhaps:

--url-prefix $(bashio::addon.ingress_entry)

I might try it out later.

[frenck]

Sounds good, feel free to PR that @pearj 👍

../Frenck

[HansRemmerswaal]

Closed? I have the latest version but get this error when the query is on 2 lines.

But from here it works. So maybe the query option on the first page just needs to be removed :)

[frenck]

Closed? I have the latest version but get this error when the query is on 2 lines.

@HansRemmerswaal It has been fixed on dev.

Should be part of the next release, currently testing out edge builds on various platforms.

[HansRemmerswaal]

Sorry, I was to fast :)

Minutes ago the update arrived and I can confirm all is working fine now.

[frenck]

No worries! Thanks for the confirmation ❤️