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

问题描述

我对使用数据库很陌生.现在我可以编写SELECT、UPDATE、DELETE 和INSERT 命令.但是我看过很多我们喜欢写的论坛:

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

...而不是:

SELECT empSalary from employee where salary = txtSalary.Text

为什么我们总是喜欢使用参数，我将如何使用它们?

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.

推荐答案

当数据库与桌面程序或网站等程序接口结合使用时，使用参数有助于防止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 代码.

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;删除表员工:

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

表 employee 然后将被删除.

就您而言，您似乎在使用 .NET.使用参数就像:

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

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

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:

根据 George Stocker 的评论，我将示例代码更改为不使用 AddWithValue.此外，通常建议您将 IDisposable 包裹在 using 语句中.

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.

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