0

Trying to delete record from my database, but I get the error "Unknown column '' in 'where clause'".

private void deleteUser() {
    String query = "DELETE FROM user WHERE Name =" + tfemail.getText() + "";
    executeQuery(query);
    showUsers();
}

enter image description here enter image description here

A J
  • 3,970
  • 14
  • 38
  • 53
Kevin399
  • 1
  • 2
    First use quotes around your values (or better prepared statements) and second don't store the password in plain text!!! – juergen d Dec 16 '20 at 10:11
  • 1
    Print the string query and check what you are saving in. – MR Mark II Dec 16 '20 at 10:13
  • 1
    Don't insert text from user into a SQL statement. It leaves the code susceptible to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) attacks, allowing the user to corrupt / destroy / steal your data. Use a `PreparedStatement`, or an equivalent helper method. It also prevents errors like this. – Andreas Dec 16 '20 at 10:13
  • *but I get error "Unknown column '' in 'where clause'".* Provide: 1) precise value of `query` variable which causes error 2) complete and unchanged error message. – Akina Dec 16 '20 at 10:15
  • Prediction: no single quotes which must wrap the literal value inserted into the query text. – Akina Dec 16 '20 at 10:16
  • Unknown column 'Admin' in 'where clause' – Kevin399 Dec 16 '20 at 10:16
  • As @Andreas mentioned you're about to make it vulnerable for SQL injections if simply wrapping the email argument with apostrophes. Especially if apostrophe is a part of the email address e.g. `johnny's_gift@mail.com`, and your query gets either syntactically broken causing SQL exceptions or exploited: https://stackoverflow.com/questions/8527180/can-there-be-an-apostrophe-in-an-email-address – terrorrussia-keeps-killing Dec 16 '20 at 10:42

2 Answers2

1

You can't write queries this way. Imagine someone put in the tfemail field this text:

"Joe' OR FALSE"

and let's see what that would do to your SQL query:

DELETE FROM user WHERE Name = 'Joe' OR FALSE;

bye, database!

Some dbs let you execute stuff on the server the db engine runs on. Which means this trick can be used to completely hack the machine or format the disk entirely. bye, entire machine.

This also means your executeQuery method needs to be removed - that abstraction ('here is some SQL, please run it') is rarely useful (as it cannot contain any user input), and entices you to write security leaks.

The solution is prepared statements:

PreparedStatement ps = con.prepareStatement("DELETE FROM user WHERE Name = ?");
ps.setString(1, "Joe");
ps.executeUpdate();

This solves your problem, and does so safely - ps.setString(1, "Joe' OR FALSE"); is now no longer an issue (the DB engine or JDBC driver guarantees that it will take care of the problem; the effect would be to delete the entry in your user table that literally reads "Joe' OR FALSE").

Furthermore, storing passwords in a database is not an acceptable strategy; the solution is e.g. bcrypt: Use a hashing algorithm designed specifically to store passwords.

rzwitserloot
  • 85,357
  • 5
  • 51
  • 72
-1
String query = "DELETE FROM user WHERE Name ='" + tfemail.getText() + "'";
                                             ^                         ^
                                             |___________add___________|
terrorrussia-keeps-killing
  • 1,533
  • 1
  • 5
  • 17
Akina
  • 39,301
  • 5
  • 14
  • 25
  • @Kevin399 Don't forget to mark your question as resolved with this answer. – Lutzi Dec 16 '20 at 10:26
  • No, don't. This is a horrible, _horrible_ answer. It seems to work, but it actually doesn't: __This will get your server hacked__. @Akina please delete or update this answer. – rzwitserloot Dec 16 '20 at 10:48
  • @rzwitserloot Everyone has the right to do as they want - including doing bad things. You have warned the author - that is enough. Smart will take into account. A fool is not a pity. – Akina Dec 16 '20 at 11:00