I've been working on a new feature for a Smart Client at work and it was time to push it to beta and have QA go over it. I was speaking to the QA team member and they were certain they could break the new feature with just a single quote. The reason this QA team member said this is they test a lot of PHP web apps which are notoriuos for this type of mistake because of magic quotes turned on or off or whatever. As I explained to them, the Smart Client shouldn't encounter this when saving or deleting data as ADO.Net does a pretty good job of handling this type of thing.
Later in the evening I got an email from the QA team member with a screen shot where they had created an entry with a single quote in it. O'Connel for example. At first I thought it was a joke and then I confirmed it by doing it myself. What happened I wondered? I've never seen this before. So, I dug into the code.
In this particular application the screen it occured on was a screen where I had applied a DataView filter to narrow the results of a DataSet. Here is a simple app I built called "Help Desk" to show you what I am talking about. Below the menu in the sample you'll notice I added a TextBox and a Filter button to the ToolStrip. As you type information into the TextBox it filters the dataset by the First Name column. Here is how that's done.
private void toolStripTextBox1_TextChanged(object sender, EventArgs e)
{
DataView dv = adventureWorksDataSet.Contact.DefaultView;
dv.RowFilter = "FirstName LIKE '%"+ toolStripTextBox1.Text + "%'";
this.contactBindingSource.DataSource = dv;
}
The problem the QA team member found was not in the datalayer. It is pretty simple to see that if you have a single quote in the toolStripTextBox1 object it is going to break the syntax of the RowFilter. So that's the problem, now let's fix it. I solved it quickly by doing this:
private void toolStripTextBox1_TextChanged(object sender, EventArgs e)
{
DataView dv = adventureWorksDataSet.Contact.DefaultView;
dv.RowFilter = "FirstName LIKE '%"+ toolStripTextBox1.Text.Replace("'", "''") + "%'";
this.contactBindingSource.DataSource = dv;
}
This got me to thinking though, what other situations would this come up. There may be others where you need to be careful to watch out for this but the only other similar situation was whereby you would do a select on a DataTable to return an array of rows. For example:
AdventureWorksDataSet
.
ContactRow[] rows = adventureWorksDataSet.Contact.Select(
"FirstName='" + toolStripTextBox1.Text.Replace(
"'",
"''") +
"'");
I then started testing all types of other characters in this situation and the single quote is the only thing I came up with that would break it. I guess in the end we were both right. I was right in the fact that the datalayer wouldn't be broken but the QA team memeber did in fact find break it with a single quote. Note to self. Self, if you are doing a RowFilter or DataSet Selects with a filter and are getting the input from the user, replace the single quotes.
I've attached the sample Help Desk solution to this post for those that want to download and play with it. All you need is the AdventureWorks database and change the connection string to your own connection string.