CODEWITHSUNDEEP
javamysqlprepared-statement
Boombastic
Boombastic

Reputation: 57

Mysql syntax error in preparedstatement

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();

Upvotes: 1

Views: 1706

Answers (1)

Mikhail Antonov
Mikhail Antonov

Reputation: 1367

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.

Upvotes: 6

Related Questions