Mysql syntax error in preparedstatement

Question

I have a simple prepared statement that updates the records in a MySQL database. The table, the column, the target attribute and the information are taken from textfields. First I made it without prepared statement, but the program was easily breakable if the user chose to insert quotes or %%$&^* other symbols.

Now Im getting this:

UPDATE client SET 'full_name'=Martinajh WHERE id='5'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 ''selectedTable2' SET 'namec' = 'cellValue' WHERE id== 5' at line 1

String cellValue3 = String.valueOf( table.getValueAt(row, 0) );
int id = Integer.parseInt(cellValue3);
String selectedTable2 = (String) listOfTablesNames.getSelectedValue();
String cellValue = String.valueOf( table.getValueAt(row, column) );
String namec = table.getColumnName(column);
String query ="UPDATE ? SET ? = ? WHERE id== ?";
PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(query);
preparedStmt.setString   (1, "selectedTable2");
preparedStmt.setString(2, "namec");
preparedStmt.setString(3, "cellValue");
preparedStmt.setInt(4, id);
preparedStmt.executeUpdate();

WHERE id== ? Try using a single equal sign here.

C. Helling
– C. Helling

2017-04-05 20:27:29 +00:00
Commented Apr 5, 2017 at 20:27 — C. Helling
– C. Helling, Commented Apr 5, 2017 at 20:27

Mikhail Antonov · Accepted Answer · 2017-04-05 20:47:47Z

6

You can't construct a preparedstatement with table name as a parameter. You need to contruct the SQL with string concatenation/placeholder with String.format. Prepared statement is for the column values not for table name. In your case:

String query ="UPDATE `" + selectedTableVar + "` SET `" + fieldNameVar + "` = ? WHERE id = ?";
//...
preparedStmt.setString(1, "cellValue");
preparedStmt.setInt(2, id);

Also, id = ? as @C. Helling mentioned.

As for sanitizing selectedTableVar and fieldNameVar variables, I can't find a link right now, but you can do the research by yourself about what is a valid qualifier in MySQL... AFAIK, any UTF character is valid, most of special symbols may be a part of a valid table name etc. Using the syntax suggested above you should bother not allowing the ` character to prevent injection and I guess that's it. But it must be investigated.

edited Apr 5, 2017 at 20:47

answered Apr 5, 2017 at 20:33

Mikhail Antonov

1,3873 gold badges24 silver badges33 bronze badges

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

3 Comments

tadman Over a year ago

It's generally a good idea to identifier escape those if there's a method for that internally.

Mikhail Antonov Over a year ago

It is, indeed. But I cannot recall one right now and I fail to google it rapidly. So let it be @Boombastic task :)

Boombastic Over a year ago

Thank you sir, I managed to solve everything after reading your answer.

Collectives™ on Stack Overflow

Mysql syntax error in preparedstatement

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related