Java MySQL executeUpdate syntax error

Question

Here's the code:

try{
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM mytable WHERE array=\"" + arrayName + "\"");
        if(rs.next()){
            String values = rs.getString("values");
            if(values == null) values = "";
            values += " " + added;
            values = values.replaceAll("\\s+"," ");
            stmt.executeUpdate("UPDATE mytable SET values = \"" + values + "\" WHERE array = \"" + arrayName + "\"");
            return true;
        }else{
            System.out.println("Missing array '" + arrayName + "', returning false");
            return false;
        }
    } catch(SQLException e) {
        String error = "MySQL crash while adding to array " + arrayName + "\n";
        error += e.getMessage();
        System.out.println(error);
        return false;
    }

Parameters of note: "mytable" is the name of the table in my database, "added" is the string I'm looking to add to a string in the "values" column of mytable, and "arrayName" is the string already stored in the "array" column of mytable ("array" and "values" are the only columns). Here's the error I'm getting:

MySQL crash while adding to array anodematerialsoptions
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 'values = "item1 item2 item3 added" WHERE array = "arrayName"' at line 1

So the "stmt.executeQuery" worked fine, and the "stmt.executeUpdate" failed on a syntax error. Can someone point out what is wrong here?

use preparedstatements

Ruchira Gayan Ranaweera
– Ruchira Gayan Ranaweera

2014-08-05 04:07:31 +00:00
Commented Aug 5, 2014 at 4:07 — Ruchira Gayan Ranaweera
– Ruchira Gayan Ranaweera, Commented Aug 5, 2014 at 4:07

Marc B · Accepted Answer · 2014-08-05 04:07:58Z

1

values is a reserved word in pretty much ANY sql database. You'll have to escape it:

        stmt.executeUpdate("UPDATE mytable SET `values` = \""  etc...
                                               ^------^---note the backticks

And note that you're vulnerable to sql injection attacks.

answered Aug 5, 2014 at 4:07

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

manivannan · Accepted Answer · 2014-08-05 04:21:28Z

0

You can use the query with alias in mysql

change

  stmt.executeUpdate("UPDATE mytable SET values = \"" + values + "\" WHERE array = \"" + arrayName + "\"");

into

  stmt.executeUpdate("UPDATE mytable t SET t.values = \"" + values + "\" WHERE t.array = \"" + arrayName + "\"");

answered Aug 5, 2014 at 4:21

manivannan

6221 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Java MySQL executeUpdate syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related