CODEWITHSUNDEEP
c#asp.netasp.net-mvcasp.net-web-api
user4912134
user4912134

Reputation: 1043

Using Command Parameters to avoid SQL injection

We created the WebAPI for querying an Oracle database. The API receives the string array of ID's as the input parameters. Below is the API controller we are using, but it is suggested to use command parameters to avoid SQLite injection. Below is the code we are using 

public HttpResponseMessage Getdetails([FromUri] string[] id)
{
string connStr = ConfigurationManager.ConnectionStrings["ProDataConnection"].ConnectionString;
using (OracleConnection dbconn = new OracleConnection(connStr))
{
     var inconditions = id.Distinct().ToArray();
     var srtcon = string.Join(",", inconditions);
     DataSet userDataset = new DataSet();
     var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(" + srtcon + ")";
     using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
     {
          using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
         {
             DataTable selectResults = new DataTable();
             adapter.Fill(selectResults);
             var returnObject = new { data = selectResults };
             var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
             ContentDispositionHeaderValue contentDisposition = null;

             if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
            {
                 response.Content.Headers.ContentDisposition = contentDisposition;
            }

            return response;
        }
    }
}
}

I tried googling on this and found that 

  var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(@strcon)";
  using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
   {
    using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
         {        
 adapter.SelectCommand.Parameters.Add("@strcon",strcon);

Will I be giving directly the strcon variable where I join the array of strings. I am new C# and Asp.Net, any help is greatly appreciated. Thanks

Upvotes: 0

Views: 1334

Answers (1)

Steve
Steve

Reputation: 216293

As explained in my comment you cannot create an unique parameter for the IN clause with all the values required separated by a comma. This creates a single string value not a list of ID to search in your STD_REF field. Instead you need a more lengthy approach creating a distinct parameter for each value and preparing the IN clause correctly

 List<OracleParameter> prms = new List<OracleParameter>();
 var strQuery = @"SELECT * from STCD_PRIO_CATEGORY 
                  where STPR_STUDY.STD_REF IN(";

 // Create a list of parameters and prepare the placeholders for the IN     
 StringBuilder sb = new StringBuilder(strQuery);
 for(int x = 0; x < inconditions.Length; x++)
 {
    // Placeholder
    sb.Append(":p" + x + ",");

    // Parameter
    OracleParameter p = new OracleParameter(":p" + x, OracleType.Int32);
    p.Value = inconditions[x];
    prms.Add(p);
 }

 // Remove the last comma
 if(sb.Length > 0) sb.Length--;
 // Prepare the correct IN clause
 strQuery = sb.ToString() + ")";

 using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
 {
     // Add the whole set of parameters
     selectCommand.Parameters.AddRange(prms.ToArray());
     using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
     {        
         DataTable selectResults = new DataTable();
         adapter.Fill(selectResults);
         .....

Upvotes: 1

Related Questions