Почему мы всегда предпочитаем использовать параметры в операторах SQL?

Ответ 1

Использование параметров помогает предотвратить атаки SQL Injection, когда база данных используется в сочетании с программным интерфейсом, таким как настольная программа или веб-сайт.

В вашем примере пользователь может напрямую запускать 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:

Согласно комментарию Джорджа Стокера, я изменил образец кода, чтобы не использовать AddWithValue. Кроме того, обычно рекомендуется обернуть IDisposable в операторы using.

Ответ 2

Вы правы, это связано с 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.

Когда вы выполните этот запрос, он выполнит SELECT и UPDATE или DROP, или что угодно. -- В конце просто комментирует оставшуюся часть вашего запроса, что было бы полезно при атаке, если вы объединяете что-либо после txtSalary.Text.

Правильный способ - использовать параметризованные запросы, например (С#):

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

При этом вы можете безопасно выполнить запрос.

Чтобы узнать, как избежать внедрения SQL на нескольких других языках, посетите сайт bobby-tables.com, поддерживаемый пользователем SO.

Ответ 3

В дополнение к другим ответам необходимо добавить, что параметры не только помогают предотвратить внедрение SQL, но могут повысить производительность запросов. Sql-сервер кэширует параметризованные планы запросов и повторно использует их при выполнении повторных запросов. Если вы не параметризировали ваш запрос, тогда sql-сервер будет компилировать новый план для каждого запроса (с некоторым исключением), если текст запроса будет отличаться.

Дополнительная информация о кешировании плана запросов

Ответ 4

В Sql, когда любое слово содержит знак @, это означает, что оно является переменной, и мы используем эту переменную для установки в нем значения и используем ее в области чисел на том же sql script, потому что она ограничена только одним script в то время как вы можете объявить множество переменных того же типа и имени на многих script. Мы используем эту переменную в партии хранимых процедур, потому что хранимая процедура представляет собой предварительно скомпилированные запросы, и мы можем передавать значения в этой переменной из script, рабочего стола и веб-сайтов для получения дополнительной информации: Объявить локальную переменную, Сохраненная процедура Sql и sql injection.

Также читайте Защитите от SQL-инъекции, это поможет вам защитить вашу базу данных.

Надеюсь, он поможет вам понять, что любой вопрос прокомментировал меня.

Ответ 5

Через два года после мой первый вариант, я возвращаюсь...

Почему мы предпочитаем параметры? SQL-инъекция, очевидно, является большой причиной, но может быть, что мы тайно стремимся вернуться к SQL как языку. SQL в строковых литералах уже является странной культурной практикой, но по крайней мере вы можете скопировать и вставить свой запрос в студию управления. SQL, динамически построенный с условными выражениями хоста и структурами управления, когда SQL имеет условные и управляющие структуры, является просто варварством уровня 0. Вы должны запустить приложение в отладке или с помощью трассировки, чтобы увидеть, какой SQL он генерирует.

Не останавливайтесь только с параметрами. Пройдите весь путь и используйте QueryFirst (отказ от ответственности: что я написал). Ваш SQL живет в файле .sql. Вы редактируете его в сказочном окне редактора TSQL с проверкой синтаксиса и Intellisense для ваших таблиц и столбцов. Вы можете назначить тестовые данные в разделе специальных комментариев и нажать "play", чтобы запустить ваш запрос прямо там, в окне. Создание параметра так же просто, как установка "@myParam" в вашем SQL. Затем каждый раз, когда вы сохраняете, QueryFirst создает оболочку С# для вашего запроса. Ваши параметры всплывают, строго типизируются, как аргументы методов Execute(). Ваши результаты возвращаются в IEnumerable или List строго типизированных POCOs, типы, созданные из фактической схемы, возвращаемой вашим запросом. Если ваш запрос не запущен, ваше приложение не будет компилироваться. Если ваша схема db изменяется и ваш запрос выполняется, но некоторые столбцы исчезают, ошибка компиляции указывает на строку в вашем коде, которая пытается получить доступ к отсутствующим данным. И есть множество других преимуществ. Почему вы хотите получить доступ к данным любым другим способом?

Ответ 6

Другие ответы касаются того, почему параметры важны, но есть недостатки! В .net существует несколько методов для создания параметров (Add, AddWithValue), но все они требуют от вас беспокоиться, независимо от имени параметра, и все они уменьшают читабельность SQL в коде. Правильно, когда вы пытаетесь медитировать на SQL, вам нужно поохотиться вокруг или ниже, чтобы узнать, какое значение было использовано в параметре.

Я смиренно утверждаю, что мой маленький класс SqlBuilder - самый элегантный способ писать параметризованные запросы. Ваш код будет выглядеть следующим образом:

С#

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

Ваш код будет короче и гораздо читабельнее. Вам даже не нужны дополнительные строки, и, когда вы читаете назад, вам не нужно искать значения параметров. Класс, в котором вы нуждаетесь, находится здесь...

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

Ответ 7

Старый пост, но хотел, чтобы новички знали о хранимых процедурах.

Моя ценность в 10 центов состоит в том, что если вы можете написать оператор SQL как хранимую процедуру, то, на мой взгляд, это оптимальный подход. Я ВСЕГДА использую хранимые процедуры и никогда не перебираю записи в моем основном коде. Например: SQL Table > SQL Stored Procedures > IIS/Dot.NET > Class.

Когда вы используете хранимые процедуры, вы можете ограничить пользователя только разрешением EXECUTE, тем самым уменьшая риски безопасности.

Ваша хранимая процедура по сути парамеризована, и вы можете указать входные и выходные параметры.

Хранимая процедура (если она возвращает данные с помощью SELECT) может быть доступна и прочитана точно так же, как и обычная SELECT в вашем коде.

Он также работает быстрее, так как он скомпилирован на SQL Server.

Я также упомянул, что вы можете сделать несколько шагов, например, update таблицу, проверить значения на другом сервере БД, а затем, наконец, завершив, вернуть данные клиенту, все на одном сервере, и не взаимодействовать с клиентом. Так что это НАМНОГО быстрее, чем кодирование этой логики в вашем коде.