Hi all,
just a small note as a warning. There is a small bug in the
postgres driver of the aolsever in connection with
literal output for something looking like a bind variable.
Let us assume we have a table t
db_dml c {create table t (a text)}
and we insert
db_dml i {insert into t (a) values ('ok :x')}
into the table. In the new tuple the attribute a has
the expected value, no bind variable substitution.
Now, suppose to insert some text with single quotes, like e.g.
db_dml i {insert into t (a) values ('hello \'world\'...')}
This works as well as expected. The syntax is actually
deprecated in PostgreSQL, so we should put an E in
front of a backslash escaped string
db_dml i {insert into t (a) values (E'hello \'world\'...')}
otherwise we need the postgres config variable
for non-standard conforming strings (OpenACS
requires this). The escape string syntax is documented in:
http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
However, when we preceed a colon before "world",
things go wrong, one gets an error message
"undefined variable `world'".
db_dml i {insert into t (a) values (E'hello \':world\'...')}
same with
db_dml i {insert into t (a) values ('hello \':world\'...')}
The only safe way is to use the double single quote syntax
db_dml i {insert into t (a) values ('hello '':world''...')}
The reason for is that the postgres driver has a
semi-intelligent sql parser that does the bind variable
substitution. For this it detects whether or not
a string is a literal under single quotes, where it does
no bind variable substitutions. However, this parser
does not handle the backslash before a single quote
in a string and thinks, it is non-literal mode,
mis-interprets the literal :world as bind variable and
tries to substitute it.
The problem is even worse, when the variable exists and is injected.
So, don't escape single quotes in SQL statements with a backslash!
