MySQL Bugs: #71929: Prefixing query with double comments cancels query DML validation

Bug #71929	Prefixing query with double comments cancels query DML validation
Submitted:	4 Mar 2014 19:09	Modified:	13 Oct 2021 15:16
Reporter:	Filipe Silva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.2.29	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
Executing a query prefixed with double comments cancels DML validation, allowing to execute DML statements with Statement.executeQuery().

The same happens in some internal validations too.

How to repeat:
Compare results of:

stmt.executeQuery("CREATE TABLE t_test (id INT)");

stmt.executeQuery("/* comment */CREATE TABLE t_test (id INT)");

stmt.executeQuery("/* comment *//* comment again */CREATE TABLE t_test (id INT)");

Suggested fix:
Review validation methods.

Third code line is a bad example. Instead use (first character in second comment matters):

stmt.executeQuery("/* comment *//* some more comment */CREATE TABLE t_test (id INT)");

Posted by developer:
 
A similar thing happens when prefixing a SELECT query with comments and use Statement.executeUpdate() method.

E.g.:

stmt.executeUpdate("SELECT 1"); // throws exception

stmt.executeUpdate("/* comment */SELECT 1"); // doesn't throw exception

1.Fixed executeQuery() related bug in StatementImpl.findStartOfStatement() method.
2.Fixed executeUpdate() related bug in StatementImpl.executeUpdateInternal() method.

here is my commit:
https://github.com/raminorujov/mysql-connector-j/commit/39b5fd3f92d7ff7845374f200c22d7a1a4...

Test case

Attachment: Bug71929.java (text/x-java), 1.10 KiB.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.27 changelog: 

"When Statement.executeQuery() was called, Connector/J's check for whether a statement would return results was inadequate, so that sometimes appropriate statements were rejected (for examples, SELECT statements starting with a WITH clause, statements preceded by consecutive comments, and so on) and, at other times, inappropriate statements were executed (for example, DO statements), resulting in various kinds of errors. With this fix, Connector/J performs more accurate checks by looking at the statement keywords and the context, as well as handling properly different corner cases. In this new mechanism, Connector/J takes a permissive approach: statements that might return results are allowed to run."

Posted by developer:
 
Also fixed Bug#76623 (20856749).