传递字符串参数时'S'附近的语法不正确 [英] Incorrect syntax near 'S' when passing string parameter

问题描述

在 C# 中，我有以下几行代码:

In c# I have the following lines of code:

 string mySqlStmt = "SELECT CASE WHEN (SELECT COUNT(*)  FROM tlGenericName WHERE ( UPPER(ltrim(rtrim(genericName_str))) = '" + this.cmbGenericName.Text.Trim().ToUpper() + "' ) ) > 0 THEN"
   + " ( SELECT top(1) isnull(genericNameCode_str, '') FROM tlGenericName "
       + " WHERE ( UPPER(ltrim(rtrim(genericName_str))) = '" + this.cmbGenericName.Text.Trim().ToUpper() + "' )  ) ELSE '' END";

然后我将 SqlCommand(在 c# 中)用于 executeScalar.但是，我收到以下错误:

Then I use an SqlCommand (in c#) for executeScalar. However, I get the following error:

Incorrect syntax near 'S'

我注意到某些记录会发生这种情况(参数值中断)；例如:

I have noticed that this occurs for some records (a break in the parameter value); for example:

'BANDAGE P.O.P.ROLL TYPE 1;200MMX2.7M 12's'

'BANDAGE P.O.P.ROLL TYPE 1;200MMX2.7M 12's'

如何在设计器(c#)中安全地传递Sql中的字符串参数?

How can I safely pass the string parameter in Sql in the designer (c#) ?

推荐答案

转义 单引号 类似于以下内容:

Escape the single quotes something like below:

this.cmbGenericName.Text.Trim().ToUpper().Replace("'", "''")

另外作为 Peter 在评论中提到的建议，避免使用内联 SQL 语句来避免 SQL 注入攻击 使用 SqlCommand 和它的 参数！

Also as a suggestion as mentioned by Peter in the comment avoid using inline SQL statements to avoid the SQL injection attacks by using the SqlCommand and its parameters!

这篇关于传递字符串参数时'S'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！