trx trx - 4 months ago 20
ASP.NET (C#) Question

passing array of parameters in ASP .NET Web API

Creating a Web API through which array of id is passed and returns the result from the OracleDB.

public class SampleController : ApiController
{
public string Getdetails([FromUri] int []id)
{
string inconditons = "";
for (int i = 0; i < id.Length; i++)
{
if (i == id.Length - 1)
{
inconditons = inconditons + id[i];
}
else
{
inconditons = inconditons + id[i] + ", ";
}
}
using (var dbConn = new OracleConnection("DATA SOURCE=X;PASSWORD=03JD;PERSIST SECURITY INFO=True;USER ID=IN"))
{
dbConn.Open();
var strQuery = @"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE as REC, REC_USER as RECUSER, DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER IN (" + inconditons + ");";
var queryResult = dbConn.Query<SamModel>(strQuery);
return JsonConvert.SerializeObject(queryResult);
}
}
}


And called the API as http://localhost:35432/api/Sample?id=1&id=83 it throws an error saying on var queryResult = dbConn.Query(strQuery);
enter image description here
But if I just give one parameter as below it works

var strQuery = @"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE as REC, REC_USER as RECUSER, DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER =" +id ;


Can anyone please suggest me what is the issue here as a single parameter works. Thanks

Answer

Check to make sure your don't have any stray characters in your query.

As stated in the comments

Use parameterized queries, otherwise you're vulnerable to errors like this and SQL Injection attacks.

So pass the id array into the parameterized query when executing.

Here is a refactored version of your example.

public class SampleController : ApiController {
    public string Getdetails([FromUri] int[] id) {
        var inconditions = id.Distinct().ToArray();
        using (var dbConn = new OracleConnection("DATA SOURCE=h;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T")) {
            dbConn.Open();
            var strQuery = "SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER  IN (:p)";
            var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });
            return JsonConvert.SerializeObject(queryResult);
        }
    }
}