SQL 문에서 항상 매개 변수를 사용하는 것을 선호하는 이유는 무엇입니까?

SQL 문에서 항상 매개 변수를 사용하는 것을 선호하는 이유는 무엇입니까?

---

저는 데이터베이스 작업에 매우 익숙합니다. 지금은 쓸 수 SELECT, UPDATE, DELETE, 및 INSERT명령. 그러나 나는 우리가 쓰는 것을 선호하는 많은 포럼을 보았습니다.

SELECT empSalary from employee where salary = @salary

...대신에:

SELECT empSalary from employee where salary = txtSalary.Text

왜 우리는 항상 매개 변수를 선호하며 어떻게 사용합니까?

첫 번째 방법의 용도와 이점을 알고 싶었습니다. SQL 인젝션에 대해 들어 봤지만 완전히 이해하지 못했습니다. SQL 주입이 내 질문과 관련이 있는지조차 모르겠습니다.

---

매개 변수를 사용 하면 데이터베이스가 데스크톱 프로그램 또는 웹 사이트와 같은 프로그램 인터페이스와 함께 사용될 때 SQL 주입 공격 을 방지하는 데 도움 이됩니다.

귀하의 예에서 사용자는 .NET에서 문을 작성하여 데이터베이스에서 SQL 코드를 직접 실행할 수 있습니다 txtSalary.

예를 들어, 작성하는 경우 0 OR 1=1실행 된 SQL은 다음과 같습니다.

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

모든 empSalaries가 반환됩니다.

또한 사용자는 데이터베이스에 대해 훨씬 더 나쁜 명령을 수행 할 수 있습니다 0; Drop Table employee.

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

employee그런 다음 테이블 이 삭제됩니다.

---

귀하의 경우에는 .NET을 사용하고있는 것 같습니다. 매개 변수 사용은 다음과 같이 쉽습니다.

씨#

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 수정 :

George Stocker의 의견에 따라 샘플 코드를 AddWithValue. 또한 일반적으로 IDisposables를 using문으로 래핑하는 것이 좋습니다 .

---

맞습니다. 이것은 SQL 주입 과 관련 이 있습니다. 이는 malicioius 사용자가 데이터베이스에 대해 임의의 명령문을 실행할 수 있도록하는 취약점입니다. 이 옛날 좋아하는 XKCD 만화 는 개념을 보여줍니다.

---

귀하의 예에서 다음을 사용하는 경우 :

var query = "SELECT empSalary from employee where salary = " + txtSalary.Text;
// and proceed to execute this query

SQL 주입에 열려 있습니다. 예를 들어 누군가 txtSalary를 입력한다고 가정 해 보겠습니다.

1; UPDATE employee SET salary = 9999999 WHERE empID = 10; --
1; DROP TABLE employee; --
// etc.

이 쿼리를 실행하면 a SELECT및 an UPDATE또는 DROP, 또는 원하는대로 수행됩니다 . --말은 단순히 당신이 후에 어떤을 연결 한 경우 공격에 유용 할 것이다 쿼리의 나머지 부분을 주석으로 txtSalary.Text.

---

