Re: Escaping placeholders

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.