JDBC, MySQL - DML Error with PreparedStatement executeUpdate

Question

I have two tables. I need to get some column values in a List from second table against col2 and update in the first table on the fly in a loop.

Code :

int ownerUpdateCount = 0;
    PreparedStatement statement = null;
    Connection connection = null;
    try{
        while(featuresIT.hasNext()){
            String feature = (String) featuresIT.next();

            String updateOwnerQuery = "UPDATE `regression_reports` "
                    + "JOIN `feature_testbed_owner_mapping_628` ON (`regression_reports`.Feature='"
                    + feature
                    + "' AND `regression_reports`.Feature = `feature_testbed_owner_mapping_628`.feature_as_on_webpage) "
                    + "SET `regression_reports`.Owner = `feature_testbed_owner_mapping_628`.owner";
            //logger.debug("\n updateOwnerQuery : " + updateOwnerQuery);
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            statement = connection.prepareStatement(updateOwnerQuery);
            //ownerUpdateCount += 
            ownerUpdateCount = statement.executeUpdate();
            System.out.println(feature + " ownerUpdateCount : " + ownerUpdateCount);
        }
        logger.debug("\n ownerUpdateCount : " + ownerUpdateCount);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
           try { if(null!=statement)statement.close();} catch (SQLException e) 
           {e.printStackTrace();}
           try { if(null!=connection)connection.close();} catch (SQLException e) 
           {e.printStackTrace();}
    }

Error :

java.sql.SQLException: Can not issue data manipulation statements with executeQuery(). at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:499) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1518) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at com.n7k.regression.RegressionDAO.updateRegReportsDB(RegressionDAO.java:71) at com.n7k.regression.RegressionServlet.doPost(RegressionServlet.java:63)

This error occurs only in the first iteration of the loop. Any suggestion to debug why its happening.

String updateFeaturesQuery = "INSERT INTO `regression_reports` "
                    + "(Feature, `Report`, `P`, `F`, `Remarks`) VALUES ('"
                    + feature + "','" + report + "'," + pass + "," + fail
                    + ") " + "ON DUPLICATE KEY UPDATE `Report` = '"
                    + report + "', `P` = " + pass + ", `F`= " + fail
                    + ", `Remarks` = " + remarks + ";";

       connection = dataSource.getConnection();
       statement = connection.prepareStatement(updateFeaturesQuery);

       Line#73 
       featureUpdateCount = statement.executeUpdate(updateFeaturesQuery);

Error :

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 '' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1811) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at com.n7k.regression.RegressionDAO.updateRegReportsDB(RegressionDAO.java:73)

The above execution gives this error. Executing the same query manually doesn't throw me any error.

Is this the actual code that you're running? while(it.hasNext){ looks odd to me. — Dawood ibn Kareem
Please post your actual code. String feature = (String) featuresIT.next(); // <-- Not it? and + colVal //<-- Not feature? Why aren't you using bind parameters? — Elliott Frisch
@DavidWallace I'm casting to String. I'm only referencing it for sample code. I'll post the original code. — Satish Jonnala
@DavidWallace Using a PreparedStatement but shoving constants into the query (which is going to render the Statement cache useless) and even worse sql injection vulnerabilities. — Elliott Frisch

Elliott Frisch Elliott Frisch · Accepted Answer · 2014-03-20T04:13:44

Your column Remarks is missing entirely here,

// No `Remarks` even though you included it in the list. `F`, `Remarks`)
 "," + fail /* + Remarks! */ + ") " + "ON DUPLICATE KEY UPDATE `Report` = '"

Your column Remarks is not escaped here,

+ ", `Remarks` = '" + remarks + "';";

But you should use bind parameters or String StringEscapeUtils#escapeSql(String) because your code is vulnerable to sql injection.

JDBC, MySQL - DML Error with PreparedStatement executeUpdate

3 Answers