MySQL Connector/NET's MySqlCommand not using Parameters

Question

So I'm having some issues with the MySQL connector (specifically for .NET). Normally, I'd just not use parameterization -- although I realize that doing so leaves my program vulnerable to SQL injection.

Unfortunately, however, I can't just do that; I need to store an array of bytes (those which are derived from reading an image file on the client side of this program) into a MySQL database. Obviously, putting the byte array into the query string isn't going to work; that'll just put "System.Byte[]" into the array.

I've used parameterized queries before -- but only in java. The following is my code, where packet is an object I've guaranteed will return correct data in all circumstances (for testing purposes, I've even added a driver class which has all hard-coded data) and the Server singleton's connection is guaranteed to be open and valid.

MySqlCommand cmd = new MySqlCommand("insert into `screenshots` (`playerId`, `serverId`, `data`, `uploadDate`, `uploadingUserId`) values(?playerId, ?serverId, \"?attachmentData\", \"?dateTime\", ?userId,)", Server.getSingleton().getDbManager().getConnection());
cmd.Parameters.AddWithValue("?playerId", packet.getPlayerId()); //string of an integer
cmd.Parameters.AddWithValue("?serverId", packet.getServerId()); //string of an integer
cmd.Parameters.AddWithValue("?attachmentData", packet.getAttachmentData()); //byte[]
cmd.Parameters.AddWithValue("?dateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("?userId", packet.getUserId()); //string of an integer
cmd.executeNonQuery();

The issue I'm having is that the MySql Connector/NET seems to never replace the parameters with their values. I've tried instantiating a new MySqlParameter object, setting its Value, then calling cmd.Parameters.Add(...), but that presents the same issue.

I took a look at the MySQL documentation, and it says to use MySqlCommmand#Parameters.AddWithValue(string, object), and there are no other steps that I'm missing.

Whenever I attach Visual Studio's debugger to the process, it is visible that the parameters have all been added to the Parameters list property; however, when the query executes, the Just-in-Time Debugger halts execution and highlights the line with cmd.executeNonQuery() on it, saying that '?dateTime' is not a valid value for column 'dateTime' (where the column dateTime is of SQL type DateTime) or something to that extent.

This obviously means that the parameters are not being replaced with their values. ?dateTime isn't a valid value for a DateTime field because it has to be in format of yyyy-MM-dd HH:mm:ss, so an exception is thrown by the Connector. But what am I doing wrong?

Thanks in advance for any help -- I'm used to doing database-related things in Java (along with socket logic and the like), but this is a project for school which requires it be done in C#.

Tieson T. · Accepted Answer · 2014-05-16 04:25:44Z

3

Those don't look like valid MySQL parameters; if you want named parameters, use @yournamehere, like so:

MySqlCommand cmd = new MySqlCommand("insert into `screenshots` (`playerId`, `serverId`, `data`, `uploadDate`, `uploadingUserId`) values(@playerId, @serverId, @attachmentData, @dateTime, @userId)", Server.getSingleton().getDbManager().getConnection());
cmd.Parameters.AddWithValue("@playerId", packet.getPlayerId());

You also shouldn't quote a parameter; ADO.NET will do that as necessary, based off the target column's datatype.

Source: http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html

edited May 16, 2014 at 4:25

answered May 16, 2014 at 4:13

Tieson T.

21.2k6 gold badges83 silver badges99 bronze badges

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

4 Comments

Dan Jesensky Over a year ago

Per your reference of MySQL's reference, it seems it's out-of-date -- I actually read that page myself; however, I also came across a version of MySQL Connector/NET's release notes for some version and it stated that the @ had been changed to ? to support users (for example, user@localhost would be invalid because it would attempt to find a parameter). There is, of course, a way to change parameters back to @ by using a connection string option. I will, however, look into the quoting parameters.

Dan Jesensky Over a year ago

Ugh, I feel so stupid for not trying to remove the quotes -- I'd assumed C# wouldn't be so nice to me. Removing them worked, but the parameters are escaped with ?. Thanks!

Tieson T. Over a year ago

@DanJesensky Hmm. The docs say "This option was deprecated in Connector/Net 5.2.2. All code should now be written using the '@' symbol as the parameter marker." - what version of the connector are you using?

Dan Jesensky Over a year ago

I'm not sure where I read it anymore, but I remember seeing a bunch of questions on StackOverflow using ? instead of @ too. I'll edit my code to use @ instead. Thanks again.

Collectives™ on Stack Overflow

MySQL Connector/NET's MySqlCommand not using Parameters

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related