Re: Using Postgres JSONB operators in queries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Using Postgres JSONB operators in queries

Augustus Saunders
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 ?. 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. 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? Thanks-

Augustus


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Postgres JSONB operators in queries

Darren Duncan
On 2014-12-04 3:38 PM, 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 ?. 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. 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? Thanks-

At the DBI level anyway, you can use this:

http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_placeholder_dollaronly_(boolean)

If you set that DBD::Pg option, then literal ? are not treated as placeholders
and you instead have to use other forms like $1,$2 etc or named placeholders; on
the plus side, those also let you use the same placeholder more than once in a
statement rather than having to pass in the same bind value multiple times.

This being said, I don't know if DBIx::Class is compatible with that way of
using PostgreSQL, not that this can't change.

-- Darren Duncan


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Postgres JSONB operators in queries

Augustus Saunders
Interesting, thanks for the information. I hadn't realized that you could use $1, $2 etc at the DBI/bind level. I do have queries where the same value is repeated, so that should be helpful regardless of the ? class of operators.

Augustus

On Dec 16, 2014, at 8:19 PM, Darren Duncan <[hidden email]> wrote:

> On 2014-12-04 3:38 PM, 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 ?. 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. 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? Thanks-
>
> At the DBI level anyway, you can use this:
>
> http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_placeholder_dollaronly_(boolean)
>
> If you set that DBD::Pg option, then literal ? are not treated as placeholders and you instead have to use other forms like $1,$2 etc or named placeholders; on the plus side, those also let you use the same placeholder more than once in a statement rather than having to pass in the same bind value multiple times.
>
> This being said, I don't know if DBIx::Class is compatible with that way of using PostgreSQL, not that this can't change.
>
> -- Darren Duncan
>
>
> _______________________________________________
> 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@...


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Postgres JSONB operators in queries

Darren Duncan
Keep in mind that those dollar-names are a PostgreSQL specific feature, their
native way to refer positional routine parameters.  In your Perl code the SQL
will have to be making those literals, eg non-interpolating dollar signs. --
Darren Duncan

On 2014-12-17 10:28 AM, Augustus Saunders wrote:

> Interesting, thanks for the information. I hadn't realized that you could use $1, $2 etc at the DBI/bind level. I do have queries where the same value is repeated, so that should be helpful regardless of the ? class of operators.
>
> Augustus
>
> On Dec 16, 2014, at 8:19 PM, Darren Duncan <[hidden email]> wrote:
>
>> On 2014-12-04 3:38 PM, 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 ?. 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. 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? Thanks-
>>
>> At the DBI level anyway, you can use this:
>>
>> http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_placeholder_dollaronly_(boolean)
>>
>> If you set that DBD::Pg option, then literal ? are not treated as placeholders and you instead have to use other forms like $1,$2 etc or named placeholders; on the plus side, those also let you use the same placeholder more than once in a statement rather than having to pass in the same bind value multiple times.
>>
>> This being said, I don't know if DBIx::Class is compatible with that way of using PostgreSQL, not that this can't change.
>>
>> -- Darren Duncan
>>
>>
>> _______________________________________________
>> 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@...
>
>
> _______________________________________________
> 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@...
>


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Postgres JSONB operators in queries

Augustus Saunders
Yeah. What I have to figure out is how to use this with DBIx::Class since it is using ? placeholders behind the scenes, and I'm getting errors about mixing ? and $1 placeholders. I haven't had a chance to look inside DBIx to see what's involved in changing that. Thanks for the help-

Augustus

On Dec 18, 2014, at 4:12 PM, Darren Duncan <[hidden email]> wrote:

> Keep in mind that those dollar-names are a PostgreSQL specific feature, their native way to refer positional routine parameters.  In your Perl code the SQL will have to be making those literals, eg non-interpolating dollar signs. -- Darren Duncan
>
> On 2014-12-17 10:28 AM, Augustus Saunders wrote:
>> Interesting, thanks for the information. I hadn't realized that you could use $1, $2 etc at the DBI/bind level. I do have queries where the same value is repeated, so that should be helpful regardless of the ? class of operators.
>>
>> Augustus
>>
>> On Dec 16, 2014, at 8:19 PM, Darren Duncan <[hidden email]> wrote:
>>
>>> On 2014-12-04 3:38 PM, 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 ?. 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. 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? Thanks-
>>>
>>> At the DBI level anyway, you can use this:
>>>
>>> http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_placeholder_dollaronly_(boolean)
>>>
>>> If you set that DBD::Pg option, then literal ? are not treated as placeholders and you instead have to use other forms like $1,$2 etc or named placeholders; on the plus side, those also let you use the same placeholder more than once in a statement rather than having to pass in the same bind value multiple times.
>>>
>>> This being said, I don't know if DBIx::Class is compatible with that way of using PostgreSQL, not that this can't change.
>>>
>>> -- Darren Duncan
>>>
>>>
>>> _______________________________________________
>>> 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@...
>>
>>
>> _______________________________________________
>> 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@...
>>
>
>
> _______________________________________________
> 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@...


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Escaping placeholders (was: Using Postgres JSONB operators in queries)

