public inbox for [email protected]
help / color / mirror / Atom feedpsycopg3 - parameters cannot be used for DDL commands?
6+ messages / 3 participants
[nested] [flat]
* psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-05 16:42 Les <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Les @ 2022-01-05 16:42 UTC (permalink / raw)
To: [email protected]
Example:
Traceback (most recent call last):
File "/usr/sbin/prepare_postgres", line 46, in <module>
pg_exec_postgres("ALTER USER postgres WITH PASSWORD %s",
[POSTGRES_PASSWORD])
File "/opt/util.py", line 101, in pg_exec_postgres
return pg_conn_postgres().execute(*args, **kwargs)
File "/usr/local/lib/python3.9/dist-packages/psycopg/connection.py", line
722, in execute
raise ex.with_traceback(None)
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: ALTER USER postgres WITH PASSWORD $1
^
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
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?
Thank you,
Laszlo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-05 17:07 Tom Lane <[email protected]>
parent: Les <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2022-01-05 17:07 UTC (permalink / raw)
To: Les <[email protected]>; +Cc: [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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-05 17:19 Les <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Les @ 2022-01-05 17:19 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Ok, thanks!
Tom Lane <[email protected]> ezt írta (időpont: 2022. jan. 5., Sze, 18:07):
> 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.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-05 17:20 Dmitry Igrishin <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Dmitry Igrishin @ 2022-01-05 17:20 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Les <[email protected]>; [email protected]
ср, 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)").
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-05 17:28 Les <[email protected]>
parent: Dmitry Igrishin <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Les @ 2022-01-05 17:28 UTC (permalink / raw)
To: Dmitry Igrishin <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
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)").
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psycopg3 - parameters cannot be used for DDL commands?
@ 2022-01-06 09:37 Les <[email protected]>
parent: Les <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Les @ 2022-01-06 09:37 UTC (permalink / raw)
To: Dmitry Igrishin <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
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)").
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-01-06 09:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-05 16:42 psycopg3 - parameters cannot be used for DDL commands? Les <[email protected]>
2022-01-05 17:07 ` Tom Lane <[email protected]>
2022-01-05 17:19 ` Les <[email protected]>
2022-01-05 17:20 ` Dmitry Igrishin <[email protected]>
2022-01-05 17:28 ` Les <[email protected]>
2022-01-06 09:37 ` Les <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox