Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sGgUk-00Fv7Q-DI for pgsql-general@arkaria.postgresql.org; Mon, 10 Jun 2024 14:59:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sGgUi-00DD7g-Vn for pgsql-general@arkaria.postgresql.org; Mon, 10 Jun 2024 14:59:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sGgUi-00DD7X-Gu for pgsql-general@lists.postgresql.org; Mon, 10 Jun 2024 14:59:37 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sGgUg-000e45-Mg for pgsql-general@postgresql.org; Mon, 10 Jun 2024 14:59:36 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-250ca14422aso2430803fac.0 for ; Mon, 10 Jun 2024 07:59:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718031573; x=1718636373; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=05q3wAhvoHzD2sZJ4MR8VAC0QzTK6ZoIYIx7ErI3Z6E=; b=BqY6XDmQ5xvYz6PT30LbEhZgh07zTyu7xlIAWQGzCh72nNkDiD6RJZfMOcRx05p0PH m261f5KmGGROdsB9ITL9I7ArR0CYyxrXsqqkdmfjujOOG1k11n4K7XukgSukrr7k1ZRs JXAeZLbhubLHWZiHPx5xyBNxUMdr2E5HhzXd2JjUSw4vTHa2+3pl3aBa4/AH6k2udAnk poUIZ5CEucgABS5JFXWnt+nRROG4HrocsVzhz9VGfyCwlqePJDI8VAiDYtWEuqIzlwiV je2spLYTrzCprPOfUCliHYDt19XYaaS/tFOGOo3rEO8IFv+gUv250e1O+zX3UFYstdyf XuqA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718031573; x=1718636373; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=05q3wAhvoHzD2sZJ4MR8VAC0QzTK6ZoIYIx7ErI3Z6E=; b=a924lENljZeKiw3t9zS6B1T6gtIiJP8uxlttHF5rGVcX+qKKSmSIC8k72HI4DPruEp G2gDy/ImnJfMiAeg9Cfvr+Oq+G4o66+ReqESAsfKPJMYgngNs35KcOi61Sn+2tTAFfxu 4TishmFZlgzB35uwU78XKKXH1Oeaoh2liO0Xwg9hP6EVJosMd4FLY1chB+NJ1Kru+0Qy UEKLsGiHMICCATEmxHhI6ofxCGLPF076/mwzuape8ncs1nqPhl1TwdPDayBNte9e4AoQ bZToCWxsZrXb8ZSZAr+W+97UYU74qW+DCViNkLnAOYCzk0hW3bsSrAYgQZRN3zlpHimp rg5g== X-Gm-Message-State: AOJu0YxjVrA2jukbNzxqovxPI7W6YIiH997gEHvSl+ef0jrVUFkURVdP 7zhgaDjWFggUTTANrPdN12q1+10b1Rq4JUacXv1yJeiWnPxkoR/muWzg5BkhGnBrxb3MddTiHNG iY1VSypJkxPh8pwspINbS2+6hyCnJGuCM X-Google-Smtp-Source: AGHT+IHPuY+bvCgvRSlhHXCu+ZTdGJAvNiPwykEINVZ7aWaReu004UogAOJ6wyrjx2t6I+MNRu036R0iVhNNjZ+xcco= X-Received: by 2002:a05:6870:56a4:b0:254:826f:a9d9 with SMTP id 586e51a60fabf-254827023d3mr9223206fac.39.1718031573502; Mon, 10 Jun 2024 07:59:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 10 Jun 2024 10:59:22 -0400 Message-ID: Subject: Re: Escaping single quotes with backslash seems not to work To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000a6cfab061a8a6574" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a6cfab061a8a6574 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jun 10, 2024 at 10:56=E2=80=AFAM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Monday, June 10, 2024, Ron Johnson wrote: > >> On Mon, Jun 10, 2024 at 10:08=E2=80=AFAM David G. Johnston < >> david.g.johnston@gmail.com> wrote: >> >>> On Mon, Jun 10, 2024 at 7:02=E2=80=AFAM Ron Johnson >>> 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 b= ut > 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=3D# show standard_conforming_strings; standard_conforming_strings ----------------------------- on (1 row) TAP=3D# TAP=3D# show backslash_quote; backslash_quote ----------------- safe_encoding (1 row) --000000000000a6cfab061a8a6574 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jun 10, 2024 at 10:56=E2=80=AFAM = David G. Johnston <david.g= .johnston@gmail.com> wrote:
On Monday, June 10, 2024, Ron= Johnson <r= onljohnsonjr@gmail.com> wrote:
On Mon, Jun 10, 2024 at 1= 0:08=E2=80=AFAM David G. Johnston <david.g.johnston@gmail.com> wrote:
On= Mon, Jun 10, 2024 at 7:02=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
PG 9.6 and PG 14

Any other character followin= g a backslash is taken literally. Thus, to include a backslash character, w= rite two backslashes (\\). Also, a single quote can be included in an= escape string by writing=C2=A0\', in addition to the normal way = of=C2=A0''.
[/quote]=


The link you provided goes to the = wrong subsection.=C2=A0 The following subsection, which discusses,=C2=A0Str= ing Constants With C-Style Escapes, requires that you write the literal as = E'abc\'def'

Not= e the E prefix on the literal, which is the thing that enables considering = backslash as an escape.
=C2=A0
This hasn't changed from 9.6, has it?

A= Java app that uses backslash escapes broke this morning on fields with sin= gle quotes,=C2=A0after=C2=A0the weekend migration from PG 9.6.24 to 14.12, = and I don't know why.=C2=A0 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 t= o 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:.=C2=A0

TA= P=3D# show standard_conforming_strings;
=C2=A0standard_conforming_string= s
-----------------------------
=C2=A0on
(1 row)

TAP=3D# <= br>TAP=3D# show backslash_quote;
=C2=A0backslash_quote
-------------= ----
=C2=A0safe_encoding
(1 row)

=C2=A0=
--000000000000a6cfab061a8a6574--