PostgresException: 42601: syntax error at or near "00"

Question

I am working on ASP.NET CORE MVC project

I need to pass two date from frontend as shown: HERE

My view:

    <form method="post" asp-controller="HOME" asp-action="AllTransactionsList">
    <label> Start Date</label>
    <input type="date"  name="startDate"></br>
    <label> End Date</label>
    <input type="date"  name="endDate"></br>
    <button type="submit">ADD</button>
</form>

My controller Method:

        public IActionResult AllTransactionsList(DateTime startDate, DateTime endDate)
    {
        var dataset = new DataSet();
        using var connection = new NpgsqlConnection(connString);
        connection.Open();
        Console.WriteLine(startDate);
        var query = String.Format(@"SELECT accounts.account,accounts.type,DATE(transactions.date),transactions.amount,transactions.note FROM transactions FULL JOIN accounts ON transactions.accountid=accounts.accountid WHERE transactions.date BETWEEN {0} AND {1};", startDate, endDate);

        using (var command = new NpgsqlCommand(query, connection))
        {

            var adapter = new NpgsqlDataAdapter(command);
            adapter.Fill(dataset);
        }

        return View(dataset);

    }

But when executing the webapp I am getting "PostgresException: 42601: syntax error at or near "00"" error.

I know there is something wrong with passing two dates as parameter. Can anyone help me?

You must have forgotten the single quotes around the date literals. — Laurenz Albe, Dec 20 '21 at 16:10
@LaurenzAlbe coming from a web form, even with single quotes, that would lead to potential SQL injections — Bruno, Dec 20 '21 at 16:13
@Bruno Right, that's why you have to properly escape the values. — Laurenz Albe, Dec 20 '21 at 16:15
@LaurenzAlbe The problem with escaping the values is that it's often hard to get right as a user of those tools ([here is a similar example with PHP/MySQL](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)): it's generally better to pass parameters with parametrized queries when the client library supports it. — Bruno, Dec 20 '21 at 16:25
@Bruno Again, quite true. I only wanted to point out the cause of the error. — Laurenz Albe, Dec 20 '21 at 16:37

score 2 · Accepted Answer · answered Dec 20 '21 at 16:12

You shouldn't pass the parameters directly in the query string like this:

var query = String.Format(@"SELECT ... BETWEEN {0} AND {1};", startDate, endDate);

Firstly, incorporating the parameter within the query string like this is likely to produce a syntax error (as you've noticed), unless it's valid SQL itself.

Secondly, and more importantly, this will lead to a SQL Injection vulnerability (even if you were to put quotes around '{0}').

Instead, you should pass parameters with placeholders. Here is the example from the documentation:

using (var cmd = new NpgsqlCommand("INSERT INTO table (col1) VALUES (@p)", conn)) {
    cmd.Parameters.AddWithValue("p", "some_value");
    cmd.ExecuteNonQuery();
}

PostgresException: 42601: syntax error at or near "00"

1 Answers1