An error message was shown in the dashboard when I entered "Richard's lg g4" for my device name.
The exact error message shown was:
could not get devices: select devices.name,count(sensors.timestamp) as num from sensors inner join devices on sensors.deviceid=devices.id WHERE devices.name in ('richard's lg g4','richard') group by sensors.deviceid: near "s": syntax error
When a single quote character exists in the input string this can be escaped in sqlite by doubling it. So the query could be made valid by making my "Richard's lg g4" input into "Richard''s lg g4".
It would be better however to run parameterised queries only, as the currently applied method of concatenating strings into a query leaves the application open to sql insertion attacks, where for example a drop table statement can be inserted into the name, letting an attacker drop a table in your database. This is even more dangerous as the error message lists the exact statement executed, so it is very easy to construct the device name such that the combined strings result in a valid statement.
IoTThinks
commented
3 years ago
An error message was shown in the dashboard when I entered "Richard's lg g4" for my device name.
The exact error message shown was: could not get devices: select devices.name,count(sensors.timestamp) as num from sensors inner join devices on sensors.deviceid=devices.id WHERE devices.name in ('richard's lg g4','richard') group by sensors.deviceid: near "s": syntax error
When a single quote character exists in the input string this can be escaped in sqlite by doubling it. So the query could be made valid by making my "Richard's lg g4" input into "Richard''s lg g4".
It would be better however to run parameterised queries only, as the currently applied method of concatenating strings into a query leaves the application open to sql insertion attacks, where for example a drop table statement can be inserted into the name, letting an attacker drop a table in your database. This is even more dangerous as the error message lists the exact statement executed, so it is very easy to construct the device name such that the combined strings result in a valid statement.