Обсуждение: Escaping single quotes with backslash seems not to work
PG 9.6 and PG 14
[quote]
Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (
\\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.[/quote]
But it doesn't seem to work. Obviously there's some misconfiguration or , but I don't see what I did wrong.
TAP=# insert into foo (name, description) values ('XYZ_Name ', '''XYZ ''');
INSERT 0 1
TAP=# insert into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP'#
TAP'# ');
ERROR: syntax error at or near "XYZ"
LINE 1: ...into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
INSERT 0 1
TAP=# insert into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP'#
TAP'# ');
ERROR: syntax error at or near "XYZ"
LINE 1: ...into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP=# show standard_conforming_strings;
standard_conforming_strings
-----------------------------
on
(1 row)
TAP=#
TAP=# show backslash_quote;
backslash_quote
-----------------
safe_encoding
(1 row)
standard_conforming_strings
-----------------------------
on
(1 row)
TAP=#
TAP=# show backslash_quote;
backslash_quote
-----------------
safe_encoding
(1 row)
PG 9.6 and PG 14[quote]Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing\', in addition to the normal way of''.[/quote]
The link you provided goes to the wrong subsection. The following subsection, which discusses, String Constants With C-Style Escapes, requires that you write the literal as E'abc\'def'
Note the E prefix on the literal, which is the thing that enables considering backslash as an escape.
David J.
On Mon, Jun 10, 2024 at 7:07 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
PG 9.6 and PG 14[quote]Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing\', in addition to the normal way of''.[/quote]The link you provided goes to the wrong subsection.
Ah...you just linked to the section on constants where all the various incarnations are subsections.
It would be a nice addition to include some examples in the section pertaining to escape syntax. Make it more clear how it differs from just a non-escaping literal. Maybe put something like \n into the basic literal section showing that it outputs the literal two characters instead of a newline.
David J.
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
PG 9.6 and PG 14[quote]Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing\', in addition to the normal way of''.[/quote]The link you provided goes to the wrong subsection. The following subsection, which discusses, String Constants With C-Style Escapes, requires that you write the literal as E'abc\'def'Note the E prefix on the literal, which is the thing that enables considering backslash as an escape.
This hasn't changed from 9.6, has it?
A Java app that uses backslash escapes broke this morning on fields with single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I don't know why. I'm not a Java programmer, though.
On Monday, June 10, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <david.g.johnston@gmail.com> wrote:PG 9.6 and PG 14[quote]Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing\', in addition to the normal way of''.[/quote]The link you provided goes to the wrong subsection. The following subsection, which discusses, String Constants With C-Style Escapes, requires that you write the literal as E'abc\'def'Note the E prefix on the literal, which is the thing that enables considering backslash as an escape.This hasn't changed from 9.6, has it?A Java app that uses backslash escapes broke this morning on fields with single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I don't know why. I'm not a Java programmer, though.
As the caution on that page says the default for standard conforming strings changed in 9.1. But maybe your 9.6 had the old value configured but when you upgraded to 14 you decided to go with the new default.
David J.
On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, June 10, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <david.g.johnston@gmail.com> wrote:PG 9.6 and PG 14[quote]Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing\', in addition to the normal way of''.[/quote]The link you provided goes to the wrong subsection. The following subsection, which discusses, String Constants With C-Style Escapes, requires that you write the literal as E'abc\'def'Note the E prefix on the literal, which is the thing that enables considering backslash as an escape.This hasn't changed from 9.6, has it?A Java app that uses backslash escapes broke this morning on fields with single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I don't know why. I'm not a Java programmer, though.As the caution on that page says the default for standard conforming strings changed in 9.1. But maybe your 9.6 had the old value configured but when you upgraded to 14 you decided to go with the new default.
That was the first thing I checked... It's the same on both the 9.6 and 14 systems:.
standard_conforming_strings
-----------------------------
on
(1 row)
TAP=#
TAP=# show backslash_quote;
backslash_quote
-----------------
safe_encoding
(1 row)
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> As the caution on that page says the default for standard conforming
>> strings changed in 9.1. But maybe your 9.6 had the old value configured but
>> when you upgraded to 14 you decided to go with the new default.
> That was the first thing I checked... It's the same on both the 9.6 and 14
> systems:.
Did you check that as the user that runs the Java app (I sure hope
it's not the superuser you evidently used here), in the DB the Java
app uses? I'm wondering about per-user or per-DB settings of
standard_conforming_strings.
regards, tom lane
On Mon, Jun 10, 2024 at 11:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> As the caution on that page says the default for standard conforming
>> strings changed in 9.1. But maybe your 9.6 had the old value configured but
>> when you upgraded to 14 you decided to go with the new default.
> That was the first thing I checked... It's the same on both the 9.6 and 14
> systems:.
Did you check that as the user that runs the Java app (I sure hope
it's not the superuser you evidently used here), in the DB the Java
app uses? I'm wondering about per-user or per-DB settings of
standard_conforming_strings.
It's a remote Java app which runs as a non-superuser. I don't know what it's doing.
I ran "pg_dumpuser -g" on the old systems, and applied the sql to the corresponding new servers.
"set standard_encoding_strings = on" is at the top, and there's no other reference to it.
On 6/10/24 07:52, Ron Johnson wrote: > On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > This hasn't changed from 9.6, has it? > > A Java app that uses backslash escapes broke this morning on fields with > single quotes, after the weekend migration from PG 9.6.24 to 14.12, and > I don't know why. I'm not a Java programmer, though. > Was there a change in the JDBC driver also? -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
"set standard_encoding_strings = on" is at the top, and there's no other reference to it.
Well, if they are not using E-strings for escapes then you have the answer why v14 is broken. Does it really matter why v9.6 apparently worked even though it should not have if that setting was also set to on?
David J.
On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:"set standard_encoding_strings = on" is at the top, and there's no other reference to it.Well, if they are not using E-strings for escapes then you have the answer why v14 is broken. Does it really matter why v9.6 apparently worked even though it should not have if that setting was also set to on?
It matters that something broke either between PG 9.6 and 14 OR the old JDBC driver and the new JDBC driver, because the client end users are HOPPING MAD.
(Don't ask why it wasn't caught in testing; that's beyond my control.)
On 6/10/24 10:51, Ron Johnson wrote: > On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com > <mailto:ronljohnsonjr@gmail.com>> wrote: > > > "set standard_encoding_strings = on" is at the top, and there's > no other reference to it. > > > Well, if they are not using E-strings for escapes then you have the > answer why v14 is broken. Does it really matter why v9.6 apparently > worked even though it should not have if that setting was also set > to on? > > > It matters that *something broke* either between PG 9.6 and 14 *OR* the > old JDBC driver and the new JDBC driver, because the client end users > are HOPPING MAD. What was the change in the JDBC driver version? What is the error you are getting currently? > > (Don't ask why it wasn't caught in testing; that's beyond my control.) > -- Adrian Klaver adrian.klaver@aklaver.com