如何在动态sql中传递列名作为参数 - C＃，sql server compact [英] How to pass column names as parameters in dynamic sql - C#, sql server compact

问题描述

大家好。很久以后我的第一个问题。请原谅我这样的基本问题，因为我正在学习/刷新它。

我有一个winform应用程序，里面有复选框控件。复选框的名称与表的列名匹配。我无法规范表格所涉及的大量数据的原因，已经收到了实时项目。所以一切都保持不变。我将选定的checbox名称作为csv col1，col2，col3，后来我将它连接到sql string。（没有SP作为sql compact 3.5 sdf dbase）。在我的DataAccess类的GetData（）方法中，我形成了sql字符串。但是为了避免sql注入，如何确保传递的列名被验证。

可以帮助如何扩展以下内容吗？



< pre lang =c＃> public static DataTable GetDataPostsCars（ string selectedMPs，DateTime fromDateTime，DateTime toDateTime）
{
var table = new DataTable（）;
#region debug block
// string [] cols = selectedMPs.Split（'，'）; //转换为数组
// object [] cols2 = cols; //获取为对象数组
// string sql = String.Format（SELECT {0} FROM GdRateFixedPosts ，cols）; //不是那样的吗？
#endregion
string sql = string .Format（
SELECT + selectedMPs + +
FROM GdRateFixedPosts +
WHERE MonitorDateTime BETWEEN' + fromDateTime + 'AND' + toDateTime + '）;

if （FkDbConnection.conn！= null &&& FkDbConnection。 conn.State == ConnectionState.Open）
{
使用（SqlCeCommand cmd = new SqlCeCommand（sql，FkDbConnection.conn））
{
cmd.CommandType = CommandType.Text;
// cmd.Parameters.Add（@ fromDateTime，DbType.DateTime）;
// cmd.Parameters.Add（@ toDateTime，DbType.DateTime）;
table = FkDbConnection.ExecuteSelectCommand（cmd）;
}
}
return 表;
}

解决方案

最好创建一个存储过程并通过你的列和日期条件到商店的程序。

这里我为你做了一个样品库程序。希望这对你有用。

   -    exec USP_GdRateFixedPosts'Part_Code，item_Name'，'2014-12-12 08 ：18：14.740'，'2014-12-12 08:18:14.740' 
 创建  PROCEDURE  [dbo]。[USP_GdRateFixedPosts] 
（
  @ MyColumns   AS   NVARCHAR （MAX）= ' '，
  @ fromDateTime   AS   DateTime ，
  @ ToDateTime   as   DateTime  
）
  AS  
  BEGIN  
  DECLARE   @ SQLquery   AS   NVARCHAR （MAX）
 
  set   @ SQLquery  = N '  SELECT' +  @ MyColumns  + N '  FROM GdRateFixedPosts 
 where MonitorDateTime BETWEEN' +  @ fromDateTime  + ' 和' +  @ ToDateTime  + ' '  
 
  exec  sp_executesql  @ SQLquery ; 
  END  

Hi all. My first question after a long time. excuse me for such basic questions cause i am learning/refreshing it.
I have a winform app which has checkbox controls in it. The names of the checkboxes matches column names of a table. I can not normalize the tables cause of huge data involved, already received for the live project. so everything stays as it is. I get the selected checbox names as a csv col1,col2,col3 which later i concatenate it to sql string.(no SPs as its a sql compact 3.5 sdf dbase). In my GetData() method of the DataAccess class i form the sql string. But to avoid sql injections how can ensure that the column names passed are validated.
Could any help how following can be extended?

public static DataTable GetDataPostsCars(string selectedMPs, DateTime fromDateTime, DateTime toDateTime)
{
   var table = new DataTable();
   #region debug block
   //string[] cols = selectedMPs.Split(','); //converts to array
   //object[] cols2 = cols;//gets as object array            
   //string sql = String.Format("SELECT {0} FROM GdRateFixedPosts", cols); // does not work like that?
   #endregion
   string sql = string.Format(
                "SELECT " + selectedMPs + " " +
                "FROM GdRateFixedPosts " +
                "WHERE MonitorDateTime BETWEEN '" + fromDateTime + "' AND '" + toDateTime + "'");

   if (FkDbConnection.conn != null && FkDbConnection.conn.State == ConnectionState.Open)
   {
      using (SqlCeCommand cmd = new SqlCeCommand(sql, FkDbConnection.conn))
      {
         cmd.CommandType = CommandType.Text;
         //cmd.Parameters.Add("@fromDateTime",DbType.DateTime);
         //cmd.Parameters.Add("@toDateTime",DbType.DateTime);
         table = FkDbConnection.ExecuteSelectCommand(cmd);
      }
   }
   return table;
 }

解决方案

Hi,
It will be good to create a Stored procedure and pass your Column and date condition to the store procedure.
here i have made a sample Store procedure for you.Hope this will help you.

-- exec USP_GdRateFixedPosts 'Item_Code,item_Name','2014-12-12 08:18:14.740','2014-12-12 08:18:14.740'
Create PROCEDURE [dbo].[USP_GdRateFixedPosts ]                                              
   (                                
          @MyColumns AS NVARCHAR(MAX)     = '' ,
          @fromDateTime    AS DateTime,
          @ToDateTime as DateTime
      )                                                        
AS                                                                
BEGIN                                                 
 DECLARE    @SQLquery  AS NVARCHAR(MAX)

set @SQLquery = N'SELECT ' + @MyColumns + N' FROM GdRateFixedPosts 
            where MonitorDateTime  BETWEEN ' + @fromDateTime + ' and  ' + @ToDateTime + ''
            
exec sp_executesql @SQLquery;
    END

这篇关于如何在动态sql中传递列名作为参数 - C＃，sql server compact的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！