Many, many years ago, DBD::Informix had to give up on the DBI-provided
parsing for placeholders because there were too many contexts in which it
was wrong for Informix. It may have improved since then, but:
INSERT INTO SomeTable(DateTimeCol)
VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND);
is valid Informix-dialect SQL with no placeholders whatsoever. The
Standard SQL notation would be:
VALUES(TIMESTAMP '2014-12-31 23:59:59')
and many systems would likely get away without needing the TIMESTAMP
(automatically coercing the string into the correct type). Indeed,
Informix accepts the string notation (without the TIMESTAMP prefix), but
the other notation is also valid and therefore DBD::Informix must support
it. The standard notation avoids problems because the colons are embedded
within strings, whereas the Informix DATETIME literal notation uses
parentheses instead of quotes around what is otherwise effectively a
string. I could also insert INTERVAL(23 13:45:19) DAY TO SECOND with
similar comments about its behaviour.
Also, Informix uses the notation:
dbase@server:owner.table
for a fully qualified table name, with no placeholders present. You can
omit the '@server' to reference the current server (hence dbase:owner.table
is valid too); you can omit the 'owner.' if you want to refer to a table
(depending on context) owned by the current user or uniquely named
belonging to an arbitrary user (so dbase:table is valid too). You can omit
the dbase altogether, but then the colon isn't present and it is no longer
relevant to the handling of placeholders prefixed by colon. This leads to
the synoptic notation:
[dbase[@server]:][owner.]table
I think I raised this as an issue back in the 1996-1998 timeframe (I said
'many years ago' and meant it). I'd have to dig through my release notes
to be more precise. Informix only supports natively the `?` placeholders.
It doesn't yet have the complexities introduced by the PostgreSQL operators.
I don't know whether this can be handled at all. It may be that
DBD::Informix has to stay out in isolation — but it would be nice if it
wasn't necessary.
On Sat, Dec 20, 2014 at 8:35 AM, Alexander Foken <alexan...@foken.de> wrote:
>
> 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/
>
>
--
Jonathan Leffler <jonathan.leff...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
