Bug #106240 StringIndexOutOfBoundsException when VALUE is at the end of the query
Submitted: 21 Jan 22:00 Modified: 29 Mar 22:25
Reporter: YIYI DENG Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.25, 8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: StringIndexOutOfBoundsException, VALUES()

[21 Jan 22:00] YIYI DENG
Description:
StringIndexOutOfBoundsException thrown from two places in ParseInfo.java, can be reproduced respectively. 

1. During rewrite, the jdbc driver is expecting to parse the "VALUES" clause in queries like "INSERT INTO table VALUES(?, ?) ON DUPLICATE KEY UPDATE xxx", assuming there's a "VALUES" clause before "ON DUPLICATE KEY". So it simply tries to extract the string between these two clause: https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/core-api/java/com/mys.... This doesn't handle when "VALUES" clause is after "ON DUPLICATE KEY UPDATE".

2. Also during rewrite, `extractValuesClause()`, the driver tries to access the chars after VALUES clause: https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/core-api/java/com/mys.... This doesn't handle when "VALUES" clause is at the end of the query. 

How to repeat:
1. When "VALUES" clause is after "ON DUPLICATE KEY UPDATE". This is syntactically correct and should be supported. 

`INSERT INTO table SET a = ?, b = ? ON DUPLICATE KEY UPDATE b = VALUES(b)`

2. When "VALUES" clause is at the end of the query. The driver should throw ER_SYNTAX_ERROR instead of StringIndexOutOfBoundsException.

`INSERT INTO test_empty_values(a, b) VALUES`

Suggested fix:
To be submitted on GitHub.
[21 Jan 22:17] YIYI DENG
The first case is just found to be the same as in https://bugs.mysql.com/bug.php?id=84365, but the second case should be a new one.
[22 Jan 7:40] MySQL Verification Team
Hello YIYI DENG,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh
[24 Jan 15:56] YIYI DENG
Hello, Bug84365 is different from the second case I proposed. 

Case for Bug84365 is

"insert into testBug84365(id, name) VALUES(?,?) on duplicate key update id = values(id) + 1".

My case is a wrong syntax query, literally an empty values function,

"insert into test_empty_values(a, b) values".

My point is instead of throwing StringIndexOutOfBoundsException, we should throw syntax error (SQLSyntaxErrorException) instead to handle such case properly.
[29 Mar 22:25] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.29 changelog: 

"When using client-side prepared statements, if the VALUES clause came after the ON DUPLICATE KEY UPDATE clause or it came at the end of the statement, a StringIndexOutOfBoundsException was thrown. This patch refactors the query parser to fix the problem behind issue, and also to improve the parser's performance. "