Bug #103437 Syntax error when calling PreparedStatement.getMetadata() with LIMIT placeholded
Submitted: 23 Apr 2021 4:51 Modified: 23 Apr 2021 6:01
Reporter: Jerrick Pua Email Updates:
Status: Verified 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

[23 Apr 2021 4:51] Jerrick Pua
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.
[23 Apr 2021 6:01] MySQL Verification Team
Hello Jerrick Pua,

Thank you for the report and feedback.

regards,
Umesh
[9 Apr 2022 18:07] Rick James
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...
[31 May 2023 8:03] xj ma
set your jdbc url with useServerPrepStmts=true