Description:
The URL parameter parser for JConnector does not allow to include '=' in the value of URL parameter. Effectively the value is cut after '='. For example, when I tried to include sessionVariables in the connection URL to set sql_mode to ANSI like in:
java.sql.Connection c = new com.mysql.jdbc.NonRegisteringDriver().
connect("jdbc:mysql://localhost:3306/DB"
+"?user=USER&password=PASSWORD"
+"&sessionVariables=sql_mode='ANSI'", null);
I got:
java.sql.SQLException: 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 '' at line 1.
This is caused by the fact that URL parser effectively sets sessionVariables to just "sql_mode" instead of "sessionVariables=sql_mode='ANSI'". That leads later to execution of "SET SESSION sql_mode" instead of "SET SESSION sql_mode='ANSI'" and error generation.
How to repeat:
Compile and run the following Java program:
public class Test
{
public static void main(String[] args)
throws Exception
{
java.sql.Connection c = new com.mysql.jdbc.NonRegisteringDriver().
connect("jdbc:mysql://localhost:3306/DB"
+"?user=USER&password=PASSWORD"
+"&sessionVariables=sql_mode='ANSI'", null);
}
}
where DB, USER, PASSWORD should be replaced by the valid values.
Suggested fix:
I use javax.sql.DataSource interface to connect to the database and configure DB properties using Tomcat Context, which allows to pass session properties only through URL.
Thus as I workaround I was forced to put explicit
statement.executeUpdate("SET SESSION sql_mode='ANSI';")
after connection initialization in the servlet code.
Description: The URL parameter parser for JConnector does not allow to include '=' in the value of URL parameter. Effectively the value is cut after '='. For example, when I tried to include sessionVariables in the connection URL to set sql_mode to ANSI like in: java.sql.Connection c = new com.mysql.jdbc.NonRegisteringDriver(). connect("jdbc:mysql://localhost:3306/DB" +"?user=USER&password=PASSWORD" +"&sessionVariables=sql_mode='ANSI'", null); I got: java.sql.SQLException: 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 '' at line 1. This is caused by the fact that URL parser effectively sets sessionVariables to just "sql_mode" instead of "sessionVariables=sql_mode='ANSI'". That leads later to execution of "SET SESSION sql_mode" instead of "SET SESSION sql_mode='ANSI'" and error generation. How to repeat: Compile and run the following Java program: public class Test { public static void main(String[] args) throws Exception { java.sql.Connection c = new com.mysql.jdbc.NonRegisteringDriver(). connect("jdbc:mysql://localhost:3306/DB" +"?user=USER&password=PASSWORD" +"&sessionVariables=sql_mode='ANSI'", null); } } where DB, USER, PASSWORD should be replaced by the valid values. Suggested fix: I use javax.sql.DataSource interface to connect to the database and configure DB properties using Tomcat Context, which allows to pass session properties only through URL. Thus as I workaround I was forced to put explicit statement.executeUpdate("SET SESSION sql_mode='ANSI';") after connection initialization in the servlet code.