Prepared Statements Without Additional Round-Trip

09 Jul, 2017 · 2 min read · #postgresql #database #sql

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!

The Extended Query Protocol

Prepared statements in PostgreSQL are executed using the extended query protocol, using the following messages.

Parse - The query with parameter placeholders is parsed and bound to a name (if provided).
Bind - Provides the values for parameters of the prepared statement.
Execute - Executes the prepared statement with the bound parameters.
Sync - Indicates end of messages for a query.

When statements are prepared and executed independently, they are be prepared with:

Parse (with name)
Sync

and then executed with:

Bind (with name)
Execute
Sync

Unnamed Statements

If you need to execute a query only once, you can send the Parse message 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 Bind, Execute and Sync messages without waiting for the response to previous messages. The sequence of messages will now be:

Parse (without name)
Bind (without name)
Execute
Sync

What About Failures?

If 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.

How to Use It?

Whether and how you can use this feature depends on your DB driver. Some drivers use this by default even if they expose separate prepare and 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.

If you liked what you read, consider subscribing to the RSS feed in your favourite feed reader.