public inbox for [email protected]
help / color / mirror / Atom feedRe: Escaping single quotes with backslash seems not to work
7+ messages / 3 participants
[nested] [flat]
* Re: Escaping single quotes with backslash seems not to work
@ 2024-06-10 14:52 Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-06-10 14:52 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
[email protected]> wrote:
> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
@ 2024-06-10 14:56 ` David G. Johnston <[email protected]>
2024-06-10 14:59 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-06-10 14:56 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Monday, June 10, 2024, Ron Johnson <[email protected]> wrote:
> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
> [email protected]> wrote:
>
>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson <[email protected]>
>> 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.
>
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
@ 2024-06-10 14:59 ` Ron Johnson <[email protected]>
2024-06-10 15:08 ` Re: Escaping single quotes with backslash seems not to work Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-06-10 14:59 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
[email protected]> wrote:
> On Monday, June 10, 2024, Ron Johnson <[email protected]> wrote:
>
>> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
>> [email protected]> wrote:
>>
>>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson <[email protected]>
>>> 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.
>>
>>
> 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:.
TAP=# show standard_conforming_strings;
standard_conforming_strings
-----------------------------
on
(1 row)
TAP=#
TAP=# show backslash_quote;
backslash_quote
-----------------
safe_encoding
(1 row)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
2024-06-10 14:59 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
@ 2024-06-10 15:08 ` Tom Lane <[email protected]>
2024-06-10 15:19 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-06-10 15:08 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
Ron Johnson <[email protected]> writes:
> On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> [email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
2024-06-10 14:59 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 15:08 ` Re: Escaping single quotes with backslash seems not to work Tom Lane <[email protected]>
@ 2024-06-10 15:19 ` Ron Johnson <[email protected]>
2024-06-10 15:42 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-06-10 15:19 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 10, 2024 at 11:08 AM Tom Lane <[email protected]> wrote:
> Ron Johnson <[email protected]> writes:
> > On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> > [email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
2024-06-10 14:59 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 15:08 ` Re: Escaping single quotes with backslash seems not to work Tom Lane <[email protected]>
2024-06-10 15:19 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
@ 2024-06-10 15:42 ` David G. Johnston <[email protected]>
2024-06-10 17:51 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-06-10 15:42 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Escaping single quotes with backslash seems not to work
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
2024-06-10 14:59 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 15:08 ` Re: Escaping single quotes with backslash seems not to work Tom Lane <[email protected]>
2024-06-10 15:19 ` Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 15:42 ` Re: Escaping single quotes with backslash seems not to work David G. Johnston <[email protected]>
@ 2024-06-10 17:51 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2024-06-10 17:51 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston <
[email protected]> wrote:
> On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson <[email protected]>
> 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.)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-06-10 17:51 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-10 14:52 Re: Escaping single quotes with backslash seems not to work Ron Johnson <[email protected]>
2024-06-10 14:56 ` David G. Johnston <[email protected]>
2024-06-10 14:59 ` Ron Johnson <[email protected]>
2024-06-10 15:08 ` Tom Lane <[email protected]>
2024-06-10 15:19 ` Ron Johnson <[email protected]>
2024-06-10 15:42 ` David G. Johnston <[email protected]>
2024-06-10 17:51 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox