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:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2021 4:51] Jerrick Pua

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());


                    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:


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.