Tim Bunce
In reply to this post by Augustus Saunders
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders (was: Using Postgres JSONB operators in queries)

Augustus Saunders
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 <[hidden email]> 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@...


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Tim Bunce
In reply to this post by Tim Bunce
On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote:

> 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.

Can you, or anyone else, think of any situation where a backslash before
a ? or :foo (or even $1) style placeholder might be valid SQL?

So far no one has come up with one, so I'm getting more comfortable
with the idea that a backslash before a placeholder is a safe change.
I.e., there's a near-zero risk that upgrading a DBI driver to support
backslashes would cause breakage in existing code.

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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders (was: Using Postgres JSONB operators in queries)

Tim Bunce
In reply to this post by Augustus Saunders
There'll need to be a change to DBD::Pg before \? will work.

I've opened a case for it https://rt.cpan.org/Ticket/Display.html?id=101030

Once implemented you'd use the usual SQL::Abstract way of specifying
operators: https://metacpan.org/pod/SQL::Abstract#Specific-comparison-operators

Tim.

On Fri, Dec 19, 2014 at 09:55:42AM -0800, Augustus Saunders wrote:

> 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 <[hidden email]> 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@...
>
>
> _______________________________________________
> 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@...

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Numbered placeholders and DBIC

Peter Rabbitson-2
In reply to this post by Tim Bunce
On 12/19/2014 11:40 AM, Tim Bunce wrote:
>
> 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.)
>

On 12/17/2014 05:19 AM, Darren Duncan wrote:
> This being said, I don't know if DBIx::Class is compatible with that way of using PostgreSQL, not that this can't change.

Actually DBIC/SQLA likely will not be able to support numbered
placeholders for a very long time if ever at all.

The issue stems from the reliance of SQLA/DBIC on "composable SQL
snippets", that is the output of intermediate renderers is "some sql
with the bind values to go with it". Such expressions are inherently
unorderable (how do you know where a particular subquery is going to be
used, before/after what other bind variables?)

There is likely a way to do a parse at the *final* assembly and replace
every ? with a numbered placeholder but it will be 1) fragile, 2) only
done for the sake of few engines.

As such I see it as a very low priority bordering on an anti-feature,
especially given that the overhead of nonexistent bind-value-reuse is
exceedingly small in practice.

Of course I could be quite misguided in my cost/benefit analyzis - feel
free to tell me where I am wrong.

Cheers


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Tim Bunce
In reply to this post by Tim Bunce
On Sat, Dec 20, 2014 at 05:35:55PM +0100, Alexander Foken wrote:

> On 20.12.2014 15:38, Tim Bunce wrote:
> >Can you, or anyone else, think of any situation where a backslash before
> >a ? or :foo (or even $1) style placeholder might be valid SQL?
>
> I found two situations for PostgreSQL:
>
> (1) PostgreSQL allows almost any character as escape character in
> Unicode string constants (<http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE>).
> With that, I can construct  an expression containing \:foo that is
> valid SQL as understood by PostgreSQL:
>
>     U&'foo\:AAAAbar' UESCAPE ':'
>
> This expression represents the string foo\Xbar, where X is the
> Unicode character U+AAAA ("TAI VIET LETTER LOW VO").

I don't think that'll be a problem because the driver code that parses
the statement looking for placeholders will skip over quoted strings.

> (2) PostgreSQL also allows "Dollar quoting" (<http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING>).
> With that, I can construct an expression containing \$1 that is
> valid SQL as understood by PostgreSQL:
>
>     $1$foo\$1$
>
> This expression represents the string foo\, quoted by dollar signs
> using the character 1 as tag.

I'm not sure if the driver code that parses statements in DBD::Pg
handles dollar quoting. I presume so. In which case this shouldn't be a
problem either for the same reason as above.

> >So far no one has come up with one, so I'm getting more comfortable
> >with the idea that a backslash before a placeholder is a safe change.
> >I.e., there's a near-zero risk that upgrading a DBI driver to support
> >backslashes would cause breakage in existing code.
>
> Do you plan to escape the escape character, i.e. use a double
> backslash at DBI level to represent a single backslash at database
> level?

