Обсуждение: BUG #4027: backslash escaping not disabled in plpgsql
The following bug has been logged online:
Bug reference: 4027
Logged by: Jonathan Guthrie
Email address: jguthrie@brokersys.com
PostgreSQL version: 8.3.0
Operating system: Debian Gnu/Linux "unstable" 2.6.24
Description: backslash escaping not disabled in plpgsql
Details:
I have set the standard_conforming_strings to "on" in my settings, and have
verified it by executing a
select '\';
which works fine and produces the expected:
?column?
----------
\
(1 row)
However, when I attempt to define this function:
create function foo (out r refcursor) as $bar$
begin
open r for
select * from user_data
where name_first like name escape '\';
end; $bar$ language plpgsql;
it complains about an unterminated string. ("ERROR: unterminated string")
However, if I double the backslashes, it compiles just fine, and does not
emit a warning even though escape_string_warning is also set to 'on'. As
expected, the system does emit a warning when I double the backslashes and
when standard_conforming_strings is set to 'off'. I also have
backslash_quote set to 'off', but it doesn't seem to change anything in this
case.
I believe that this is incorrect behavior and that the backslash should be
just a character in that string when standard_conforming_strings is set to
'on'.
"Jonathan Guthrie" <jguthrie@brokersys.com> writes:
> I have set the standard_conforming_strings to "on" in my settings ...
> However, when I attempt to define this function:
> create function foo (out r refcursor) as $bar$
> begin
> open r for
> select * from user_data
> where name_first like name escape '\';
> end; $bar$ language plpgsql;
plpgsql does not consider standard_conforming_strings --- it still uses
backslash escaping in its function bodies regardless. Since the
language itself is not standardized, I see no particular reason that
standard_conforming_strings should govern it. I believe the reason for
not changing it was that it seemed too likely to break existing
functions, with potentially nasty consequences if they chanced to be
security definers.
regards, tom lane
Tom Lane wrote: > plpgsql does not consider standard_conforming_strings --- it still uses > backslash escaping in its function bodies regardless. Since the > language itself is not standardized, I see no particular reason that > standard_conforming_strings should govern it. I think plpgsql should behave either consistently with the rest of PostgreSQL or with Oracle, which it is copied from. > I believe the reason for > not changing it was that it seemed too likely to break existing > functions, with potentially nasty consequences if they chanced to be > security definers. Is this actually true or did we just forget it? :-)
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> I believe the reason for
>> not changing it was that it seemed too likely to break existing
>> functions, with potentially nasty consequences if they chanced to be
>> security definers.
> Is this actually true or did we just forget it? :-)
I recall thinking about the point. The decision could've been wrong ...
regards, tom lane
Peter Eisentraut wrote: > Tom Lane wrote: > > plpgsql does not consider standard_conforming_strings --- it still uses > > backslash escaping in its function bodies regardless. Since the > > language itself is not standardized, I see no particular reason that > > standard_conforming_strings should govern it. > > I think plpgsql should behave either consistently with the rest of PostgreSQL > or with Oracle, which it is copied from. Agreed. standard_conforming_strings should affect _all_ strings. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Agreed. standard_conforming_strings should affect _all_ strings. We might need another transition period over a few releases with a separate "plpgsql_standard_conforming_strings" parameter. Just changing it immediately is perhaps a bit risky.
Peter Eisentraut wrote: > Bruce Momjian wrote: > > Agreed. standard_conforming_strings should affect _all_ strings. > > We might need another transition period over a few releases with a > separate "plpgsql_standard_conforming_strings" parameter. Just changing it > immediately is perhaps a bit risky. We haven't even enabled standard_conforming_strings by default yet. It was added as changeable in 8.2. Is this never going to be enabled by default? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Peter Eisentraut wrote: > Tom Lane wrote: > > plpgsql does not consider standard_conforming_strings --- it still uses > > backslash escaping in its function bodies regardless. Since the > > language itself is not standardized, I see no particular reason that > > standard_conforming_strings should govern it. > > I think plpgsql should behave either consistently with the rest of PostgreSQL > or with Oracle, which it is copied from. > > > I believe the reason for > > not changing it was that it seemed too likely to break existing > > functions, with potentially nasty consequences if they chanced to be > > security definers. > > Is this actually true or did we just forget it? :-) I would like to add a TODO item for this, but I am concerned that people running functions with different standard_conforming_strings values would have function syntax errors on mismatch. Is that acceptable? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Peter Eisentraut wrote: > Tom Lane wrote: > > plpgsql does not consider standard_conforming_strings --- it still uses > > backslash escaping in its function bodies regardless. Since the > > language itself is not standardized, I see no particular reason that > > standard_conforming_strings should govern it. > > I think plpgsql should behave either consistently with the rest of PostgreSQL > or with Oracle, which it is copied from. > > > I believe the reason for > > not changing it was that it seemed too likely to break existing > > functions, with potentially nasty consequences if they chanced to be > > security definers. > > Is this actually true or did we just forget it? :-) Did we ever address this? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +