MySQL filter by date

Question

I am building a query string like this.

string query = "SELECT * FROM " +  table + " where DATE(Date) > " + howFarBack.ToString("yyyy-MM-dd");

Hwowever, when it executes

while (dataReader.Read())

I am seeing Dates well before the howFarBack ????

    public List<OHLC> Select(string table, System.DateTime howFarBack)
    {
            string query = "SELECT * FROM " +  table + " where DATE(Date) > " + howFarBack.ToString("yyyy-MM-dd");

            //Create a list to store the result
            var list = new List<OHLC>();

            //Open connection
            if (OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                
                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    var ohlc = new OHLC();

                    ohlc.Date = (System.DateTime)dataReader[0];
                    ohlc.Open = Math.Round((double)dataReader[1], 2);

please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. it will most likely also fix your immediate problem. — Franz Gleichmann, Nov 03 '21 at 18:13
Also you should *not* cache the connection object, instead create and dispose after use. So `OpenConnection() == true` instead you want `using(var connection = new MySqlConnection...`. You also need `using` on `cmd` and `dataReader` — Charlieface, Nov 03 '21 at 21:46

score 4 · Accepted Answer · answered Nov 03 '21 at 18:32

When in doubt, try to debug by examining the resulting SQL query, not the C# code that formats the SQL query.

I would guess that your query lacks single-quote delimiters around the date literal. So it is ultimately a query like:

SELECT * FROM MyTable where DATE(Date) > 2021-11-02

But 2021-11-02 isn't a date, it's an arithmetic expression that evaluates to an integer: 2021 minus 11 minus 2 = 2008. This will certainly match a lot of dates you didn't intend it to.

You could solve this by ensuring that the right type of quotes are around your date literal (it's actually a string literal that is interpreted as a date when compared to a date).

SELECT * FROM MyTable where DATE(Date) > '2021-11-02'

But it's far better to use query parameters, as mentioned in the comment above.

SELECT * FROM MyTable where DATE(Date) > @howFarBack

Then you don't need quotes. In fact you must not use quotes around the parameter placeholder.

See Parameterized Query for MySQL with C# or many other references for using parameters in SQL statements in C#.

Also remember that parameters can only be used in place of a single literal value. You can't use parameters for table or column identifiers, or a list of values, or SQL keywords, etc.

MySQL filter by date

1 Answers1