That's a good question. I'm not sure. I think the answer has to be no.
I'd welcome any input on that.

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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Tim Bunce
In reply to this post by Tim Bunce
On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote:
>    Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders because
>    there were too many contexts in which it was wrong for Informix.  It may have improved since then, but:
>
>          INSERT INTO SomeTable(DateTimeCol)
>            VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND);

>    I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and meant
>    it).  I'd have to dig through my release notes to be more precise.  Informix only supports natively the
>    `?` placeholders.  It doesn't yet have the complexities introduced by the PostgreSQL operators.
>
>    I don't know whether this can be handled at all.  It may be that DBD::Informix has to stay out in
>    isolation but it would be nice if it wasn't necessary.

The `?` placeholders are 'standard' (for some definition) so DBD::Informix
isn't really 'in isolation'. There are quite a few drivers that only
support `?` placeholders.

In theory, if this proposal goes ahead, and is applied to `:` placeholders
as seems likely, then you'd be able to write the above as:

           INSERT INTO SomeTable(DateTimeCol)
             VALUES(DATETIME(2014-12-31 23\:59\:59) YEAR TO SECOND);

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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Tim Bunce
On Sat, Dec 20, 2014 at 02:23:43PM -0800, Jonathan Leffler wrote:

>
>      INSERT INTO SomeTable(DateTimeCol)
>                  VALUES(DATETIME[1](2014-12-31 23\:59\:59) YEAR TO SECOND);
>
>    I really won't want people have to futz with their legitimate Informix SQL in order to pass it through
>    DBD::Informix.
>
>    Whatever is provided, whether by DBI or DBD::Informix, must accept the code without the
>    backslashes in front of the colons.  It is simply not acceptable to have to modify valid SQL to get it
>    past the gatekeeper code.
Understood. I wouldn't expect DBD::Informix to enable this by default.

We were only discussing a hypothetical situation where DBD::Informix
could optionally enable use of colon placeholders, if desired.

>    At the moment, the unescaped code works fine.  It will continue to work fine.  As long as DBI does not
>    break the currently working code, I will survive �" like I have for the last decade and more.  Just
>    make sure that whatever you do does not break working valid Informix SQL code.

I have absolutely no intention of breaking anything :)

It'll be up to the individual driver authors to add support for escaping
placeholders, if they want to.

(The DBI has a built-in preparse function that's intended for parsing
placeholders but few, if any, drivers use it. I know DBD::Informix doesn't.)

Tim.

>    On Sat, Dec 20, 2014 at 2:17 PM, Tim Bunce <[2][hidden email]> wrote:
>
>      On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote:
>      >    Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders
>      because
>      >    there were too many contexts in which it was wrong for Informix.  It may have improved since
>      then, but:
>      >
>      >          INSERT INTO SomeTable(DateTimeCol)
>      >            VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND);
>
>      >    I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and
>      meant
>      >    it).  I'd have to dig through my release notes to be more precise.  Informix only supports
>      natively the
>      >    `?` placeholders.  It doesn't yet have the complexities introduced by the PostgreSQL operators.
>      >
>      >    I don't know whether this can be handled at all.  It may be that DBD::Informix has to stay out in
>      >    isolation but it would be nice if it wasn't necessary.
>
>      The `?` placeholders are 'standard' (for some definition) so DBD::Informix
>      isn't really 'in isolation'. There are quite a few drivers that only
>      support `?` placeholders.
>
>      In theory, if this proposal goes ahead, and is applied to `:` placeholders
>      as seems likely, then you'd be able to write the above as:
>                  INSERT INTO SomeTable(DateTimeCol)
>                    VALUES(DATETIME[3](2014-12-31 23\:59\:59) YEAR TO SECOND);
>
>      Tim.
>
>    --
>    Jonathan Leffler <[4][hidden email]>  #include <disclaimer.h>
>    Guardian of DBD::Informix - v2013.0521 - [5]http://dbi.perl.org
>    "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
>
> References
>
>    Visible links
>    1. file:///tmp/tel:%282014-12-31%2023
>    2. mailto:[hidden email]
>    3. file:///tmp/tel:%282014-12-31%2023
>    4. mailto:[hidden email]
>    5. http://dbi.perl.org/

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Tim Bunce
In reply to this post by Tim Bunce
On Sun, Dec 21, 2014 at 10:27:18AM +0100, [hidden email] wrote:

