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?

Answer 1

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

Answer 2

@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;

Answer 3

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.