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:alexan...@foken.de http://www.foken.de/alexander/
