0

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.

Community
  • 1
  • 1
Nish
  • 19
  • 7

1 Answers1

1

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.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Hi Erik! could you elaborate with some reference resources? I have edited my original code with additional 2 params fromDateTime and ToDateTime. shouldn't these input params validated (by name, type etc in database) from C# code itself, if not completely unnecessary? – Nish Dec 15 '14 at 12:05
  • for those additional parameters, use parameterized sql. – ErikEJ Dec 15 '14 at 12:40