Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)
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.
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=9506395904r=1w=2,
http://173.79.223.25/?l=dbi-devm=95077716125217w=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/
Re: Escaping placeholders
On Fri, Dec 19, 2014 at 6:12 AM, Alexander Foken alexan...@foken.de wrote: 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. But the fundamental problem was not solved, there was and still is no way to escape placeholders. I think the suggestion of making ::(\w+) become :$1 and exempting that from placeholder recognition seems like a complete winner and DBD maintainers could do that right away, and by do that I mean accepting, applying, and redistributing patches..
Re: Escaping placeholders
Please disregard my previous. After reading Tim Bunce's earlier response and thinking about this some more, although backslash escaping can be tricky, that is how Perl does these things, and Perl coders are familiar with the nuances of when they must be doubled to get through quoting. Doubling of syntax characters is a database thing, but it would have to be revised for every new escapable character. Backslash escaping can be embraced once and will continue to work in potentialfutures where other significant characters (aside from colon and question mark) might need to be escaped too. On Fri, Dec 19, 2014 at 10:27 AM, David Nicol davidni...@gmail.com wrote: I think the suggestion of making ::(\w+) become :$1 and exempting that from placeholder recognition seems like a complete winner and DBD maintainers could do that right away, and by do that I mean accepting, applying, and redistributing patches..
