BindingSource.Filter crashing (injection)

Question

So I have some simple code:

source.Filter = "n LIKE '%" + txtSearch.Text + "%'";

Source being a BindingSource object.

txtSearch being a TextBox.

What is the proper method to make sure it will always consider the contents of txtSearch.Text as a string, and not crash every time I type a "(" or a number of other characters (not sure which yet).

Surely there must be a function somewhere to escape all these, or something?

score 1 · Answer 1 · answered Oct 14 '14 at 14:16

Just ran into same issue and found this solution from http://www.csharp-examples.net/dataview-rowfilter/

public static string EscapeLikeValue(string valueWithoutWildcards)
{
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < valueWithoutWildcards.Length; i++)
  {
     char c = valueWithoutWildcards[i];
     if (c == '*' || c == '%' || c == '[' || c == ']')
       sb.Append("[").Append(c).Append("]");
     else if (c == '\'')
       sb.Append("''");
     else
     sb.Append(c);
   }
   return sb.ToString();
}

score 0 · Answer 2 · answered Jun 11 '13 at 15:42

0

There is no in-built function. But, still you can write your own extension method like this:

public static string RemoveSpecialCharacters(this string str)
{
   StringBuilder stringBuilder  = new StringBuilder();
   foreach (char c in str)
    {
      if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') ||
          (c >= 'a' && c <= 'z') || c == '.' || c == '_') 
      {
         stringBuilder.Append(c);
      }
   }
   return stringBuilder.ToString();
}

and your code will become

source.Filter = "n LIKE '%" + txtSearch.Text.RemoveSpecialCharacters() + "%'";

Note:

You can rename the method to a suitable one. I have used this name for your understanding purpose only.

answered Jun 11 '13 at 15:42

Bhushan Firake

9,338
5
44
79

-1 Really not. In this way you just **remove** every non-ASCII character (not very suitable solution for non US customers). There are [different rules](http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx) to escape that string. – Adriano Repetti Jun 11 '13 at 16:04
@Adriano It is just a sample method. I have asked OP to write his own like this one.It is assumed that he will skip charactrs according to his culture and region – Bhushan Firake Jun 11 '13 at 16:15
1

I can't agree, he has to write his own method but the way to do it is **not to discard _unknown_ characters**. He should **escape characters he knows** have _special meaning_, everything else must be left untouched. – Adriano Repetti Jun 11 '13 at 16:25
Yes， sorry for the response speed, I need the characters, I can't just throw them away. The way I discovered this issue existed was when searching for a valid result containing these characters. If the results don't show, it's pointless having the search. – Matty Jun 20 '13 at 16:59
@Matty You won't be using any more characters than these , are you? – Bhushan Firake Jun 20 '13 at 17:01
@Bhushan Firake I'll be using () [] and potentially any other symbols that another organisation providing the data may think to use in the future. If I had a list of all characters which needed to be escaped and what the appropriate escaping manner is, I could write something, but I can't find this information either. – Matty Jun 20 '13 at 17:03

score 0 · Answer 3 · answered Jun 11 '13 at 16:16

0

Why not include the search string as a parameter in the original query? The only time I've used BindingSource.Filter is when I want to filter by data I know exists, i.e. populate a CheckedListBox with all possible values on a particular column and allow user to select which ones to view.

The alternative would be to catch the exception from special characters and handle it with a message to the user.

answered Jun 11 '13 at 16:16

ashishduh

6,629
3
30
35

I would, but there is no original query, it's just an XML file. The list is quite large. Part of the problem is I need brackets to be allowed, so I can't just ignore it. – Matty Jun 11 '13 at 17:05

BindingSource.Filter crashing (injection)

3 Answers3