Динамическое построение запросов
Разбор форматирования строки фильтрации
При построении фильтра для каждого столбца, тип данных которого включает символы ( char, nchar, varchar, nvarchar, text, or ntext ), значения, сравниваемые с этим столбцом, должны быть заключены в символы апострофа (" ).
При фильтрации типов данных smalldatetime или datetime код зависит от региональных и языковых параметров компьютера пользователя. Можете ли вы сказать, что дата " 11/10/05 " означает 10 ноября 2005 года или 11 октября 2005 года или, может быть, 5 октября 2011 года? Это может стать настоящей проблемой для любого приложения, но задача еще усложняется, если вы создаете приложение для интернета. В этом случае следует учитывать пользователей по всему миру.
Самым простым решением может быть инструкция для пользователя по поводу того, в каком формате следует вводить дату. Но было бы лучше иметь решение, которое не требовало бы обучения пользователя. SQL Server использует форматы даты и времени, определяемые региональными и языковыми настройками операционной системы, на которой выполняется сервер. Тем не менее, он всегда распознает формат ISO, который соответствует следующему шаблону: гггг-мм-дд.
Если вы примените на компьютере пользователя параметр CultureInfo, то можете получить значение DateTime. Используйте это значение для создания адекватного сравниваемого значения в вашем фильтре. Кроме того, просто для того, чтобы быть уверенным, что SQL Server была отправлена правильная строка, можно использовать функцию CONVERT. Вот как это делается:
Dim theDate As Date = DateTime.Parse(el.SubItems(1).Text, _
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)
sparam = String.Format("Convert(datetime,'{0}-{1}-{2}',120)", _
theDate.Year, _
theDate.Month, _
theDate.Day)Процедура tsbGen_Click генерирует следующий динамический запрос с использованием полей, показанных на рис. 3.1.
SELECT [Name], [ProductNumber], [Color], [ListPrice], [Size], [Weight], [Style] FROM Production.Product WHERE [ListPrice] >100 ORDER BY [Product].[Name]
Параметры и безопасность динамических запросов
Создание запросов, использующих вводимые пользователем значения, подвергает риску безопасность системы, особенно если приложение является открытым веб-сайтом; в этом случае вы не можете знать, кто ваши пользователи и какими знаниями они обладают. Если пользователь знает синтаксис SQL, он может проникнуть в вашу базу данных с помощью метода, который получил говорящее название - атака "SQL-injection" (инъекция SQL).
Принцип атаки SQL-injection
Давайте рассмотрим очень простой пример. Представьте себе открытый веб-сайт, который разрешает пользователям выполнять поиск продукции через интернет. На этом сайте приложение дает возможность пользователю выполнять поиск по части названия, поэтому оно создает и использует простые динамические запросы, подобные приведенному ниже:
SqlDataSource1.SelectCommand = _
"SELECT ProductNumber, Name, ListPrice " _
"FROM Production.Product WHERE Name LIKE "" _
& TextBox1.Text & "%'" Me.GridView1.DataBind()Пользователь может ввести первые символы имени в текстовое поле Search By (Поиск по), и код будет искать все изделия, названия которых начинаются с этих символов, как показано на рис. 3.2.
Итак, как эксперт по SQL, даже если вы ничего не знаете о приложении, вы можете представить себе, что незаметно для пользователя выполняется код, аналогичный тому, о котором мы говорили в этой лекции. Можно проверить, правы ли вы. Что произойдет, если вы введете в текстовое поле Search By следующую строку?
a' UNION select @@Version, @@SERVERNAME, 0;-
На самом деле вы получите результат, показанный на рис. 3.3, потому что простодушное приложение вставит пользовательский ввод в следующую инструкцию SQL:
Рис. 3.2. Простое приложение, которое использует текст из текстового поля для создания динамического запроса
SELECT ProductNumber,
Name,
ListPrice
FROM Production.Product
WHERE Name LIKE "a"
UNION select @@Version,@@SERVERNAME,0;-%'Обратите внимание на символ закрывающей кавычки ( " ) и символы комментариев ( - - ), которые хитроумный пользователь использовал для трансформации динамического запроса в несколько корректных инструкций SQL.
Теперь вам известно, какое ядро базы данных работает на сервере, знаете имя сервера, но что еще важнее, вы знаете, что сервер может выполнить ваши команды. Если вы захотите предоставить себе максимальные привилегия на этом сервере, вы можете создать своего пользователя!
Совет. Если вам трудно запомнить точный синтаксис определенной команды, можно воспользоваться SQL Server Management Studio и создать сценарий, а затем использовать его, чтобы освежить память.Следующий код создает пользователя BadBoy и добавляет его в серверную роль sysadmin!
USE [master] CREATE LOGIN [BadBoy] WITH PASSWORD='Mad', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF EXEC master.sp_addsrvrolemember @loginame = "BadBoy", @rolename = "sysadmin"
Если ввести его в текстовое поле с закрывающей кавычкой ( " ) в конце предложения WHERE и символами комментария для нейтрализации закрывающей кавычки, вставленной приложением, приложение будет вынуждено сгенерировать и выполнить следующий код для добавления нового пользователя.
SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE "a"; USE [master]; CREATE LOGIN [BadBoy] WITH PASSWORD='Mad', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; EXEC master..sp_addsrvrolemember @loginame = "BadBoy", @rolename = "sysadmin"; -%'
Кроме того, можно проверить, был ли создан этот пользователь, если ввести в текстовое поле следующий код:
a' UNION SELECT name, CONVERT(nvarchar(1), sysadmin) AS IsAdmin, 0 AS Expr1 FROM sys.syslogins WHERE (name = "BadBoy")-%' " В результате получается следующая инструкция SQL: SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE "a" UNION SELECT name, CONVERT(nvarchar(1), sysadmin) AS IsAdmin, 0 AS Expr1 FROM sys.syslogins WHERE (name = "BadBoy")-%'
Как предотвратить атаку типа SQL-injection
Теперь вы хорошо понимаете, что может произойти, поэтому потратьте немного усилий и времени на то, чтобы защитить вашу базу данных от этого вида атак; для этого выполните следующие рекомендации:
- Старайтесь избегать построения инструкций SQL, используйте вместо этого указание отдельных параметров. В приложениях, подобных тому, что мы только что рассмотрели, используйте следующую инструкцию SQL, которая имеет параметр, и выполните привязку этого параметра к текстовому полю, чтобы текст в нем расценивался только как строка. Любая команда в этом поле не будет выполняться.
SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE (Name LIKE @Param1 + "%")
- По возможности храните запросы в хранимых процедурах.
- Чтобы передать запрос SQL Server, используйте процедуру sp_ExecuteSql. Об этой системной хранимой процедуре рассказывается в следующем разделе.
Как использовать процедуру spExecuteSql
Системная хранимая процедура sp_executeSql позволяет выполнять динамически определяемые инструкции T-SQL по тому же принципу, который использует команда EXECUTE. Однако sp_executeSql требует, чтобы вы указали параметры и типы данных, а также значения для этих параметров.
При первом выполнении запроса системная хранимая процедура sp_executeSql создает и сохраняет план выполнения. Поскольку необходимо определить параметры, при каждом последующем выполнении запроса sp_executeSql просто обновляет значения параметров и использует один и тот же план выполнения.
Поскольку системная хранимая процедура нуждается в определении параметров, можно создавать сложные запросы не только для инструкций SELECT, но и для любых действий в отношении базы данных, которые требуются вашему приложению. Ваши инструкции могут содержать даже параметры вывода.
Предположим, что нужно указать новую причину задержки выпуска изделий в базе данных Adventure Works, при этом новую причину следует указать для всех заказов, ожидаемая дата поставки которых больше, чем конечная дата выполнения заказа. Для выполнения двух этих задач в одном пакете можно использовать следующий сценарий T-SQL. Этот код можно найти в файлах примеров под именем newReason.sql.
-Переменная для инструкции T-SQL
DECLARE @sql nvarchar(300);
SET @sql='INSERT INTO [AdventureWorks].[Production].[ScrapReason]
([Name]
,[ModifiedDate])
VALUES
(@NewNameSQL ,GetDate());"
SET @sql=@sql + "SET @NewIdSQL=(SELECT ScrapReasonID " +
"FROM [AdventureWorks].[Production].[ScrapReason] " +
"WHERE Name=@NewNameSQL)"
-Переменная для получения нового идентификатора
DECLARE @NewId int;
-Объявляем параметры
DECLARE @Params nvarchar(200);
SET @Params='@NewNameSQL nvarchar(100), @NewIdSQL int OUTPUT';
-Добавляем новое имя
DECLARE @NewName nvarchar(100);
SET @NewName='Delayed
Production';
-Выполнение вставки в ScrapReason
EXEC sp_executeSql @sql,@params,@NewNameSQL=@NewName,
@NewIdSQL=@NewId OUTPUT;
/*
Заменяем инструкцию T-SQL для обновления столбца ScrapReasonID
на указанную новую инструкцию для всех записей, в которой конечная дата
выполнения заказа превышает ожидаемую дату
*/
SET @sql="UPDATE [AdventureWorks].[Production].[WorkOrder]" +
"SET ScrapReasonID=@NewIdSQL " +
"WHERE (EndDate > DueDate) AND (ScrapReasonID IS NULL)";
-Определяем параметр для этого нового предложения
SET @Params='@NewIdSQL int ";
EXEC sp_executeSql @sql, @params, @NewIdSQL=@NewId;
GOКак видите, системная хранимая процедура sp_executeSql позволяет применить параметры к динамическим инструкциям SQL, не являющимся простыми запросами SELECT.
Заключение
Прочитав эту лекцию, вы узнали, как собирать пользовательский ввод и форматировать его для динамического создания запросов. Вы получили информацию о некоторых недостатках построения динамических запросов, а также о потенциальном риске для безопасности, который с ними связан.
Чтобы сохранить в безопасности базу данных, используйте параметризованные запросы и выполняйте их через хранимую процедуру sp_executeSql. Эту методику можно использовать не только для задач, связанных с простым возвращением данных пользователю.