[Dbix-class] Escaping placeholders (was: Using Postgres JSONB operators in queries)

[Tim Bunce]

Hello Augustus.

On Thu, Dec 04, 2014 at 03:38:14PM -0800, Augustus Saunders wrote:
>    Hi all, I have been unable to find a way to use some of the new
>    JSONB operators in Postgres 9.4 with DBIx::Class. A quick search
>    for JSONB on the email archive site didn't turn up any results, so
>    I thought I would ask.
>
>    In particular, ?, ?&, and ?| are now operators, and we run into
>    problems with the DBI placeholder being ?.

As more people start using Postgres 9.4 and the JSON operators this
is going to become a significant problem.

>    I read that putting single quotes around the question mark would
>    allow a literal question mark in DBI, but I can't seem to make this
>    work from DBIx::Class.

It won't do what you want. Question marks in quotes are ignored by the
DBI driver, but question marks in quotes won't work as JSON operators.

>    Can anybody tell me whether this is currently possible, if so how,
>    and if not what might be involved or where in the code to look?

For code not using DBIx::Class the pg_placeholder_dollaronly attribute
might work, see https://metacpan.org/pod/DBD::Pg#Placeholders

For code using DBIx::Class the problem is more tricky. I'm pretty sure
that SQL::Abstract and thus DBIx::Class only support question mark
placeholders. That means it probably impossible to use expressions
containing a question mark operator with SQL::Abstract/DBIx::Class.
(Though I'd be delighted to be proven wrong.)

So I think the DBI spec for placeholders needs to be extended to allow a
way to 'escape' a question mark that the driver would otherwise treat as
a placeholder.

The obvious and natural approach would be to use a backslash before a
question mark. The backslash would be removed by the driver before the
statement is passed to the backend.

  $dbh->selectrow_array(q{ SELECT {"a":1, "b":2}'::jsonb  ? 'b' }); # breaks
  $dbh->selectrow_array(q{ SELECT {"a":1, "b":2}'::jsonb \? 'b' }); # would work

The key question is: what is the risk of any existing DBI SQL statements
containing a question mark placeholder that's preceeded by a backslash?

Can anyone think of realistic examples? (For ANY DBI driver or backend.)

Tim.