Oracle 的 C# 参数化查询 - 严重且危险的错误！
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3876856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# parameterized queries for Oracle - serious & dangerous bug!
提问by Shaul Behr
This is an absolute howler. I cannot believe my own eyes, and I cannot believe nobody before me would have discovered this if it was a genuine bug in C#, so I'm putting it out for the rest of the developer community to tell me what I am doing wrong. I'm sure this question is going to involve me saying "DOH!" and smacking my head very hard with the palm of my hand - but here goes, anyway...
这是绝对的咆哮。我不敢相信自己的眼睛，而且我无法相信在我之前没有人会发现它，如果它是 C# 中的真正错误，所以我将它发布给其他开发人员社区，告诉我我做错了什么。我敢肯定这个问题会让我说“DOH！” 并用我的手掌狠狠地拍打我的头——但无论如何……
For the sake of testing, I have created a table Test_1, with script as follows:
为了测试，我创建了一个表Test_1，脚本如下：
CREATE TABLE TEST_1 (
COLUMN1 NUMBER(12) NOT NULL,
COLUMN2 VARCHAR2(20),
COLUMN3 NUMBER(12))
TABLESPACE USERS
STORAGE (
INITIAL 64K
MAXEXTENTS UNLIMITED
)
LOGGING;
Now I execute the following code:
现在我执行以下代码：
var conn = new OracleConnection("connectionblahblah");
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText =
"insert into Test_1(Column1, Column2, Column3) " +
"values(:Column1, :Column2, :Column3)";
var p = cmd.Parameters;
p.Add("Column1", 1);
p.Add("Column3", null);
p.Add("Column2", "record 1");
cmd.ExecuteNonQuery();
Whoa! I get an ORA-01722 error - "invalid number"! What's wrong, though? Column1is numeric, and has a value of 1, so that's fine; Column2is a string, and Column3is a nullable column, so that shouldn't cause any trouble...
哇！我收到一个 ORA-01722 错误 - “无效号码”！不过怎么了？ Column1是数字，值为 1，所以没问题；Column2是一个字符串，并且Column3是一个可以为空的列，所以应该不会造成任何麻烦......
Now sit down for this one... the problem here is that Column3and Column2are transposed in the order in which they are added to the OracleParameterCollection. Switch them around, and presto! It works!
现在坐下来看看这个……这里的问题是Column3和Column2是按照它们添加到OracleParameterCollection. 切换它们，然后快速！有用！
This, of course, leads me to the next obvious experiment... let's change that block of code for adding parameters like so:
当然，这将引导我进行下一个明显的实验……让我们更改用于添加参数的代码块，如下所示：
p.Add("Foo", 1);
p.Add("Bar", "record 1");
p.Add("hahahahahahaha", null);
You think that'll work? Well guess what - it does!
你认为这会奏效吗？好吧，猜猜是什么 - 确实如此！
I am sitting here absolutely stunned. I cannot believe what I am seeing, and I likewise cannot believe that nobody before me has discovered this behavior (unless I don't know how to use Google properly).
我坐在这里完全惊呆了。我无法相信我所看到的，而且我也无法相信在我之前没有人发现过这种行为（除非我不知道如何正确使用 Google）。
This is not just an annoyance - it is seriously dangerous. What would have happened if I'd transposed two columns of the same data type? I wouldn't have even got an error - I would have simply inserted the wrong data into the wrong columns, and been none the wiser.
这不仅仅是一种烦恼——这是非常危险的。如果我调换了相同数据类型的两列会发生什么？我什至不会出现错误 - 我只是将错误的数据插入到错误的列中，并且没有更明智的选择。
Does anyone have any ideas for a workaround - other than just being careful not to add parameters in the wrong order?
有没有人有任何解决方法的想法 - 除了小心不要以错误的顺序添加参数？
回答by softveda
This is not a bug but explicitly mentioned in Oracle ODP.Net documentation. In a OracleCommand class the parameters are bound by position as default. If you want to bind by name then set the property cmd.BindByName = true;explicitly.
这不是错误，而是在 Oracle ODP.Net 文档中明确提及。在 OracleCommand 类中，参数默认按位置绑定。如果要按名称绑定，则cmd.BindByName = true;显式设置属性。
Reference to Oracle documentation. http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666
参考 Oracle 文档。 http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666
回答by OMG Ponies
Is that a typo that you have column3 being added before column2?
您在第 2 列之前添加了第 3 列是错别字吗？
Because the colon syntax signifies a bind variable--name doesn't matter to BIND variables in PLSQL, they're populated in order of submission. Which would mean you'd be attempting to set column2 value as "record 1", which would explain the invalid number error...
因为冒号语法表示绑定变量——名称与 PLSQL 中的 BIND 变量无关，它们按提交顺序填充。这意味着您将尝试将 column2 值设置为“记录 1”，这将解释无效数字错误...
You currently have:
您目前拥有：
p.Add("Column1", 1);
p.Add("Column3", null);
p.Add("Column2", "record 1");
...see if this alteration fixes your issue:
...看看这个改动是否能解决你的问题：
p.Add("Column1", 1);
p.Add("Column2", "record 1");
p.Add("Column3", null);
Getting Named Parameters to Work?
让命名参数起作用？
I have to defer to someone with more C# experience to explain how to get named parameters working. But I'm glad we confirmed that the colon appears to be interpreting as an Oracle BIND variable.
我必须请有更多 C# 经验的人来解释如何使命名参数工作。但我很高兴我们确认冒号似乎被解释为 Oracle BIND 变量。
回答by Luke
p.Add(":Column1", 1);
p.Add(":Column2", "record 1");
p.Add(":Column3", null);
//NOTE i have added : to the parameter names to be recognised by oracle data client
//注意我已经添加：到oracle数据客户端要识别的参数名称
