2

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?

Improve this question
1
  • Try adding quotes around the date when you append it to the query. Commented Aug 30, 2012 at 10:48

3 Answers 3

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the idea. Went searching for parameters, found this little gem of a page link. Modified my code to: string query = "UPDATE gbpjpy SET read = 1 WHERE date = @Date"; and added mysqlUpdateCmd.Parameters.Add("@Date", MySqlDbType.DateTime).Value = dateTime; and, hey presto. Thanks again for the idea!
0

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.

Improve this answer

3 Comments

Funny enough, the link I mentioned in the above comment suggested a similar fix, although it did explain that the main disadvantage is that you're not really building a SQL command, you're making a string, and all the variables that you're adding (like date in this example) have to be converted to strings, which has its own set of problems. About the SQL injection, I'm running this database on my lan, it's not on a website or exposed to the outside world. Can I assume then it's something I don't have to be too concerned about?
How well do you trust your users? What if at some point there are more users than you thought originally and they want to do harm? Those best practices work, because software requirements and usage tends to change... a lot. In your place, I'd avoid concatenating strings.
Well, this is actually just a "one-man-show" project. It's a personal project. I'm the only user. Eventually, I'll scale up to multiple computers to the database, but even then, it'll just be me. I actually ended up implementing the Parameters.Add. Thanks for the comment, though.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.