.Net Framework DataTable.Select(String) method when the filter expression contains ' or "

Question

In a .Net web application I use the public DataRow[] Select(string filterExpression) method in many places. Due to a last minute change characters such as ' and " are now valid input. What options do I have, is there a way to change the filterExpression in an adequate way and still preserve the Select functionality on the datatable, can I switch to LINQ?

Answer 1

not only you can switch to linq, you should.

Answer 2

Escape the single quote (') in an expression literal by doubling it: ''

No need to escape the double quote (") within a string literal. String literals are bound by single quotes, so the double quote need only be standard C# escaped: \" (or "" if within a verbatim string starting with the @ symbol)

See this link for more information.

Answer 3

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