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

Question

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.

The manual says otherwise. params - An array of parameter values to substitute for the $1, $2, etc. placeholders in the original prepared query string. The number of elements in the array must match the number of placeholders. — Dave
– Dave, Commented Feb 3, 2021 at 20:39
But you can't assign $a=array() then do pg_query_params($conn,"SELECT * FROM table WHERE id=ANY($1)",[$a]); php and/or postgres throws an error. — Adam
– Adam, Commented Feb 4, 2021 at 9:14

Stefanov.sm · Accepted Answer · 2021-02-03 18:18:27Z

1

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.

answered Feb 3, 2021 at 18:18

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

Thanks. Appreciate the help.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related