MySQL Bugs: #107577: rewriteBatchedStatements not work when table column name contains 'value' string

Bug #107577	rewriteBatchedStatements not work when table column name contains 'value' string
Submitted:	16 Jun 2022 8:38	Modified:	30 Nov 2023 12:03
Reporter:	Charles Yang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	REWRITEBATCHEDSTATEMENTS

Description:
setting rewriteBatchedStatements to true, and create a table with column name contains 'value', such like 'p_value' and using insert sql `insert into tableName(p_value) values (?)` to insert data into table, rewriteBatchedStatements not working correct.

How to repeat:
1. create a table with any column name contains 'value', such like 'p_value',
2. setting rewriteBatchedStatements to true
3. use preparedstatement to insert data, such like `insert into tableName(p_value) values (?)`

Suggested fix:
in com.mysql.cj.QueryInfo, strInspector.matchesIgnoreCase(VALUE_CLAUSE) should skip column names

Hi,

Are you getting the same result with server side prepared statement set to true and to false ?

thanks

thanks for your replay.
we tested these cases:
1. column name not contains 'value',
2. on column name contains 'value'
and check the server side log, the final sql is:
for case 1: insert into table_name ('p_val') values ('1'),('2'),('3')
for case 2: insert into table_name ('p_value') values ('1');insert into table_name ('p_value') values ('2');insert into table_name ('p_value') values ('3');
so i don't think the the bug is cased by the server side parameter setting.

according by the comments of https://github.com/mysql/mysql-connector-j/blob/8.0.29/src/main/core-api/java/com/mysql/cj..., 

if (valuesClauseBegin == -1 && strInspector.matchesIgnoreCase(VALUE_CLAUSE) != -1) { // VALUE(S) clause found.
    strInspector.incrementPosition(VALUE_CLAUSE.length()); // Advance to the end of "VALUE".
    if (strInspector.matchesIgnoreCase("S") != -1) { // Check for the "S" in "VALUE(S)" and advance 1 more character if needed.
        strInspector.incrementPosition();
    }
    withinValuesClause = true;
    strInspector.indexOfNextChar(); // Position on the first values list character.
    valuesClauseBegin = strInspector.getPosition();

    if (rewritableAsMultiValues) {
        valuesEndpointStart = valuesClauseBegin;
    }

}

if a sql like `insert into table_name(id, p_value) values(?,?)`, the if block will be executed, i think it is not correct, am i wrong?

Hi,

That part is supposed to be ok as com.mysql.cj.util.* should know to search outside `` and outside /* */ and before -- and only between whitespaces etc etc .. but looks someone forgot something as I can reproduce the issue so it is def. there :( 

Thank you for your report

Related to Bug#109864.

Added the following entry to the Connector/J 8.0.33 changelog: 
 
"Rewriting of batched statements failed when a closing parenthesis was found 
within a VALUES clause. It was because QueryInfo failed to parse the prepared 
statement properly in that case. With this fix, the parser of VALUES clauses 
are improved, so that Connector/J is now able to recognize rewritability of 
statements that contain function calls or multiple VALUES lists, and it also 
handles well the cases when the string "value" is part of a table name, a 
column name, or a keyword."

Bug #109818 marked as duplicate of this one

Bug #108978 marked as duplicate of this one