Escaping the escape character does not work – SQL LIKE Operator

Question

I have used \ as escape character for LIKE operator. I am escaping following four characters

1 % 2 [ 3 ] 4 _

When I pass the escape character as input, the query does not return a value. How can I make it work?

Data Insert

DECLARE @Text VARCHAR(MAX)
SET @Text = 'Error \\\ \\  C:\toolbox\line 180'

INSERT INTO Account (AccountNumber,AccountType,Duration,ModifiedTime) 
VALUES (198,@Text,1,GETDATE())

CODE

    static void Main(string[] args)
    {

        string searchValue1 = @"Error \\\ \\  C:\toolbox\line 180";
        string searchValue2 = @"55555";

        string result1 = DisplayTest(searchValue1);
        string result2 =  DisplayTest(searchValue2);

        Console.WriteLine("result1:: " + result1);
        Console.WriteLine("result2:: " + result2);
        Console.ReadLine();

    }}


     private static string DisplayTest(string searchValue)
    {
        searchValue = CustomFormat(searchValue);


        string test = String.Empty;
        string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE @input ESCAPE '\'";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddWithValue("@input", "%" + searchValue + "%");

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {

                            test = reader.GetString(0);
                        }
                    }
                }
            }
        }

        return test;
    }


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

REFERENCE:

Have you considered using an escape character that isn't also a special character in C# and that won't naturally appear in the data or the search string? — Aaron Bertrand, Dec 13 '12 at 13:49
Your SQL statement bears no resemblance to the code sample you have provided — Ben Robinson, Dec 13 '12 at 13:52
@BenRobinson I don't understand what you mean. Can you please explain it more? — LCJ, Dec 13 '12 at 14:45
You have changed your post since i wrote that but i was just pointing out that the SQL in Data Insert was nothing like the SQL in your DisplayTest method. It's slightly clearer what you are trying to explain with that code now. — Ben Robinson, Dec 13 '12 at 15:19

score 11 · Accepted Answer · answered Dec 13 '12 at 14:07

11

Modify your CustomFormat method like this:

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

answered Dec 13 '12 at 14:07

Alex Filipovici

31,789
6
54
78

You should probably also add `input = input.Replace(@"'", @"\'");` in there too. – gunwin Feb 26 '14 at 13:33
`input = input.Replace(@"'", @"''");` is correct way for escaping with single quote. – Zameer Fouzan Nov 27 '17 at 09:55
1

Surely escaping a single quote is the most important one of the lot? – Ian Warburton Jan 18 '18 at 17:08
Why has the "'" character to be escaped for the **LIKE** operator? Of cause the whole value has to be handled as parameter `command.Parameters.AddWithValue` which should include this escaping. This LIKE escaping (CustomFormat) is not a protection against SQL injection! – Tom Jun 24 '20 at 07:12

score 1 · Answer 2 · edited May 23 '17 at 10:33

C# Code for LIKE handling

     public static string WildcardFormatSpecialCharacter(string source)
    {
        string formattedResult = string.Empty;
        if (!String.IsNullOrEmpty(source))
        {
            //Escape the escape character
            formattedResult = source.Replace(DataLayerConstants.EscapeCharacter, DataLayerConstants.EscapeCharacterWithEscape);
            //The %
            formattedResult = formattedResult.Replace(DataLayerConstants.Percentage, DataLayerConstants.PercentageWithEscape);
            //The [
            formattedResult = formattedResult.Replace(DataLayerConstants.OpenSqaureBracket, DataLayerConstants.OpenSqaureBracketWithEscape);
            //The ]
            formattedResult = formattedResult.Replace(DataLayerConstants.CloseSqaureBracket, DataLayerConstants.CloseSqaureBracketWithEscape);
            //The _
            formattedResult = formattedResult.Replace(DataLayerConstants.Underscore, DataLayerConstants.UnderscoreWithEscape);
        }
        return formattedResult;
    }


    public const string EscapeCharacter = @"\";
    public const string EscapeCharacterWithEscape = @"\\";
    public const string Percentage = "%";
    public const string PercentageWithEscape = @"\%";
    public const string OpenSqaureBracket = "[";
    public const string OpenSqaureBracketWithEscape = @"\[";
    public const string CloseSqaureBracket = "]";
    public const string CloseSqaureBracketWithEscape = @"\]";
    public const string Underscore = "_";
    public const string UnderscoreWithEscape = @"\_";

Other things to check Use of REPLACE in SQL Query for newline/ carriage return characters

Escaping the escape character does not work – SQL LIKE Operator

2 Answers2

Linked