CODEWITHSUNDEEP
.netado.net
Ady
Ady

Reputation: 4736

Correct way to escape characters in a DataTable Filter Expression

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.

Upvotes: 26

Views: 36273

Answers (3)

Soenhay
Soenhay

Reputation: 4048

   /// <summary>
    /// <para>If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].</para>
    /// <para>If the pattern is not in a like clause then you can pass valueIsForLIKEcomparison = false to not escape brackets.</para>
    /// <para>Examples:</para>
    /// <para>- strFilter = "[Something] LIKE '%" + DataTableHelper.EscapeLikeValue(filterValue) + "%'";</para>
    /// <para></para>
    /// <para>http://www.csharp-examples.net/dataview-rowfilter/</para>
    /// </summary>
    /// <param name="filterValue">LIKE filterValue. This should not be the entire filter string... just the part that is being compared.</param>
    /// <param name="valueIsForLIKEcomparison">Whether or not the filterValue is being used in a LIKE comparison.</param>
    /// <returns></returns>
    public static string EscapeFilterValue(string filterValue, bool valueIsForLIKEcomparison = true)
    {
        string lb = "~~LeftBracket~~";
        string rb = "~~RightBracket~~";
        filterValue = filterValue.Replace("[", lb).Replace("]", rb).Replace("​*", "[*​]").Replace("%", "[%]").Replace("'", "''");
        if (valueIsForLIKEcomparison)
        {
            filterValue = filterValue.Replace(lb, "[[]").Replace(rb, "[]]");
        }
        else
        {
            filterValue = filterValue.Replace(lb, "[").Replace(rb, "]");
        }

        return filterValue;
    }

Upvotes: 0

Ady
Ady

Reputation: 4736

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

Upvotes: 7

Rory
Rory

Reputation: 41817

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

Upvotes: 37

Related Questions