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. Consult your driver’s documentation or source code.

pgx v5 provides a QueryExecMode named 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 application thoroughly.

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