MIME-Version: 1.0
References: 
 <CAKXe9UDix+STcO8akzNNhjS4tEZd7xs203XNn72G35ELm81=WQ@mail.gmail.com>
 <303861.1641402467@sss.pgh.pa.us>
 <CAAfz9KNgYfJhHL5NKS1frPDo286sn-c0PNA6ZOF81f5TBByc=A@mail.gmail.com>
In-Reply-To: 
 <CAAfz9KNgYfJhHL5NKS1frPDo286sn-c0PNA6ZOF81f5TBByc=A@mail.gmail.com>
From: Les <nagylzs@gmail.com>
Date: Wed, 5 Jan 2022 18:28:37 +0100
Message-ID: 
 <CAKXe9UA_4zhcah-3w9GhG+=JUW2WiA9q0QBkXz_wHQhmr+R=mw@mail.gmail.com>
Subject: Re: psycopg3 - parameters cannot be used for DDL commands?
To: Dmitry Igrishin <dmitigr@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-interfaces@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000022c73005d4d91650"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKXe9UA_4zhcah-3w9GhG%2B%3DJUW2WiA9q0QBkXz_wHQhmr%2BR%3Dmw%40mail.gmail.com>
Precedence: bulk

--00000000000022c73005d4d91650
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 c=
ountry
   will break into your computer and steal not only your disks, but also yo=
ur
   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 prov=
ided
   methods
   <https://www.psycopg.org/psycopg3/docs/basic/params.html#query-parameter=
s>
   instead.


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

   Laszlo

Dmitry Igrishin <dmitigr@gmail.com> ezt =C3=ADrta (id=C5=91pont: 2022. jan.=
 5., Sze,
18:19):

> =D1=81=D1=80, 5 =D1=8F=D0=BD=D0=B2. 2022 =D0=B3. =D0=B2 20:07, Tom Lane <=
tgl@sss.pgh.pa.us>:
> >
> > Les <nagylzs@gmail.com> 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 WITH
> > > 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)").
>

--00000000000022c73005d4d91650
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Well okay, I&#39;m obviously using python. psycopg3 d=
oes not have an escape function, they try to avoid this at all costs.</div>=
<div><br></div><div>Actually their documentation is very funny, at <a href=
=3D"https://www.psycopg.org/psycopg3/docs/basic/params.html">https://www.ps=
ycopg.org/psycopg3/docs/basic/params.html</a> there are these warnings:</di=
v><div><br></div><div><ul class=3D"gmail-simple"><li><p>Don=E2=80=99t manua=
lly 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.</p></li><li><p>If you use the <code class=
=3D"gmail-docutils gmail-literal gmail-notranslate"><span class=3D"gmail-pr=
e">%</span></code> 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.</p></li><li><p>If you use <code class=3D"gma=
il-docutils gmail-literal gmail-notranslate"><span class=3D"gmail-pre">+</s=
pan></code> 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.</p=
></li><li><p>You don=E2=80=99t want to manually merge values to a query: <a=
 class=3D"gmail-reference gmail-internal" href=3D"https://www.psycopg.org/p=
sycopg3/docs/basic/params.html#query-parameters"><span class=3D"gmail-std g=
mail-std-ref">use the
provided methods</span></a> instead.</p></li></ul></div><div><br></div><div=
>I think I&#39;ll open an issue because it looks like manual string quoting=
 cannot be avoided in some cases.</div><div><br></div><div>=C2=A0=C2=A0 Las=
zlo<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">Dmitry Igrishin &lt;<a href=3D"mailto:dmitigr@gmail.com">dmiti=
gr@gmail.com</a>&gt; ezt =C3=ADrta (id=C5=91pont: 2022. jan. 5., Sze, 18:19=
):<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=D1=81=D1=80,=
 5 =D1=8F=D0=BD=D0=B2. 2022 =D0=B3. =D0=B2 20:07, Tom Lane &lt;<a href=3D"m=
ailto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt;:<br>
&gt;<br>
&gt; Les &lt;<a href=3D"mailto:nagylzs@gmail.com" target=3D"_blank">nagylzs=
@gmail.com</a>&gt; writes:<br>
&gt; &gt; PostgreSQL server log:<br>
&gt;<br>
&gt; &gt; 2022-01-05 17:35:25.831 CET [58] ERROR:=C2=A0 syntax error at or =
near &quot;$1&quot; at<br>
&gt; &gt; character 35<br>
&gt; &gt; 2022-01-05 17:35:25.831 CET [58] STATEMENT:=C2=A0 ALTER USER post=
gres WITH<br>
&gt; &gt; PASSWORD $1<br>
&gt;<br>
&gt; Yeah, as a general rule parameters can only be used in DML commands<br=
>
&gt; (SELECT/INSERT/UPDATE/DELETE).=C2=A0 Utility commands don&#39;t suppor=
t them<br>
&gt; because they don&#39;t have expression-evaluation capability.<br>
&gt;<br>
&gt; (Perhaps this will change someday, but don&#39;t hold your breath.)<br=
>
&gt;<br>
&gt; &gt; Passwords can also contain special characters. If I can&#39;t use=
 parameters to<br>
&gt; &gt; do this, then how should I quote them in a safe way?<br>
&gt;<br>
&gt; Most client libraries should have a function to convert an arbitrary<b=
r>
&gt; string into a safely-quoted SQL literal that you can embed into the<br=
>
&gt; command.=C2=A0 I don&#39;t know psycopg3, so I don&#39;t know what it =
has for that.<br>
My C++ library, - Pgfe, - can convert any named parameter into an<br>
arbitrary part of SQL expression by using Sql_string::replace()<br>
method. For example:<br>
=C2=A0 update :foo<br>
could be replaced to<br>
=C2=A0 update foo set bar =3D &#39;baz&#39; where id =3D 1<br>
by using<br>
=C2=A0 s.replace(&quot;foo&quot;, R&quot;(set bar=3D&#39;baz&#39; where id =
=3D 1)&quot;).<br>
</blockquote></div>

--00000000000022c73005d4d91650--