SqlCommand参数与String.Format [英] SqlCommand Parameters vs. String.Format

查看:141
本文介绍了SqlCommand参数与String.Format的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在互联网上搜索,但是似乎找不到任何可以解释我的问题的信息(可能是因为我没有使用正确的搜索字符串),所以我在这里发帖希望有人可以帮助我。 (我的程序是使用Visual Studio 2010编写的C#)。

I've been searching on the internet, but I just can't seem to find anything that explain my question (could be that I'm not using the correct search string), so I'm posting here hoping that someone can help me with this. (My program is written is C# using Visual Studio 2010)

我注意到在C#中,有多种构造SQL命令的方法。

I notice that in C#, there are multiple ways of constructing a SQL command.

SqlConnection connection = GetAndOpenConnection(); //function containing connection string and open connection
SqlCommand command = connection.CreateCommand();

到目前为止,我没有任何问题。我遇到的问题是 CommandText 。我在代码中使用了几个不同的命令( SELECT / INSERT / UPDATE / 删除),但让我们以 SELECT 为例。

Up to this point, I have no questions. The problem I have is with CommandText. I am using several different commands in my code(SELECT/INSERT/UPDATE/DELETE), but lets take SELECT for example.

//Example 1:
command.CommandText = String.Format("SELECT * FROM myTable WHERE name = '{0}'", "bob");

//Example 2:
command.CommandText = "SELECT * FROM myTable WHERE name = @myName";
command.Parameters.Add(new SqlParameter("myName", "bob"));

以上两个示例之间有什么区别? (性能方面/结构方面等)

我要问的原因是因为在同一.cs文件中,当我使用示例中的方法时2,有时代码正常工作,有时却无法正常工作,然后我最终做出了与示例1一样的效果,每次都可以工作。

The reason I'm asking is because within the same .cs file, when I use the method in Example 2, sometime the code works properly and sometime it doesn't, then I eventually makes everything like in Example 1, which works every single time.

是否有显着收益/损失使用任何一种方法?

Is there a significant gain/loss using either method? Which is the more proper way of completing a task like this?

附加问题

好,所以我认为方法2是执行此操作的更合适方法。

Okay, so I see that method 2 is the more appropriate way to do this.

但是,如果我使用方法2,则会出现问题。

However, there's a problem if I use method 2.

我有一个循环遍历 List< string>名称。在循环内部,当我使用方法2并添加名称作为参数时,出现一个错误,指出该参数已经存在并且无法添加。

I have a loop that loop through a List<string> names. Inside the loop, when I use method 2 and add name as parameters, I got an error saying that the parameter already exist and cannot be added.

我该怎么办解决问题?

List<string> names = new List<string> {"adam", "bob", "john"};
foreach(string name in names)
{
    command.CommandText = "SELECT * FROM myTable WHERE name = @myName";
    command.Parameters.Add(new SqlParameter("myName", name));
    reader = command.ExecuteReader();

    while(reader.Read())
    {
        //loop through each cell and print on the Console
    }
}

另外,我知道人们提到在参数中应该为 @ myName 而不是 myName 。我记得有这个问题,因为我很困惑使用哪种方法,只能测试一下。 @ myName 不适用于我,但 myName 是,这就是正确的代码现在在仍然使用方法2的部分中。我使用的是.Net 4.0,不知道这是否会有所作为。

Also, I know that people mentioned that in the parameter it should be "@myName" instead of "myName". I remember having this issue as I was confused which way to use and had to just test it. "@myName" was not working for me but "myName" is, and this is how it's in the code right now in the parts where I still use method 2. I'm using .Net 4.0, don't know if this will make a difference.

推荐答案

存在用于防止SQL注入的参数。例如,考虑如果使用 string.Format 而不是 bob 发生了什么,该怎么办? TextBox1.Text 其中包含 1'; DROP TABLE myTable;'

Parameters exist to prevent SQL Injection. For example, consider what happens in case of string.Format if instead of bob there was TextBox1.Text which contained 1';DROP TABLE myTable;'.

SQL如果您对参数有完全的控制,则无法进行注入,例如在使用字符串文字作为参数的情况下。但是,您永远都不知道代码将来会如何变化,因此,根据经验,您应该始终坚持使用带有参数的更安全方法。

SQL Injection is not possible if you have complete control over the parameters, like in your case with string literal for parameter. However you never know how your code might change in future, so as a rule of thumb you should always stick to safer approach with parameters.

如果您遇到某些特殊问题第二种方法-搜索并在此处发布,很可能已经有解决方案。例如,在您的代码段中,实际参数名称为带有$符号的 @myName ,而这应该提供给 SqlParameter 构造函数。

If you are facing some particular problems with second approach - search and maybe post here, there is most likely a solution already. For example, in your code snippet actual parameter name is @myName with @ symbol, and that is what should be supplied to the SqlParameter constructor.

更新。在您的另一个问题中,问题恰恰在于参数命名-应该为 @ myName

Update. In your additional question problem is exactly in parameters naming - it should be @myName:

command.Parameters.Add(new SqlParameter("@myName", name));

也应在每次迭代中清除参数集合:

Also you should clear parameters collection on each iteration:

command.Parameters.Clear();

尽管最好在每次迭代中创建新命令以避免混乱-检查此线程以了解详细信息

Although it would be better to create new command on every iteration to avoid mess - check this thread for details.

这篇关于SqlCommand参数与String.Format的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