Unknown column 'xy' in 'where clause'

Question

I'm using this query

var query = $"SELECT id, username, password, salt FROM users WHERE username={username}";

Assuming username is set toxic, the error thrown is the following:

Unknown column 'toxic' in 'where clause'

I already tried to add single quotes ('), threw another error (having an error near 'toxic' in syntax). However, I wonder what is wrong with that query? I elaborated and found evidence that this comes from the very query itself.

A text field like username requires a values between quotes. However try to read how to use parameterized queries — Steve, Jun 04 '16 at 20:46
it worked for other queries too, so I didn't see a necessity to do it differently? If even so, I wouldn't know how, though — toxic, Jun 04 '16 at 20:48
Could you add the code with the single quotes that you have used? — Steve, Jun 04 '16 at 20:49
Print your SQL query before execution to see how your parameter handled. BTW, I agree with @Steve, use parameters. — Ali1928, Jun 04 '16 at 20:52
`var query = $"SELECT id, username, password, salt, online, nickname, rank, firstlogin, banned, bantime, clanid, clanrank FROM users WHERE username='{username}'";` — toxic, Jun 04 '16 at 20:52
What happen with that code? Do you have any error message displayed? Please [edit] your question adding the block of code used not just a single line. As is the latest code should work provided the variable username is filled with a valid value to search for (existing value in the table) — Steve, Jun 04 '16 at 20:55

score 2 · Accepted Answer · edited May 23 '17 at 10:34

If the column username is a text column, then everytime you want to search on this column, the literal value should be enclosed between single quotes

string username = "Steve";
var query = $"SELECT id, username, password, salt FROM users WHERE username='{username}'";

However this is the wrong way to make a query text for two main problems:

Sql Injection: a tecnique used by hackers to insert in your code malicious text that could destroy your database data See: Sql Injection

Parsing problems: Strings that contains single quotes need to be properly formatted, decimal values need to be converted to strings with the proper decimal separator valid for the database locale, dates....well...

So the parameters approach will free you from all these problems

var query = @"SELECT id, username, password, salt 
              FROM users WHERE username=@username";
using(MySqlConnection cnn = new MySqlConnection(.......))
using(MySqlCommand cmd = new MySqlCommand(query, cnn))
{
   cnn.Open();
   cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
   using(MySqlDataReader reader = cmd.ExecuteReader())
   {
       ..... use your data
   }
}

I was using a self-written MySql wrapper (featuring async functions, etc) and I added "cmd.Parameters.Add...". It works. Thanks! — toxic, Jun 04 '16 at 21:17

Unknown column 'xy' in 'where clause'

1 Answers1