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. So consult
your driver’s documentation or source code.
For example, if you are using github.com/lib/pq, then you must set the
binary_parameters connection parameter to take advantage of this feature.
Please note that enabling this parameter has implications beyond how statements
are prepared. So re-test your code thoroughly, especially if you’re passing
values of type
byte as parameter to any query.
All opinions are my own. Copyright 2005 Chandra Sekar S.