DataTable select method with single quote conflict C#

Question

I recently found when I do a LINQ select under a field that contains an apostrophe, it makes my application to throw an exception.

DataRow[] newDr = this.ds.Tables["TableName"].Select("Name = '" + drData["Name"].ToString() + "'");

If drData["Name"] = "The business's money"

I got an exception of "Syntax error: Missing operand after 'S' operator "

Can anyone tell me how to preserve it and no replace it or remove it, please?

This is a textbook example of what happens when you do not sanitize user input — lc., Sep 06 '13 at 17:48
FWIW, [the documentation](http://msdn.microsoft.com/en-us/library/det4aw50.aspx) refers to [this page](http://www.csharp-examples.net/dataview-rowfilter/) explaining exactly how and what to escape — lc., Sep 06 '13 at 17:53
@siride Technically, yes, there is nothing "wrong" with the input, and it may very well be within the expected range of valid input (on the design end at least). But blindly executing it in a query transforms this seemingly innocuous input to have disastrous effects. However you slice it, you need to take care the string does not contain any characters that would be misinterpreted, which IMHO is all part of sanitization process. Whether this means disallowing `'` characters entirely, encoding them, or escaping them is entirely up to the architecture/business design. — lc., May 26 '15 at 00:57

King King · Accepted Answer · 2013-09-06T18:00:00.010

23

You have to escape the single quote to 2 single quotes, there may be other special characters so you have to take care of them:

DataRow[] newDr = this.ds.Tables["TableName"]
                         .Select(string.Format("Name ='{0}'",drData["Name"].ToString().Replace("'","''")));

More examples on escaping special characters in a filter string (used in Select method): Row filter string examples

edited Sep 06 '13 at 18:00

answered Sep 06 '13 at 17:49

King King

61,710
16
105
130

score 7 · Answer 2 · edited May 23 '17 at 11:59

Its Not LINQ Select, instead its DataTable.Select method. The error you are getting is because of character ', you need to escape it with double ' '. Simple way would be to replace it like:

DataRow[] newDr = this.ds.Tables["TableName"]
                     .Select("Name = '" + drData["Name"].ToString().Replace("'", "''" + "'");

You may see this question for how to escape characters in DataTable.Select

DataTable select method with single quote conflict C#

2 Answers2

Linked

Related