Description:
Stored functions that contain certain keywords will fail because of the way the connector is iterating through keywords. The most obvious test case involves any stored function that contains a comma, followed by the word NOT.
The findEndOfReturnsClause function in the DatabaseMetaData class defines these tokens in this order:
String[] tokens = new String[] { "LANGUAGE", "NOT", "DETERMINISTIC",
"CONTAINS", "NO", "READ", "MODIFIES", "SQL", "COMMENT", "BEGIN",
"RETURN" };
How to repeat:
Create table and function:
create table x (id int not null, val varchar(100) null);
delimiter ;;
CREATE FUNCTION getNotNull() RETURNS int(11)
BEGIN
declare retval int(11);
select count(*) into retval
from x
where id in (1, 2)
and val IS NOT NULL;
return retval;
END;
;;
delimiter ;
insert into x values (1, null), (1, 'test'), (2, 'test'), (3, 'test'), (4, null);
Create java program:
package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestBug {
public static void main(String args[]) throws Throwable {
String dbUrl = "<yourdb>";
String dbUser = "<youruser>";
String dbPass = "<yourpass>";
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
CallableStatement cs = conn.prepareCall("{? = call getNotNull()}");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));
}
}
Execute, and get the following error:
Exception in thread "main" java.sql.SQLException: java.lang.NumberFormatException: For input string: "11)
BEGIN
declare retval int(11);
select count(*) into retval
from x
where id in (1"
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:430)
at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:505)
at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3881)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939)
at test.TestBug.main(TestBug.java:16)
Suggested fix:
The problem is that the first pass in the DatabaseMetaData takes a lot more data than just the return value. It's often hidden by later processing that looks for the first (, but before that happens, there is a check for a comma that is causing it to choke. It seems like the cleanest solution is to return the correct data in that first pass. Rather than guess what that should be, this solution takes the closest index of a keyword to the RETURNS keyword.
Replace the code at 1893:
int startLookingAt = positionOfReturnKeyword + "RETURNS".length() + 1;
for (int i = 0; i < tokens.length; i++) {
int endOfReturn = StringUtils.indexOfIgnoreCaseRespectQuotes(
startLookingAt, procedureDefn, tokens[i], quoteChar
.charAt(0), !this.conn.isNoBackslashEscapesSet());
if (endOfReturn != -1) {
return endOfReturn;
}
}
with this:
int firstKeyword = Integer.MAX_VALUE;
for (int i = 0; i < tokens.length; i++) {
int endOfReturn = StringUtils.indexOfIgnoreCaseRespectQuotes(
startLookingAt, procedureDefn, tokens[i], quoteChar
.charAt(0), !this.conn.isNoBackslashEscapesSet());
if (endOfReturn >= 0 && endOfReturn < firstKeyword) {
firstKeyword = endOfReturn;
}
}
if(firstKeyword < Integer.MAX_VALUE) {
return firstKeyword;
}
Description: Stored functions that contain certain keywords will fail because of the way the connector is iterating through keywords. The most obvious test case involves any stored function that contains a comma, followed by the word NOT. The findEndOfReturnsClause function in the DatabaseMetaData class defines these tokens in this order: String[] tokens = new String[] { "LANGUAGE", "NOT", "DETERMINISTIC", "CONTAINS", "NO", "READ", "MODIFIES", "SQL", "COMMENT", "BEGIN", "RETURN" }; How to repeat: Create table and function: create table x (id int not null, val varchar(100) null); delimiter ;; CREATE FUNCTION getNotNull() RETURNS int(11) BEGIN declare retval int(11); select count(*) into retval from x where id in (1, 2) and val IS NOT NULL; return retval; END; ;; delimiter ; insert into x values (1, null), (1, 'test'), (2, 'test'), (3, 'test'), (4, null); Create java program: package test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Types; public class TestBug { public static void main(String args[]) throws Throwable { String dbUrl = "<yourdb>"; String dbUser = "<youruser>"; String dbPass = "<yourpass>"; DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass); CallableStatement cs = conn.prepareCall("{? = call getNotNull()}"); cs.registerOutParameter(1, Types.INTEGER); cs.execute(); System.out.println(cs.getInt(1)); } } Execute, and get the following error: Exception in thread "main" java.sql.SQLException: java.lang.NumberFormatException: For input string: "11) BEGIN declare retval int(11); select count(*) into retval from x where id in (1" at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.Util.handleNewInstance(Util.java:430) at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:505) at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3881) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939) at test.TestBug.main(TestBug.java:16) Suggested fix: The problem is that the first pass in the DatabaseMetaData takes a lot more data than just the return value. It's often hidden by later processing that looks for the first (, but before that happens, there is a check for a comma that is causing it to choke. It seems like the cleanest solution is to return the correct data in that first pass. Rather than guess what that should be, this solution takes the closest index of a keyword to the RETURNS keyword. Replace the code at 1893: int startLookingAt = positionOfReturnKeyword + "RETURNS".length() + 1; for (int i = 0; i < tokens.length; i++) { int endOfReturn = StringUtils.indexOfIgnoreCaseRespectQuotes( startLookingAt, procedureDefn, tokens[i], quoteChar .charAt(0), !this.conn.isNoBackslashEscapesSet()); if (endOfReturn != -1) { return endOfReturn; } } with this: int firstKeyword = Integer.MAX_VALUE; for (int i = 0; i < tokens.length; i++) { int endOfReturn = StringUtils.indexOfIgnoreCaseRespectQuotes( startLookingAt, procedureDefn, tokens[i], quoteChar .charAt(0), !this.conn.isNoBackslashEscapesSet()); if (endOfReturn >= 0 && endOfReturn < firstKeyword) { firstKeyword = endOfReturn; } } if(firstKeyword < Integer.MAX_VALUE) { return firstKeyword; }