CODEWITHSUNDEEP
c#asp.netoracle-databaseasp.net-web-api
trx
trx

Reputation: 2167

Web API to get input parameters to query Oracle Database

I am creating the Web API with four input parameters. The input parameters are going to be used in the where clause of the Select statement.The fields in Oracle are ROOM (Varchar),SUBMIT_DATE(Date)(eg:01-JAN-16). The URL should be something like `/api/TGSSampleDatas?Room=654&SUBMITDATE='01-Jan-16'. So in C# I am creating the Controller with the Get action like

public class TGSSampleDatasController : ApiController
{
    [HttpGet]
    public HttpResponseMessage Getdetails(string ROOM,DateTime ? SUBMITDATE = null)
        {
            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 ROOM = " + ROOM +"and SUBMIT_DATE =" +"'"+SUBMITDATE+"'";
           var returnObject = new { data = new OracleDataTableJsonResponse(connStr, strQuery, prms.ToArray()) };
            var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
            ContentDispositionHeaderValue contentDisposition = null;
            if (ContentDispositionHeaderValue.TryParse("inline; filename=TGSData.json", out contentDisposition))
            {
                response.Content.Headers.ContentDisposition = contentDisposition;
            }
            return response;

Getting the below error in the fiddler {"Message":"The request is invalid.","MessageDetail":"The parameters dictionary contains a null entry for parameter 'SUBMITDATE' of non-nullable type 'System.DateTime' for method 'System.Net.Http.HttpResponseMessage Getdetails(System.String, System.DateTime)' in 'TGSSampleData.Controllers.TGSSampleDatasController'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter."} enter image description here

Upvotes: 0

Views: 1083

Answers (1)

Igor
Igor

Reputation: 62258

Your Sql statement is wrong.

"SELECT * from LIMS_SAMPLE_RESULTS_VW where ROOM = " + ROOM +"and SUBMIT_DATE =" +"'"+SUBMITDATE+"'";
//--------------------------------no ticks-^----^ -no space--^
  • Room is a string so if you were to use string concatination (BUT DON'T) you should enclose it with ', otherwise it becomes a part of the statement.
  • Also there is no space between ROOM and and.

The real fix is to use parameters for both ROOM and SUBMITDATE. Doing this will prevent issues like that from happening to begin with.

I am guessing on the parameter types, you might have to correct them.

List<OracleParameter> prms = new List<OracleParameter>();
prms.Add(new OracleParameter("ROOM", OracleDbType.Varchar2, ROOM, ParameterDirection.Input));
prms.Add(new OracleParameter("SUBMITDATE", OracleDbType.Date, SUBMITDATE ?? System.DBNull.Value, ParameterDirection.Input));
// note that because you are using a nullable type as input you should pass in DBNull.Value as the value if the value is null in your c# code.

var strQuery = "SELECT * from LIMS_SAMPLE_RESULTS_VW where ROOM = :ROOM and SUBMIT_DATE = :SUBMITDATE";

String concatenation like you are doing makes your system vulnerable to sql injection attacks and adds issues to your sql code. The latter can be illustrated by passing in a value that contains a ' mark. Do it again and you could end the sql statement and add another one at the end.

Upvotes: 1

Related Questions