什么是向 SQL 传递参数，为什么需要它? [英] What is passing parameters to SQL and why do I need it?

问题描述

这里的初学者:

在这个回答我的问题将数据插入到 SQL Server 他提到传递参数而不是像我目前那样的字符串连接.

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.

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

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.

谢谢.

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

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.

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(); 


    }

}

推荐答案

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

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 注入攻击的很好的例子:

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

SQL 注入攻击示例

向 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).

参数化查询在 .NET 中实际上很容易:

A parameterized query is actually quite easy in .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);
}

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

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

这篇关于什么是向 SQL 传递参数，为什么需要它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！