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.
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 írta (időpont: 2022. jan. 5., Sze, 18:19):
ср, 5 янв. 2022 г. в 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 = 'baz' where id = 1 by using s.replace("foo", R"(set bar='baz' where id = 1)").