Handling special character input for data selection

Question

UPDATE

Following four characters need to be escaped when '\' is the escape helper %[]_

QUESTION

Though I searched a lot, I could not find out a problem exactly matching the following, in stack overflow.

I have a database column called Log_Description. It has two records.

1) "Sample % value record"

2) "Sample free record"

I am using SQL Command and setting parameters as shown below

commandText = commandText + “Log_Description LIKE @Log_Description”;
command.Parameters.AddwithValue(“@Log_Description”, “%”+obj. LogDescription+”%”);

Suppose the user enters “%” as search param for txtLogDescription textbox, I need to show only first record. But currently it is showing both the records.

What are the possible ways to overcome this?
What are the other characters that may cause such issues with the above code?

Note: I cannot prevent user from entering “%” as input

Note: I am using SQL Server as database

EDIT:

Solution I am using now is Escaping the escape character does not work – SQL LIKE Operator

    private static string CustomFormat(string input)
    {
        input = input.Replace(@"\", @"\\");
        input = input.Replace(@"%", @"\%");
        input = input.Replace(@"[", @"\[");
        input = input.Replace(@"]", @"\]");
        input = input.Replace(@"_", @"\_");
        return input;
    }

LINQ approach (with performance hit) is below

        Collection<Log> resultLogs = null;


        if (!String.IsNullOrEmpty(logSearch.LogDescription))
        {
            resultLogs = new Collection<Log>();
            var results = from o in logs where o.LogDescription.Contains(logSearch.LogDescription) select o;
            if (results != null)
            {
                foreach (var log in results)
                {
                    resultLogs.Add((Log) log);
                }
            }
        }
        else
        {
            resultLogs = logs;
        }

If you are using SQL Server there is a similar question here http://stackoverflow.com/a/258947/1045728 , basically you would escape any % found in the search value — Tommy Grovnes, Nov 15 '12 at 07:41
Refer http://stackoverflow.com/questions/13861004/escaping-the-escape-character-does-not-work-sql-like-operator — LCJ, May 23 '13 at 11:16

score 1 · Answer 1 · edited May 23 '17 at 11:56

As far as escaping %, see Tommy Grovnes' comment on the question.

If you can use a List<T> instead of a Collection<T> (see here), this can be written more concisely:

var descr = logSearch.logDescription;
var results = (
    from o in logs
    where String.IsNullOrEmpty(descr) ||
            o.LogDescription.Contains(descr)
    select o
).ToList();

If you still need a Collection<T>, you can wrap the results of the LINQ query in a Collection constructor:

var results = new Collection<Log>((
    from o in logs
    where String.IsNullOrEmpty(descr) ||
            o.LogDescription.Contains(descr)
    select o
).ToList());

Handling special character input for data selection

1 Answers1