Динамическое построение запросов

Форматирование пользовательского ввода для динамического запроса

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
   Handles Button1.Click 
   Dim baseSQL As String = "SELECT {0} FROM {1}" 
   Dim sbFields As New System.Text.StringBuilder 
   Dim numChecked As Integer = 0 
   With sbFields
     For Each el As ListViewItem In ListView1.Items 
       If el.Checked Then
         numChecked = numChecked + 1 
         If .Length <> 0 Then
            .Append(",") 
         End If
         Console.WriteLine(el.Text) 
         AppendFormat("[{0}]", el.Text) 
       End If 
     Next 
   End With
   Console.WriteLine(sbFields) 
End Sub

Строим и выполняем динамический запрос

Dim tblsql As String 
  Dim txtsql As String
  tblsql = String.Format("{0}.{1}", SchemaName, TableName)
  txtsql = String.Format(baseSQL, _ 
           sbFields.ToString, tblsql)

Dim MyConnection As New SqlClient.SqlConnection( _
           "Data Source=.\SQLExpress;"  _
           "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") 
  Dim com As New SqlClient.SqlCommand(txtsql, MyConnection) 
  Dim tableResults As New DataTable tableResults.Clear()
  Dim da As New SqlClient.SqlDataAdapter(com) 
  
  da.Fill(tableResults) 
  For i As Integer = 0 To tableResults.Rows.Count - 1
    With tableResults.Rows.Item(i)
      Dim rowstr As New System.Text.StringBuilder 
      For j As Integer = 0 To numChecked - 1 
        rowstr.Append(.Item(j)) 
        rowstr.Append(" ") 
      Next
      Console.WriteLine(rowstr) 
    End With
    If i > 100 Then Exit For 
  Next

Выполнение динамического запроса от имени другой учетной записи

Одной из лучших методик обеспечения безопасности считается запрещение разрешения READ для таблиц с целью предотвращения несанкционированного доступа, поэтому иногда приложению приходится выполнять запрос, динамически сгенерированный пользователем с использованием других учетных данных.

В SQL Server 2005 для того, чтобы действовать от имени другого пользователя в рамках определенной команды, можно выполнить команду EXECUTE. Синтаксис команды EXECUTE:

EXECUTE ("<dynamic query>") as 
        USER='<User Name>'

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

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

Добавление порядка сортировки в динамический запрос

Чтобы выполнить сортировку динамического запроса, просто добавьте предложение ORDER BY, указав после него список столбцов в порядке, выбранном пользователем. Если пользователь хочет получить информацию в порядке убывания, добавьте также ключевое слово DESC.

В следующем коде упорядоченные выборки сохраняются в специальной коллекции, которая называется SortInfo ; это особый пользовательский класс, предназначенный для хранения всей информации, необходимой для каждой упорядоченной выборки.

Public Class SortInfo
Public SchemaName As String
Public TableName As String
Public ColumnName As String
Public SortOrder As SortOrder
Public SortPosition As Integer End Class

Чтобы создать упорядоченный список, код проходит по этому списку, добавляя определение в StringBuilder, и в случае, если задано какое-либо упорядочение, добавляет предложение ORDER TO в начале содержимого StringBuilder.

For Each o As System.Collections.Generic.KeyValuePair( _ 
           Of Integer, SortInfo) In OrderList 
  With sbOrderBy
    If .Length <> 0 Then
       .Append(",") 
    End If
    .AppendFormat("[{0}].[{1}]", o.Value.TableName, _
                  o.Value.ColumnName) 
    If o.Value.SortOrder = SortOrder.Descending Then
       .Append(" DESC") 
    End If 
  End With 
Next 
If sbOrderBy.Length > 0 Then
  sbOrderBy.Insert(0, " ORDER BY ") 
End If

Фильтрация динамического запроса

Добавить фильтр в запрос немного сложнее, чем добавить порядок сортировки - приходится иметь в виду много различных операторов и типов данных. Но результат стоит затраченных усилий. Как и раньше, нашей исходной точкой будет базовый синтаксис предложения WHERE, задающего фильтрацию в инструкции SELECT.

SELECT <Field List> 
   FROM <Table Name> 
   WHERE <Condition List> 
   ORDER BY <Order List>

