standard_conforming_strings and pg_escape_string()
standard_conforming_strings and pg_escape_string()
От:
Conrad Lender <crlender@gmail.com>
Дата:
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
Re: standard_conforming_strings and pg_escape_string()
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Conrad Lender 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
Re: standard_conforming_strings and pg_escape_string()
От:
Conrad Lender <crlender@gmail.com>
Дата:
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
Re: standard_conforming_strings and pg_escape_string()
От:
"Joshua D. Drake" <jd@commandprompt.com>
Дата:
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
Re: standard_conforming_strings and pg_escape_string()
От:
"Daniel Verite" <daniel@manitou-mail.org>
Дата:
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 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
Re: standard_conforming_strings and pg_escape_string()
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Conrad Lender 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
Re: standard_conforming_strings and pg_escape_string()
От:
Conrad Lender <crlender@gmail.com>
Дата:
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