Microsoft Security Bulletin (MS02-007)
20 февраля 2002г Микрософт выпущен бюллетень "SQL Server Remote Data Source Function Contain
Unchecked Buffers". Исправлена уязвимость, которая позволяла злоумышленнику, при наличии
специальных OLE DB соединений с удалёнными источниками данных (без организации прилинкованного
сервера), получить права в контексте безопасности учётной записи, от имени которой стартует
MSSQLServer, за счёт отсутствия контроля переполнения буфера.
Затронутые продукты:
Microsoft SQL Server 7.0
Microsoft SQL Server 2000
Обоснование использования динамических SQL запросов
По материалам статьи Robert Marda на сайте sqlservercentral.com:
When to Use Dynamic SQL
Динамический SQL запрос - это некоторый код, который создаётся и сохраняется в
переменной, пока не возникнет необходимость его выполнения. Большинство DBA и разработчиков
предпочитают не использовать динамические SQL запросы по целому ряду причин. Среди наиболее типичных
таких причин то, что план выполнения динамического запроса не будет кэшироваться и то, что
безопасность должна устанавливаться другими способами. Тем не менее, автор использует динамические
запросы всякий раз, когда, считает, что это лучший способ получения результата, и твёрдо полает,
что для динамических запросов есть место почти в каждой базе данных.
Таблицы, необходимые для демонстрационных примеров
С помощью представленных ниже скриптов, можно создать необходимые для демонстрационных примеров
таблицы и заполнит их данными:
CREATE TABLE #ColumnNames (ColumnID int IDENTITY (1, 1) NOT NULL, ColumnName
varchar (50) NULL)
CREATE TABLE #ColumnValues (ColumnID int NOT NULL, Value varchar (20) NULL, ClientName varchar
(50) NULL)
INSERT INTO #ColumnNames (ColumnName)
SELECT 'Has Special License' UNION
SELECT 'Dealer'
INSERT INTO #ColumnValues (ColumnID, Value, CLientName)
SELECT 1,'Yes','Database Hobby Shoppe' UNION
SELECT 2,'No','Imaginary Databases Inc' UNION
SELECT 2,'Yes','Super Databases Management'
Пример №1
Этот первый пример показывает, как получить ColumnName из существующей таблицы и использовать
это, как алиас для столбца, который содержит соответствующие данные. Это одно из возможных
решений, с помощью которого можно предоставить возможность пользователям создавать их собственные
столбцы и заполнять их значениями посредством обычной вставки в обе таблицы. В реальных применениях
используются не временные таблицы, как в этом примере. Если Вы решите предоставить возможность
пользователям выполнять подобные операции, единственным способом возвращения им требуемых
результирующих наборов, будет использование ColumnName в динамическом запросе. В примере
представлено одно из возможных решений, с помощью которого Вы можете решить описанную выше
задачу:
DECLARE @Query varchar(300), @ColumnName varchar(50)
SELECT @ColumnName = ColumnName
FROM #ColumnNames
WHERE ColumnID = 2
SET @Query = '
SELECT ClientName, Value AS [' + @ColumnName + ']
FROM #ColumnValues v
INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID
WHERE n.ColumnID = 2'
PRINT @Query
PRINT ''
EXEC (@Query)
Представленный в примере алгоритм полезен, когда Вы хотите передать результат, установленного
кем - то значения, используя ColumnName добавленного пользователем столбца, по аналогии с электронной
таблицей Excel.
Пример №2
Представленный в предыдущем примере алгоритм для ColumnName также может быть применён и к именам
таблиц TableName. Если Вам нужно в запросе или в хранимой процедуре получить данные об указанном
в переменной имени таблицы, используйте нижеследующий алгоритм:
DECLARE @Query varchar(300), @TableName varchar(30)
SET @TableName = '#ColumnValues'
SET @Query = '
SELECT *
FROM ' + @TableName
PRINT @Query PRINT ''
EXEC (@Query)
Строки, выводимые PRINT, продемонстрируют результат работы алгоритма.
Пример №3
Довольно часто для разграничения списка в переменной применяют запятую, когда необходимо
использовать ключевое слово IN для ограничения результирующего набора. Подобный запрос может
иметь следующий вид:
DECLARE @InList varchar(30)
SET @InList = '1,2'
SELECT *
FROM #ColumnValues
WHERE ColumnID IN (@InList)
Если Вы используете только одинарные цифры в переменной @InList, представленный выше запрос будет
работать. Конечно, если будет использоваться эквивалентный признак для ключевого слова IN. SQL
сервер попытается конвертировать список в int, что заработает только если в списке будет одинарные
цифры.
Есть несколько способов разрешить эту проблему. Ниже показано, как можно использовать для этого
динамический запрос:
DECLARE @InList varchar(30), @Query varchar(300)
SET @InList = '1,2'
SET @Query = '
SELECT *
FROM #ColumnValues
WHERE ColumnID IN (' + @InList + ')'
PRINT @Query PRINT ''
EXEC (@Query)
Строки, выводимые PRINT, используются только для демонстрации просмотра переменной @InList.
Пример №4
Когда разграниченный запятыми список не является списком из чисел, необходимо будет внести небольшие
коррективы, для учёта специфики такого списка:
DECLARE @InList varchar(30)
SET @InList = 'yes,no'
SELECT *
FROM #ColumnValues
WHERE Value IN (@InList)
Если в SET @InList необходимо использовать значения 'yes' или "no", всё будет выполнено, как надо.
Но, при использовании запятой, SQL сервер будет пытаться вычленять полную символьную строку, куда
попадёт и запятая. Есть много способов разрешения этой проблемы, и все они, кроме одного, не
используют динамический запрос. Рассмотрим метод с динамическим запросом:
DECLARE @InList varchar(30), @Query varchar(300), @Position int
SET @InList = 'yes,no,maybe'
SET @Position = 0
WHILE @Position <> 1
BEGIN
SET @Position = CHARINDEX (',', @InList, @Position)
IF @Position <> 0
BEGIN
SET @InList = STUFF(@InList,@Position,1,''',''')
SET @Position = @Position + 2
END
ELSE
SET @Position = 1
END
SET @Query = '
SELECT *
FROM #ColumnValues
WHERE Value IN (''' + @InList + ''')'
PRINT @Query PRINT ''
EXEC (@Query)
В этом примере, WHILE LOOP используется для вычленения отдельного значения, необходимого для
присвоения переменной @InList, используемой при формировании динамического запроса.
Пример №5
Динамическое формирование большого запроса, основанного на значениях в различных переменных, это
то, что вызывает чувство сомнения у большинства администраторов базы данных и разработчиков.
Автор считает, что использование одного динамического запроса может заменить порой 10, 20 или
более хранимых процедур и избавить от настройки эффективности их исполнения. В следующем примере
Вы жертвуете эффективностью ради удобства разработки запроса:
DECLARE @Query varchar(1000), @ClientName varchar(50), @ColumnName varchar (50),
@ColumnDisplay tinyint, @Debug bit
SET @ClientName = 'y'
SET @ColumnName = ''
SET @ColumnDisplay = 0
SET @Debug = 1
SET @Query = 'SELECT ClientName'
IF @ColumnDisplay > 1
SET @Query = @Query + ',' + CHAR(10) + 'v.ColumnID'
IF @ColumnDisplay > 0
SET @Query = @Query + ',' + CHAR(10) + 'Value'
IF @ColumnDisplay > 0
SET @Query = @Query + ',' + CHAR(10) + 'ColumnName'
SET @Query = @Query + CHAR(10) + 'FROM #ColumnValues v'
IF @ColumnDisplay > 0 OR @ColumnName <> ''
SET @Query = @Query + CHAR(10) + 'INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID'
SET @Query = @Query + CHAR(10) + 'WHERE v.ColumnID > 0'
IF @ClientName <> ''
SET @Query = @Query + CHAR(10) + 'AND ClientName LIKE ''%' + @ClientName + '%'''
IF @ColumnName <> ''
SET @Query = @Query + CHAR(10) + 'AND ColumnName = ''' + @ColumnName + ''''
IF @Debug = 1
BEGIN
PRINT @Query PRINT ''
END
EXEC (@Query)
DROP TABLE #ColumnNames
DROP TABLE #ColumnValues
Этот пример очень показателен. Так как многие из Вас, скорее всего, просто скопируют код примера,
чтобы опробовать его в действии, автор включил команды DROP TABLE, предвидя то, что Вы можете
захотеть изменить значения четырех переменных: @ClientName, @ColumnName, @ColumnDisplay и @Debug.
Это может быть сделано, что бы увидеть, как подобные изменения затрагивают структуру запроса.
Можно добавить @Debug в запрос и хранимую процедуру с динамическим запросом так, чтобы можно было
видеть, как они формируются, причём, без их изменения и с гарантией, что они будут функционировать
нормально для всех, кто их будет использовать.
Вот некоторые значения, которые Вы можете попробовать:
@ColumnName = 'dealer'
@ColumnDisplay = 1 and then = 2
@Debug = 0
@ClientName = ''
Заключение
Есть задачи, когда динамический запрос является самым легким и, возможно, единственным решением и
может исполняться также как статический запрос. Когда эффективность и быстродействие имеют высший
приоритет, тогда Вы должны всячески избегать динамических запросов.
ССЫЛКИ НА СТАТЬИ
Crystal Reports FAQ
Автор: НПО "Компьютер"
Часто задаваемы вопросы...
[подробнее] MS SQL 7.0: Restore database
Автор: А. Шкондин
Восстановление базы данных с использованием WITH MOVE.
Восстановление логинов пользователей базы данных...
[подробнее] FAQ: Разработка на MS SQL Server
Автор: НПО "Компьютер"
Часто задаваемые вопросы по администрированию MS SQL
Server...
[подробнее]