为什么我们总是preFER在SQL语句中使用参数？ [英] Why do we always prefer using parameters in SQL statements?

问题描述

我很新的使用数据库。现在，我可以写 SELECT ，更新，删除，和插入命令。但我见过很多，我们preFER论坛写的：

I am very new to working with databases. Now I can write SELECT, UPDATE, DELETE, and INSERT commands. But I have seen many forums where we prefer to write:

SELECT empSalary from employee where salary = @salary

...而不是：

...instead of:

SELECT empSalary from employee where salary = txtSalary.Text

为什么我们总是preFER使用的参数，我将如何使用它们？

Why do we always prefer to use parameters and how would I use them?

我想知道第一种方法的使用和收益。我甚至听说过SQL注入的，但我不完全理解。我甚至不知道SQL注入是与我的问题。

I wanted to know the use and benefits of the first method. I have even heard of SQL injection but I don't fully understand it. I don't even know if SQL injection is related to my question.

推荐答案

使用参数有助于prevent SQL注入攻击当数据库与程序接口一起使用，如桌面程序或网站。

Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a program interface such as a desktop program or web site.

在您的例子中，用户可以直接在数据库上通过制定报表 txtSalary 运行SQL code。

In your example, a user can directly run SQL code on your database by crafting statements in txtSalary.

例如，如果他们写的 0 OR 1 = 1 ，已执行的SQL会

For example, if they were to write 0 OR 1=1, the executed SQL would be

 SELECT empSalary from employee where salary = 0 or 1=1

使所有empSalaries将被返回。

whereby all empSalaries would be returned.

此外，用户可以对数据库执行差远了命令，包括删除它。如果他们写了 0; DROP TABLE员工：

Further, a user could perform far worse commands against your database, including deleting it If they wrote 0; Drop Table employee:

SELECT empSalary from employee where salary = 0; Drop Table employee

表员工随后将被删除。

在您的情况下，它看起来像你使用.NET。使用参数是一样容易：

In your case, it looks like you're using .NET. Using parameters is as easy as:

C＃

string sql = "SELECT empSalary from employee where salary = @salary";
using (SqlConnection connection = new SqlConnection(/* connection info */))
{
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        var salaryParam = new SqlParameter("salary", SqlDbType.Money);
        salaryParam.Value = txtMoney.Text;

        command.Parameters.Add(salaryParam);

        var results = command.ExecuteReader();
    }
}

VB.NET

Dim sql As String = "SELECT empSalary from employee where salary = @salary"
Using connection As New SqlConnection("connectionString")
    Using command As New SqlCommand(sql, connection)
        Dim salaryParam = New SqlParameter("salary", SqlDbType.Money)
        salaryParam.Value = txtMoney.Text

        command.Parameters.Add(salaryParam)

        Dim results = command.ExecuteReader()
    End Using
End Using

---

编辑2016年4月25日：

---

Edit 2016-4-25:

根据乔治·斯托克的评论，我改变了样品code不使用 AddWithValue 。此外，通常建议您包装的IDisposable S IN 使用语句。

As per George Stocker's comment, I changed the sample code to not use AddWithValue. Also, it is generally recommended that you wrap IDisposables in using statements.

这篇关于为什么我们总是preFER在SQL语句中使用参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！