c#将字符串变量中的转义字符转换为SQL查询的字符串文字

本文关键字：字符串 SQL 查询 文字 转换 转义字符 变量 | 更新日期: 2023-09-27 18:06:45

我正在尝试将c#中包含转义字符如"'r'n"的字符串变量转换为字符串字面值，以便可以在SQL查询中使用。

// 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 + "')";

现在，每当我尝试使用这个SQL字符串进行搜索时，它都会转换转义字符并将查询弄乱，如下所示:

@"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.')"

所以我的问题是，我怎样才能让它工作，使它使用以下搜索:

@"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.')"

我已经找到并尝试使用这个代码，但它不工作:

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

它仍然转换转义字符。

任何帮助都会很感激。提前感谢!

c#将字符串变量中的转义字符转换为SQL查询的字符串文字

您不应该生成嵌入文字字符串的SQL语句，这是查询参数的作用。

使用字符串字面值:

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

另外，在sql中使用参数来确保正确插入，并防止sql注入。

您的问题不是由转义字符引起的。这些只对c#有意义。当将字符串连接到sql查询时，它们将只是常规的回车和换行。

真正的问题是由首先使用字符串连接引起的!您的数据中有一个撇号，一旦将其连接起来，就会弄乱最终的查询。

相反，使用参数化查询，这将不会是一个问题，你将避免sql注入漏洞!

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

注意sql查询中参数@EmailText的使用，以及如何将其添加到sqlCmd对象的Parameters集合中。

这种方法将消除查询中撇号的问题，更重要的是，消除sql注入漏洞。

相关文章：