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

Reputation: 2167

Dynamically changing the where clause according to input parameter

We are currently having a view in the Oracle DB.Currently I created the web API to accept one input parameter and using them in the particular field in the where clause. Below is the code for the service

 public HttpResponseMessage Getdetails(string JRS_NO)
 {

    List<OracleParameter> prms = new List<OracleParameter>();
    List<string> selectionStrings = new List<string>();
    string connStr = ConfigurationManager.ConnectionStrings["TGSDataConnection"].ConnectionString;
    using (OracleConnection dbconn = new OracleConnection(connStr))
    {
        DataSet userDataset = new DataSet();
        var strQuery = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =" + JRS_NO;

        var returnObject = new { data = new OracleDataTableJsonResponses(connStr, strQuery, prms.ToArray()) };
        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;
    }
}

The view has 16 columns in it.Like REQUEST_ID,JRS_NO,ROOM,SUBMITDATE,DOBetc I am checking if there is away we can get any of the fields as input parameters and we can change our where clause accordingly. Like if we calling the API with api/TGSSampData?JRS_NO=379&SUBMITDATE='01-JAN-2016' or api/TGSSampData?ROOM=111&DOB='22-AUG-2014'. I am not sure if we can dynamically be changing the where clause according to the input parameter

Upvotes: 0

Views: 206

Answers (1)

Sefe
Sefe

Reputation: 14017

Your query is a string, so of course you can modify it however you want, by parametrizing your text. You need to add a seconde parameter in your action method.

An entirely different question is, whether you should change the where clause. That can not be immediately answerde with a yes. You have to consider that you are receiving your criteria from the HTTP request string, which can contain anything. Your example does already this, which is extremely dangerous:

var strQuery = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =" + JRS_NO;

You are opening the floodgates for an SQL injection. You are not validating your JRS_NO and appending it directly to your query string. This is a huge security risk. If you now also compose the string using a dynamic column name, you multiply the possibilities for an attacker.

So the answer is: You can make your code more dynamic, but you should not compose the query string like you do. Use parameters in your query, which protects you from SQL injection there:

"SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO = @Value"

You already have prms to add your OracleParameter to.

As far as making your fields dynamic, create a list of valid columns on server side and validate your input against it. Only if you are sure that the value is legitimate, use it to compose the query.

Upvotes: 1

Related Questions