Список условий следует проектировать с учетом следующих рекомендаций:

Необходимо реализовать для пользователей интерфейс, позволяющий указать значения и операторы сравнения для каждого фильтра. Цель -получить пользовательский ввод и создать строку с условиями фильтрации, которую можно будет вставить в динамический запрос.

Законченный пример - приложение Dynamic Query

Следующие примеры основаны на примере приложения Dynamic Queries, которое находится в папке Filters в файлах примеров к 3 лекции. Пример приложения Dynamic Queries - это расширенная версия приложения, которое мы создали в этой лекции. Это приложение включает элемент управления ListView, который показывает все настройки, сделанные пользователем для создания динамического запроса, как показано на рис. 3.1. Чтобы попробовать сделать то же самое, выполните действия, описанные ниже.

Запускаем пример приложения Dynamic Queries

Дважды щелкните файл Ch07.sln в папке Chapter07\Filter, чтобы открыть проект в Visual Studio.

Выберите команду Start Debugging (Начать отладку) из меню Debug (Отладка). Выберите сервер и нажмите кнопку ОК, Visual Studio построит и запустит проект, отобразив начальную форму, как показано ниже.

В форме frmPpal щелкните раскрывающийся список Data в панели инструментов, а затем выберите Table Sales, Customer.

Выберите элементы CustomerID, TerritoryID и AccountNumber в поле со списком и нажмите кнопку "Стрелка вправо" в центре формы.

Нажмите кнопку Build Query на панели инструментов. Теперь окно программы вверху справа отображает поля, которые вы выбрали, а внизу справа - соответствующий запрос SQL, как показано на рисунке.

Щелкните правой кнопкой мыши в строке CustomerID и выберите из контекстного меню команды Order, Ascending.

Щелкните правой кнопкой мыши в строке CustomerID и выберите из контекстного меню команды Order, Ascending. В диалоговом окне Filter выберите в раскрывающемся списке Less_Than и введите в текстовое поле 20. Нажмите кнопку ОК.

Снова нажмите кнопку Build Query на панели инструментов. Теперь динамический запрос выглядит следующим образом:

SELECT [CustomerID],[TerritoryID],[AccountNumber] 
   FROM Sales.Customer WHERE [CustomerID] < 20 
   ORDER BY [Customer].[CustomerID]

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

Как пример приложения строит строку фильтрации

Это приложение хранит различные значения, показанные в правой верхней части формы, в элементе управления ListView с именем lvUseFields, показанном в табл. 3.1.

Когда вы нажимаете кнопку Build Query на панели инструментов, следующий код выполняет построение динамического запроса, используя содержимое элемента управления ListView lvUseFields. Обратите особое внимание на ту часть кода, которая показана полужирным шрифтом и демонстрирует построение предложения WHERE для фильтрации динамического запроса.

