[Dbix-class] Escaping placeholders

[Darren Duncan]

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