sql server запрос с параметрами
Указание параметров
Путем указания параметров процедуры вызывающие программы могут передавать значения в тело процедуры. Эти значения могут использоваться для разных целей во время исполнения процедуры. Параметры процедуры могут также возвращать значения вызывающей программе, если параметр помечен признаком OUTPUT.
Хранимая процедура может иметь не более 2100 параметров, каждый из которых имеет имя, тип данных и направление. При необходимости параметрам можно задавать значения по умолчанию.
В следующем разделе содержатся сведения о передаче значений параметрам и о том, как каждый из атрибутов параметров используется во время вызова процедуры.
Передача значений в параметры
Значения параметра, переданные при вызове процедуры, должны быть константами или переменными. Имя функции не может быть значением параметра. Переменные могут быть пользовательскими или системными, например @@spid.
Указание имен параметров
При создании процедуры и объявлении имени параметра, последнее должно начинаться с единичного символа @ и быть уникальным для всей процедуры.
При указании одного значения параметра в формате @parameter = value необходимо точно так же предоставить все последующие параметры. Если значения параметра передаются не в формате @parameter = value, значения должны передаваться в том порядке (слева направо), в котором они перечислены в инструкции CREATE PROCEDURE.
Передача параметров в формате @parameter = value с ошибками приведет к возникновению ошибки SQL Server и невозможности выполнения процедуры.
Указание типов данных параметров
Параметры должны быть определены с типом данных в момент объявления в инструкции CREATE PROCEDURE. Тип данных параметра определяет тип и диапазон допустимых значений параметра при вызове процедуры. Например, параметр типа tinyint может принимать только численные значения в диапазоне от 0 до 255 в момент передачи этому параметру. При попытке выполнить процедуру со значением, не совместимым с типом данных, происходит ошибка.
Указание значений параметра по умолчанию
Параметр считается необязательным, если он имеет значение по умолчанию при объявлении. Нет необходимости указывать значение необязательного параметра при вызове процедуры.
Значение параметра по умолчанию используется, когда:
не указано значение для параметра при вызове процедуры.
в качестве значения при вызове процедуры указывается ключевое слово DEFAULT.
Если значением по умолчанию является символьная строка, включающая в себя знаки пробела или пунктуации, либо содержащая первым элементом число, например 6ххх, то ее следует заключить в одинарные прямые кавычки.
Параметры по умолчанию не поддерживаются для Azure Synapse Analytics и Parallel Data Warehouse.
Если значение по умолчанию указать нельзя, укажите NULL. Желательно, чтобы процедура возвращала сообщение, если она выполняется без значения для параметра.
Следующий пример выполняет процедуру. Первая инструкция выполняет процедуру без указания входного значения. В результате чего инструкции обработки ошибок процедуры возвращают пользовательское сообщение об ошибке. Вторая инструкция задает входное значение и возвращает ожидаемый результирующий набор.
Хотя разрешается опустить параметры, для которых предоставлены значения по умолчанию, можно лишь подвергнуть усечению список параметров. Например, если у процедуры пять параметров, можно опустить как четвертый, так и пятый параметр. При этом нельзя пропустить четвертый параметр, если включен пятый, если только параметры не передаются в формате @parameter = value.
Указание направления параметров
Параметр может быть как входным, когда значение передается в тело процедуры, так и выходным, возвращаемым процедурой вызывающей программе. По умолчанию параметр определен как входной.
Для указания выходного параметра в определении процедуры необходимо указать ключевое слово OUTPUT в инструкции CREATE PROCEDURE. Процедура, завершая свою работу, возвращает текущее значение выходного параметра в вызывающую программу. При выполнении процедуры вызывающая программа также должна использовать ключевое слово OUTPUT для сохранения значения параметра в переменной, которое затем может быть использовано в вызывающей программе.
Переменная OUTPUT должна быть определена во время создания процедуры, а также в ходе использования переменной. Имена параметра и переменной не должны совпадать. При этом тип данных и положение параметра должны быть одинаковыми (если только не используется @listprice= переменная).
Указание механизма параметризации запросов с помощью структур плана
Если для параметра базы данных PARAMETERIZATION установлено значение SIMPLE, оптимизатор запросов SQL Server может выбрать параметризацию запросов. Это значит, что все литеральные значения, содержащиеся в запросе, заменяются параметрами. Этот процесс называется простой параметризацией. При применении простой (SIMPLE) параметризации невозможно контролировать, какие запросы параметризуются, а какие нет. Однако можно параметризовать все запросы в базе данных, присвоив параметру базы данных PARAMETERIZATION значение FORCED. Этот процесс называется принудительной параметризацией.
Механизм параметризации в базе данных можно переопределить с помощью структур планов следующим путем.
Если значение параметра базы данных PARAMETERIZATION равно FORCED, можно указать, чтобы для определенного класса запросов выполнялись только попытки простой параметризации вместо принудительной. Это можно сделать путем создания структуры плана TEMPLATE для формы запроса с принудительной параметризацией и задания указания запроса PARAMETERIZATION SIMPLE с помощью хранимой процедуры sp_create_plan_guide. Дополнительные сведения о принудительной параметризации см. в разделе Руководство по архитектуре обработки запросов.
Рассмотрим следующий запрос к базе данных AdventureWorks2012 :
Администратор базы данных решил не включать принудительную параметризацию для всех запросов к базе данных. Однако необходимо избегать затрат на компиляцию для всех запросов, синтаксически эквивалентных предыдущему и различающихся только литеральными значениями констант. Иными словами, необходимо реализовать параметризацию запроса таким образом, чтобы план для данного вида запроса использовался повторно. В этом случае нужно выполнить следующее.
Создать структуру плана для параметризованной формы запроса, задав указание запроса PARAMETERIZATION FORCED.
В ходе параметризации запроса SQL Server присваивает параметрам, заменяющим литеральные значения, определенный тип данных в зависимости от значения и размера литерала. Подобный процесс выполняется и для значений констант-литералов, передаваемых в качестве выходного параметра @stmt процедуры sp_get_query_template. Так как тип данных, указанный в аргументе @params процедуры sp_create_plan_guide, должен соответствовать типу данных в запросе после его параметризации SQL Server, может потребоваться создание нескольких структур планов для охвата всего диапазона возможных значений параметров запроса.
Следующий скрипт можно использовать как для получения параметризированного запроса, так и для дальнейшего создания по нему структуры плана:
Подобным образом в базе данных, в которой уже включена принудительная параметризация, можно гарантировать, что указанный в качестве примера запрос и другие синтаксически ему эквивалентные, в которых различаются только литеральные значения констант, будут параметризованы согласно правилам простой параметризации. Для этого следует указать PARAMETERIZATION SIMPLE вместо PARAMETERIZATION FORCED в предложении OPTION.
С помощью структур плана TEMPLATE осуществляется сопоставление инструкций с запросами, поступающими в пакетах, каждый из которых состоит только из одной инструкции. Инструкции, находящиеся в пакетах с несколькими инструкциями, не подлежат сопоставлению со структурами планов TEMPLATE.
Панацея от SQL-инъекций — запросы с параметрами
Введение
Этот топик — начало небольшого цикла о панацеях от различных уязвимостей Web-приложений.
Так уж случилось, что SQL-инъекциями страдает большое количество Web-приложений. И сколько не пишется статей, сколько не публикуется багрепортов — все равно, программисты забивают забывают, что пользователь может подставить в параметры их приложению все, что им захочется.
Предлагаю им, да и вообще всем использовать так называемые prepared-statements. Их еще называют параметризованные запросы.
Идея проста как и все гениальное — отделить запрос от пользовательских данных.
Вы, наверно, спросите: что означит «отделить»? Имеется в виду: отделить их от составления запроса.
Ведь ваш сайт должен обрабатывать пользовательские данные, а не составлять на их основе SQL-запросы (хотя кто знает?). Последнее даже звучит глупо, но именно из-за этого мы имеем подобную ситуацию.
Реализация
Реализация не сложнее идеи. Для примера возьмем запрос получения записи блога:
SELECT ` date `,`title`,`text`,`tags` FROM `posts` WHERE `url_title`= ‘bla-bla-bla’
Я даже, когда печатал этот безопасный пример, ошибся и допустил синтаксическую ошибку где-то в районе кавычек и экранирующих слешей.
Чем больше запрос, тем больше неразбериха. В глазах рябит и совсем не читается.
По-моему, очень удобно. Вместо конструкций :N подставляется соответствующий аргумент. Их, конечно же, можно дублировать и все такое.
А что происходит за кулисами (код взят из одного проекта, где я и придумал[хоть идея и не моя, но я об этом не знал] это)?
Использование параметризованных запросов с помощью элемента управления SqlDataSource (C#)
В этом учебнике мы продолжаем знакомство с элементом управления SqlDataSource и научитесь определять параметризованные запросы. Параметры можно указать как декларативно, так и программно, и их можно извлечь из нескольких расположений, таких как строка запроса, состояние сеанса, другие элементы управления и многое другое.
Введение
Создание параметризованного запроса
Мастер настройки источника данных SqlDataSource предлагает три способа определения выполняемой команды для получения записей базы данных:
При выборе столбцов из существующей таблицы или представления параметры для предложения WHERE должны быть указаны в диалоговом окне Добавление WHERE предложения. Однако при создании пользовательской инструкции SQL вы можете вводить параметры непосредственно в предложение WHERE (используя @parameterName для обозначения каждого параметра). Хранимая процедура состоит из одной или нескольких инструкций SQL, и эти инструкции могут быть параметризованы. Однако параметры, используемые в инструкциях SQL, должны передаваться в качестве входных параметров хранимой процедуре.
Шаг 1. Добавление предложения WHERE при выборе столбцов из таблицы или представления
Рис. 1. Выбор столбцов, возвращаемых из таблицы или представления (щелкните, чтобы просмотреть изображение с полным размером)
После добавления параметра нажмите кнопку ОК, чтобы вернуться в мастер настройки источника данных. Инструкция SELECT в нижней части мастера должна включать предложение WHERE с параметром с именем @UnitPrice :
Шаг 2. Добавление параметров в пользовательскую инструкцию SQL
После ввода запроса (вручную или с помощью конструктор запросов) нажмите кнопку Далее.
Рис. 4. возврат только тех продуктов, которые меньше или равны значению параметра (щелкните, чтобы просмотреть изображение с полным размером)
Рис. 5. свойство MaxPrice TextBox Text используется в качестве источника параметра (щелкните, чтобы просмотреть изображение с полным размером)
Завершите работу мастера настройки источника данных, нажав кнопку Далее, а затем Готово. Декларативная разметка для GridView, TextBox, Button и SqlDataSource выглядит следующим образом:
Потратьте минуту, чтобы просмотреть эту страницу в браузере. При первом посещении страницы или при отсутствии значения в текстовом поле MaxPrice в GridView не отображаются никакие записи.
Рис. 6. никакие записи не отображаются, если пустое текстовое поле MaxPrice (щелкните, чтобы просмотреть изображение с полным размером)
Причина, по которой продукты не отображаются, заключается в том, что по умолчанию пустая строка для значения параметра преобразуется в значение NULL базы данных. Поскольку при сравнении [UnitPrice] всегда вычисляется значение false, результаты не возвращаются.
Изначально отображаются все продукты
В предыдущих учебных курсах — декларативные параметры и Фильтрация «основной/подробности» с помощью DropDownList мы столкнулись с подобной проблемой. Наше решение поместит эту логику на уровне бизнес-логики. В частности, BLL проверил входящее значение и, если оно было NULL или какое зарезервированное значение, вызов направляется к методу DAL, который вернул все записи. Если входящее значение было обычной фильтрацией, в метод DAL был выполнен вызов, который выполнил инструкцию SQL, которая использовала параметризованное WHERE предложение с предоставленным значением.
Рис. 8. Теперь все продукты отображаются, если пустое текстовое поле MaxPrice (щелкните, чтобы просмотреть изображение с полным размером)
Шаг 3. Создание и использование параметризованных хранимых процедур
Хранимые процедуры могут включать набор входных параметров, которые затем можно использовать в инструкциях SQL, определенных в хранимой процедуре. При настройке SqlDataSource для использования хранимой процедуры, принимающей входные параметры, эти значения параметров можно указать с помощью тех же методов, что и для специальных инструкций SQL.
В базе данных NORTHWND.MDF щелкните правой кнопкой мыши папку Хранимые процедуры, выберите команду Добавить новую хранимую процедуру и введите следующий синтаксис:
Рис. 9. GetProductsByCategory хранимая процедура при выполнении с @CategoryID 1 (щелкните, чтобы просмотреть изображение с полным размером)
Рис. 10. Выбор хранимой процедуры GetProductsByCategory из раскрывающегося списка (щелкните, чтобы просмотреть изображение с полным размером)
Поскольку хранимая процедура принимает входной параметр ( @CategoryID ), при нажатии кнопки Далее будет предложено указать источник для этого значения параметра s. CategoryID напитки имеет значение 1, поэтому оставьте в раскрывающемся списке Источник параметра значение нет и введите 1 в текстовое поле DefaultValue (значение по умолчанию).
Рис. 11. использование жестко запрограммированного значения 1 для возврата продуктов из категории «напитки» (щелкните, чтобы просмотреть изображение с полным размером)
Шаг 4. программный вызов инструкции Select() SqlDataSource
В примерах, приведенных в предыдущем учебном курсе, и в этом руководстве ранее привязываются элементы управления SqlDataSource к GridView. Однако данные элемента управления SqlDataSource могут быть программно доступны и перечислены в коде. Это может быть особенно полезно, если необходимо запросить данные для проверки, но не нужно отображать их. Вместо того чтобы писать весь стандартный код ADO.NET для подключения к базе данных, указать команду и получить результаты, можно разрешить SqlDataSource обработку этого кода монотонную.
ORDER BY NEWID() возвращает записи, отсортированные в случайном порядке (см. раздел использование NEWID() для случайной сортировки записей). SELECT TOP 1 возвращает первую запись из результирующего набора. Вместе этот запрос возвращает значения столбцов CategoryID и CategoryName из одной, случайной выбранной категории.
В следующем коде показано, как извлечь записи из RandomCategoryDataSource SqlDataSource как DataView, а также как считать значение столбца CategoryName из первой строки DataView:
randomCategoryView[0] возвращает первый DataRowView в объекте DataView. randomCategoryView[0][«CategoryName»] возвращает значение столбца CategoryName в первой строке. Обратите внимание, что объект DataView слабо типизирован. Чтобы сослаться на конкретное значение столбца, необходимо передать имя столбца в виде строки (CategoryName, в данном случае). На рис. 13 показано сообщение, отображаемое в CategoryNameLabel при просмотре страницы. Конечно, фактически отображаемое имя категории выбирается случайным образом RandomCategoryDataSource SqlDataSource при каждом посещении страницы (включая обратные передачи).
Рис. 13. отображается случайно выбранное имя категории s (щелкните, чтобы просмотреть изображение с полным размером)
При случайном выборе категории с помощью SqlDataSource мы повторно готовы добавить GridView, в котором перечислены продукты категории.
Вместо использования элемента управления Label для вывода имени категории s можно было добавить FormView или DetailsView на страницу, привязывая его к SqlDataSource. Однако использование метки позволило нам исследовать, как программным способом вызвать инструкцию SqlDataSource Select() и работать с полученными в коде данными.
Шаг 5. программное присвоение значений параметров
Рис. 14. не указывайте источник параметра или значение по умолчанию (щелкните, чтобы просмотреть изображение с полным размером)
После завершения работы мастера SqlDataSource полученная декларативная разметка должна выглядеть следующим образом:
В обработчике событий Page_Load можно программно назначить DefaultValue параметра CategoryID :
С помощью этого добавления страница содержит элемент управления GridView, в котором отображаются продукты, связанные с выбранным случайным образом категорией.
Рис. 15. не указывайте источник параметра или значение по умолчанию (щелкните, чтобы просмотреть изображение с полным размером)
Сводка
SqlDataSource позволяет разработчикам страниц определять параметризованные запросы, значения параметров которых могут быть жестко запрограммированы, извлечены из предварительно определенных источников параметров или назначены программно. В этом учебнике мы увидели, как создавать параметризованные запросы из мастера настройки источников данных для нерегламентированных запросов SQL и хранимых процедур. Мы также рассматривали использование жестко заданных источников параметров, веб-элемента управления в качестве источника параметров и программного указания значения параметра.
Передача параметров в динамический запрос в T-SQL
Я не раз сталкивался с необходимостью построения динамического запроса и здесь есть ряд подводных камней о которых я расскажу ниже. Пример динамического запроса:
1. Запуск строки через Execute создает отдельный блок кода, в котором текущие переменные будут не видны, но видны все временные таблицы.
2. Обратите внимание на передачу переменных со значением NULL. Любое слияние с NULL в результате даст NULL, следовательно, вместо запроса, вы можете получить пустую строку.
3. Передачу дат и времени. Даты лучше передавать в формате ГГГГММДД. При передаче параметров со временем следует обратить внимание на потерю точности. Для сохранения точности значения лучше передавать через временную таблицу.
4. Передача параметров с плавающей десятичной точкой имеет те же проблемы, что и передача времени внутрь построенного запроса.
5. Строковые значения – потенциально опасный код. Для начала внутри строки все одинарные кавычки должны быть продублированы. Сама строка заключена в одинарные кавычки.
Пример ошибочного кода:
6. Подстановка списков в секцию IN. Основная опасность – пустой список. В этом случае секция будет иметь вид типа ‘поле IN ()’, что при компиляции вызовет ошибку. Как метод борьбы: в начало списка всегда включать NULL или заменить пустую строку на NULL. NULL можно сравнивать с любым типом данных. Сравнение с NULL всегда дает отрицательный результат, но при этом список гарантированно не пустой.
Вот пример безопасной передачи сложных параметров через временную таблицу:
Ну и на закуску маленькие хитрости:
Передаваемые параметры лучше вначале объявить через переменные, инициализировать эти переменные и уже эти переменные использовать в ходе вычислений. В этом случае повышается читаемость текста запроса и отлаживать его легче.
Если обходится без переменных, то можно использовать следующий метод:
Кроме вышеперечисленных особенностей есть еще пару способов передачи параметров:
1. Использовать sp_executesql (как правильно мне подсказали в комментариях)
2. Обернуть запрос во временную хранимую процедуру и запускать ее. При большом количестве запусков этот способ даже эффективнее.