How do i escape mysql strings in C# for use with LIKE

Question

I want to find a word in my list of tags. They can be at the beginning, end or middle so i tried writing.

Where name like "%@0%"

That didnt work so i tried

@"LIKE ""%" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(q) +@"%"

I thought that worked so i tried searching %. The results showed up all my tags, i expected only tags with % in them (so that would be none ATM).

How do i escape strings properly? and use it to search the middle of text?

-edit-

The solution is the below. I ran it against a few test and it passed them all. The query is

... where n.name LIKE CONCAT("%",  @some_name , "%") ...;

then in code

cmd.Parameters.AddWithValue("@some_name", val.Replace("\\", "\\\\").Replace("_", "\\_").Replace("%", "\\%")));

cdhowie · Accepted Answer · 2011-01-14 01:44:24Z

3

You also need to escape % and _ characters in the string, since those are special wildcards used by the LIKE operator:

@"LIKE ""%" +
    MySql.Data.MySqlClient.MySqlHelper.EscapeString(q)
        .Replace("_", "\\_").Replace("%", "\\%") +
    @"%"

You might consider wrapping this in a static method for reusability.

answered Jan 14, 2011 at 1:44

cdhowie

172k25 gold badges303 silver badges324 bronze badges

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

4 Comments

user34537 Over a year ago

I'll add that in but it feels like a hack. -edit- geeze, it doesnt even escape ". I cant use MySqlHelper.EscapeString at all. This isnt working

cdhowie Over a year ago

Try using a parametrized query. Then your query text will be ... LIKE CONCAT("%", @likeParm, "%"). You will then only have to apply the two Replace methods to the string to make it ready to be passed in as @likeParm.

user34537 Over a year ago

Hmm, close. ok this works with .Replace("\\", "\\\\").Replace("_", "\\_").Replace("%", "\\%")) -edit- i cant +1 cause i undid it 45mins ago

cdhowie Over a year ago

Make sure you quote quotation marks too or you will be vulnerable to SQL injection. Using a parametrized query is really the best approach here.

Collectives™ on Stack Overflow

How do i escape mysql strings in C# for use with LIKE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related