Обсуждение: standard_conforming_strings and pg_escape_string()

Hi.

I'm using PostgreSQL 8.3 with PHP's "pgsql" module (libpq 8.3.7). When
the server's standard_conforming_strings setting is off (this is
currently still the default, I believe), I use something like this to
escape strings:

  if ($escWildcards) {
    $str = strtr($str, array("%" => '\%', "_" => '\_'));
  }
  return "E'" . pg_escape_string($str) . "'";

I would like our database abstraction to be able to handle both settings
for standard_conforming_strings transparently, i.e. perform the escaping
according to the current DB server settings. Since pg_escape_string() is
aware of the current database connection, I had expected its behavior to
change accordingly:

no std strings:
   x\y --> x\\y
with std strings:
   x\y --> x\y

Unfortunately, this doesn't happen. Isn't pg_escape_string() the
preferred way to escape strings for PostgreSQL in PHP?

And finally, would it be safe to always use the E'\\' syntax, regardless
of how standard_conforming_strings is set on the server?


  - Conrad

Conrad Lender <crlender@gmail.com> writes:
> I'm using PostgreSQL 8.3 with PHP's "pgsql" module (libpq 8.3.7). When
> the server's standard_conforming_strings setting is off (this is
> currently still the default, I believe), I use something like this to
> escape strings:

>   if ($escWildcards) {
>     $str = strtr($str, array("%" => '\%', "_" => '\_'));
>   }
>   return "E'" . pg_escape_string($str) . "'";

The above cannot possibly work.  pg_escape_string is generating what it
supposes to be a normal string literal, and then you are sticking an 'E'
on the front which changes the escaping rules.  It is not the function's
fault that this fails.

            regards, tom lane

Tom,

thanks for your reply.

On 24/04/09 00:56, Tom Lane wrote:
>>   if ($escWildcards) {
>>     $str = strtr($str, array("%" => '\%', "_" => '\_'));
>>   }
>>   return "E'" . pg_escape_string($str) . "'";
>
> The above cannot possibly work.  pg_escape_string is generating what it
> supposes to be a normal string literal, and then you are sticking an 'E'
> on the front which changes the escaping rules.  It is not the function's
> fault that this fails.

I'm afraid I don't understand why it fails (it appears to work, at
least). I have to enclose the result of pg_escape_string() in single
quotes to get a string literal, and if I don't add the "E" in front, I
see warnings in the server log about "nonstandard use of \\ in a string
literal" (standard_conforming_strings is off, escape_string_warning is on).

I could disable the warnings, of course, but I suppose they are there
for a reason.


Thanks,
  - Conrad

On Thu, 2009-04-23 at 21:33 +0200, Conrad Lender wrote:
> Hi.

> I would like our database abstraction to be able to handle both settings
> for standard_conforming_strings transparently, i.e. perform the escaping
> according to the current DB server settings. Since pg_escape_string() is
> aware of the current database connection, I had expected its behavior to
> change accordingly:


> And finally, would it be safe to always use the E'\\' syntax, regardless
> of how standard_conforming_strings is set on the server?
>

You can set standard_conforming_strings on the fly (see SET). So why not
just make sure it is always on if going through your layer. That way
none of this is an issue.

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

    Conrad Lender wrote:

> I would like our database abstraction to be able to handle both
settings
> for standard_conforming_strings transparently, i.e. perform the
escaping
> according to the current DB server settings. Since pg_escape_string()
is
> aware of the current database connection, I had expected its behavior
to
> change accordingly:
>
> no std strings:
>    x\y --> x\\y
> with std strings:
>    x\y --> x\y

It works for me:

$ php -e
<?
echo phpversion(), "\n";
$c=pg_connect("dbname=mail user=daniel host=/tmp port=5000");
pg_query("SET standard_conforming_strings=off");
echo pg_escape_string('toto\titi'), "\n";
pg_query("SET standard_conforming_strings=on");
echo pg_escape_string('toto\titi'), "\n";
?>

Output:
5.2.0-8+etch13
toto\\titi
toto\titi

Best regards,

--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Conrad Lender <crlender@gmail.com> writes:
> On 24/04/09 00:56, Tom Lane wrote:
>> The above cannot possibly work.  pg_escape_string is generating what it
>> supposes to be a normal string literal, and then you are sticking an 'E'
>> on the front which changes the escaping rules.  It is not the function's
>> fault that this fails.

> I'm afraid I don't understand why it fails (it appears to work, at
> least).

I should have said "it will fail when standard_conforming_strings is on".
pg_escape_string will see that, think that it shouldn't double
backslashes, and then when you stick E on the front, the result is
wrong.  So what you have here is a time bomb.

> I have to enclose the result of pg_escape_string() in single
> quotes to get a string literal, and if I don't add the "E" in front, I
> see warnings in the server log about "nonstandard use of \\ in a string
> literal" (standard_conforming_strings is off, escape_string_warning is on).

> I could disable the warnings, of course, but I suppose they are there
> for a reason.

Well, they're there to get you to pay attention to this problem ;-).
You can get rid of the warnings by inverting either of those settings,
but as long as you leave them as they are, you're going to get chatter.

            regards, tom lane

On 24/04/09 14:49, Daniel Verite wrote:
> It works for me:
>
> $ php -e
> <?
> echo phpversion(), "\n";
> $c=pg_connect("dbname=mail user=daniel host=/tmp port=5000");
> pg_query("SET standard_conforming_strings=off");
> echo pg_escape_string('toto\titi'), "\n";
> pg_query("SET standard_conforming_strings=on");
> echo pg_escape_string('toto\titi'), "\n";
> ?>
>
> Output:
> 5.2.0-8+etch13
> toto\\titi
> toto\titi

Very interesting! That's exactly what I had hoped would happen, but for
some reason it didn't. I've managed to replicate this with a standalone
test script, which means that here must be something else going wrong in
our application.

Thank you all for your help!

  - Conrad