Pass datetime parameter in Web API?

Question

I want to ask how to pass datetime parameter in Web API, how to pass datetime parameter in optional date. I want to search in the URL can be optional date without time such as:

localhost:IP/api/values?date=2020-01-01

Expected results:

<ArrayOfTest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<Test>
<UserId>341</UserId>
<Name>Emily</Name>
<Mobile>386754298</Mobile>
<Age>24</Age>
<Date>2021-11-06T16:04:00</Date>
</Test>
<Test>
<UserId>2555</UserId>
<Name>Peter</Name>
<Mobile>48295729</Mobile>
<Age>45</Age>
<Date>2020-10-12T20:35:00</Date>
</Test>

It can found out date after 2020-01-01. some value are null from SQL Server database, so I used dbnull to make sure the code works and I didn't use Entity Framework to connect the database, I have try to just pass datetime parameter in get method and it is not work as well. Is it possible to pass datatime parameter like this way?

Class code:

    public class TestClass
    {

        public string UserId { get; set; }

        public string Name { get; set; }

        public string Mobile { get; set; }

        public int Age { get; set; }

        public DateTime? Date { get; set; }
    }

controller code:

        public IHttpActionResult Get(DateTime date)
        {
            List<UserClass> Test = new List<UserClass>();
            string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date="+date;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
            SqlDataReader reader = sqlcomm.ExecuteReader();
            while (reader.Read())
                {
                    Test.Add(new UserClass()
                    {
                        UserId = reader.GetValue.ToString(0),
                        Name = reader.GetValue.ToString(1),
                        Mobile = reader.GetValue.ToString(2),
                        Access = Convert.ToInt32(reader.GetValue(3)),
                        Date = Convert.ToDateTime(reader.GetValue(4))
                    });
                }
            return Ok(Test);
        }

what version of SQL Server are you using? For instance in 2022 (v16) there is a new DATETRUNC function that helps for this very query — Chris Schaller
– Chris Schaller, Commented Aug 8, 2022 at 3:21
@Dai no it is not, it will still result in an INDEX SCAN if you applied it to the indexed value, instead you would apply DATETRUNC to the comparison value. Using BETWEEN or >= ... AND ... < against a Date/Time/2/Offset column will use Index Search Arguments (INDEX SEEK) at the cost of 3 reads per row. (still better than 2 reads of INDEX SCAN) The end result will be more standard and efficient than the DATEADD(DATEDIFF()) style logic we used in the past to truncate dates. But it wouldn't surprise me if they optimise this in the future to improve Time-Series queries. — Chris Schaller
– Chris Schaller, Commented Aug 9, 2022 at 6:51

Chris Schaller · Accepted Answer · 2022-08-09 07:02:46Z

When using string concatenation to build your query, it is recommended that you use ISO date format for dates:

In C# we can use the u format specifier to obtain an ISO date for SQL, but it appends the value with a Z, the following is a simple way to use this in strings:

date.ToString("u").TrimEnd('Z');

That is equivalent to:

date.ToString("yyyy-MM-dd HH:mm:ss");

But now we have a problem, does the incoming date parameter represent a whole day, or a specific point in time (for the purposes of this query)? Your request is that we want to specify the Date only, without time, for that we can use this format:

date.ToString("yyyy-MM-dd")

Now we have a new problem, in SQL if your data contains time values, then you need to truncate the data to just the date component, to pickup all the values for the provided date.

So now your query looks like this:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE Cast([Date] as Date) = '"+date.ToString("yyyy-MM-dd")+"'";

Using a function in the where clause makes this a non-SARGable query, which is hard for the database to optimise, you will generally get better performance by using a BETWEEN and passing in a value for the date and the date of the next day:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE [Date] BETWEEN '"+date.ToString("yyyy-MM-dd")+"' AND '"+date.AddDays(1).ToString("yyyy-MM-dd")+"'";

