MySQL Bugs: #116860: mysql_stmt_bind_named_param() not respecting CLIENT_QUERY

Thank you for your help! If the status of the bug report you submitted changes, you will be notified. You may return here and check on the status or update your report at any time. That URL for your bug report is: http://bugs.mysql.com/116860.

Bug #116860	mysql_stmt_bind_named_param() not respecting CLIENT_QUERY_ATTRIBUTES capability
Submitted:	3 Dec 2024 23:06	Modified:	5 Feb 17:27
Reporter:	Yoni Shalom	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The MySQL 8.4 C API mysql_stmt_bind_named_param() constructs and sends an invalid COM_STMT_EXECUTE packet when the number of bound parameters (both unnamed and named query attributes) exceeds the number of placeholder parameters (?) in the SQL query, and the server has the CLIENT_QUERY_ATTRIBUTES flag disabled. 

This issue leads to the server misinterpreting the packet structure, resulting in unexpected behavior during statement execution.  

see
https://dev.mysql.com/doc/dev/mysql-server/8.4.2/group__group__cs__capabilities__flags.htm...

For instance, consider a statement like SELECT ?, where the server expects only one parameter (num_params = 1 as returned by PREPARE response). If two unnamed parameters (e.g., 1, 2) are bound to the prepared statement, the server misinterprets the incoming packet, resulting in an invalid packet structure during execution.

This issue does not occur when CLIENT_QUERY_ATTRIBUTES is enabled because, in such cases, the client includes the parameter_count field in its packet to explicitly inform the server of how many parameters it should expect.

Expected Behavior:  

The client should construct a valid COM_STMT_EXECUTE packet that adheres to the server's expected structure, even when CLIENT_QUERY_ATTRIBUTES is disabled.  

Observed Behavior:  

* The MySQL 8.4 C API sends a malformed COM_STMT_EXECUTE packet, causing the server to misinterpret the packet structure.  
* The invalid packet structure arises because the client fails to account for the mismatch between the number of bound parameters and placeholder parameters in the absence of the CLIENT_QUERY_ATTRIBUTES flag.  

How to repeat:
1. Ensure CLIENT_QUERY_ATTRIBUTES is disabled on the server.  
2. Use the MySQL C API mysql_stmt_bind_named_param() to prepare and execute a statement like SELECT ?.  
3. Bind more parameters than the query expects. For example:  
    1. Query: SELECT ?  
    2. Bound Unnamed Parameters: {1}  
    3. Bound Named Parameters: {a = 2}

Hi,

Thank you for the report. I verified it. I reduced the severity to S3 as this is not a S1 bug. One could discuss if this is a bug at all as if you do not want to enable CLIENT_QUERY_ATTRIBUTES it is up to you to make sure you are binding proper number of parameters. I'd use printf() as comparison example, if your number of parameters to printf do not match the format string you can get all kind of errors / crashes in your code, but that is not a bug in printf() but in your code. Same here. Anyhow, I did verify the report so we will see what the appropriate team will say about it, maybe we can make this safer.

Thanks for using MySQL

> if you do not want to enable CLIENT_QUERY_ATTRIBUTES it is up to you to make sure you are binding proper number of parameters.

Since this has the potential of returning incorrect data, its probably best to be made safe (fail fast or similar) both on server and client end.

Hi,

I do partially agree hence I verified the bug.

I think this could be seen as:

- not a bug: But then the example and description on https://dev.mysql.com/doc/c-api/9.2/en/mysql-stmt-bind-named-param.html should show how to check for this flag/capability and warn that this is something the user needs to do.

- a bug: Then the code should check for CLIENT_QUERY_ATTRIBUTES and only send the query attributes if the flag is set. However I don't think the code would be able to do this as it can't distinguish between regular parameters and query attributes. Currently query attributes are named and others are (usually? always?) unnamed, however this might not be true in the future.

hi Daniël, on ability to validate - 
1. looking at the commit that introduced this new API, seems named parameters support is new. Shouldn't the client validation be to fail-fast if CLIENT_QUERY_ATTRIBUTES is not supported AND _any_ named parameters are passed in ?  https://github.com/mysql/mysql-server/commit/a3b9e624a0a94a6ee53cd2054d42cfcd66aa3d5f

2. please also consider the relevant tests that rely on the new api (test_datetime_ranges, test_logs, test_bug1500, test_bug32915973). I assume these should pass against a server that doesn't have the CLIENT_QUERY_ATTRIBUTES capability (either by skipping the test as n/a or otherwise)

To be clear: I'm not working for Oracle/MySQL.

Note that all query attributes are named parameters, but not all named parameters have to be query attributes (they might be now, but that's not something to rely on).

I think examples and tests should be made to work both against servers that support query attributes and those that don't. 

Maybe something like this:
bool qa;
mysql_get_option(&mysql, MYSQL_OPT_QUERY_ATTRIBUTE, &qa); // but with error checking
if (qa) {
    mysql_bind_param(...)
    mysql_stmt_bind_named_param(...) // with query attributes
} else {
    mysql_stmt_bind_named_param(...) // without query attributes
}

After looking at this again I'm more inclined to agree with your initial statement.

So for the example on https://dev.mysql.com/doc/c-api/9.2/en/mysql-stmt-bind-named-param.html it has two MYSQL_BIND's and only one of them has a name.

It could only send the ones that don't have a name depending on the CLIENT_QUERY_ATTRIBUTES flag. However it might be allowed to set a name for regular attributes (the name then isn't used, but might be send to the server anyway).