Search code examples
c#mysqlsqldatetimeparameterization

MySQL Connector/NET's MySqlCommand not using Parameters

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 . 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#.

Solution

  • 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