CODEWITHSUNDEEP
c#sql-server-cesql-injectiondynamic-sqlstring.format
Nish
Nish

Reputation: 19

How to pass columns as parameters in dynamic sql - C#, sql server compact

This question is an extension to another I asked Here

I have a win form 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.

//  Get Data
// selectedMPs: string csv, generated from the list of selected posts(checkboxes) from the UI, forming the col names in select
public static DataTable GetDataPostsCars(string selectedMPs, DateTime fromDateTime, DateTime toDateTime)
{
  DataTable dt;
  //string[] cols = selectedMPs.Split(','); //converts to array
  //object[] cols2 = cols;//gets as object array            
  //=== using cols or cols 2 in String.Format does not help

  // this WORKS, but as i am aware its prone to injections. so how can i validate the "selectedMPs" that those are columns from a list or dictionary or so on? i am not experienced with that.

  string sql = string.Format(
            "SELECT " + selectedMPs + " " +
            "FROM GdRateFixedPosts " +
            "WHERE MonitorDateTime BETWEEN '" + fromDateTime + "' AND '" + toDateTime +
  using (cmd = new SqlCeCommand(sql,conn))
  {
    cmd.CommandType = CommandType.Text;                //cmd.Parameters.Add("@toDateTime",DbType.DateTime);
    dt = ExecuteSelectCommand(cmd);
  }
  return dt;
}

this WORKS, but as i am aware its prone to injections. so how can i validate the "selectedMPs" that those are columns from a list or dictionary or so on? i am not experienced with that. I would really appreciate your help. Thanks in advance.

Upvotes: 0

Views: 777

Answers (1)

ErikEJ
ErikEJ

Reputation: 41819

This is the only possible approach, and there is no risk of injection with SQL Server Compact, as that database engine only executes a single statement per batch.

Upvotes: 1

Related Questions