How to search User Input within DataTable Column

Question

Currently I'm searching as below.

DataRow[] rows = dataTable.Select("FieldName='"+ userInput + "'");

The problem here is whenever user provides an input with single quote ('), it throws error.

I can easily correct it by

DataRow[] rows = dataTable.Select("FieldName='" + userInput.Replace("'","''") + "'");

I'm worried what other user inputs might cause problem?

what u have done is correct... u don't need to worry about inputs — Binil, Dec 27 '10 at 05:02
I don't agree with the above comment, you must always sanitize your inputs. Either write a custom regex to replace bad input or use a library function of which many are available. — Bnjmn, Dec 27 '10 at 05:09

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

Here is the exact answer from honourable Mr. Jon Skeet.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 27 '10 at 09:05

IsmailS

10,797
21
82
134

Jeevan Bhatt · Answer 2 · 2010-12-27T07:14:34.567

@Ismail: It would be a good habit if we validate user input before using that in front end query or in back-end query.

So i think in your scenario you must have function like...

if(ValidateInput(userInput))
{
  DataRow[] rows = dataTable.Select("FieldName='"+ userInput + "'");
}

and in validation you can do any check. right now you only want to check ' but in future, may be you will have to check some thing else.

and based on your need you can checge return type of validate function, if you want to modify input data then modify and return that else just return bool.

If you want to use DataTable.Select(filter) for data filter then you have to format/ignore or replace special character from filter statement and for that u will have to write more code. If you dont want to be panic for special character then you can use LINQ like

        DataTable dataTable = new DataTable();
        DataColumn dc = new DataColumn("FieldName");
        dataTable.Columns.Add(dc);
        DataRow dr = dataTable.NewRow();
        dr[0] = "D'sulja";
        dataTable.Rows.Add(dr);
        string input = "D'sulja";

        var result = from item in dataTable.AsEnumerable()
                     where item.Field<string>("FieldName") == input select item;

What if I have to match userInput with a value having single quote within it. For e.g. my field have a value `D'Souza` and user is trying to match with it? Or even it might have a value with `%` or `#`? — IsmailS, Dec 27 '10 at 05:59

score 0 · Answer 3 · answered Dec 27 '10 at 06:29

In this case, I think the single quote is the only character you have to worry about since it is used to delimit string values. For more information on expression syntax, see the MSDN entry for DataColumn.Expression (creating a filter expression uses the same rules as for the DataColumn.Expression property).

You don't indicate which version of C# you are using, but with LINQ, you can do this:

var rows = table.AsEnumerable()
                .Where(r => r.Field<string>("Name") == "O'Hare")
                .Select(r => r)
                .ToArray();

One tradeoff is that you'll also need to check the RowState if you have any deleted rows in the DataTable, but it does provide another option.

How to search User Input within DataTable Column

3 Answers3