Update statement syntax error

Question

I have the following string which holds the query I want to execute:

     query = "UPDATE inventario"
     + " set descripcion = '" + descripcion + "',"
     + " set bodega = '" + bodega + "'"
     + " where codigo = " + codigo;

I get an Update statement syntax error but I dont see where is the error. Any help is appreciated. columns "descripcion" and "bodega" are text type columns.

Answer 1

Well it's probably because you've got multiple set parts instead of using comma separation, and potentially because you don't have quotes around the codigo value (if that's another string)... but I'd strongly advise you not to create SQL like this anyway, with values directly in the SQL.

Instead, use a prepared statement:

String sql = "UPDATE inventario set descripcion=?, bodega=? where codigo=?";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, descripcion);
st.setString(2, bodega);
st.setString(3, codigo);

Using prepared statements has three immediate benefits:

• It avoids SQL injection attacks (think about what happens if your description has a quote in it)
• It separates code (SQL) from data (the values)
• It means you avoid conversions for types like datetime, where going via a string representation is a huge potential source of error

Answer 2

Remove extra SET on your query.

query = "UPDATE inventario"
 + " set descripcion = '" + descripcion + "',"
 + "     bodega = '" + bodega + "'"
 + " where codigo = " + codigo;

but that query is vulnerable with SQL Injection. Please parameterize your query. Example,

String query = "UPDATE inventario" + 
               "   set descripcion = ?, bodega = ? " + 
               " where codigo = ?";
PreparedStatement prep = connection.prepareStatement(query);
prep.setString(1, descripcion);
prep.setString(2, bodega);
prep.setInt(3, codigo);
prep.executeUpdate();