passing array of parameters in ASP .NET Web API

Question

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

Press on the View Details and expand Internal Exception property for more accurate information. — Orel Eraki
– Orel Eraki, Commented Jul 22, 2016 at 15:06
Do not use concatenated strings to perform your queries. Use parameterized queries, otherwise you're vulnerable to errors like this and SQL Injection attacks. — mason
– mason, Commented Jul 22, 2016 at 15:14
have you tried by removing ; (semicolon) from the end of your query? — Mohammed Dawood Ansari
– Mohammed Dawood Ansari, Commented Jul 22, 2016 at 15:23

Nkosi · Accepted Answer · 2016-07-22 17:12:15Z

1

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

edited Jul 22, 2016 at 17:12

answered Jul 22, 2016 at 15:44

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

trx Over a year ago

Thanks NKosi, The issue is it says inconditions is not in current context in var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });

Nkosi Over a year ago

@trx Simple typo. fixed

trx Over a year ago

Still getting the same error {"ORA-00936: missing expression"} in var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });

trx Over a year ago

I tried to add the breakboint and it doesnot return any result query. When use the same query in DB it returns the records

Nkosi Over a year ago

@trx it Seems to be something with the query statement. checked and I was using the wrong character @ instead of :. See if that fixes it.

|

marc_s · Accepted Answer · 2016-08-02 11:22:15Z

Your code looks fine to me. It might fail if your id array parameter is empty (but it will be a different error than what you see now). Put a breakpoint in your code and inspect the value of that.

Also for converting your array to string, You may use the String.Join method.

var ids = String.Join(",",id);

This will give the result like "1,3,5", assuming your int array has 3 items ,1,3 and 5

Now you can use this string variable in your query. Also you may consider passing this data as a parameter.

 var q= " ...  where REC_USER IN (@ids);" //Please fill the  missing part of query
 var result = con.Query<SomeModel>(q,new { ids });

Collectives™ on Stack Overflow

passing array of parameters in ASP .NET Web API

2 Answers 2

8 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Related