Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n5PDH-0000aD-Fj for pgsql-interfaces@arkaria.postgresql.org; Thu, 06 Jan 2022 09:37:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n5PDE-0003Bk-LG for pgsql-interfaces@arkaria.postgresql.org; Thu, 06 Jan 2022 09:37:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n5PDE-0003Bb-BQ for pgsql-interfaces@lists.postgresql.org; Thu, 06 Jan 2022 09:37:36 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n5PDB-0002Ar-Dh for pgsql-interfaces@lists.postgresql.org; Thu, 06 Jan 2022 09:37:36 +0000 Received: by mail-qt1-x82d.google.com with SMTP id a1so1709520qtx.11 for ; Thu, 06 Jan 2022 01:37:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=/NlbtbXRTPYVosVoyLhZwVMJtTWPusbRniN+9zAfz30=; b=arDCIIswdNAnt7t2EumB0l3chvVm20Nh/Xr0OKfwxyRpK5hJBIdEA/iBDcL3Dy+Mh8 4aE8r3d3MR8cRWB1eNUY5zPm3vH2kDbddSvKQW60GYDQjq4kEDDLkkbJW1MblxbYfJYe DuSiJwGy2tsLhmW9JfCN3sZ5sTe33AjoO+mstx8PRVThwGdwfgcjBgk4MV06l+WIuKGG 24IkeUKx2vDoSUmG5tLJikhpw/C4COkxQGU8oU+K+RF38z3fi10+sS565qXnksPsSTt9 idBAhMxlX+FNOsZh+I3pKZ4ZNm6YG8fksTSgaCM5jqikuME+pdFsTcrvJITof1Fj3HGK Og5A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=/NlbtbXRTPYVosVoyLhZwVMJtTWPusbRniN+9zAfz30=; b=XpKv1ZFzZWx6jdusXV3VyB/coVQBDFFzz+d+02l9dzyEQLmKysqGrTVuFLgpnEO/Uj pw3Df5j4T5JEi0hiS09oBaaSJdoTuxUfIQ3biBWv5OPiDObFjfNwmN++F8+749WJGaAq e2SHLZOkjzPPcH8e9jZV3om4IDULl3mH2po7Z7Z10NKg3AO4lcIGug75C2jzjiG4jBaV OqcISGSnlDDq4K6i+bWO4pYflQXfiMbtDcSceaRHa9S5mYHSKmSZCgc+yX677Ey1zSeA ngRkDWSairwU99+nsh5LL/uY9lHoDq5icXz04HN5+MrsHMebbQrSJqvKZJjdDDlTa9v8 opjw== X-Gm-Message-State: AOAM531SBRPj6nAQ6E38dF68lLN6h4z5LoKb3VKEge79CfRIOVC8pQZz S+KwytrYQRc8GM2nhKN+QkPFQGvRytZUwo1x75M= X-Google-Smtp-Source: ABdhPJxRh83Y5zcvpRwTwEXqEfnEjHRz64CME/Czp4P2q/hieymVR+wiPOG9+zymGM4HU9B0s6ZS/pQTL6Ks/LJCuu4= X-Received: by 2002:a05:622a:ca:: with SMTP id p10mr51512035qtw.89.1641461850993; Thu, 06 Jan 2022 01:37:30 -0800 (PST) MIME-Version: 1.0 References: <303861.1641402467@sss.pgh.pa.us> In-Reply-To: From: Les Date: Thu, 6 Jan 2022 10:37:20 +0100 Message-ID: Subject: Re: psycopg3 - parameters cannot be used for DDL commands? To: Dmitry Igrishin Cc: Tom Lane , pgsql-interfaces@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000008a775b05d4e69ea9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008a775b05d4e69ea9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Okay I was wrong. I just did not know that it existed in psycopg3. There is "client side binding": https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-bindi= ng from psycopg import sqlconn.execute(sql.SQL("ALTER USER some_user WITH PASSWORD {}").format(PASSWORD)) Issue was closed: https://github.com/psycopg/psycopg/issues/199 Les ezt =C3=ADrta (id=C5=91pont: 2022. jan. 5., Sze, 18= :28): > Well okay, I'm obviously using python. psycopg3 does not have an escape > function, they try to avoid this at all costs. > > Actually their documentation is very funny, at > https://www.psycopg.org/psycopg3/docs/basic/params.html there are these > warnings: > > > - > > Don=E2=80=99t manually merge values to a query: hackers from a foreign= country > will break into your computer and steal not only your disks, but also = your > cds, leaving you only with the three most embarrassing records you eve= r > bought. On cassette tapes. > - > > If you use the % operator to merge values to a query, con artists will > seduce your cat, who will run away taking your credit card and your > sunglasses with them. > - > > If you use + to merge a textual value to a string, bad guys in > balaclava will find their way to your fridge, drink all your beer, and > leave your toilet sit up and your toilet paper in the wrong orientatio= n. > - > > You don=E2=80=99t want to manually merge values to a query: use the pr= ovided > methods > > instead. > > > I think I'll open an issue because it looks like manual string quoting > cannot be avoided in some cases. > > Laszlo > > Dmitry Igrishin ezt =C3=ADrta (id=C5=91pont: 2022. ja= n. 5., > Sze, 18:19): > >> =D1=81=D1=80, 5 =D1=8F=D0=BD=D0=B2. 2022 =D0=B3. =D0=B2 20:07, Tom Lane = : >> > >> > Les writes: >> > > PostgreSQL server log: >> > >> > > 2022-01-05 17:35:25.831 CET [58] ERROR: syntax error at or near "$1= " >> at >> > > character 35 >> > > 2022-01-05 17:35:25.831 CET [58] STATEMENT: ALTER USER postgres WIT= H >> > > PASSWORD $1 >> > >> > Yeah, as a general rule parameters can only be used in DML commands >> > (SELECT/INSERT/UPDATE/DELETE). Utility commands don't support them >> > because they don't have expression-evaluation capability. >> > >> > (Perhaps this will change someday, but don't hold your breath.) >> > >> > > Passwords can also contain special characters. If I can't use >> parameters to >> > > do this, then how should I quote them in a safe way? >> > >> > Most client libraries should have a function to convert an arbitrary >> > string into a safely-quoted SQL literal that you can embed into the >> > command. I don't know psycopg3, so I don't know what it has for that. >> My C++ library, - Pgfe, - can convert any named parameter into an >> arbitrary part of SQL expression by using Sql_string::replace() >> method. For example: >> update :foo >> could be replaced to >> update foo set bar =3D 'baz' where id =3D 1 >> by using >> s.replace("foo", R"(set bar=3D'baz' where id =3D 1)"). >> > --0000000000008a775b05d4e69ea9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Okay I was wrong. I just did not kno= w that it existed in psycopg3.