SARGable functions in SQL Server
SARGable is an adjective in SQL that means that an item can be found using an index (assuming one exists). Understanding SARGability can really impact your ability to have well-performing queries. Incidentally – SARGable is short for Search ARGument Able.

But now this query is a bit of a mess, instead we should use parameters to pass through the value, this way we do not need to be aware of ISO dates and formats, the ADO.Net runtime will deal with this for us.

Parameterizing queries offers other benefits, like SQL Injection prevention through input sanitization and other processing optimizations

Lets change the query to use parameters:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE Date BETWEEN @fromDate AND @toDate";

Then you need to pass the dates to the SqlCommand as a parameter,

notice here that we are using the .ToDate() method on the DateTime object to trim the time portion that the user might have provided.

SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.Add("@fromDate ", SqlDbType.Date);
sqlcomm.Parameters["@fromDate "].Value = date.Date;
sqlcomm.Parameters.Add("@toDate ", SqlDbType.Date);
sqlcomm.Parameters["@toDate "].Value = date.AddDays(1).Date;

This is a good post for comparing string concatenated SQL with parameterized queries: C#: SQL Injection Protection Using Parameterized Queries

UPDATE See this fiddle for a live demo: https://dotnetfiddle.net/8zLLrR

Because your controller is pre-sanitizing the input and constraining it to a DateTime typed value, SQL Injection is not likely to be an issue in this specific example, however the string-concatenation approach to build your query is generally a red flag in Web API as this is where your code is most vulnerable to external inputs.

SQL Server has an optimization where it tries to re-use execution plans when queries are re-executed. You will notice in SSMS when you run a query the first time, it is almost always slower than the second time if you re-execute the same query. That is because it has an internal table of stored execution plans and the SQL that you submit is the main key to this index.

When the date filter changes, if you use string concatenation this results in a different SQL so it is harder for the database engine to lookup an existing query execution plan, and in many cases it will generate a new one. When we use parameters, the actual SQL query is the same each time, so the optimizer can simply lookup any previously saved execution plans.

Later versions of SQL Server have additional optimisations to automatically parameterise query expressions for the execution plan index, but it's not 100% and not an excuse to be lazy developers ;)

I had to re-write this post, SQL Injection is not a real issue here, but the date filtering and string formatting are the main issues to focus on.
Sorry about that, missed that when I switched languages... It's just DateTime.Date (so just .Date. I've updated the post and included a fiddle: dotnetfiddle.net/8zLLrR

Chris Schaller · Accepted Answer · 2022-08-08 04:09:07Z

0

You should not add a string like this

SELECT UserID, Name, Mobile, Age, Date From tbluser where Date="+date+"

This may cause SQL injection. Instead, follow this documentation using SQL parameters with type Date https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-6.0

Here is the example:

        string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date= @YourDate";
        SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
        sqlcomm.Parameters.Add("@YourDate", SqlDbType.Date);
        sqlcomm.Parameters["@YourDate"].Value = date;

edited Aug 8, 2022 at 4:09

Chris Schaller

17.1k3 gold badges57 silver badges101 bronze badges

answered Aug 8, 2022 at 2:52

Mai QD

94 bronze badges

5 Comments

Chris Schaller Over a year ago

You beat me to it ;) to help new devs, please use the same names for parameters that were used in the original post, that will help them to understand how your solution specifically applies to them.

Mai QD Over a year ago

Thank you for your advice :) I edited my comment using the same names as his parameters.

user19613128 Over a year ago

so my string will get sql injection if i run it, is it right?

Mai QD Over a year ago

Yes, when you concat string like that. It will be easy to have a SQL injection attack. you can read more about SQL Injection in here w3schools.com/sql/sql_injection.asp

Chris Schaller Over a year ago

The issue with this solution is that it does not address the requirement to filter for the date ignoring time.

Collectives™ on Stack Overflow

Pass datetime parameter in Web API?

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related