Re: jsonb problematic operators

On 16 December 2016 at 17:08, Matteo Beccati <php(at)beccati(dot)com> wrote:
> Hi,
>
> On 12/12/2016 05:09, Craig Ringer wrote:
>> Does PDO let you double question marks to escape them, writing ?? or
>> \? instead of ? or anything like that?
>>
>> If not, I suggest that you (a) submit a postgres patch adding
>> alternative operator names for ? and ?|, and (b) submit a PDO patch to
>> allow ?? or \? as an escape for ? .
>
> For reference, my plan would be to get "\?" into PDO_pgsql for PHP 7.2.
> I've tried to get it into 7.1, but I was a bit too late into the RC
> process to safely do that.
>
> Since PDO itself has no escaping yet, I'm open to suggestions wrt to the
> actual escape method to use.

SQL typically uses doubling, such that the literal

I'm

becomes

'I''m'

and the identifier

Bob "Kaboom" Jones

becomes

"Bob ""Kaboom"" Jones"

so it would be consistent with that to use ?? as a literal ? in the
output query.

This is also what PgJDBC does, per
https://jdbc.postgresql.org/documentation/head/statement.html . So
it's consistent .

PHP usually uses backslash escapes, C-style. But this is UGLY when
you're escaping something in a string. Since \? already has a defined
meaning in PHP, you have to write \\? so that the first \ is consumed
by string parsing and the resulting \? is sent to PDO, which then
turns it into ? in the output SQL. This will confuse a lot of users.
Using ?? has no such issues.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services