Re: Escaping placeholders

On 22/12/2014 00:39, Darren Duncan wrote:
> 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

I tend to agree with Greg and Darren on this for all the same reasons.

If it could be done in DBI without affecting DBDs then I might have less 
of a problem with it as anything wanting this feature would only have to 
check DBI. There are already too many differences between DBDs and 
introducing yet another one which would make applications and interfaces 
even more complex. Plus, this seems to be a postgres specific issue and 
escaping of ? or : would only benefit other DBDs in the few rare cases 
where those are legitmate chrs in themselves.

There is already an inconsistency in placeholders as some DBDs 
need/support ':name' when binding and some only support 'name'.

DBD::ODBC already has a ignore placeholders switch for when users want 
to pass SQL containing a ? or : when neither is a placeholder e.g., SQL 
Server procedures often contain colons.

If it was not for some poor ODBC drivers that don't support SQLNumParams 
DBD::ODBC could get away with not parsing the SQL at all which would be 
my preferred solution and in fact, the version I use does not parse the 
SQL at all. Adding escaping would mean more work and probably taking a 
copy of the scalar for modification.

However, I think Greg and Darren's arguments are strong enough on their own.

If, it ends up where the DBI spec is changed to allow escaping can we 
please introduce the DBD capabilities API at the same time. I know I 
started the thread some time ago and put together an initial list then 
did not move it forward, but it would make application and interface 
layers so much easier if they could just say 'does this DBD do X' and 
maybe 'how do I make it do X'.

Martin
-- 
Martin J. Evans
Wetherby, UK

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