Bug #108275 CREATE DATABASE does not work as prepared statement
Submitted: 24 Aug 2022 21:37 Modified: 18 Oct 2022 11:53
Reporter: Björn Voigt (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.30 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: Connector J, java, prepared statement

[24 Aug 2022 21:37] Björn Voigt
Description:
Documentation (https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html) says that CREATE DATABASE is permitted for prepared statements.

Unfortunately this does not work at, at least not with the MySQL Connector/J.

It does not work with useServerPrepStmts true or false.

How to repeat:
1. Use an existing MySQL 8.0.x server (tested with 8.0.30)
2. compile and run the following JAVA source
3. See the error message "java.sql.SQLSyntaxErrorException: 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 ''testdb'' at line 1"

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CreateDatabase {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pStmt = null;

        try {
            String extra = "?useServerPrepStmts=false"; // or true
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:6603?" + extra, "root", "root-password");
            pStmt = conn.prepareStatement("CREATE DATABASE IF NOT EXISTS ?");
            pStmt.setString(1, "testdb");
            pStmt.execute();
            pStmt.close();
            pStmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }

                pStmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    // ignore
                }
                conn = null;
            }
        }
    }
}

Suggested fix:
Probably the quoting of the database name is work. The error message suggests such the folling SQL statement is executed internally:

CREATE DATABASE IF NOT EXISTS 'testdb';

By this does not work, even in MySQL client.

These statements would work:

CREATE DATABASE IF NOT EXISTS testdb;
CREATE DATABASE IF NOT EXISTS `testdb`;
[18 Oct 2022 11:53] Filipe Silva
Database names, table names, columns or any other database entity in general cannot be parameterized in prepared statements. Only values are allowed.

See the example demonstrating how to choose the table on which to perform a query at runtime in the documentation https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html. Mind that this is just a workaround for a similar situation and this is not something you should be doing though a connector, anyway.

The documentation says CREATE DATABASE is permitted in Prepared Statements but that's true only it is written in its static form, i.e., without placeholders.

Also note that Connector/J doesn't always prepare statements using server-side PREPARE/EXECUTE/DEALLOCATE commands. Even when `useServerPrepStmts=true` there are times where prepared statements get emulated at client side. This may cause different error messages or at different moments (while preparing vs while executing).