Web API to get input parameters to query Oracle Database

Question

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."}

Duplicate question, you can find the solution here : stackoverflow.com/questions/11862069/… — Turrican
– Turrican, Commented Nov 16, 2016 at 17:27
@Div when I try /api/TGSSampleDatas?Room=654&SUBMITDATE=01-Jan-16 error saying An exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll but was not handled in user code — trx
– trx, Commented Nov 16, 2016 at 17:33
@trx - Did this solve your issue or do you have multiple issues like Div pointed out above? — Igor
– Igor, Commented Nov 16, 2016 at 18:26
@Igor yest using the parameters solved the issue. I have changed the Date in the input parameter not to be nullable. thanks. — trx
– trx, Commented Nov 16, 2016 at 19:07

Igor · Accepted Answer · 2016-11-16 18:26:25Z

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.

Collectives™ on Stack Overflow

Web API to get input parameters to query Oracle Database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related