Re: Escaping placeholders

On 20.12.2014 23:10, Tim Bunce wrote:
> 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?
[...]
>> 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.

The current problem with any kind of DBI placeholders is that there is 
no way to prevent DBI (including any DBDs) to interpret them as 
placeholders. This hasn't changed since 2000. The patches from 2000 
disable placeholders by type (?, $1, :foo) or entirely, but it is not 
possible to pass :foo unchanged and uninterpreted to the database while 
at the same time having a named placeholder :bar.

The proposed backslash escape allows exactly this, so you can have a 
named placeholder :bar and can pass :foo unchanged and uninterpreted to 
the database, but you have to add a backslash in front of :foo. No 
problems with old code for this usage, because this was simply not possible.

But with the proposed backslash escape, the backslash in SQL statements 
passed to the DBI will suddenly have a new meaning, were it is currently 
passed unmodified to the database. This will break old code.

Making the backslash escape special only directly in front of possible 
placeholders will probably break less code, because all other 
backslashes will behave as before. At the same time, the rules when a 
backslash is needed will become more complicated, and they may depend on 
the DBD, because different DBDs implement differrent placeholders (as 
far as I understand DBI).

Also, the rule that a backslash in front of a possible placeholder 
passes the placeholder, but not the backslash, unmodified to the 
database prevents passing a backslash followed by an actual placeholder 
to the database. This is possible with the current DBI code.

So, the backslash-is-only-special-in-front-of-placeholders rule breaks 
some existing code, makes it impossible to have a backslask in front of 
a placeholder, and the rules for when to use a backslash depend on the 
DBD implementation. This will likely lead to another change that will 
again changes the rules and break old code.

Can we do better? I think that we can:

As there is currently no escaping mechanism for placeholders in DBI, any 
new escaping mechanism will break some existing code. Breaking old code 
that uses DBI has happened before, e.g. in  DBI 1.20, 24th August 2001 
(selectall_hashref) DBI 1.19, 20th July 2001 (fetchall_arrayref), DBI 
0.91, 10th December 1997 (connect).

1. Use a clean escaping mechanism that won't need future changes that 
will again break old code:

Make all backslashes in SQL special. Require double backslashes for a 
single backslash at database level. Require escaping characters that 
otherwise might be parsed as placeholders (:, ?, $). Make unknown escape 
sequences fatal errors. This allows mixing placeholders and 
placeholder-like SQL sequences as before, it allows placing backslashes 
in front of placeholders, and it allows future extensions using the 
currently unknown escape sequences.

Existing code just has to double each and every backslash in SQL code.

2. Slowly introduce the new mechanism:

First, the backslash-escape must explicitly be enabled using an 
attribute BackslashEscape during connect(). Default is off, i.e. no 
backslash escaping. No old code should have problems. New code can 
enable backslash-escapes when needed by using connect() with 
BackslashEscape=on.

After a while, change the default to warn, so that backslash escaping 
stays off, but any backslash in SQL statements will issue a 
compatibility warning. Old code will still run unmodified, but will 
likely issue some warnings. Those warnings can be disabled by adding 
BackslashEscape=off to connect().

After some more time, make changing BackslashEscape to off cause a 
compatibility warning. Old unmaintained code will still run unmodified, 
but issue some warnings. Maintained old code that sets BackslashEscape 
to off will issue one warning per connect.

After even more time, change the default to on, so backslash escaping is 
enabled. This will intentionally break old unmaintained code that was 
not modified to switch off BackslashEscape. Old maintained code will 
continue to run, but issue one warning per connect.

Finally, remove the non-backslash-escape code and make swiching to that 
code (off or warn) a fatal error. This will intentionally break old code 
that had a one-time maintainance but was not changed to use backslashes 
properly.

The above is the user view of DBI.

DBI developer view:

As far as I understand the DBI, DBDs may also parse SQL code. They need 
to be changed to respect the BackslashEscape attribute. Using old DBDs 
that can't handle the BackslashEscape attribute must cause a fatal error 
when the user attempts to set BackslashEscape to on.

Because the backslash has no special meaning in existing SQL code, it 
can safely be doubled and passed to a new parser when BackslashEscape is 
not on. There is no need to keep the old parser around, neither in DBI 
nor in any DBD.

Alexander

>
> Tim.


-- 
Alexander Foken
mailto:[email protected]  http://www.foken.de/alexander/