Forum OpenACS Development: SQL backslash escapes and bind variables in the aolserver postgres driver

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!

So, don't escape single quotes in SQL statements with a backslash!
But this is a PG-ism in the first place, isn't it? Isn't the double single-quote the SQL standard, and required if you want the string to work in Oracle + PG in the first place?
The backslash substitution in PG is a non-standard postgres legacy and deprecated also in PG, but the extended syntax (with the preceding E) is supported.

There seem to be quite a few places, where the non-standard backslash substitution is used in OpenACS, see e.g. https://openacs.org/forums/message-view?message_id=544436
Sooner or later, we should address this.