Web API with input parameters to query Oracle DB

Question

We created the Web API for querying the Oracle DB returning the result in the JSON in the below format.So the API will be getting the array of input parameters

Currently I am using the below URL for querying the DB as

https://bhbl.abc.org/api/Sample?id='BL001'&id='TM002'

So the query internally will be converted like

SELECT * 
FROM STCD_PRIO_CATEGORY 
WHERE STPR_STUDY.STD_REF IN ("BL001,TM002")

Below is the code we are using

public class SampleController : ApiController
{
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {
        using (OracleConnection dbconn = new OracleConnection("DATA SOURCE=J;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T"))
        {
            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 + ")";

            OracleCommand selectCommand = new OracleCommand(strQuery, dbconn);
            OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);

            DataTable selectResults = new DataTable();
            adapter.Fill(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 am wondering if we can query the entire record using the same API (i.e, without skipping the where STPR_STUDY.STD_REF IN("BL001,TM002")) by without passing anything in the URL like

 https://bhbl.abc.org/api/Sample

I tried this but it keeps loading without showing any error. I am not sure if what I am doing is right.

Richard · Accepted Answer · 2016-08-02 21:53:08Z

1

First, you could just check the array of ids to see if it is empty. If it is empty then don't append your where clause otherwise do.

More importantly, you shouldn't be using parameters from the url to directly construct your sql statements. You're creating a nice candidate for sql injection attacks. You should be doing something to sanitize your inputs.

answered Aug 2, 2016 at 21:53

Richard

1431 silver badge6 bronze badges

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

4 Comments

trx Over a year ago

If I give Array of IDs in the URL its being loading forever

Richard Over a year ago

Have you successfully executed your db query in Sql Developer or SQL Plus with some sample ids? The query itself looks wrong.

trx Over a year ago

I was able to execute the query in SQL Query but the no.of records returned while executing the above both ids as SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN('BL001','TM002') is 313,568

Richard Over a year ago

That's why loading takes forever. I'm guessing that isn't what you want. Shouldn't your query be something like SELECT * from STCD_PRIO_CATEGORY where STD_REF IN('BL001','TM002')? Your current where clause doesn't seem to limit the number of rows selected from the STCD_PRIO_CATEGORY. Alternately, you may want to join STCD_PRIO_CATEGORY and STD_REF on some column. I don't know the structure of your tables. You seem to have some query problems to sort out.

Collectives™ on Stack Overflow

Web API with input parameters to query Oracle DB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related