Carto queries with special characters fail

[timwis]

https://phl.carto.com/api/v2/sql?q=SELECT count(*) from latest_employee_salaries where department = 'OFFICE OF INNOVATION & TECHNOLOGY'

throws the error:

{"error":["unterminated quoted string at or near \"'OFFICE OF INNOVATION \""]}

This appears to be because of the ampersand not being escaped. I swear this worked fine before... I think this issue only started happening in the last few months, perhaps since Carto upgraded their version of Postgres?

There's also an issue with apostrophes, though this one makes more sense and may have been happening earlier without us noticing.

https://phl.carto.com/api/v2/sql?q=SELECT count(*) from latest_employee_salaries where department = 'MAYOR'S OFFICE''

throws the error:

{"error":["syntax error at or near \"S\""]}

@andrewbt any suggestions on the proper way to escape these string literals? I imagine we'll need to replace ' with '' for the second issue; perhaps use E'' for the ampersands?

[andrewbt]

Hey @timwis ! It’s actually a way simpler fix than any of that. CARTO needs the full query string to be URL encoded. So I just took your first example and used my favorite quick page for that and this works fine as a request with no error: https://phl.carto.com/api/v2/sql?q=SELECT%20count(*)%20from%20latest_employee_salaries%20where%20department%20%3D%20%27OFFICE%20OF%20INNOVATION%20%26%20TECHNOLOGY%27

[timwis]

wow I really thought we were already doing that! thanks

[andrewbt]

@timwis I just tried the second example with the apostrophe though, that one did fail even when encoded: http://phl.carto.com/api/v2/sql?q=SELECT%20count(*)%20from%20latest_employee_salaries%20where%20department%20%3D%20%27MAYOR%27S%20OFFICE%27

But dollar quoting works on that one and I imagine would for most cases. Maybe just switch to that as a default when dealing with string columns?

So SELECT count(*) from latest_employee_salaries where department = $$MAYOR'S OFFICE$$ becomes SELECT%20count(*)%20from%20latest_employee_salaries%20where%20department%20%3D%20%24%24MAYOR%27S%20OFFICE%24%24 and works:

http://phl.carto.com/api/v2/sql?q=SELECT%20count(*)%20from%20latest_employee_salaries%20where%20department%20%3D%20%24%24MAYOR%27S%20OFFICE%24%24

[timwis]

interesting idea, thanks!