Java MySQL UPDATE error using WHERE in conjunction with AND

Question

I am trying to UPDATE an entry in MySQL, the structure of the table is table_rankings (ip text, field text, rank double). Where ip is a user's IP address, field is an object being ranked and rank is the numerical rank of a field. My statement is:

//String for query
String query = "UPDATE "+catagory+"_rankings SET rank = ? WHERE ip = ? AND field = ? )";
PreparedStatement ps = conn.prepareStatement(query);
//fill ? variables
ps.setDouble(1, r[i]);
ps.setString(2, user);
ps.setString(3, s[i]);

ps.executeUpdate();

In this code catagory refers to a given table, for instance baseball_rankings or basketball_rankings. The error I am receiving is:

SQLException: 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 ')' at line 1
SQLState: 42000
VendorError: 1064

Is the issue that I cannot use WHERE as well as AND in an UPDATE statement? I am also curious if my update is subject to a SQL injection attack because I am using catagory+"_rankings". I have tried to use a ? variable however it results in another error.

@TimBiegeleisen I don't think he can. Not for the table name. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented May 15, 2017 at 2:00
That seems to be correct, when I replace the concatenation with the prepared statement I get an error. — user1709237
– user1709237, Commented May 15, 2017 at 2:02
@TimBiegeleisen You'd have to ask the designers of JDBC why you can't use a parameter for the table name. I don't know. I'm just fairly sure it doesn't work. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented May 15, 2017 at 2:03

Mobility · Accepted Answer · 2017-05-15 01:55:47Z

1

you add a extra “)”， or can say, you missed a "(".

try

String query = "UPDATE "+catagory+"_rankings SET rank = ? WHERE ip = ? AND field = ? ";

answered May 15, 2017 at 1:55

Mobility

3,3552 gold badges22 silver badges34 bronze badges

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

1 Comment

user1709237 Over a year ago

That is infuriating, I have been looking at this code for hours.

Collectives™ on Stack Overflow

Java MySQL UPDATE error using WHERE in conjunction with AND

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related