Web API using command parameters is not returning any data

Question

The web API to query the oracle database which receives the array of strings as an input parameter. I am trying to use the command parameters to avoid the SqL injection, but the below code does not throw any error but does not give the result.

public class PDataController : ApiController
{
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {

        List<OracleParameter> prms = new List<OracleParameter>();
        string connStr = ConfigurationManager.ConnectionStrings["PDataConn"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            var inconditions = id.Distinct().ToArray();
            var srtcon = string.Join(",", inconditions);
            DataSet userDataset = new Dataset();
            var strQuer = @"SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,  
            STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,  
            Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, 
            STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  
            FROM STCD_PRIO_CATEGORY_DESCR 
            WHERE STCD_PRIO_CATEGORY_DESCR.STD_REF(";
            StringBuilder sb = new StringBuilder(strQuery);
            for(int x = 0; x < inconditions.Length; x++)
            {
                sb.Append(":p" + x + ",");
                OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2 );
                p.Value = inconditions[x];
                prms.Add(p);
            }
            if(sb.Length > 0) sb.Length--;
            strQuery = strQuery + sb.ToString() + ")";
            using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
            {
                 selectCommand.Parameters.AddRange(prms.ToArray());
                 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;
                }
            }

        }
    }
}

Below is what I am getting while debugging in commandText of selectCommand

"SELECT \r\n STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
 STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
 Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
 STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
 FROM \r\n 
 STCD_PRIO_CATEGORY_DESCR \r\n 
 WHERE \r\n 
 STCD_PRIO_CATEGORY_DESCR.STD_REF IN(SELECT \r\n 
 STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
 STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
 Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
 STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
 FROM \r\n 
 STCD_PRIO_CATEGORY_DESCR \r\n 
 WHERE \r\n 
 STCD_PRIO_CATEGORY_DESCR.STD_REF IN(:p0)"

Since I am giving currently

strQuery = strQuery+ sb.ToString() + ")";

The select is being repeated. But if I just give

 strQuery = sb.ToString() + ")";

Whereas the strQuery while debugging is

SELECT \r\n STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
FROM \r\n 
STCD_PRIO_CATEGORY_DESCR \r\n 
WHERE \r\n 
STCD_PRIO_CATEGORY_DESCR.STD_REF IN(:p0)

What I get in return is

{"data":[]}

Should I enclose p0 in '' because the input we receive is array of Strings.

But the same ID when i try in the SQL developer I am getting the records. any help is greatly appreciated.

what is your `id` parameter? how does the generated sql query look like before you exectue it? — shirbr510, Aug 04 '16 at 05:43
ID is the string we give while calling the API the generated query should be something like Select * from STCD where STDREF IN('a001','a002') if we call the API as http:// local host:80/API/PData?id='a001'&I'd='a002' — user4912134, Aug 04 '16 at 05:48
I know. I'm asking for an example of your GET request. for an instance: `http://example.com/api/somecontroller/getdetails?id=1&id=2&id=3& id=4` — shirbr510, Aug 04 '16 at 05:51
Yes it will be just 'http://localhost/api/PData?id='a001'&id='a002' ' this is my API controller public class PDataController : ApiController — user4912134, Aug 04 '16 at 06:14
and I assume you have tried getting the output from your `StringBuilder` and tested the value in an Oracle Client? maybe there is an issue with the formatting of your SQL or the appending of the string? — shirbr510, Aug 04 '16 at 06:24
This should be _strQuery = strQuery + sb.ToString() + ")";_ Just a typo? — Steve, Aug 04 '16 at 07:16
@steve No Steve I removed the strQuery because strQuery holds the entire query statement and even sb has the entire entire query again so I removed it. If I run with strQuery = strQuery + sb.ToString() + ")"; it give me an error like missing paranthesis in the adapter.Fill(selectResults) statement — user4912134, Aug 04 '16 at 09:35
You need to use a debugger then to see what is exactly the query text submitted in the adapter.Fill and verifiy if the list of parameters matches the parameters placeholders. By the way your mention of SQLite is confusing. We are talking about Oracle right? — Steve, Aug 04 '16 at 09:50
That was typo I tried in SQL Developer and it returns result — user4912134, Aug 04 '16 at 09:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120111/discussion-between-user4912134-and-steve). — user4912134, Aug 04 '16 at 11:33
@Steve the Dataset is empty. I have added the image of it. But with the same id I tried debugging I get results from SQLDeveloper — user4912134, Aug 04 '16 at 12:15

score 1 · Accepted Answer · answered Aug 04 '16 at 12:42

From the chat with the OP it turned out that the OP added single quotes around the array parameter ID. The values where received from a query string formatted in this way

http:// localhost:80/api/PData?id='JW217T_01'

and this was an attempt to pass a string as the parameter value.

However if you use a parameter and specify its datatype (NVarChar2) then the database engine knows enough about the value to do the correct quoting by itself, so the values for the parameters should not have single quotes around them.

Changing the format of the querystring to

http:// localhost:80/api/PData?id=JW217T_01

fixed the problem

Web API using command parameters is not returning any data

1 Answers1

Linked