MySql Where Clause Parameter

Question

MySqlCommand command = new MySqlCommand("
    select * 
    from singlecustomer 
    where name like concat ('%',@search,'%') 
        or code like concat ('%',@search,'%') 
        or id like concat ('%',@search,'%')", con);

command.Parameters.Add(new MySqlParameter("@search", "%"+sc.sc_SearchBox.Text+"%"));

MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
DataTable dt = new DataTable();
adapter.Fill(dt);
sc.dgClientLog.DataSource = dt;

this is the method that verify if the search is successful or not.

SearchClientDataGrid(sc);
if (sc.dgClientLog.Rows.Count == 0)
{
    sc.NoItemFoundLabel.Visible = true;
    sc.NoItemFoundLabel.BringToFront();
    return;
}
else
{
    sc.NoItemFoundLabel.Visible = false;
}

It works but whenever I search starting with the character "%" for Example: %David, it still work, is there a way to remove %? I'm recently learning MySql Parameter to avoid Sql Injection. It just ticks me off that % actually work instead of returning no found.

hi fubo thanks for the reply. the % still works even with trim, I'd do a statement too, but I'm afraid it wont be a dynamic search since doing statement would make it application level search, not database level search. — Akagami Shanks
– Akagami Shanks, Commented May 19, 2020 at 14:01
could you specify your example a bit? I'm not sure if I'm getting this question right. Input / result / expected result — fubo
– fubo, Commented May 19, 2020 at 20:15
hi fubo, I applied this to datagridview, by searching from database and feeding it up to dataset to display to datagridview. I get the error in input/result. example when i search name. David, it works fine, but when I add % character like this %David, it still output the result David, what I want to expect is if I type %David it shouldn't return a row from the table containing the data of David, because of the % character, if I try to search using #David, it doesn't return a row that's What I want to happen with % too. — Akagami Shanks
– Akagami Shanks, Commented May 20, 2020 at 3:31

fubo · Accepted Answer · 2020-05-20 05:54:18Z

1

For that you have to escape the % character reference with the mysql escape character \. Then it will not be treated as a special character anymore.

so the solution would be

string search = "%" + sc.sc_SearchBox.Text.Replace("%", @"\%") + "%";
command.Parameters.Add(new MySqlParameter("@search",search));

probably you should take a look at the referenced table and also handle the other special character _ which is also a wildcard character used by the LIKE search. The other escaping is handled by the mySQLParameter. e.g. the following turns " into \" (MySqlHelper.EscapeString is internally applied when you use parameters)

string result = MySqlHelper.EscapeString("foo\"bar");

edited May 20, 2020 at 5:54

answered May 20, 2020 at 5:45

fubo

46.2k20 gold badges109 silver badges139 bronze badges

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

1 Comment

Akagami Shanks Over a year ago

It Worked!, I will read about MySqlHelper and EscapeString thanks for your Effort Fubo. thank you very muchhhh.

Collectives™ on Stack Overflow

MySql Where Clause Parameter

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related