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:alexan...@foken.de http://www.foken.de/alexander/