> On 20.12.2014 15:38, Tim Bunce wrote:
> > [...]
> > Can you, or anyone else, think of any situation where a backslash before
> > a ? or :foo (or even $1) style placeholder might be valid SQL?
>
> Inside quoted text: of course, yes.
> Outside quoted text: maybe some RDBMS accept a backslash as a valid character
> in the name of a table or column? (Haven't tried this out yet.)
> At least make sure "\?" will not be handled as placeholder inside column name
> quoting, as in e.g.
> "foo \? bar"

That's standard identifier quoting so should be ignored by the driver
for the same reason that single quoted strings are.

> or in
> [foo \? bar]
> with SQL Server, or
> `foo \? bar`
> for MySQL.

For databases that support those non-standard identifier quoting styles
the driver should be treating them as strings and so skipping them anyway.

> But my fear is that even
> foo\?bar
> would be accepted as valid column or table name by some RDBMS...

I find that very hard to believe without any evidence.
Even if true, the driver for such a database would, I presume,
treat that ? as a placeholder and so it would already be broken.

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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders

Darren Duncan
In reply to this post by Tim Bunce
I agree with Greg's counter-proposal, from which I derive my own words here.

1.  I propose that there be no modification to the DBI spec related to new
escaping whether optional or not, so leave things the way they are here, SQL
continues to be interpreted the way it long has by default.

2.  When users want to use operators in PostgreSQL that contain literal ? in
their names, then they enable DBD::Pg's pg_placeholder_dollaronly so that ? are
no longer treated as placeholders.  Likewise, pg_placeholder_nocolons can be
enabled when they don't want literal : to indicate a placeholder either.  Users
would either do this directly if they're using DBI/DBD::Pg directly, or
indirectly using their over-top framework of choice.  When users aren't using
the ? operators et al, they can leave things alone which will work as normal.

3.  SQL::Abstract et al, those tools external to DBI/DBDs, are the correct and
proper places to modify where users of said want to use the operators with ?
names and such.  These tools already have special knowledge of individual DBMS
systems to work with them effectively, and the ? operators is just one more of
those things.  The users of said tools may have to flip a configuration switch
possibly so $1 etc are used behind the scenes, if necessary, but that's okay
because the use of ? operators only happens when the users choose to make a
change to use them anyway.

In summary, now is not the time or place to be introducing backslashing doubled
or otherwise in DBI such as discussed, that's a poor solution and its better to
save such risky/etc changes for when there's a more compelling case for them.

I should also mention I feel it is perfectly reasonable for each DBMS to have
operators composed of any characters they want where doing so makes sense within
the context of the SQL/etc itself.  See also that Perl itself has both ? and :
and etc as operator names, Perl 6 even more so, and I don't see anyone thinking
that's a bad idea.  So I have no problem with PostgreSQL having ? in operator
names such as it did.  Its not like the SQL standard reserves ? or whatever for
prepared statement parameters, that's defined to be implementation dependent I
believe (WD 9075-2:200w(E) 20.6 <prepare statement>).

-- Darren Duncan

On 2014-12-21 7:17 AM, Greg Sabino Mullane (the tenth man) wrote:

> Tim Bunce wrote:
>
>> For code not using DBIx::Class the pg_placeholder_dollaronly attribute
>> might work, see https://metacpan.org/pod/DBD::Pg#Placeholders
>
> Yes, this is the current canonical solution. It's a real shame
> that ? was used as an operator, but that horse has left the barn.
>
>> 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.
>
> I'm going to play devil's advocate a bit here. There are some problems with
> this approach. First, it will require that the user know if the underlying
> DBD supports backslashes. Which likely means that SQL::Abstract and/or
> DBIx::Class will need to know as well. (Unless they expose the DBD directly
> to the user, which ruins the point a bit). Since we will thus need to patch
> those other modules, so why not fix them to do the right thing? (see below
> for a counter proposal).
>
> Another problem is that we have now extended the SQL syntax for our own
> purposes. While one could argue that placeholders already do so, their
> current use is consistent, widespread (e.g. not just DBI), and in part
> used by the underlying RDBMSs themselves (e.g. Postgres uses dollar-number
> placeholders). So we will have both escaped and unescaped versions of SQL
> floating around, subject to the whims of whether or not your particular
> DBD supports it (and in which version). All of which seems like an awful
> lot of work to "fix" SQL::Abstract. Why bother patching every DBD in
> existence when we can simply patch SQL::Abstract?
>
> Which leads to my counter-proposal: have SQL::Abstract accept dollar-number
> placeholders. It can pass pg_placeholder_dollaronly down the stack as
> needed. This neatly puts the onus back onto the frameworks, rather than
> having the DBDs selectively remove backslashes before passing to the
> RDBMS (ick). DBIx::Class and friends could even map dollar signs back to
> a format supported by the underlying DBDs, if they don't support dollar
> signs (that is one of their grand purposes after all - abstracting out
> details and taking care of things in the background).


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Escaping placeholders (was: Using Postgres JSONB operators in queries)

Tim Bunce
In reply to this post by Tim Bunce
Sometime over the holiday period I hope to write up a summary of the
arguments for and against and suggest ways forwards.

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@...