Winforms BindingSource Does Not Support Parameterized Queries - So Your App Crashes When Your User Types "doesn't"

Introduction

The Winforms BindingControl is very useful to filter DataGridView and similar controls quickly, because it has a Filter property which allows the display data to change without modification of the underlying data source. But ... it's a string. And there is no "Parameters" collection.

So you can't pass your user input as a parameterized query, you have to use string concatenation:

dataSource.Filter = string.Format("description LIKE '%{0}%' OR FullText LIKE '%{0}%'", s);

And that means that if your user enters "doesn't work" or "don't do it!" or "100% or more" then your app crashes (you can catch the exception, yes - but that doesn't help your user much) because the filter sees the string:

description LIKE '%doesn't%' OR FullText LIKE '%doesn't%'

and doesn't know what to do with it.

Background

The solution - or at least the only solution I've found - is to escape the "dodgy" characters in the input string:

'*', '%', '[', and ']' need to be surrounded by square brackets

'\'' needs to be replaced with a pair of single quotes.

Simple enough, so I knocked up an extension method:

using System.Diagnostics;
using System.Text;

namespace GeneralTesting
    {
    public static class ExtensionMethods
        {
        private static string[] replace = { "*", "%", "[", "]", "'" };
        private static string[] with = { "[*]", "[%]", "[[]", "[]]", "''" };
        /// <summary>
        /// Escapes special characters for LIKE queries
        /// </summary>
        /// <remarks>
        /// BindingSource doesn't accept parameterized queries: so we have to
        /// manually "fix" user input to prevent "doesn't", "don't", and so forth
        /// crashing the app.
        /// </remarks>
        /// <param name="raw"></param>
        /// <returns></returns>
        public static string EscapeLikeValue(this string raw)
            {
            Debug.Assert(replace.Length == with.Length, "replace and with do not match");
            StringBuilder sb = new StringBuilder(raw.Length * 3);
            sb.Append(raw);
            for (int i = 0; i < replace.Length; i++)
                {
                sb.Replace(replace[i], with[i]);
                }
            return sb.ToString();
            }
        }
    }

Using the Code

Simple:

/// <summary>
/// Filter changed - apply it.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tbFilter_TextChanged(object sender, EventArgs e)
    {
    string s = tbFilter.Text;
    if (!string.IsNullOrWhiteSpace(s))
        {
        s = s.EscapeLikeValue();
        dataSource.Filter = string.Format("description LIKE '%{0}%' OR FullText LIKE '%{0}%'", s);
        }
    else
        {
        dataSource.Filter = null;
        }
    }

Points of Interest

I learned that Microsoft doesn't think ahead and too many of its programmers don't understand parameterized queries, which is both sad and worrying...

History

• 2018-08-01: First version