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 work 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 = (reader.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(reader.GetValue(4)) : (DateTime?)null
                    });
                }
            return Ok(Test);
        }

CodePudding user response：

STOP! You have committed the crime of deliberately inviting a SQL Injection attack!

The first step here is to parameterize your query. By doing so the query will be sanitised from invalid inputs, but it will also help you to parse the incoming date correctly.

Forst we change the query to use a parameter for the filter:

string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date=@filterDate";

Then you need to pass the date to the SqlCommand as a parameter:

SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.Add("@filterDate", SqlDbType.Date);
sqlcomm.Parameters["@filterDate"].Value = yourDate;

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

CodePudding user response：

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://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-6.0

Here the example:

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