0

I am having difficulty with the following statement that I want to send to the MySQL server:

UPDATE abonament SET ? = '1' WHERE abonutid = ?

And I replace them with:

ps.setString(1, "`" + (indexLunaPlatita + 1) + "`");
ps.setInt(2, selVal);

But when I run the program I get this into MySQL:

''`24`' = '1' WHERE abonutid = 2'

So it automatically surrounds the back ticked expression with ' '.

Any help please ?

Thank you !

Dragonthoughts
  • 2,103
  • 8
  • 24
  • 26
Dorian-Catalin Badirca
  • 31
  • 1
  • don't use setString, just concatenate them with your query – YCF_L May 17 '18 at 08:47
  • @YCF_L Do not concatenate your query, that leads to SQL Injection. – Dragonthoughts May 17 '18 at 08:48
  • 2
    You can't use `?` placeholders for column names (or table names) . – Arnaud May 17 '18 at 08:49
  • @Dragonthoughts I agree but there are no way to use setString I already answers someone here https://stackoverflow.com/questions/50320515/can-not-create-database-jdbc/50320560#50320560 the OP can check the column name before he/she concatenate it with the query – YCF_L May 17 '18 at 08:50
  • in general prepared statements, whatever your programming language, don't need the punctuation to be added by you, they are doing something more sophisticated than string replacements. They introduce values. – Dragonthoughts May 17 '18 at 08:50
  • @YCF_L In that case I get: Parameter index out of range. I already tried that. – Dorian-Catalin Badirca May 17 '18 at 09:05
  • @Dorian-CatalinBadirca I don't think you test it correctly, can you please show me your code? – YCF_L May 17 '18 at 09:07
  • `ps = con.prepareStatement("UPDATE abonament SET" + "\`" + "?" + "\`" + "= '1' WHERE abonutid = ?");` – Dorian-Catalin Badirca May 17 '18 at 09:11
  • @Dorian-CatalinBadirca try this **ps = con.prepareStatement("UPDATE abonament SET `" + (indexLunaPlatita + 1) + "`= '1' WHERE abonutid = ?");** – YCF_L May 17 '18 at 09:14
  • @YCF_L It worked like this `ps = con.prepareStatement("UPDATE abonament SET \`" + (indexLunaPlatita + 1) + "\` = '1' WHERE abonutid = ?");` THANK YOU VERY MUCH ! Is this something susceptible to SQL inject ? – Dorian-Catalin Badirca May 17 '18 at 09:22
  • @Dorian-CatalinBadirca as I already in my comment, You have to check your input before you concatenate it with your query. – YCF_L May 17 '18 at 09:24
  • Do you really have a column called 24? – Mark Rotteveel May 17 '18 at 11:27

1 Answers1

2

You cannot use placeholders (?) for column names, just for values. This is a general rule (not limited to MySQL).

If you need to determine column name based on user input, be very careful - you'll have to sanitize the input to avoid SQL Injection attack yourself, JDBC can't help you prevent the attack in this case.

The reason for ? not being viable for your case is because those parameterized queries are implemented using prepared statements:

  • A statement is sent to database, parsed, compiled and possibly optimized without the values being known at this time
    • (primary use case intended for prepared statements is performance improvements through reuse of the compiled statement)
  • The values are then provided in second step, instructing the database to perform the statement with those values
  • Another set of values then may be sent to execute the prepared statement with, etc.

Since the values are not sent until the second step, they cannot be used for anything that determines what the statement really does (e.g. which table or columns it uses).

Jiri Tousek
  • 11,964
  • 5
  • 27
  • 41