[Dbix-class] Escaping placeholders (was: Using Postgres JSONB operators in queries)
Augustus Saunders
asaunders at solfo.com
Fri Dec 19 17:55:42 GMT 2014
Tim, thanks for the detailed response. One other poster had suggested I use the dollar only placeholder setting, but as you pointed out, DBIx::Class (I guess due to SQL::Abstract) is using ?, so I got errors about mixing the two. I'm happy to contribute any discussion on the ramifications, but if you could tell me where in the DBIx::Class code I to look, then we could try \? here. Thanks!
Augustus
On Dec 19, 2014, at 2:40 AM, Tim Bunce <Tim.Bunce at pobox.com> wrote:
> 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.
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
More information about the DBIx-Class
mailing list