Re: [Bacula-devel] PostgreSQL quoting

[Bill M. <wm...@co...>]

In response to "Dan Langille" <da...@la...>:

> On 3 Sep 2007 at 13:35, Martin Simmons wrote:
> 
> > >>>>> On Sat, 01 Sep 2007 11:44:03 +0200, Marc Cousin said:
> > > 
> > > I think you're trying to solve a fake problem here :
> > > PQescapeStringConn does the job as required, there is no problem except a 
> > > warning.
> 
> As I understand it, the warning is there to highlight behaviour which 
> will not be accepted in future versions of PostgreSQL.

The warning is there for a reason, as Dan points out, future versions of
PostgreSQL will _not_ work with the syntax that Bacula uses now, and the
PGDG is _trying_ to give people ample notice of that.

[snip]

> > > We therefore have three solutions :
> > > - either we tell postgresql to stop whining (we disable escape_string_warning) 
> > > at the session level (it only means sending a 'set escape_string_warning to 
> > > off' as we start the session)

This is a bad idea, as it will prevent developers from seeing that they're
using deprecated syntax.

> > > - we enable standard_conforming_strings ('set standard_conforming_string to 
> > > on')

This will cause Bacula to _fail_.  Bacula does not _use_ standard conforming
strings, so telling PG to expect them will produce a database that
Bacula will not understand.

With standard_conforming_strings on, \ has not special meaning, and thus
will be inserted as-is.  If you run the string through the PG escape
function first, you'll end up with (essentially) a corrupt string.

> > > - we put in the documentation the prerequisite that the administrator sets one 
> > > of those (I don't like this one, as we can do it ourselves)

Which, of course, shares the previous two problems.

> > > In both cases, the code will just work, with every version of postgresql...
> > > We just have to set one of theses values if they exist...
> > 
> > Yes, that probably a better idea, if they exist, i.e. postgresql 8.2 onwards.
> 
> I'm confused.  Isn't the use of PQescapeStringConn supposed to avoid 
> such things?

PQescapeStringConn creates strings that are _not_ compliant with the
SQL standard.  Old versions of PG accepted this because it was a
very common approach to dealing with strange characters (MySQL does
it as well).  It is _not_ compliant with the SQL standard.  To improve
PG's standards-compliance, the PGDB added the E'' syntax, which basically
tells PG that you're using non-SQL-standard escape syntax, and to expect
a string that has been passed through PQescapeStringConn.

There are two ways to approach this that would be considered "correct"
by the PGDG:
1) Use the E'' syntax for any string that needs to be escaped.  This
   would be good, but is difficult to implement, from my understanding.
2) Better would be to switch to the use of PQexecParams(), which does
   not require strings to be escaped.  I expect this would be even
   _more_ difficult to implement, but would (in my opinion) be the
   absolutely best way to fix the problem.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wm...@co...
Phone: 412-422-3463x4023