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

118

Я новичок в работе с базами данных. Теперь я могу написать SELECT, UPDATE, DELETEи INSERTкоманду. Но я видел много форумов, где мы предпочитаем писать:

SELECT empSalary from employee where salary = @salary

...вместо того:

SELECT empSalary from employee where salary = txtSalary.Text

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

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

3
  • 2
    Вы правы, это связано с SQL-инъекцией. Как работать с параметрами обычно зависит от того, на каком языке / фреймворке работает ваша программа, и может зависеть от языка. Пожалуйста, опубликуйте как свою СУБД (полезно), так и структуру ORM (необходимо). 21 сен.
  • 1
    Я использую C # как язык программирования и Sql Server 2008 как базу данных. Я использую Microsoft dotNet framework 4.0. Мне действительно очень жаль, что я не уверен в том, о чем вы спрашиваете (RDBMS или ORM), возможно, вы можете дать мне мои версии RDBMS и ORM сейчас :-). Большое спасибо
    Sandy
    21 сен.
  • 1
    СУБД - это ваша база данных, в вашем случае SQL Server 2008. ORM - это метод, с помощью которого вы получаете доступ к своей базе данных, в данном случае ADO.NET. Другие включают LINQ to SQL и Entity Framework . Фактически, как только вы изучите основы ADO.NET и SQL, я рекомендую использовать ORM, например LINQ или EF, поскольку они решают многие проблемы, с которыми вы можете столкнуться, написав SQL вручную. 21 сен.
136

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

В вашем примере пользователь может напрямую запускать код 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();
}
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. Кроме того, обычно рекомендуется заключать IDisposables в usingоператоры.

11
  • отличное решение. Но не могли бы вы объяснить немного больше, почему и как безопасно использовать параметры. Я имею в виду, что по-прежнему похоже, что команда sql будет такой же
    Sandy
    21 сен.
  • 2
    SQL Server обрабатывает текст внутри параметров только как ввод и никогда не выполнит его. 21 сен.
  • 3
    Да, вы можете добавить несколько параметров: Insert Into table (Col1, Col2) Values (@Col1, @Col2). В свой код вы должны добавить несколько AddWithValues. 21 сен.
  • 1
    Пожалуйста, не используйте AddWithValue! Это может вызвать проблемы с неявным преобразованием. Всегда задавайте размер явно и добавляйте значение параметра с помощью parameter.Value = someValue. 11 марта '15 в 15:41
  • 1
    Вы действительно должны использовать salaryParam.Value = CDec(txtMoney.Text): SQL Server moneyнаходится Decimalв .NET: сопоставления типов данных SQL Server . И имя параметра требует символа «@», как в «@salary». 16 марта '17 в 19:30
80

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


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

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

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

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

4
  • 1
    отличное решение. Но не могли бы вы объяснить немного больше, почему и как безопасно использовать параметры. Я имею в виду, что по-прежнему похоже, что команда sql будет такой же.
    Sandy
    21 сен.
  • 1
    @ user815600: распространенное заблуждение - вы все еще верите, что запрос с параметрами примет значение и заменит параметры на фактические значения - верно? Нет, этого не происходит! - вместо этого оператор SQL с параметрами будет передан на SQL Server вместе со списком параметров и их значений - оператор SQL не будет таким же
    marc_s
    21 сен.
  • 1
    это означает, что внедрение sql контролируется внутренним механизмом или безопасностью sql server. Благодарю.
    Sandy
    21 сен.
  • 5
    Как бы мне ни нравились мультфильмы, если вы запускаете свой код с достаточными привилегиями для удаления таблиц, у вас, вероятно, есть более широкие проблемы.
    philw
    03 авг.
10

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

Дополнительные сведения о кэшировании плана запроса

1
  • 2
    Это более уместно, чем можно было бы подумать. Даже «небольшой» запрос может выполняться тысячи или миллионы раз, эффективно очищая весь кеш запроса.
    James
    12 янв.
6

Через два года после первой попытки я рецидивирую ...

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

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

0
4

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

Также прочтите « Защита от внедрения sql», в котором рассказывается, как защитить свою базу данных.

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

0
4

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

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

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

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

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

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

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

3

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

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

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

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

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();
}
}
5
  • Именование ваших параметров, безусловно, помогает при профилировании запросов, выполняемых сервером.
    Dave R.
    5 мая '15 в 16:48
  • Мой босс сказал то же самое. Если для вас важны значимые имена параметров, добавьте аргумент paramName в метод значения. Я подозреваю, что вы напрасно все усложняете. 4 июня '15 в 9:09
  • Плохая идея. Как было сказано ранее, AddWithValueможет вызвать проблемы с неявным преобразованием. 25 янв.
  • @Adam, вы правы, но это не мешает AddWithValue () широко использоваться, и я не думаю, что это опровергает идею. Но пока что я придумал гораздо лучший способ написания параметризованных запросов, в котором не используется AddWithValue () :-) 25 янв.
  • Верно! Обещаю, я скоро посмотрю на это! 25 янв.