Escape a literal so that it is wrapped with double quotes

Question

We use doctrine 2 and want to write parameterised code like this:

attributes @> \'{' . $con->quote($attrId) . ':' . (int)$value . '}\'';

to have a query like this:

WHERE attributes @>'{"color":14}';

The "color" is the custom (user chosen) name of an attribute. So I feel that quote() is an appropriate function to shield it. But it wraps a parameter with single quotes, what makes the request syntax incorrect.

quoteIdentifier() function wraps with double quotes, BUT I'm not sure if it's right to use it in this context.

How to build a safe code to get the request I need?

score 1 · Answer 1 · answered Jan 24 '19 at 19:51

1

Here is a way to do it with json_build_object and pg_exec_params:

<?php
$dbconn = pg_connect('');
$data = 'some"th\'ing';
pg_query_params($dbconn, 'SELECT json_build_object($1::text, $2::integer)', [$data, 14]);
?>

You need the explicit type casts so that PostgreSQL knows whether the argument is a string or a number.

answered Jan 24 '19 at 19:51

Laurenz Albe

209,280
17
206
263

Thank you! But, do we really need pg_query_params() when json_build_object() kind of does the thing? – 7cart project Jan 24 '19 at 22:19
Just found in Postgres docs about similar function to_json(anyelement) "...For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, properly quoted and escaped so that it is a valid JSON string." – 7cart project Jan 24 '19 at 22:44

score 0 · Answer 2 · answered Jan 24 '19 at 19:31

You can include the double quotes in the string.

$attr = '{"' . $attrId . '":' . (int) $value . '}';

Don't depend on quoting to keep you safe, but instead execute the query with a method that binds the value to a prepared statement.

$statement = $con->executeQuery('SELECT * FROM your_table WHERE attributes @> ?', [$attr]);

Escape a literal so that it is wrapped with double quotes

2 Answers2