I have a query that uses an array as follows:
SELECT * FROM devices WHERE code = ANY ('{"value1","value2","..."}');
If value1
is instead val"ue1
the query will fail. I can solve this by using a backslash before the double quote. I’m trying to escape this using standard pg_ functions however pg_escape_string
, pg_escape_literal
or pg_escape_identifier
don’t appear to format the strings correctly.
Is there a specific function to escape these types of array or do I just use write my own?
Are there any better suggestions as to how I could write the query?
Ideally I’d like to use parameterized queries for this but it doesn’t seem possible to pass arrays as parameters in pg_query_params()
Many thanks in advance.
Advertisement
Answer
You can use this modified version of the query:
SELECT * FROM devices WHERE code = ANY (ARRAY['value1','value2','...']::text[]);
Escape value1
.. valueN
using pg_escape_string
and enclose them in single quotes.