from psycopg<=
/span> import sql
conn.execute(sql.SQL(&quo=
t;ALTER USER some_user WITH PASSWORD {}&quo=
t;).format(PASSWORD=
))



Les <nagylzs@= gmail.com> ezt =C3=ADrta (id=C5=91pont: 2022. jan. 5., Sze, 18:28):<= br>
Well okay, I'm obviously using python. psycopg3 does not have an = escape function, they try to avoid this at all costs.

<= div>Actually their documentation is very funny, at https://www.p= sycopg.org/psycopg3/docs/basic/params.html there are these warnings:

  • Don=E2=80=99t manually merge values to a = query: hackers from a foreign country will break into your computer and steal not only your disks, but also your cds, leaving you only with the three most embarrassing records you ever bought. On cassette tapes.

  • If you use the %= operator to merge values to a query, con artists will seduce your cat, who will run away taking your credit card and your sunglasses with them.

  • If you use + to merge a textual value to a string, bad guys in balaclava will find their way to your fridge, drink all your beer, and leave your toilet sit up and your toilet paper in the wrong orientation.

  • You don=E2=80=99t want to manually merge values to a query: use the provided methods instead.


I think I'll open an issue because it looks like manual string quoting= cannot be avoided in some cases.

=C2=A0=C2=A0 Las= zlo

Dmitry Igrishin <dmitigr@gmail.com> ezt =C3=ADrta (id=C5=91pont: 2022. j= an. 5., Sze, 18:19):
=D1=81=D1=80, 5 =D1=8F=D0=BD=D0=B2. 2022 =D0=B3. =D0=B2 20:07, Tom Lan= e <tgl@sss.pgh.pa= .us>:
>
> Les <nagylzs= @gmail.com> writes:
> > PostgreSQL server log:
>
> > 2022-01-05 17:35:25.831 CET [58] ERROR:=C2=A0 syntax error at or = near "$1" at
> > character 35
> > 2022-01-05 17:35:25.831 CET [58] STATEMENT:=C2=A0 ALTER USER post= gres WITH
> > PASSWORD $1
>
> Yeah, as a general rule parameters can only be used in DML commands > (SELECT/INSERT/UPDATE/DELETE).=C2=A0 Utility commands don't suppor= t them
> because they don't have expression-evaluation capability.
>
> (Perhaps this will change someday, but don't hold your breath.) >
> > Passwords can also contain special characters. If I can't use= parameters to
> > do this, then how should I quote them in a safe way?
>
> Most client libraries should have a function to convert an arbitrary > string into a safely-quoted SQL literal that you can embed into the > command.=C2=A0 I don't know psycopg3, so I don't know what it = has for that.
My C++ library, - Pgfe, - can convert any named parameter into an
arbitrary part of SQL expression by using Sql_string::replace()
method. For example:
=C2=A0 update :foo
could be replaced to
=C2=A0 update foo set bar =3D 'baz' where id =3D 1
by using
=C2=A0 s.replace("foo", R"(set bar=3D'baz' where id = =3D 1)").
--0000000000008a775b05d4e69ea9--