This is my fairly simple SQL:
SELECT
CONCAT(
'{ ',
'"test": ',
'{',
'"a": true,', /* don't set to false */
'"b": { "t": true },',
'"c": 2',
'},',
'"b": { "t": true } ',
'}'
) AS test
I'm using mysql-connector-java-5.1.7-bin.jar
in Java to connect to a MariaDB database.
In my SQL editor if I run that sql this is what gets returned with no errors:
{ "test": {"a": true,"b": { "t": true },"c": 2},"b": { "t": true } }
However, for some reason Java returns:
{ "test": {"a": true,"b": ,"c": 2},"b": { "t": true } }
Notice that the first "b"
has its value { "t": true }
completely removed for some reason, but only the first instance of "b"
, the second one is fine.
This is what I'm doing in Java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class testSQL {
private static final String DB_URL = "jdbc:mysql://localhost:3306/database?characterEncoding=utf-8";
private static final String DB_USER = "your_username";
private static final String DB_PASSWORD = "your_password";
public static void main(String[] args) {
String sql = "SELECT\n\tCONCAT(\n\t'{ ',\n\t\t'\"test\": ',\n\t\t'{',\n\t\t\t\t'\"a\": true,',\t/* don't set to false */\n\t\t\t\t'\"b\": { \"t\": true },',\n\t\t\t\t'\"c\": 2',\n\t\t'},',\n\t\t'\"b\": { \"t\": true } ',\n\t'}'\n\t) AS test";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getObject(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
What could be causing this?
Issue is with EscapeTokenizer.java in mysql-connector-java-5.1.7. Apostrophe in comment is not properly interpreted.
This works fine with mysql-connector-java-5.1.49. If upgrade is not an option, you will have to remove or escape the apostrophe in comment.