public inbox for [email protected]  

help / color / mirror / Atom feed

From: Les <[email protected]>
To: Dmitry Igrishin <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: psycopg3 - parameters cannot be used for DDL commands?
Date: Thu, 6 Jan 2022 10:37:20 +0100
Message-ID: <CAKXe9UB9+BC6czrWKH++--Pcdhrdjko=_B3VyDNkpHqKLn6tAA@mail.gmail.com> (raw)
In-Reply-To: <CAKXe9UA_4zhcah-3w9GhG+=JUW2WiA9q0QBkXz_wHQhmr+R=mw@mail.gmail.com>
References: <CAKXe9UDix+STcO8akzNNhjS4tEZd7xs203XNn72G35ELm81=WQ@mail.gmail.com>
	<[email protected]>
	<CAAfz9KNgYfJhHL5NKS1frPDo286sn-c0PNA6ZOF81f5TBByc=A@mail.gmail.com>
	<CAKXe9UA_4zhcah-3w9GhG+=JUW2WiA9q0QBkXz_wHQhmr+R=mw@mail.gmail.com>

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 <[email protected]> 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 <[email protected]> ezt írta (időpont: 2022. jan. 5.,
> Sze, 18:19):
>
>> ср, 5 янв. 2022 г. в 20:07, Tom Lane <[email protected]>:
>> >
>> > Les <[email protected]> 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)").
>>
>

view thread (6+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: psycopg3 - parameters cannot be used for DDL commands?
  In-Reply-To: <CAKXe9UB9+BC6czrWKH++--Pcdhrdjko=_B3VyDNkpHqKLn6tAA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox