Possible problem with PQescapeStringConn and standard_conforming_strings

On Thu, 2006-10-26 at 16:19 -0700, Jeff Davis wrote:

You can set standard_conforming_strings in postgresql.conf at any time
and reload the config, changing the value for all active connections.

I should have mentioned, my email only applies to the upcoming 8.2
release.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

You can set standard_conforming_strings in postgresql.conf at any time
and reload the config, changing the value for all active connections.
That means that if a client opens a connection, and you SIGHUP postgres,
and then the client issues a PQescapeStringConn, the client will get an
incorrectly-escaped string.

The window for this is pretty narrow, because PQescapeStringConn will
use the latest-delivered parameter status, but it's certainly true that
randomly changing standard_conforming_strings wouldn't be a bright idea.
Probably a documentation note recommending against changing it via
SIGHUP would be sufficient.

This could be a security vulnerability. Webservers which hold open
connections for long periods of time could be incorrectly escaping
values for long periods of time

Not if they're using PQescapeStringConn. Handmade escaping code
that thinks it needn't recheck the status could be at risk ...
but realistically, handmade escaping code is likely already broken
by the mere existence of standard_conforming_strings.

regards, tom lane

On Thu, 2006-10-26 at 19:46 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

You can set standard_conforming_strings in postgresql.conf at any time
and reload the config, changing the value for all active connections.
That means that if a client opens a connection, and you SIGHUP postgres,
and then the client issues a PQescapeStringConn, the client will get an
incorrectly-escaped string.

The window for this is pretty narrow, because PQescapeStringConn will
use the latest-delivered parameter status, but it's certainly true that
randomly changing standard_conforming_strings wouldn't be a bright idea.
Probably a documentation note recommending against changing it via
SIGHUP would be sufficient.

It's not a narrow time window (which was my original test), but you're
right that it is narrow in the sense that any command executed on that
connection will update the status. So, a potential attacker has one
chance :)

It seems like a documentation note would be sufficient to prevent people
from changing it too haphazardly. You wouldn't want to change it at
runtime if the bulk of your queries involved escape sequences.

Regards,
Jeff Davis

I tried to add this to the documentation, config.sgml, but everything I
come up with sounds so long-winded that it would do more harm than good.

---------------------------------------------------------------------------

Jeff Davis wrote:

On Thu, 2006-10-26 at 19:46 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

You can set standard_conforming_strings in postgresql.conf at any time
and reload the config, changing the value for all active connections.
That means that if a client opens a connection, and you SIGHUP postgres,
and then the client issues a PQescapeStringConn, the client will get an
incorrectly-escaped string.

The window for this is pretty narrow, because PQescapeStringConn will
use the latest-delivered parameter status, but it's certainly true that
randomly changing standard_conforming_strings wouldn't be a bright idea.
Probably a documentation note recommending against changing it via
SIGHUP would be sufficient.

It's not a narrow time window (which was my original test), but you're
right that it is narrow in the sense that any command executed on that
connection will update the status. So, a potential attacker has one
chance :)

It seems like a documentation note would be sufficient to prevent people
from changing it too haphazardly. You wouldn't want to change it at
runtime if the bulk of your queries involved escape sequences.

Regards,
Jeff Davis

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +