CODEWITHSUNDEEP
c#asp.netasp.net-mvcoracle-databaseasp.net-web-api
trx
trx

Reputation: 2167

Web API with input parameters to query Oracle DB

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.

Upvotes: 0

Views: 696

Answers (1)

Richard
Richard

Reputation: 143

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.

Upvotes: 1

Related Questions