MySQL Bugs: #103437: Syntax error when calling PreparedStatement.getMetadata() with LIMIT placeholded

Bug #103437	Syntax error when calling PreparedStatement.getMetadata() with LIMIT placeholded
Submitted:	23 Apr 2021 4:51	Modified:	2 Dec 21:01
Reporter:	Jerrick Pua	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.24, 8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

SQL statement of "SELECT * FROM USER LIMIT ? OFFSET ?" causes some exception on the connector when calling PreparedStatement.getMetadata() 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null OFFSET null' at line 2

however it will not cause any error when I execute PreparedStatement.executeQuery(). After some investigation I found out the main culprit in the error was the connector code com.mysql.cj.jdbc.ClientPreparedStatement#getMetaData

A snippet of the code in the method below
                    mdStmt = new ClientPreparedStatement(this.connection, ((PreparedQuery<?>) this.query).getOriginalSql(), this.getCurrentDatabase(),
                            ((PreparedQuery<?>) this.query).getParseInfo());

                    mdStmt.setMaxRows(1);

                    int paramCount = ((PreparedQuery<?>) this.query).getParameterCount();

                    for (int i = 1; i <= paramCount; i++) {
                        mdStmt.setString(i, null);
                    }

                    boolean hadResults = mdStmt.execute();

It seems like when executing getMetadata() it will try to set the placeholder by looping through it and set a null String however I don't think this is acceptable on mysql as it will only accept integer for LIMIT and OFFSET. I tried to swap the value to setInt and the query works.

How to repeat:

Execute 

java.sql.Connection.prepareStatement( "SELECT * FROM USER LIMIT ? OFFSET ?" ).getMetadata()

should do the trick.

Hello Jerrick Pua,

Thank you for the report and feedback.

regards,
Umesh

A simpler test case:

mysql> select * from canada limit '2';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 1

This seems to be a regression in MySQL 8.0 since MySQL 5.x.

Another, using Rails:
https://stackoverflow.com/questions/71727983/ruby-find-by-sql-string-parameters-converting...

set your jdbc url with useServerPrepStmts=true

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

"Calling getMetadata() on a prepared statement with the LIMIT and OFFSET clauses resulted in a SQL syntax error even if the statement was syntactically correct."