MySQL Bugs: #106240: StringIndexOutOfBoundsException when VALUE is at the end of the query

Bug #106240	StringIndexOutOfBoundsException when VALUE is at the end of the query
Submitted:	21 Jan 2022 22:00	Modified:	29 Mar 2022 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()

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.

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.

Hello YIYI DENG,

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

regards,
Umesh

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.

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. "