MySQL Insert error?

Question

I am writing a program that tracks sales for a local business. I wrote a method to export a table to a csv file, then right after load that csv file into a new table for archiving purposes; however, when it runs through the method archive() it will only run through the while loop once, works but then on the second loop through it gives me this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'N' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
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:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at serverarchiver.ServerGui.archive(ServerGui.java:118)
at serverarchiver.ServerGui.run(ServerGui.java:144)
at java.lang.Thread.run(Thread.java:722)

Here is the relevant code:

 public void archive(){
      try {

             //Archive and new table

             stmt.executeQuery("SELECT * INTO OUTFILE '"+ getMonth()+"" +  getYear() +  ".csv' FIELDS TERMINATED BY ',' FROM last");
             stmt.executeUpdate("CREATE  TABLE `lastdb`.`"+ getMonth()+"" +  getYear() +  "` (`Name` VARCHAR(50) NOT NULL ,`Goal` INT  NOT NULL ,`New` INT NOT NULL , `Used` INT NOT NULL  , `Total` INT NOT NULL , `Pace` INT NOT NULL  ,PRIMARY KEY (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) );");

             CSVReader reader = new CSVReader(new FileReader("C:/ProgramData/MySQL/MySQL Server 5.5/data/lastdb/"+getMonth()+"" +  cal.getInstance().get(Calendar.YEAR) +  ".csv"));
             String[] nextLine;

             while((nextLine = reader.readNext()) != null){
                 stmt.executeUpdate("INSERT INTO `lastdb`.`"+ getMonth()+"" +  cal.getInstance().get(Calendar.YEAR) +  "` (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) VALUES ('"+ nextLine[0]+ "', " +nextLine[1]+ " , " +nextLine[2]+ "  , " +nextLine[3]+ "  , " +nextLine[4]+ "  , " +nextLine[5]+ ")");
             }
         }catch(FileNotFoundException e){
            e.printStackTrace();
         }catch(IOException ed){
            ed.printStackTrace();
         }catch(SQLException eds){
            eds.printStackTrace();
         }
 }

Sorry, I am still new with MySQL what do you want to see exactly? — Aaron Decker
– Aaron Decker, Commented Nov 23, 2011 at 19:23
The complete query at the specific line. So the query as it's being execute, with the variables replaced by their values — Michiel van Vaardegem
– Michiel van Vaardegem, Commented Nov 23, 2011 at 19:25
print this before stmt.executeUpdate String query = "INSERT INTO lastdb."+ getMonth()+"" + cal.getInstance().get(Calendar.YEAR) + "` (Name, Goal, New, Used, Total, Pace) VALUES ('"+ nextLine[0]+ "', " +nextLine[1]+ " , " +nextLine[2]+ " , " +nextLine[3]+ " , " +nextLine[4]+ " , " +nextLine[5]+ ")"` — abhinav
– abhinav, Commented Nov 23, 2011 at 19:25

BalusC · Accepted Answer · 2011-11-23 19:30:24Z

That can only be caused by a SQL injection attack of the CSV file which you're reading.

In other words, the SQL string which you're concatenating there with unvalidated CSV values could have resulted in a malformed SQL string. I suggest to do a System.out.println() of the populated SQL string before it's been executed, so that you can check what exactly made the SQL query invalid.

You can fix it by escaping the CSV values accordingly, but best solution would be to use PreparedStatement instead of Statement. E.g.

preparedStatement = connection.prepareStatement("INSERT INTO `lastdb`.`" + getMonth() + "" +  cal.getInstance().get(Calendar.YEAR) 
    + "` (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) VALUES (?, ?, ?, ?, ?, ?)");

while ((nextLine = reader.readNext()) != null) {
    // ...
    preparedStatement.setString(1, nextLine[0]);
    preparedStatement.setString(2, nextLine[1]);
    preparedStatement.setString(3, nextLine[2]);
    preparedStatement.setString(4, nextLine[3]);
    preparedStatement.setString(5, nextLine[4]);
    preparedStatement.setString(6, nextLine[5]);
    preparedStatement.executeUpdate();
}

It not only escapes column values, but it also improves performance. You can even improve it further by using batches by addBatch() and executeBatch().

while ((nextLine = reader.readNext()) != null) {
    // ...
    preparedStatement.setString(1, nextLine[0]);
    preparedStatement.setString(2, nextLine[1]);
    preparedStatement.setString(3, nextLine[2]);
    preparedStatement.setString(4, nextLine[3]);
    preparedStatement.setString(5, nextLine[4]);
    preparedStatement.setString(6, nextLine[5]);
    preparedStatement.addBatch();
}

preparedStatement.executeBatch();

Collectives™ on Stack Overflow

MySQL Insert error?

2 Answers 2

See also:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

See also:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related