C# Convert Escape Characters inside string variable into string literal for SQL query

Question

I am trying to convert a string variable in C# that contains escape characters like "\r\n" into a string literal so that it can be used in a SQL query.

// Here is the value of the string which IS NOT actually a verbatim string literal, but a value passed on from a selected dropdown list.
strEmailText = "We're writing in regard to XXX mentioned above.\r\nPlease contact us.\r\nWe're available by direct reply.\r\nThank you for your assistance."

// Here I create the actual SQL string for use to query the database
strSQL = @"SELECT DISTINCT TBL_EmailText.ID FROM TBL_EmailText WHERE (TBL_EmailText.Text = N'" + strEmailText + "')";

Now, whenever I try to search with this SQL string, it converts the escape characters and messes up the query like so:

@"SELECT DISTINCT TBL_EmailText.ID FROM TBL_EmailText WHERE (TBL_EmailText.Text = N'We''re writing in regard to XXX mentioned above.

Please contact us.

We''re available by direct reply.

Thank you for your assistance.')"

So my question is, how can I get this to work so that it searches using the following:

@"SELECT DISTINCT TBL_EmailText.ID FROM TBL_EmailText WHERE (TBL_EmailText.Text = N'We''re writing in regard to XXX mentioned above.\r\nPlease contact us.\r\nWe''re available by direct reply.\r\nThank you for your assistance.')"

I have found and tried using this code but it does not work:

protected internal static StringWriter ToLiteral(string strString)
    {
        using (StringWriter strWriter = new StringWriter())
        {
            using (CodeDomProvider cdpProvider = CodeDomProvider.CreateProvider("CSharp"))
            {
                cdpProvider.GenerateCodeFromExpression(new CodePrimitiveExpression(strString), strWriter, null);
                return strWriter.ToString();
            }
        }
    }

It still converts the escape characters.

Any help would be appreciated. Thanks in advance!

Answer 1

Your problem is not caused by the escaped characters. Those only matter to C#. When the string is concatenated to your sql query, they will just be a regular carriage return and linefeed.

The real problem is caused by using string concatenation in the first place! You have an apostrophe in your data that will mess up the final query once it is concatenated.

Instead, use a parameterized query and this will not be a problem and you will avoid the sql injection vulnerability too!

// Here is the value of the string which IS NOT actually a verbatim string literal, but a value passed on from a selected dropdown list.
strEmailText = "We're writing in regard to XXX mentioned above.\r\nPlease contact us.\r\nWe're available by direct reply.\r\nThank you for your assistance."

// Here I create the actual SQL string for use to query the database
strSQL = @"SELECT DISTINCT TBL_EmailText.ID FROM TBL_EmailText WHERE TBL_EmailText.Text = @EmailText";

using (var sqlCmd = new SqlCommand(strSQL, conn))
{
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.Parameters.Add(new SqlParameter { ParameterName = "@EmailText", SqlDbType = SqlDbType.NVarChar, Value = strEmailText });

    using(SqlDataReader rdr = sqlCmd.ExecuteReader())
    {
        //Do something with the data
    }
}

Note the use of the parameter @EmailText in the sql query and how it is added to the Parameters collection of the sqlCmd object.

This approach will eliminate the problem with apostrophes in the query and, more importantly, the sql injection vulnerability.