Re: psycopg3 - parameters cannot be used for DDL commands?

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-binding

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 <nagylzs(at)gmail(dot)com> ezt írta (időpont: 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’t 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’t want to manually merge values to a query: use the provided
> methods
> <https://www.psycopg.org/psycopg3/docs/basic/params.html#query-parameters>
> 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(at)gmail(dot)com> ezt írta (időpont: 2022. jan. 5.,
> Sze, 18:19):
>
>> ср, 5 янв. 2022 г. в 20:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> >
>> > Les <nagylzs(at)gmail(dot)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 = 'baz' where id = 1
>> by using
>> s.replace("foo", R"(set bar='baz' where id = 1)").
>>
>