Prepared statements are the best way to avoid SQL injections in applications accepting user input. However they require an additional round-trip to prepare the query, which increases latency when your queries are dynamic and the statements are not re-used. PostgreSQL allows execution of such queries without the additional cost!
Prepared statements in PostgreSQL are executed using the extended query protocol, using the following messages.
When statements are prepared and executed independently, they are be prepared with:
and then executed with:
If you need to execute a query only once, you can send the
without a name. This will create an unnamed statement that will be destroyed
upon next query on the same connection. PostgreSQL also allows you to send the
Sync messages without waiting for the response to
previous messages. The sequence of messages will now be:
Parse or any subsequent message cannot be processed, PostgreSQL server
will read and discard all messages until the
Sync message. It’ll then
continue handling other messages received on the connection. So the connection
is not left in a corrupt state or closed even if the query being prepared or
parameters provided is incorrect, allowing clients to execute parameterized
queries in a single round-trip.
Whether and how you can use this feature depends on your DB driver. Some
drivers use this by default even if they expose separate
execute functions, while others may need additional configuration. Consult
your driver’s documentation or source code.
pgx v5 provides a
QueryExecModeExec to take
advantage of this ability. The mode can be specified either for the entire
connection or for individual queries. The latter is useful, when you want to
use it only for dynamic queries. Note that this mode changes how types of query
parameters are inferred. This can break your queries, so re-test your
All opinions are my own. Copyright 2005 Chandra Sekar S.