MySQL Connector/NET的MySqlCommand不使用参数 [英] MySQL Connector/NET's MySqlCommand not using Parameters
问题描述
因此，我在使用MySQL连接器(特别是.NET)时遇到了一些问题.通常，我只是不使用参数化-尽管我意识到这样做会使我的程序容易受到SQL注入的攻击.</p>
但是，不幸的是，我不能这样做.我需要将字节数组(这些字节数组是从读取此程序的客户端上的图像文件派生的)存储到MySQL数据库中.显然，将字节数组放入查询字符串中是行不通的.只需将"System.Byte[]"放入数组中即可.
我之前使用过参数化查询-但仅在 java .以下是我的代码，其中packet是我保证在所有情况下都会返回正确数据的对象(出于测试目的，我什至添加了一个具有所有硬编码数据的驱动程序类)，而Server单例的连接被保证是开放和有效的.
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();
我遇到的问题是MySql Connector/NET似乎从未将参数替换为其值.我尝试实例化一个new MySqlParameter对象，设置其Value，然后调用cmd.Parameters.Add(...)，但这存在相同的问题.
我看了一下MySQL文档，它说要使用MySqlCommmand#Parameters.AddWithValue(string, object)，而且我没有其他步骤.
每当我将Visual Studio的调试器附加到该进程时，就可以看到所有参数均已添加到Parameters列表属性中.但是，执行查询时，即时调试器会暂停执行并突出显示带有cmd.executeNonQuery()的行，并说'?dateTime' is not a valid value for column 'dateTime'(其中列dateTime属于SQL类型DateTime)或这样的程度.
这显然意味着参数不会被其值替换. ?dateTime对于DateTime字段不是有效值，因为它必须采用yyyy-MM-dd HH:mm:ss格式，因此连接器会引发异常.但是我在做什么错了?
在此先感谢您的帮助-我已经习惯于用Java进行数据库相关的工作(以及套接字逻辑等)，但这是一个学校项目，需要使用C#完成.
那些看起来不像有效的MySQL参数；如果要命名参数，请使用@yournamehere，如下所示:
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());
您也不应该引用参数； ADO.NET将根据目标列的数据类型根据需要执行此操作.
来源: http://dev. mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html
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#.
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
这篇关于MySQL Connector/NET的MySqlCommand不使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
