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!

possible duplicate of [single quotes escape during string insertion into a database](http://stackoverflow.com/questions/11912412/single-quotes-escape-during-string-insertion-into-a-database) — Viacheslav Smityukh, Aug 06 '15 at 14:20
Please read this http://stackoverflow.com/questions/11912412/single-quotes-escape-during-string-insertion-into-a-database — Viacheslav Smityukh, Aug 06 '15 at 14:21
[Hello SQL Injection!](https://www.owasp.org/index.php/SQL_Injection) — DGibbs, Aug 06 '15 at 14:23
Thanks Viacheslav for the response but I do not think you understand my issue. I already know how to fix the apostrophe problem by replacing quotes. The problem is with escape characters "\r\n" being converted to carriage returns during the SQL query. I need the "\r\n" escape characters to stay in the SQL query. — ForeverLearningAndCoding, Aug 06 '15 at 14:28
If you save `\r\n`, you'll get `\r\n`... which will be and appears as a new line. A quick hack would be to replace the `\` with `\\`. — Kilazur, Aug 06 '15 at 14:28
Hi Kilazur I tried the replace with * but after replacing back to \ and searching, it still converted into carriage returns. Thanks for response! — ForeverLearningAndCoding, Aug 06 '15 at 14:30

score 3 · Answer 1 · answered Aug 06 '15 at 14:21

3

You should not generate SQL sentences with literal strings embedded in it, that's what query parameters are for.

answered Aug 06 '15 at 14:21

Konamiman

49,681
17
108
138

1

Query parameters fix a host of potential problems like avoiding the remote possibility of SQL injection attacks--particularly if you are using text generated by a user. https://xkcd.com/327/ – Berin Loritsch Aug 06 '15 at 14:24
Okay I will try this and let you know if it works. Do you know if this method will allow me to use the escape characters as parameters, without converting the carriage returns? – ForeverLearningAndCoding Aug 06 '15 at 14:25
1

@ForeverLearningAndCoding If you use parameters you don't need to worry at all about the contents of your strings. The database library/engine handles everything you, no need to escape anything. – Konamiman Aug 06 '15 at 14:30
This worked. Thanks! I wish I could choose 2 answers for this question but Chris' answer helped me more... – ForeverLearningAndCoding Aug 06 '15 at 17:11

user1666620 · Answer 2 · 2015-08-06T14:24:08.543

2

Use a string literal:

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."

Also, use parameters in your sql to ensure it is inserted correctly, and to prevent sql injection.

edited Aug 06 '15 at 14:24

answered Aug 06 '15 at 14:20

user1666620

4,800
18
27

I can't use string literals as the strings are coming from a dropdown list as mentioned above in the post. They are passed in and assigned. – ForeverLearningAndCoding Aug 06 '15 at 14:23

score 1 · Accepted Answer · answered Aug 06 '15 at 14:39

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.

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

3 Answers3