MySQL Bugs: #65871: DatabaseMetaData.getColumns() thows an MySQLSyntaxErrorException

Bug #65871	DatabaseMetaData.getColumns() thows an MySQLSyntaxErrorException
Submitted:	11 Jul 2012 12:04	Modified:	27 Jun 2013 17:06
Reporter:	iriyoox iriyoox	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.21	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	getColumns, jdbc, metadata

Description:
DatabaseMetaData.getColumns() thows an MySQLSyntaxErrorException if schema contains table with delimited name.

How to repeat:
1. Create the table with delimited name like this:

create database h;
use h;
create table `delimited``name`(id integer);

2. Connect to this database with followed Java code:

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

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/h?user=root&password=password");
        connection.getMetaData().getColumns("h", null, null, null);
    }
}

3. Get an MySQLSyntaxErrorException:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'name` FROM `h` LIKE '%'' at line 1

Suggested fix:
In DatabaseMetaData.java, line 2502 (method getColumns) we got such code:

fullColumnQueryBuf.append("COLUMNS FROM ");
fullColumnQueryBuf.append(quotedId);
fullColumnQueryBuf.append(tableName);
fullColumnQueryBuf.append(quotedId);
fullColumnQueryBuf.append(" FROM ");
fullColumnQueryBuf.append(quotedId);

So we need to handle correctly delimited table names, for example:
fullColumnQueryBuf.append(tableName.replaceAll(quoteId, qouteId + quoteId));

Hello iriyoox,

Thanks for your report.

Verified as described.

Added entry to Connector/J 5.1.26 changelog:

"DatabaseMetaData.getColumns() threw an MySQLSyntaxErrorException if the schema contains tables with ANSI quoted names with leading and trailing back quotes (`). When those names were passed as parameters in unquoted form, Connector/J treated them as quoted because of the back quotes, and thus the error. This fix adds the behavior that when the connection property pedantic was set to true, methods like DatabaseMetaData.getColumns() treat all parameters as unquoted."