Executing update query, with prepared statement

Question

 int selectie = toernooienUitvoer.getSelectedRow(); int selectiec = toernooienUitvoer.getSelectedColumn(); String primarykey = (String) toernooienUitvoer.getValueAt(selectie, 0).toString(); String waarde = toernooienUitvoer.getValueAt(selectie, selectiec).toString(); String columnaam = toernooienUitvoer.getModel().getColumnName(selectiec).toString(); String input = JOptionPane.showInputDialog("wijzig geselecteerde data", waarde); toernooienUitvoer.setValueAt(input, selectie, selectiec); PreparedStatement stat = con.prepareStatement("UPDATE fullhouse.toernooi SET ? = ? WHERE toernooi.T_code = ?"); stat.setString(1, columnaam); stat.setString(2, input); stat.setString(3, primarykey);

Guys, i know the query is correct, if i input the values. my guess my mistake is somewhere in the preparedstatement i am getting a MySQLSyntaxErrorException:

Miserable Variable · Accepted Answer · 2013-01-24 22:42:21Z

As mentioned in other answer, the placeholder ? can only be used for values, not for table and column names. Since you are not reusing the PreparedStatement this is quite simple.

Change from

PreparedStatement stat = con.prepareStatement("UPDATE fullhouse.toernooi SET ? = ? WHERE toernooi.T_code = ?")

to

PreparedStatement stat = con.prepareStatement("UPDATE fullhouse.toernooi SET " + columnName + " = ? WHERE toernooi.T_code = ?")

And adjust the index parameter in the setString calls.

but that isnt safe for mysql injection? someone has mentioned that on here
If you are accepting column name as input, you need to verify it using other mechanism, you already seem to be doing something converting from selectiec to columnName, if the column name is invalid then you don't need to execute the statement at all
just validate the variable if it comes from input (check it's one word, identifier and so on)

PermGenError · Accepted Answer · 2013-01-24 22:51:21Z

3

I don't think you can use place holder for dynamically passing the column name,your query should be:

"UPDATE fullhouse.toernooi SET colname = ? WHERE toernooi.T_code = ?"

edited Jan 24, 2013 at 22:51

answered Jan 24, 2013 at 22:38

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

3 Comments

hamchi Over a year ago

but that column name changes, i need for it to be based on a selection?!?!? i also thought that might of been the problem

Smit Over a year ago

@hamchi In that case you better use a variable, but I dont think this is good idea.

Miserable Variable Over a year ago

@smit, yes can't :) I can't edit the comment anymore so I am going to delete it

GeorgeVremescu · Accepted Answer · 2013-01-24 22:40:30Z

When you use bind variables, it means the statement is precompiled and on the next executions, it will be faster. You are trying to make the name of the column to be a bind variable, which is not possible.

because you obviously need to update several different columns, in order to achieve some speed, you should declare several prepared statements, one for each column. Keep them in a HashMap<String, PreparedStatement>

Patashu · Accepted Answer · 2013-01-24 22:42:30Z

The column name of a prepared statement cannot be dynamic because, depending on the column name, the query plan will be wildly different (e.g. sometimes table scan will be the fastest, sometimes using an index, sometimes something even more esoteric).

If SQL can't rely on a certain plan being the fastest, it needs to come up with a new one every time - which means there's no point in making a prepared statement which is why you can't do it.

Collectives™ on Stack Overflow

Executing update query, with prepared statement

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Related