JDBC + Java Query execution error

Question

I'm getting this exception

java.sql.SQLException: Unknown column 'auyu' in 'where clause'

My Query and method in my database facade class.

db.save("delete from users where name = auyu");
public static void save(String sql) throws Exception {
 new DBFacade().connect();
 synchronized (c) {
 c.createStatement().executeUpdate(sql);
 }
}

Answer 1

I suspect you meant:

delete from users where name = 'auyu'

This is still a pretty odd SQL command to give to a "save" method.

I'd also strongly suggest that you use parameterised SQL statements instead of embedding data directly into the SQL itself - particularly if the data has come from the user.

• Switch: Thanks what if I need to use a String like this: String s = "auyu"
• T.J. Crowder: @Rocky: Use a preparedStatement (or string concatenation); see my answer for details on the PS option.

Answer 2

+1 to Jon Skeet's answer. Expanding and perhaps going OT, but it's best to parameterize these things and ensure escaping so that you aren't susceptible to SQL-injection attacks. E.g.:

public static void deleteUser(userName)
throws Exception
{
 PreparedStatement ps;
 new DBFacade().connect();
 // (Assuming 'c' is a connection that's in scope somehow)
 synchronized (c) {
 // (You'd want to cache the prepared statement in an appropriate
 // way related to how you're handling connections and pooling)
 ps = c.prepareStatement("delete from users where name = ?");
 ps.setString(1, userName);
 ps.executeUpdate();
 }
}

Otherwise, if a user provides a name like "anyu'; drop table users;", you could be for it.

Answer 3

You need single quotes around the auya ('auyu') and you'll need to escape them like so:

"delete from users where name = \'auyu\'"