用于 Oracle 的 C# 参数化查询 - 认真&危险的错误! [英] C# parameterized queries for Oracle - serious & dangerous bug!
问题描述
这绝对是一个咆哮者.我不敢相信自己的眼睛,而且我无法相信在我之前没有人会发现它,如果它是 C# 中的真正错误,所以我将它发布给其他开发人员社区,告诉我我做错了什么.我敢肯定这个问题会让我说DOH!"并用我的手掌狠狠地拍打我的头 - 但无论如何……
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...
为了测试,我创建了一个表Test_1
,脚本如下:
For the sake of testing, I have created a table Test_1
, with script as follows:
CREATE TABLE TEST_1 (
COLUMN1 NUMBER(12) NOT NULL,
COLUMN2 VARCHAR2(20),
COLUMN3 NUMBER(12))
TABLESPACE USERS
STORAGE (
INITIAL 64K
MAXEXTENTS UNLIMITED
)
LOGGING;
现在我执行以下代码:
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();
哇!我收到一个 ORA-01722 错误 - 无效号码"!不过怎么了?Column1
是数字,值为 1,所以没问题;Column2
是一个字符串,而 Column3
是一个可以为空的列,所以应该不会造成任何麻烦...
Whoa! I get an ORA-01722 error - "invalid number"! What's wrong, though? Column1
is numeric, and has a value of 1, so that's fine; Column2
is a string, and Column3
is a nullable column, so that shouldn't cause any trouble...
现在坐下来看看这个……这里的问题是 Column3
和 Column2
按照它们添加到 OracleParameterCollection 的顺序转置代码>.切换它们,然后快速!它有效!
Now sit down for this one... the problem here is that Column3
and Column2
are transposed in the order in which they are added to the OracleParameterCollection
. Switch them around, and presto! It works!
当然,这将引导我进行下一个明显的实验...让我们更改用于添加参数的代码块,如下所示:
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!
我坐在这里完全惊呆了.我不敢相信我所看到的,我也不敢相信在我之前没有人发现过这种行为(除非我不知道如何正确使用 Google).
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).
这不仅仅是一种烦恼 - 这是非常危险的.如果我调换了相同数据类型的两列会发生什么?我什至不会出错 - 我会简单地将错误的数据插入到错误的列中,而这并不明智.
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?
推荐答案
这不是错误,而是在 Oracle ODP.Net 文档中明确提及.在 OracleCommand 类中,参数默认按位置绑定.如果要按名称绑定,请显式设置属性 cmd.BindByName = true;
.
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 文档.http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666
Reference to Oracle documentation. http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666
这篇关于用于 Oracle 的 C# 参数化查询 - 认真&危险的错误!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!