Forum OpenACS Development: Postgres 8.2b3

1: Postgres 8.2b3

Posted by Malte Sussdorff on 11/20/06 11:11 AM

First of all, OpenACS runs with PG 8.2beta3 just fine and the performance increase on inserts is significant. I followed the instructions for PG 8.1 and did not run into any trouble with our tests so far.

What I am seeing though are Warning messages like:

WARNING: nonstandard use of \\ in a string literal at character 758 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

Before I start looking more deeply, does anyone have a clue where we are using the \\ in SQL insert statements (probably cr_item or cr_revision, other places?)

2: Re: Postgres 8.2b3 (response to 1)

Posted by Malte Sussdorff on 11/20/06 11:28 AM

The parameter to get rid of this can be set in postgresql.conf:

backslash_quote (string)

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII \. If client-side code does escaping incorrectly then a SQL-injection attack is possible. This risk can be prevented by making the server reject queries in which a quote mark appears to be escaped by a backslash. The allowed values of backslash_quote are on (allow \' always), off (reject always), and safe_encoding (allow only if client encoding does not allow ASCII \ within a multibyte character). safe_encoding is the default setting.

Note that in a standard-conforming string literal, \ just means \ anyway. This parameter affects the handling of non-standard-conforming literals, including escape string syntax (E'...').