Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I have developed a asmx project that gets data on input string. the Input string contains from and to date.
when I am trying to retrieve data from asmx, it comes blank but when I am running same query from logs into SQL developer (oracle) DB it is working.

Query called by asmx
DateTime DTFromDate = Convert.ToDateTime(FromDate);
                DateTime DTToDate = Convert.ToDateTime(ToDate);

strbldSelectDetails = (@"Select * from table_z where 
TRUNC(DT) >='" + DTFromDate.ToShortDateString() + "' and Trunc(DT) <= '" + DTToDate.ToShortDateString() + "'");


in logs the printed query is
Select * from table_z where TRUNC(DT) >='08-09-2021' and Trunc(DT) <= '10-09-2021'

printed query in logs gives output when executed on SQL Developer but the same output does not come in asmx

What I have tried:

I tried changing formats, data comes when same query is executed on SQL developer but via asmx it fails
Posted
Updated 14-Sep-21 21:43pm
v2
Comments
Member 15329613 14-Sep-21 16:39pm    
How do you think anyone can help you? We can't see any of the code and can't see anything that you are doing. What do you want us to do?
Wendelius 14-Sep-21 23:03pm    
Please post the relevant code related to the question
lmoelleb 15-Sep-21 3:12am    
Working with dates as strings is extremely error prone. I am not sure if it is the cause of your problem, but personally I would never try to troubleshoot code like this before I had changed it as follows: 1) All dates where passed as DateTime or DateTimeOffset parameters to the SQL query - never as strings, and never concatenated into the SQL statement. 2) The API only accepts and return dates in ISO 8601 format (or even a subset of the format, i.e. first I would disallow "local time" as you never know when that is).

1 solution

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Whilst in this particular instance you're probably safe, since the values have been parsed to DateTimes, concatenating values into your queries can and will leave you vulnerable to SQL Injection[^].

It will also pollute your DBMSs plan cache, since every query is different. Unless your server has an option to "optimise for ad-hoc workflows" or similar, you will end up with a cache full of plans for variants of this one query, rather than one cached plan for the parameterized version of the query.
C#
using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM table_z WHERE TRUNC(DT) >= @FromDate AND TRUNC(DT) <= @ToDate";
    command.Parameters.AddWithValue("@FromDate", DTFromDate);
    command.Parameters.AddWithValue("@ToDate", DTToDate);
    ...
}

Once you've got that working, you'll want to reconsider how you construct your query. Calling a function on a column in the WHERE clause will not be SARGable[^], so your query will never be able to use an index on the DT column.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900