using c# datetime in mysql update query

Question

I'm trying to run a query from C# to MySQL (version 5.5.27) using the mysql connector for .net from the MySQL website.

The ultimate goal of the .dll I'm developing is to keep track of the rows that I've read.

The query I use to read the database is:

string strSQL = "SELECT date,ask,bid,volume FROM gbpjpy where `read` = 0";

To read in the date, I have:

DateTime dateTime = mysqlReader.GetDateTime(0);

That works fine.

The table has 5 columns, the last column is called "read". Right after the select query and parsing is done, I execute the following query:

string sqlFormattedDate = dateTime.ToString("yyyy/MM/dd HH:mm:ss");
string query = "UPDATE gbpjpy SET `read` = 1 WHERE `date` = " + sqlFormattedDate;

However, when I check my log, I have the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01:20:08' at line 1.

The first date read in is 2012/08/30 01:20:08 (that's how it appears in the MySQL table, so that's where it gets the 01:20:08).

I've tried var sqlFormattedDate, changing the ToString overload to yyyy-MM-dd (using dashes and not forward slashes) and dateTime.ToString() all to no avail.

Why is MySQL not picking up the entire string?

Answer 1

I suppose you have to put the whole value for the date in quotes. If you actually concatenate your query, it would look like

UPDATE gbpjpy SET `read` = 1 WHERE `date` = yyyy/MM/dd HH:mm:ss

That equal sign will only take the value until the first space.

Instead, it should look like

UPDATE gbpjpy SET `read` = 1 WHERE `date` = 'yyyy/MM/dd HH:mm:ss'

This is the particular reason in this case, however, concatenating queries like this leads to a real possibility of SQL injection. As a rule of thumb, you shouldn't do it. You can use parameterized queries and there's probably an API of the .NET connector you are using to do that.

Answer 2

Putting the info in a parameter allows the code to format as it needs. Likely, your original issue may have stemmed from using slashes instead of dashes in your date format. I would assume that slashes can work, but most all of the documentation I've seen has dashes separating dates with MySqlDateTimes.

Answer 3

Basically you should avoid including values in your query directly.

No doubt you could put quotes around the value... but you shouldn't. Instead, you should use paramterized SQL, and put the value in the parameter. That way you don't an error-prone string conversion, you avoid SQL injection attacks (for string parameters), and you separate code from data.

(As an example of how subtly-broken this can be, your current code will use the "current culture"'s date and time separators - which may not be / and :. You could fix this by specifying CultureInfo.InvariantCulture... but it's best not to do the conversion at all.)

Look for documentation of a Parameters property on whatever Command type you're using (e.g. MySqlCommand.Parameters) which will hopefully give you examples. There may even be a tutorial section in the documentation for parameterized SQL. For example, this page may be what you're after.