0

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

trx
  • 2,077
  • 9
  • 48
  • 97
  • 1
    Duplicate question, you can find the solution here : http://stackoverflow.com/questions/11862069/optional-parameters-in-asp-net-web-api – Turrican Nov 16 '16 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 Nov 16 '16 at 17:33
  • That is not the actual error, look at your stack trace. – Divyang Desai Nov 16 '16 at 17:43
  • Happening in Run time when debugging. I have added the screenshot – trx Nov 16 '16 at 17:48
  • Also you are vulnerable to Sql Injection attacks when you use string concatenation, you should be using parameters. – Igor Nov 16 '16 at 17:50
  • @Igor Should I put ; at the end? – trx Nov 16 '16 at 17:54
  • @trx - no, your sql statement was not well formed. You should be using parameters. See my answer below, this should fix it. Change your sql string to what I have and add those 2 parameters. – Igor Nov 16 '16 at 18:07
  • Seems like OP pointed out two different questions at once now, first pointed out the issue of `parameters dictionary contains a null entry for parameter 'SUBMITDATE'` and now the question is totally converted into [SQL Error: ORA-00933: SQL command not properly ended](http://stackoverflow.com/questions/8940471/sql-error-ora-00933-sql-command-not-properly-ended), where it seems like a duplicate question. – Divyang Desai Nov 16 '16 at 18:22
  • 1
    @trx - Did this solve your issue or do you have multiple issues like Div pointed out above? – Igor Nov 16 '16 at 18:26
  • 1
    @Igor yest using the parameters solved the issue. I have changed the Date in the input parameter not to be nullable. thanks. – trx Nov 16 '16 at 19:07

1 Answers1

1

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.

Igor
  • 60,821
  • 10
  • 100
  • 175