How to properly escape SQL when using BindingSource's Filter property

Question

I've inherited a C# application that lists data from a database table via a DataGridView. There are also a few text fields used to filter that data. This is done like so:

String listFilter = string.Format("City = '{0}'", this.toolStripSearch.Text);
this.customersBindingSource.Filter = listFilter;

toolStripSearch is a text field for searching by city name. The problem is that there is no SQL escaping. Adding a quote to the field crashes the app.

What is the proper way of escaping SQL here?

Answer 1

I think the 'correct' way for BindingSource.Filter is to simply escape the single quotes using '' as horrible as that sounds. E.g, something like.

String listFilter = string.Format("City = '{0}'", this.toolStripSearch.Text.Replace("'", "''")); 
this.customersBindingSource.Filter = listFilter;  

The Filter property uses the same syntax as DataColumn expressions when BindingSource is bound to data from a database.

http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.filter.aspx

You can find documentation on the required syntax here:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

From that link:

String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character).