SQL queries don't work with new lines anymore

[pearj]

trafficstars

The problem

With the latest core release, my SQL queries stopped working, including inside SQLlite web.

This used to work:

SELECT *
FROM   "states" s
       INNER JOIN "states_meta" sm
               ON sm.metadata_id = s.metadata_id
WHERE  sm.entity_id = "sensor.shower_humidity"
       AND Datetime(last_updated_ts, 'unixepoch') BETWEEN
           Datetime('now', '-12 hours') AND Datetime('now', '-5 minutes')
ORDER BY last_updated_ts DESC
LIMIT  1

But I get 400 bad requests inside SQLlite web, or in the SQL integration, an unavailable result.

But removing the newlines makes it work. Is this expected?

SELECT * FROM   "states" s        INNER JOIN "states_meta" sm                ON sm.metadata_id = s.metadata_id WHERE  sm.entity_id = "sensor.shower_humidity"        AND Datetime(last_updated_ts, 'unixepoch') BETWEEN            Datetime('now', '-12 hours') AND Datetime('now', '-5 minutes') ORDER BY last_updated_ts DESC LIMIT  1 

What version of Home Assistant Core has the issue?

core-2024.1.3

What was the last working version of Home Assistant Core?

2023.12.something

What type of installation are you running?

Home Assistant OS

Integration causing the issue

sql

Link to integration documentation on our website

https://www.home-assistant.io/integrations/sql/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[home-assistant[bot]]

Hey there @gjohansson-st, @dougiteixeira, mind taking a look at this issue as it has been labeled with an integration (sql) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of sql can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign sql Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

sql documentation sql source _{^{(message by IssueLinks)}}

[gjohansson-ST]

Seems very strange and also isn't then related to the integration. I can test later and see if I get the same issue but I don't seen why it wouldn't work if it spans multiple lines.

Are you setting it from UI or yaml config?

[pearj]

I'm setting the SQL query from the UI

[pearj]

I trawled through the logs looking for the error, and I found this:

2024-01-13 11:11:11.437 ERROR (MainThread) [homeassistant.helpers.entity] Update for sensor.shower_humidity_5_min_ago fails
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 639, in state
numerical_value = int(value)
^^^^^^^^^^
ValueError: invalid literal for int() with base 10: 'unknown'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 642, in state
numerical_value = float(value)
^^^^^^^^^^^^
ValueError: could not convert string to float: 'unknown'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 898, in async_update_ha_state
await self.async_device_update()
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1216, in async_device_update
await self.async_update()
File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 350, in async_update
self._process_manual_data(data)
File "/usr/src/homeassistant/homeassistant/helpers/trigger_template_entity.py", line 238, in _process_manual_data
self.async_write_ha_state()
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 945, in async_write_ha_state
self._async_write_ha_state()
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1066, in _async_write_ha_state
state, attr, capabilities, shadowed_attr = self.__async_calculate_state()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1003, in __async_calculate_state
state = self._stringify_state(available)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 951, in _stringify_state
if (state := self.state) is None:
^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 646, in state
raise ValueError(
ValueError: Sensor sensor.shower_humidity_5_min_ago has device class 'None', state class 'None' unit '%' and suggested precision 'None' thus indicating it has a numeric value; however, it has the non-numeric value: 'unknown' (<class 'str'>)

Does that mean I just had unexpected data returned from the query? And SQLite Web was just a red herring?

Looks like a security filter kills it for SQLite Web

Logger: homeassistant.components.http.security_filter
Source: components/http/security_filter.py:66
Integration: HTTP (documentation, issues)
First occurred: January 13, 2024 at 11:44:52 AM (6 occurrences)
Last logged: 10:54:46 AM

Filtered a request with unsafe byte query string: /query/?sql=SELECT+*%0D%0AFROM+++%22states%22+s%0D%0A+++++++INNER+JOIN+%22states_meta%22+sm%0D%0A+++++++++++++++ON+sm.metadata_id+%3D+s.metadata_id%0D%0AWHERE++sm.entity_id+%3D+%22sensor.shower_humidity%22%0D%0A+++++++AND+Datetime%28last_updated_ts%2C+%27unixepoch%27%29+BETWEEN%0D%0A+++++++++++Datetime%28%27now%27%2C+%27-12+hours%27%29+AND+Datetime%28%27now%27%2C+%27-5+minutes%27%29%0D%0AORDER+BY+last_updated_ts+DESC%0D%0ALIMIT++1
Filtered a request with unsafe byte query string: /api/hassio_ingress/Du7FLgsUERqCV3vhRqJ6XMoQHrXp5pjoXxgLi6ZgA8Q/states/query/?ordering=&export_ordering=&sql=SELECT+*+%0D%0AFROM+%22states%22+s+LIMIT+1%0D%0A
Filtered a request with unsafe byte query string: /api/hassio_ingress/Du7FLgsUERqCV3vhRqJ6XMoQHrXp5pjoXxgLi6ZgA8Q/states/query/?ordering=&export_ordering=&sql=SELECT+*+%0D%0AFROM+%22states%22+s+LIMIT+1
Filtered a request with unsafe byte query string: /api/hassio_ingress/Du7FLgsUERqCV3vhRqJ6XMoQHrXp5pjoXxgLi6ZgA8Q/states/query/?ordering=&export_ordering=&sql=SELECT+*+FROM+%22states%22+s+LIMIT+1%0D%0A
Filtered a request with unsafe byte query string: /api/hassio_ingress/Du7FLgsUERqCV3vhRqJ6XMoQHrXp5pjoXxgLi6ZgA8Q/states/query/?ordering=&export_ordering=&sql=SELECT+*+%0D%0AFROM+%22states%22+LIMIT+1

[pearj]

I found complaints about the SQLite addon, it looks like it's a known issue: https://github.com/hassio-addons/addon-sqlite-web/issues/281

I've put the query back to how it was with new lines in it and it seems to be working fine. I'm unsure why simply updating the query has fixed it? Would home assistant disable a SQL query if it starts returning bad data?

[pearj]

It’s been working with spaces for a week or so now, so I’ll just close this.