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:

1. How can I escape square brackets in a LIKE clause?
2. How to escape a string for use with the LIKE operator in SQL Server?

Rhinitis

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? – Latinalatinate

Your SQL statement bears no resemblance to the code sample you have provided – Trellas

@BenRobinson I don't understand what you mean. Can you please explain it more? – Rhinitis

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. – Trellas

Answer 1

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

Alinaaline

You should probably also add input = input.Replace(@"'", @"\'"); in there too. – Keen

input = input.Replace(@"'", @"''"); is correct way for escaping with single quote. – Emmery

Surely escaping a single quote is the most important one of the lot? – Elias

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! – Belsky

Answer 2

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

Rhinitis