Parsing SQL Like to avoid overloads SQL Server

When using Stored Procedures or embeds SQL in a query generally we forget about parse the parameters where a Like condition is present. The are two possible wildcards for the LIKE function in SQL Server, that are _ (underscored), that replaces a character ant %, that replaces any quantity of characters.

For example if we have the following Stored Procedure:

CREATE PROCEDURE [Production].[GetProductsByName]
    @Name nvarchar(50)
AS
SET NOCOUNT ON;
SELECT
    [ProductID],
    [Name],
    [ProductNumber],
    [StandardCost],
    [ListPrice]
FROM
    [Production].[Product]
WHERE
    [Name] LIKE @Name

And the following snippet of code:

    Database database = DatabaseFactory.CreateDatabase();
    using (DbCommand command =
        database.GetStoredProcCommand("Production.GetProductsByName"))
    {
        database.AddInParameter(command, "Name", DbType.String, name + "%");

        using (IDataReader reader = database.ExecuteReader(command))
        {

        }
    }

If a user send the value "%Foo%" in the parameter the query will get all the records that contains the string "Foo" in the name, but the really thing that we want to do were get all the records that begin with "%Foo%". To accomplish this we can add the following method:

    private static string ParseLike(string value)
    {
        if (value == null)
        {
            return string.Empty;
        }
        return value.Replace("%", "[%]").Replace("_", "[_]");
    }

And apply the method to the line that sets the name parameter:

    database.AddInParameter(
        command, "Name", DbType.String, ParseLike(name) + "%");

Basically in order to say to the SQL Server that "_" and "%" are characters and not wildcards is necessary encloses between brackets.