MySQL Bugs: #117903: Please consider allowing preparing and executing - and maybe closing

Bug #117903	Please consider allowing preparing and executing - and maybe closing - a statement in a single round-trip.
Submitted:	7 Apr 18:28	Modified:	8 Apr 14:29
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S4 (Feature request)
Version:	8.x, 9.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

during the FOSDEM talk "MySQL Network Protocol: A walkthrough" [1] (thanks Daniël for the talk). It was mentioned that preparing, executing and closing a statement needs three protocol messages.

[1]: https://fosdem.org/2025/schedule/event/fosdem-2025-4956-mysql-network-protocol-a-walkthrou...

After the talk, I discussed with an Oracle / MySQL Engineer about preparing and executing (and maybe closing) a statement in a single protocol message, and I was encouraged to do a feature request, so here it is.

So please consider implementing a COM_STMT_PREPARE_N_EXECUTE to combine COM_STMT_PREPARE and COM_STMT_EXECUTE in a single protocol message (and a single round-trip to the database). This new command would have a flag to optionally close the statement, avoiding another round-trip for COM_STMT_CLOSE. When this flag is not used, the new command would return a statement_id (like COM_STMT_PREPARE) and the execution results (like COM_STMT_EXECUTE).

The flag to immediately close a prepared statement after execution might look like a deviated usage for prepared statements. After all, they were designed to be prepared once and executed many times. However, it became a commun usage to use prepared statements to implement parameterized queries (for avoiding SQL injections as an example), so even if this deviates from the initial design, it became common-enough that this use-case should be optimized. It is fully understood that this would cause additional burden on the database to prepare and close the statement, but this might be acceptable for improving security and usability, and for simplifying client-side implementation.

Doing this feature request was reminded to me by the blog post "Database Protocols Are Underwhelming" [2], see Prepared Statement section with quote below.

[2]: https://byroot.github.io/performance/2025/03/21/database-protocols.html

> In many cases, when you have no reason to believe a particular query will be executed again soon, it is actually advantageous not to use prepared statements. Ideally, you’d still use a parameterized query, but then it means doing 2-3 rountrips to the database instead of just one.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
N/A for feature request.

One possible way to optimize this as stated in [1] and [2] is to use COM_QUERY with query attributes.

Currently the choice is either
a. COM_QUERY: just a query string
b. COM_STMT_PREPARE, COM_STMT_EXECUTE, COM_STMT_CLOSE

With query attributes it would be possible to have a query string with placeholders in COM_QUERY and also send parameters for the placeholders in the same packet. And then the closing is implicit.

So instead of:
COM_STMT_PREPARE: "SELECT a FROM t1 WHERE a=?"
COM_STMT_EXECUTE: 6
COM_STMT_CLOSE

Or:
COM_QUERY "SELECT a FROM t1 WHERE a=6"

This would become:
COM_QUERY: "SELECT a FROM t1 WHERE a=?", 6

The benefits are:
- Less roundtrips (compared against regular prepared protocol)
- More secure against SQL injection (compared againts COM_QUERY)
- The server *could* more potentially cache the execution plan based on a hash of a query string. (when executing x times)

There is also another option that is already in use: client side prepared statements. MySQL Connector/J does this based on the  `useServerPrepStmts` option. This does add a lot of complexity to the connector.

1. https://databaseblog.myname.nl/2025/02/the-potential-of-query-attributes-in.html
2. https://archive.fosdem.org/2021/schedule/event/mysql_protocl/

Hello Jean-François,

Thank you for the feature request!

regards,
Umesh

> One possible way to optimize this [...] is to use COM_QUERY with query attributes.

Right, above is an alternative way of implementing parameterized queries (alternative to COM_STMT_PREPARE_N_EXECUTE with the close flag set), but I think this is a different feature request.

The feature request of this bug is to allow PREPARE and EXECUTE in a single round-trip.  On its own, this is a useful-enough optimization (even without implementing the close flag; though IMHO the close flag should be considered as it is a small addition allowing implementing parameterized queries).