Re: Escaping placeholders

On 20.12.2014 15:38, Tim Bunce wrote:
> 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?

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

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


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

Alexander

>
> Tim.


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