Re: Escaping placeholders

Hello all,

this reminds me of a similar problem I had in 2000 with DBI, 
DBD::Oracle, and Oracle. See 
<http://marc.info/?t=95063959000004&r=1&w=2>, 
<http://173.79.223.25/?l=dbi-dev&m=95077716125217&w=2>.

Problem was using named placeholders (":foo") in DBI and at the same 
time use PL/SQL code containing variables (":bar"), DBI considered both 
":foo" and ":bar" to be placeholders instead of leaving ":bar" alone and 
pass it to Oracle. A set of patches from Michael A. Chase allowed 
disabling parts or all of the placeholder parsing, so using unnamed 
placeholders ("?") allowed using PL/SQL variables in SQL statements.

But the fundamental problem was not solved, there was and still is no 
way to escape placeholders.

I think being able to escape placeholders (both unnamed AND named ones) 
would help. A backslash seems reasonable, but doubling the question mark 
resp. the colon works with single and double quoting, you don't have to 
think about escaping backslashes in double quoted strings. And with 
doubling, you don't need to quote backslashes.

My ancient problem could have been solved by simply replacing every 
colon in the PL/SQL code with either a backslash and a colon or two colons.

Alexander


On 19.12.2014 11:40, Tim Bunce 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.


-- 
Alexander Foken
mailto:[email protected]  http://www.foken.de/alexander/