Quote characters in pageviews id column

[jakubgs]

I found today a lot of errors like this in my app logs:

pq: syntax error at end of input

I checked the DB logs and found these:

ERROR:  syntax error at end of input at character 115006
STATEMENT:  DELETE FROM pageviews WHERE id IN('JZxMDeg4rDsD3sJ4sOZf','OvharGiHOo7kw9kwAAQ8','as34d' or 8=8 -- -','JRyfPt6NuRKZX4nrJna5', ...

It continues like that for a while, but the point is the 3rd entry in the IN clause, which appears to contain a ' character in it. I found these two records by querying the pageviews table:

fathom=# select * from pageviews where id like '%''%';
     hostname      | pathname | is_new_visitor | is_new_session | is_unique | is_bounce | referrer | duration |         timestamp          |         id         | site_tracking_id | is_finished 
-------------------+----------+----------------+----------------+-----------+-----------+----------+----------+----------------------------+--------------------+------------------+-------------
 https://status.im | /        | t              | t              | t         | f         |          |      268 | 2020-08-15 14:21:12.292102 | as34d' or 8=8 -- - | BMGJI            | t
 https://status.im | /get/    | f              | f              | t         | f         |          |      268 | 2020-08-15 14:25:37.997278 | as34d' or 8=8 -- - | BMGJI            | t

After removing them the errors stopped happening:

fathom=# delete from pageviews where id like '%''%';
DELETE 2

Any idea why this kind of id field value could have been generated?

[jakubgs]

Since there doesn't seem to be any interest in fixing this I have mitigated it by adding a constraint to the id column:

ALTER TABLE pageviews ADD CONSTRAINT no_single_quotes CHECK (id NOT LIKE '%''%');

Which now results in an error when trying to insert a row with single quotes in the id column:

ERROR:  new row for relation "pageviews" violates check constraint "no_single_quotes"

Better than nothing.

[JackEllis]

@LKaemmerling Not sure if this can be fixed easily, but please take a look as this is odd.