MIME-Version: 1.0
References: 
 <CAKXe9UDix+STcO8akzNNhjS4tEZd7xs203XNn72G35ELm81=WQ@mail.gmail.com>
 <303861.1641402467@sss.pgh.pa.us>
In-Reply-To: <303861.1641402467@sss.pgh.pa.us>
From: Les <nagylzs@gmail.com>
Date: Wed, 5 Jan 2022 18:19:12 +0100
Message-ID: 
 <CAKXe9UApjJiajmbYJxTK8MSyop5r1tvqjbrmHgueZ=MFkd706g@mail.gmail.com>
Subject: Re: psycopg3 - parameters cannot be used for DDL commands?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-interfaces@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000084e6a905d4d8f4a0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKXe9UApjJiajmbYJxTK8MSyop5r1tvqjbrmHgueZ%3DMFkd706g%40mail.gmail.com>
Precedence: bulk

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

Ok, thanks!

Tom Lane <tgl@sss.pgh.pa.us> ezt =C3=ADrta (id=C5=91pont: 2022. jan. 5., Sz=
e, 18:07):

> Les <nagylzs@gmail.com> writes:
> > PostgreSQL server log:
>
> > 2022-01-05 17:35:25.831 CET [58] ERROR:  syntax error at or near "$1" a=
t
> > 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 parameter=
s
> 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.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr">Ok, thanks! <br></div><br><div class=3D"gmail_quote"><div =
dir=3D"ltr" class=3D"gmail_attr">Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh=
.pa.us">tgl@sss.pgh.pa.us</a>&gt; ezt =C3=ADrta (id=C5=91pont: 2022. jan. 5=
., Sze, 18:07):<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">=
Les &lt;<a href=3D"mailto:nagylzs@gmail.com" target=3D"_blank">nagylzs@gmai=
l.com</a>&gt; writes:<br>
&gt; PostgreSQL server log:<br>
<br>
&gt; 2022-01-05 17:35:25.831 CET [58] ERROR:=C2=A0 syntax error at or near =
&quot;$1&quot; at<br>
&gt; character 35<br>
&gt; 2022-01-05 17:35:25.831 CET [58] STATEMENT:=C2=A0 ALTER USER postgres =
WITH<br>
&gt; PASSWORD $1<br>
<br>
Yeah, as a general rule parameters can only be used in DML commands<br>
(SELECT/INSERT/UPDATE/DELETE).=C2=A0 Utility commands don&#39;t support the=
m<br>
because they don&#39;t have expression-evaluation capability.<br>
<br>
(Perhaps this will change someday, but don&#39;t hold your breath.)<br>
<br>
&gt; Passwords can also contain special characters. If I can&#39;t use para=
meters to<br>
&gt; do this, then how should I quote them in a safe way?<br>
<br>
Most client libraries should have a function to convert an arbitrary<br>
string into a safely-quoted SQL literal that you can embed into the<br>
command.=C2=A0 I don&#39;t know psycopg3, so I don&#39;t know what it has f=
or that.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--00000000000084e6a905d4d8f4a0--