Using Command Parameters to avoid SQL injection

Question

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

No your example of using parameters with IN is wrong. This will create a unique string of values that cannot be parsed in individual parameters — Steve, Aug 03 '16 at 21:56
This link shows a few options: [http://stackoverflow.com/questions/541466/oracleparameter-and-in-clause](http://stackoverflow.com/questions/541466/oracleparameter-and-in-clause) — Martin Staufcik, Aug 03 '16 at 22:04

Steve · Accepted Answer · 2016-08-04T12:15:54.590

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);
         .....

Hello Steve I am not able to find System.Data.OracleClient.OracleParameter.dll to add as reference. I am getting error in the OracleParameter statements — user4912134, Aug 04 '16 at 03:09
Steve I tried as you suggested but not getting the data back I have opened a new question on that http://stackoverflow.com/questions/38758637/web-api-using-command-parameters-is-not-returning-any-data here. I tried to debug it does not return any error but no data is returned. Your help is greatly appreciated — user4912134, Aug 04 '16 at 04:53

Using Command Parameters to avoid SQL injection

1 Answers1