How to resolve MySQLSyntaxErrorException in JDBC preparedStatement

Question

I have a query that throws a com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException and I can't figure out why. I am using xampp and when I try the same query directly, it works fine. I also have a whole bunch of other methods that use very similar queries that all work. The problem seems to be with updating the date, I noticed in the error message that java puts ' ' around the date, which makes it a string and is probably the reason for the error. However I'm not sure how to fix this to insert the date as a date.

Here's the code:

public void update(int userId, String date, String column, String value){
    try {
        // convert date from String to Date
        DateTime dt = DateTime.parse(date);  
        java.sql.Date sqlDate = new java.sql.Date(dt.getMillis()); 
        // create prepared statement
        conn = DriverManager.getConnection("jdbc:mysql://localhost/db","root", "");
        String query = "UPDATE expenses_income SET ? = ? WHERE userId = ? AND date = CAST(? AS DATETIME);";
        PreparedStatement preparedStmt = conn.prepareStatement(query);
        preparedStmt.setString(1, column);
        preparedStmt.setString(2, value);
        preparedStmt.setInt(3, userId);
        preparedStmt.setDate(4, sqlDate);
        preparedStmt.executeUpdate();

        conn.close();
    } catch (Exception e) {
        System.err.println("MySQL exception: " + e);
    }
}

And the error message:

MySQL exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''comment' = '123' WHERE userId = 1 AND date = CAST('2018-01-06' AS DATETIME)' at line 1

I also tried the query without casting as datetime:

String query = "UPDATE expenses_income SET ? = ? WHERE userId = ? AND date = ?;";

But I get the same error. I then tried to use java.util.Date instead of Joda DateTime, but it doesn't help. Any ideas?

Thanks!

A column name can't be a parameter of the prepared statement. — Eran, Jan 14 '18 at 12:39
Parameters are only for **values**, a column name is not a value. — Mark Rotteveel, Jan 14 '18 at 14:12

Ravi · Accepted Answer · 2018-01-14T12:45:06.570

1

.. right syntax to use near ''comment' = '123'

You are getting exception because column name parameterization, which is not correct.

 UPDATE expenses_income SET ? = ?

It should be

 UPDATE expenses_income SET column_name = ?

I also noticed a semicolon ; at the end of the SQL, which should be removed and you don't need to cast Date explicitly. It should be just

UPDATE expenses_income SET column_name = ? WHERE userId = ? AND date = ?

Also, you shouldn't name column name like date, it should be last_updated or something meaningful.

edited Jan 14 '18 at 12:45

answered Jan 14 '18 at 12:39

Ravi

30,829
42
119
173

I changed the query to "String query = "UPDATE expenses_income SET ? = ? WHERE userId = " + column + " AND date = CAST(? AS DATETIME);";" But I'm still getting the same error message. EDIT Sorry I just noticed my mistake. Thanks! – kattapillar Jan 14 '18 at 12:44
@kattapillar You didn't bother to look at my solution. I said, you are suppose to keep column name. Look at my solution carefully. – Ravi Jan 14 '18 at 12:45

How to resolve MySQLSyntaxErrorException in JDBC preparedStatement

1 Answers1