올바른 방법은 매개 변수화 된 쿼리 (예 : (C #))를 사용하는 것입니다.

SqlCommand query =  new SqlCommand("SELECT empSalary FROM employee 
                                    WHERE salary = @sal;");
query.Parameters.AddWithValue("@sal", txtSalary.Text);

이를 통해 쿼리를 안전하게 실행할 수 있습니다.

For reference on how to avoid SQL injection in several other languages, check bobby-tables.com, a website maintained by a SO user.

---

In addition to other answers need to add that parameters not only helps prevent sql injection but can improve performance of queries. Sql server caching parameterized query plans and reuse them on repeated queries execution. If you not parameterized your query then sql server would compile new plan on each query(with some exclusion) execution if text of query would differ.

More information about query plan caching

---

In Sql when any word contain @ sign it means it is variable and we use this variable to set value in it and use it on number area on the same sql script because it is only restricted on the single script while you can declare lot of variables of same type and name on many script. We use this variable in stored procedure lot because stored procedure are pre-compiled queries and we can pass values in these variable from script, desktop and websites for further information read Declare Local Variable, Sql Stored Procedure and sql injections.

Also read Protect from sql injection it will guide how you can protect your database.

Hope it help you to understand also any question comment me.

---

Two years after my first go, I'm recidivating...

Why do we prefer parameters? SQL injection is obviously a big reason, but could it be that we're secretly longing to get back to SQL as a language. SQL in string literals is already a weird cultural practice, but at least you can copy and paste your request into management studio. SQL dynamically constructed with host language conditionals and control structures, when SQL has conditionals and control structures, is just level 0 barbarism. You have to run your app in debug, or with a trace, to see what SQL it generates.

Don't stop with just parameters. Go all the way and use QueryFirst (disclaimer: which I wrote). Your SQL lives in a .sql file. You edit it in the fabulous TSQL editor window, with syntax validation and Intellisense for your tables and columns. You can assign test data in the special comments section and click "play" to run your query right there in the window. Creating a parameter is as easy as putting "@myParam" in your SQL. Then, each time you save, QueryFirst generates the C# wrapper for your query. Your parameters pop up, strongly typed, as arguments to the Execute() methods. Your results are returned in an IEnumerable or List of strongly typed POCOs, the types generated from the actual schema returned by your query. If your query doesn't run, your app won't compile. If your db schema changes and your query runs but some columns disappear, the compile error points to the line in your code that tries to access the missing data. And there are numerous other advantages. Why would you want to access data any other way?

---

Other answers cover why parameters are important, but there is a downside! In .net, there are several methods for creating parameters (Add, AddWithValue), but they all require you to worry, needlessly, about the parameter name, and they all reduce the readability of the SQL in the code. Right when you're trying to meditate on the SQL, you need to hunt around above or below to see what value has been used in the parameter.

I humbly claim my little SqlBuilder class is the most elegant way to write parameterized queries. Your code will look like this...

C#

var bldr = new SqlBuilder( myCommand );
bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId);
//or
bldr.Append("SELECT * FROM CUSTOMERS WHERE NAME LIKE ").FuzzyValue(myName);
myCommand.CommandText = bldr.ToString();

Your code will be shorter and much more readable. You don't even need extra lines, and, when you're reading back, you don't need to hunt around for the value of parameters. The class you need is here...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlBuilder
{
private StringBuilder _rq;
private SqlCommand _cmd;
private int _seq;
public SqlBuilder(SqlCommand cmd)
{
    _rq = new StringBuilder();
    _cmd = cmd;
    _seq = 0;
}
public SqlBuilder Append(String str)
{
    _rq.Append(str);
    return this;
}
public SqlBuilder Value(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append(paramName);
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public SqlBuilder FuzzyValue(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append("'%' + " + paramName + " + '%'");
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public override string ToString()
{
    return _rq.ToString();
}
}

---

Old post but wanted to ensure newcomers are aware of Stored procedures.

My 10¢ worth here is that if you are able to write your SQL statement as a stored procedure, that in my view is the optimum approach. I ALWAYS use stored procs and never loop through records in my main code. For Example: SQL Table > SQL Stored Procedures > IIS/Dot.NET > Class.

When you use stored procedures, you can restrict the user to EXECUTE permission only, thus reducing security risks.

Your stored procedure is inherently paramerised, and you can specify input and output parameters.

The stored procedure (if it returns data via SELECT statement) can be accessed and read in the exact same way as you would a regular SELECT statement in your code.

It also runs faster as it is compiled on the SQL Server.

Did I also mention you can do multiple steps, e.g. update a table, check values on another DB server, and then once finally finished, return data to the client, all on the same server, and no interaction with the client. So this is MUCH faster than coding this logic in your code.

참고URL : https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements