将字符串参数传递给带引号的命令文本

Question

我试图将一个参数传递给查询，但使用引号作为字符串值。但我似乎能让它工作起来。我到底做错了什么。

SqlConnection conn = new SqlConnection(SERP_FT_connection);
        SqlCommand cmd = new SqlCommand("SELECT sp.* "
                                         + " FROM [serp_post] sp "
                                         + " LEFT JOIN [serp_m3_data] m3 ON m3.serp_post_id = sp.serp_post_id "
                                         + " WHERE sp.[serp_status_id]='CLEAR_DONE' AND sp.m3UpdateStatus <> '2' AND sp.process_type='POST' AND m3.EGTRCD = '40' AND m3.EPPYME = @paymentTerm ", conn);

        cmd.CommandType = CommandType.Text;
        conn.Open();

        SqlParameter param = new SqlParameter();
        param.ParameterName = "@paymentTerm";
        param.Value = paymentTerm; // when debugged here it shows as "CH1"
        cmd.Parameters.Add(param);

查询在调试时如下所示，

SELECT sp.*  FROM [serp_post] sp  LEFT JOIN [serp_m3_data] m3 ON m3.serp_post_id = sp.serp_post_id  WHERE sp.[serp_status_id]='CLEAR_DONE' AND sp.m3UpdateStatus <> '2' AND sp.process_type='POST' AND m3.EGTRCD = '40' AND m3.EPPYME = @paymentTerm

最后，查询应该看起来像这个带引号的值

SELECT sp.*  FROM [serp_post] sp  LEFT JOIN [serp_m3_data] m3 ON m3.serp_post_id = sp.serp_post_id  WHERE sp.[serp_status_id]='CLEAR_DONE' AND sp.m3UpdateStatus <> '2' AND sp.process_type='POST' AND m3.EGTRCD = '40' AND m3.EPPYME = 'CH1'

Answer 1

尝试在事件探查器中捕获参数化查询。正确的查询将如下所示

exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON;SELECT sp.*  FROM [serp_post] sp  LEFT JOIN [serp_m3_data] m3 ON m3.serp_post_id = sp.serp_post_id  WHERE sp.[serp_status_id]='CLEAR_DONE' AND sp.m3UpdateStatus <> '2' AND sp.process_type='POST' AND m3.EGTRCD = '40' AND m3.EPPYME = @paymentTerm',N'@paymentTerm varchar(10)',@paymentTerm='CH1'

正如Jon Skeet所说，SQL参数不会插入到查询中。

Answer 2

除了实际呈现的注释之外，参数在post期间被自动处理，并且在查询中不是文字，以防止sql注入。

至于另一个关于双引号的答案，我已经养成了用C#编写我的sql作为示例的习惯，以帮助防止意外使用双引号。

  SqlCommand cmd = new SqlCommand( "", conn);
  cmd.CommandText = 
@"SELECT 
      sp.*
   FROM 
      [serp_post] sp 
         LEFT JOIN [serp_m3_data] m3 
            ON m3.serp_post_id = sp.serp_post_id
   WHERE 
          sp.[serp_status_id]='CLEAR_DONE' 
      AND sp.m3UpdateStatus <> '2' 
      AND sp.process_type='POST' 
      AND m3.EGTRCD = '40' 
      AND m3.EPPYME = @paymentTerm ";

注意完全可读的查询，没有滚动或忘记下一行的闭合双引号+，等等...同样，这只是构建sql命令的风格问题。前导@表示整个文本，直到它结束另一个双引号。因为SQL忽略了语句中的enter键，所以它们仍然可以正常工作，并且提高了可读性。