Subject: Re: Escaping single quotes with backslash seems not to work

From: Ron Johnson <[email-masked]>
Date: Mon, 10 Jun 2024 10:52:10 -0400

On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
[email-masked]> wrote:

> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson <[email-masked]>
> wrote:
>
>> PG 9.6 and PG 14
>>
>>
>> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>>
>> [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.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 10 Jun 2024 07:56:21 -0700

> 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.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 10 Jun 2024 10:59:22 -0400

> 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:.

TAP=# show standard_conforming_strings;
 standard_conforming_strings
-----------------------------
 on
(1 row)

TAP=#
TAP=# show backslash_quote;
 backslash_quote
-----------------
 safe_encoding
(1 row)

---

From: Tom Lane <[email-masked]>
Date: Mon, 10 Jun 2024 11:08:31 -0400

> That was the first thing I checked... It's the same on both the 9.6 and 14

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

---

From: Ron Johnson <[email-masked]>
Date: Mon, 10 Jun 2024 11:19:32 -0400

> 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.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 10 Jun 2024 08:42:00 -0700

> "set standard_encoding_strings = on" is at the top, and there's no other
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.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 10 Jun 2024 13:51:50 -0400

> 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.)