public inbox for [email protected]
help / color / mirror / Atom feedUsing standard SQL placeholders in PG
2+ messages / 2 participants
[nested] [flat]
* Using standard SQL placeholders in PG
@ 2021-10-13 13:11 Lembark, Steven <[email protected]>
2021-10-13 13:56 ` Re: Using standard SQL placeholders in PG Daniele Varrazzo <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Lembark, Steven @ 2021-10-13 13:11 UTC (permalink / raw)
To: psycopg; +Cc: Loga, Dharani <[email protected]>
PostgreSQL ("PG") supports the notion of placeholders, as do many other relational databases. The placeholder notation in PG uses $X within the SQL. For example:
select foo
from bar
where bletch = $1
is valid SQL. The doller-notation allows re-use of positional parameters and avoids SQL injection entirely by placing the input is appropriately-typed buffers w/in the SQL statement handler. It also avoids issues with double-interpolating quotes as the placeholders are not literals and do not require SQL-quoting to be interpolated properly.
We have quite a bit of SQL here that has to be shared between Python and other packages, so using standard PG SQL statements is required.
Q: Is there any way to prepare and execute standard PG SQL statements with dollar placeholders in PsychoPG2?
Thanks
This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using standard SQL placeholders in PG
2021-10-13 13:11 Using standard SQL placeholders in PG Lembark, Steven <[email protected]>
@ 2021-10-13 13:56 ` Daniele Varrazzo <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Daniele Varrazzo @ 2021-10-13 13:56 UTC (permalink / raw)
To: Lembark, Steven <[email protected]>; +Cc: psycopg; Loga, Dharani <[email protected]>
On Wed, 13 Oct 2021 at 15:44, Lembark, Steven
<[email protected]> wrote:
>... The doller-notation allows re-use of positional parameters and avoids SQL injection entirely by placing the input is appropriately-typed buffers w/in the SQL statement handler. It also avoids issues with double-interpolating quotes as the placeholders are not literals and do not require SQL-quoting to be interpolated properly.
You still require to convert items to string in the right format
understood by Postgres, which might be different from the Python
string form.
> Q: Is there any way to prepare and execute standard PG SQL statements with dollar placeholders in PsychoPG2?
No. psycopg2 is entirely built around "convert to postgres format and
add quotes". We have just released Psycopg 3 exactly to solve this
kind of problem. It also has no direct access to PQexecParams, but it
only uses PQexec so there's no easy way to plug in the $1 params.
Note that still Psycopg 3 doesn't allow you to use $1 placeholders,
but they are the ones it uses internally. There is an internal object,
PostgresQuery, that takes a %s-style query and, among other things,
converts it in $n format. It wouldn't be a difficult thing to write a
cursor to do less work and just convert the arguments.
In Psycopg 3 you can also go a level lower and call directly
PQexecParams passing the query and arguments you want, in postgres
format, but then you have to do the conversion from Python objects to
Postgres format yourself. I would rather put together a cursor to do
so, as above.
So, things can be done, but in Psycopg 3.
-- Daniele
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-10-13 13:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-13 13:11 Using standard SQL placeholders in PG Lembark, Steven <[email protected]>
2021-10-13 13:56 ` Daniele Varrazzo <[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