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.

Answer 1

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
   }
}