向SQL传递参数是什么，为什么需要它?

Beginner here:

初学者在这里:

In this answer to my question of how to insert data into SQL Server he mentioned passing parameters instead of string concatenation like I currently have.

在回答我关于如何将数据插入SQL Server的问题时，他提到了传递参数，而不是像我现在这样的字符串连接。

Is this really necessary for security? If so, what exactly is passing parameters? When i google it I get a lot about stored procedures. Is that what I want, I do not know about stored procedures....yet.

这对安全真的有必要吗?如果是，那么传递参数到底是什么?当我谷歌时，我得到了很多关于存储过程的信息。这就是我想要的,我不知道存储过程....呢。

If you can point me in the right direction, I would appreciate that.

如果你能给我指出正确的方向，我会很感激的。

Thanks.

谢谢。

EDIT:

编辑:

Ok, here is what I got. It seems to update the database correctly and eventually I will change the hard coded ints to inputs from a label. Please confirm if how I did this is not vulnerable to any sql injection or hacks.

这是我得到的。它似乎正确地更新了数据库，最终我将把硬编码的ints更改为来自标签的输入。请确认我是如何做到这一点的，不容易受到任何sql注入或黑客攻击。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;



public partial class Stats : System.Web.UI.Page
{

    public SqlDataReader DataReader;
    public SqlCommand Command;
    string queryString = ("INSERT INTO UserData (UserProfileID, ConfidenceLevel, LoveLevel, HappinessLevel) VALUES (@UID, @CL, @LL, @HL);");
    //string queryString = ("INSERT INTO UserData (UserProfileID, ConfidenceLevel, LoveLevel, HappinessLevel) VALUES ('a051fc1b-4f51-485b-a07d-0f378528974e', 2, 2, 2);"); 

    protected void Page_Load(object sender, EventArgs e)
    {
       LabelUserID.Text = Membership.GetUser().ProviderUserKey.ToString();

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

        //connect to database
        MySqlConnection database = new MySqlConnection();
        database.CreateConn(); 

        //create command object
        Command = new SqlCommand(queryString, database.Connection);

        //add parameters. used to prevent sql injection
        Command.Parameters.Add("@UID", SqlDbType.UniqueIdentifier);
        Command.Parameters["@UID"].Value = Membership.GetUser().ProviderUserKey;

        Command.Parameters.Add("@CL", SqlDbType.Int);
        Command.Parameters["@CL"].Value = 9;

        Command.Parameters.Add("@LL", SqlDbType.Int);
        Command.Parameters["@LL"].Value = 9;

        Command.Parameters.Add("@HL", SqlDbType.Int);
        Command.Parameters["@HL"].Value = 9;

        Command.ExecuteNonQuery(); 


    }

}

6 个解决方案

#1

Passing parameters to SQL saves you from having to build a dynamic SQL string.

将参数传递给SQL将使您不必构建动态SQL字符串。

Building dynamic SQL statements is a HUGE security risk because people can inject their own SQL code into your application, possibly executing undesirable commands against your data.

构建动态SQL语句是一个巨大的安全风险，因为人们可以将自己的SQL代码注入到应用程序中，可能对数据执行不需要的命令。

There are some good samples of possible SQL Injection attacks at:

下面是一些很好的SQL注入攻击示例:

SQL Injection Attacks by Example

SQL注入攻击的例子

There are two ways of passing parameters to SQL statements. One is to use Stored Procedures like you mentioned. The other is to use parameterized queries (which is actually what I prefer).

向SQL语句传递参数有两种方法。一个是使用您提到的存储过程。另一个是使用参数化查询(实际上我更喜欢这种查询)。

A parameterized query is actually quite easy in .NET:

参数化查询在。net中非常简单:

using(SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand command = 
        new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn);

    command.Parameters.Add(new SqlParameter("@Username", "Justin Niessner"));

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable dt = new DataTable();

    adapter.Fill(dt);
}

In that example, the parameter was @Username and we used the Parameters collection of the SqlCommand object to pass in the value.

在这个示例中，参数是@Username，我们使用SqlCommand对象的参数集合来传递值。

#2

It'll protect you from little Bobby Tables.

它会保护你不受小博比桌的影响。

http://xkcd.com/327/

#3

Here is an example:

这是一个例子:

        SqlConnection myConn = new SqlConnection("my connection string");

        SqlCommand myCmd = new SqlCommand("myStoredProcName", myConn);

        myCmd.CommandType = CommandType.StoredProcedure;

        myCmd.Parameters.AddWithValue("@cGroupID", 0).Direction = ParameterDirection.InputOutput;
        myCmd.Parameters.AddWithValue("@gType", "C");
        myCmd.Parameters.AddWithValue("@requirement", "Y");
        myCmd.Parameters.AddWithValue("@usercode", "XX");

        myConn.Open();
        myCmd.ExecuteNonQuery();

        int covID = (int)myCmd.Parameters["@cGroupID"].Value;

Using Parameters is a good way to prevent some errors, and help stop injection vectors. It also allows OUTPUT parameters, as in the example above where cGroupID comes back with a value that I can access.

使用参数是防止某些错误和帮助停止注入向量的好方法。它还允许输出参数，如上面的示例中cGroupID返回一个我可以访问的值。

#4

This site has an extremely thorough overview of what you're looking for.

这个网站对你要找的东西有一个非常全面的概述。

In addition, there have been other * questions on this topic. Wikipedia also happens to have a fairly good entry for SQL injections.

此外，还有其他关于这个主题的*问题。Wikipedia也恰好有一个相当好的SQL注入条目。

#5

The primary issue with simply concatenating strings is that it often leaves you vulnerable to SQL Injection attacks.

简单地连接字符串的主要问题是，它常常使您容易受到SQL注入攻击的攻击。

Google SQL Injection or read here.

谷歌SQL注入或阅读这里。

#6

In addition to SQL injection and the like, parameterized queries always look the same to the SQL server. Most likely the query execution plan will be cached, so if you send the same query again it will run faster. This would be very noticeable if you are running the same query many times in a loop or you have many clients querying your server.

除了SQL注入之类的，参数化查询在SQL服务器上看起来总是一样的。查询执行计划很可能会被缓存，所以如果您再次发送相同的查询，它将运行得更快。如果您在循环中多次运行相同的查询，或者您有许多客户端查询您的服务器，那么这将非常明显。

#1