Private Sub tsbGen_Click(ByVal sender As System.Object, _ 
                         ByVal e As System.EventArgs) 
  Handles tsbGen.Click 
  Dim baseSQL As String = "SELECT {0} from {1}" 
  "Список отображаемых полей 
  Dim sbFields As New System.Text.StringBuilder           
  "Порядок списка столбцов 
  Dim sbOrderBy As New System.Text.StringBuilder("")
  "Фильтры
  Dim sbFilter As New System.Text.StringBuilder("")
  
  With sbFields
    For Each el As ListViewItem In lvUseFields.Items 
      If .Length <> 0 Then
        .Append(",") 
      End If
      "Добавляем в список полей 
      Column .AppendFormat("[{0}]", _
                       el.SubItems(UseFieldsColumnsEnum.Field).Text) 
      "Если Name не совпадает с именем столбца, добавляем Alias 
      If el.SubItems(UseFieldsColumnsEnum.Name).Text <> _
                     el.SubItems(UseFieldsColumnsEnum.Field).Text Then
        .AppendFormat(" AS [{0}]", _
        el.SubItems(UseFieldsColumnsEnum.Name).Text) 
      End If 
      "Если существует фильтр...
      If el.SubItems(UseFieldsColumnsEnum.Filter).Text <> "" Then 
         With sbFilter
           If .Length > 0 Then
             .Append(" AND ") End If
             "Добавляем имя столбца в список фильтров 
             .AppendFormat("[{0}]", el.SubItems( _
                           UseFieldsColumnsEnum.Field).Text) 
             "Добавляем оператор 
             Select Case CType([Enum].Parse( _
                         GetType(FilterTypeEnum), _
                         el.SubItems(UseFieldsColumnsEnum.Filter _
                         ).Text.Replace(" ", "_")), FilterTypeEnum)
               Case FilterTypeEnum.Equal 
                  .Append(" = ")
               Case FilterTypeEnum.Not_Equal 
                  .Append(" <> ")
               Case FilterTypeEnum.Greather_Than 
                  .Append(" >")
               Case FilterTypeEnum.Less_Than 
                  .Append(" < ")
               Case FilterTypeEnum.Like 
                  .Append(" LIKE ")
               Case FilterTypeEnum.Between 
                  .Append(" BETWEEN ") 
             End Select
             "Получаем тип данных из определений столбцов 
             Dim Typename As String = _
                 tableColumns.Select(String.Format("COLUMN_NAME='{0}'", _
                 el.SubItems(UseFieldsColumnsEnum.Field).Text))(0).Item( _
                 "DATA_TYPE").ToString 
             "Если тип данных принадлежит к типам с символьными значениями,
             "значение следует заключить в апострофы 
             If Typename.ToUpper.IndexOf("CHAR") > -1 _
                OrElse Typename.ToUpper.IndexOf("TEXT") > -1 Then
               .Append(""") 
             End If
             .Append(el.SubItems(UseFieldsColumnsEnum.Filter_Value).Text) 
             "Если оператор с Like, добавляем групповой символ "%" 
             If CType([Enum].Parse(GetType(FilterTypeEnum), _
                      el.SubItems(UseFieldsColumnsEnum.Filter).Text.Replace( _ 
                      " ", "_")), FilterTypeEnum) = FilterTypeEnum.Like Then 
              .Append("%") 
             End If
             If Typename.ToUpper.IndexOf("CHAR") > -1 _ 
                 OrElse Typename.ToUpper.IndexOf("TEXT") > -1 Then .Append(""") 
           End If 
         End With 
       End If 
     Next 
  End With
  "Добавляем порядок
  For Each o As System.Collections.Generic.KeyValuePair( _ 
              Of Integer, SortInfo) In OrderList 
    With sbOrderBy
       If .Length <> 0 Then
         .Append(",") 
       End If
       .AppendFormat("[{0}].[{1}]", o.Value.TableName, o.Value.ColumnName) 
       If o.Value.SortOrder = SortOrder.Descending Then
         .Append(" DESC") 
       End If 
     End With 
  Next 
  If sbOrderBy.Length > 0 Then
    sbOrderBy.Insert(0, " ORDER BY ") 
  End If
  If sbFilter.Length > 0 Then
    sbFilter.Insert(0, " WHERE ") 
  End If
  "Строка запроса должна выглядеть так: SELECT columns FROM table, 
  "затем WHERE и в завершение ORDER 
  txtsql.Text = _ 
         String.Format(baseSQL, _
                      sbFields.ToString, _
                       String.Format("{0}.{1}", SchemaName, TableName))  _
                       sbFilter.ToString  " "  sbOrderBy.ToString 
End Sub

Разбор форматирования строки фильтрации

При построении фильтра для каждого столбца, тип данных которого включает символы ( 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.

Теперь вам известно, какое ядро базы данных работает на сервере, знаете имя сервера, но что еще важнее, вы знаете, что сервер может выполнить ваши команды. Если вы захотите предоставить себе максимальные привилегия на этом сервере, вы можете создать своего пользователя!

Следующий код создает пользователя 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

Теперь вы хорошо понимаете, что может произойти, поэтому потратьте немного усилий и времени на то, чтобы защитить вашу базу данных от этого вида атак; для этого выполните следующие рекомендации:

SELECT ProductNumber,
Name,
ListPrice FROM Production.Product WHERE (Name LIKE @Param1 + "%")

Как использовать процедуру 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. Эту методику можно использовать не только для задач, связанных с простым возвращением данных пользователю.

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
   FROM INFORMATION_SCHEMA.TABLES