What php function to use when escaping strings inside arrays for postgresql queries

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.

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.