Correct way to escape characters in a DataTable Filter Expression

Question

I would like to know if there is a function to correctly escape string literals for filter expressions. e.g.:

DataTable.Select(String.Format("[name] = '{0}'", MyName)) 

If MyName contains ' or a number of other key characters an exception is generated. The Microsoft documentation indicates that these charaters should be correctly escaped, however there is a bit of confusion on how this is to be done.

I have tried replacing ' with ' and also ['] as indicated in the documentation, however the query still fails.

Answer 1

Escape the single quote ' by doubling it to ''. Escape * % [ ] characters by wrapping in []. e.g.

private string EscapeLikeValue(string value) {     StringBuilder sb = new StringBuilder(value.Length);     for (int i = 0; i < value.Length; i++)     {         char c = value[i];         switch (c)         {             case ']':             case '[':             case '%':             case '*':                 sb.Append("[").Append(c).Append("]");                 break;             case '\'':                 sb.Append("''");                 break;             default:                 sb.Append(c);                 break;         }     }     return sb.ToString(); }  public DataRow[] SearchTheDataTable(string searchText) {       return myDataTable.Select("someColumn LIKE '"                                   + EscapeLikeValue(searchText) + "'"); }  

Thanks to examples here

Answer 2

If I replace ' with two single ' the query works.