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.

I am also facing same issue, in my case i just want to check that any row exist with given name so i was used LINQ for this. Data.AsEnumerable().Where(row => Convert.ToString(row["Name"]).ToLower().Equals(newName.ToLower())).Any() — Nilay Mehta
– Nilay Mehta, Commented Dec 21, 2017 at 11:34

Rory · Accepted Answer · 2010-05-28 10:30:03Z

37

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

answered May 28, 2010 at 10:30

Rory

42.2k56 gold badges191 silver badges271 bronze badges

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

2 Comments

Ross Presser Over a year ago

This is not working for me for strings containing brackets. The string "Images [Three-Up]" is being found by the filter "entryBlockName='Images [Three-Up]'" and not the filter "entryBlockName='Images [[]Three-Up[]]'"

Ross Presser Over a year ago

Ah. The escaping of brackets is only when the filter is a LIKE clause. Not when it's an = clause.

Brian Webster · Accepted Answer · 2012-02-17 00:27:21Z

7

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

edited Feb 17, 2012 at 0:27

Brian Webster

31k51 gold badges158 silver badges227 bronze badges

answered Dec 22, 2008 at 12:20

Ady

4,7662 gold badges25 silver badges20 bronze badges

4 Comments

Tomalak Over a year ago

That's not even DataTable specific, but rather the general way of doing it in SQL. :-)

Ady Over a year ago

Although I use parameterised queries, so don't come across the need for this in SQL. But I get you.

Tomalak Over a year ago

If you use parameterized queries you are on the safe side anyway, but a TableFilter is an in-memory subset of SQL, consisting of a WHERE clause that must conform to SQL syntax rules. It's just a string, so no parameters here. That's why this extra step must be taken.

binki Over a year ago

I admit that i was hoping the answer to this question would be “the parameterized version of DataTable.Select() is…”, but seems that MS decided every programmer needs to reimplement value escaping themselves.

Soenhay · Accepted Answer · 2020-07-17 16:54:53Z

0

   /// <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;
    }

edited Jul 17, 2020 at 16:54

answered Jul 18, 2016 at 15:05

Soenhay

4,1086 gold badges39 silver badges62 bronze badges

6 Comments

Cee McSharpface Over a year ago

how is this safe? it appears to trade one vulnerability for a more obscure one.

Soenhay Over a year ago

What vulnerabilities are you talking about? If you mean passing user input into the function then just don't pass user input.

Cee McSharpface Over a year ago

just sayin' as the main driver for this escaping is usually bobby tables-types of attacks. which would not apply in OP case unless you send it to an RDBMS. besides enforcing syntactical correctness of the composed statement, of course. the actual flaw is the use of "magic string constants" which by themselves are a valid input.

Cee McSharpface Over a year ago

simply put: use the code of your answer as the input of the function you posted, and it will return an incorrect output :)

Soenhay Over a year ago

True, but that is why I picked something that would never be used in my system for any reason other than this. "~~LeftBracket~~". It is a hack but it worked for me.

|

Collectives™ on Stack Overflow

Correct way to escape characters in a DataTable Filter Expression

3 Answers 3

2 Comments

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related