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

В большинстве случаев из всего, что умеет современная RDBMS, востребованы лишь четыре действия (как от сторожевой собаки) — Сидеть! Ждать! Взять! Ко мне! (C-R-U-D).

Если вы продолжаете использовать полный набор возможностей движков баз данных, используете осознанно DSQL, но при этом хотите сделать всю конструкцию безопаснее - читаем дальше.

Я рассмотрю достаточно типичный пример, когда интерфейс поиска собирает пользовательский ввод, строит по нему предикат, и отправляет предикат на бек в T-SQL синтаксисе, чтобы дальше с его участием составить и выполнить T-SQL выражение динамически. Такой пример без валидации или параметризации запроса для решения в статике будет казаться либо наивным, либо надуманным - но он будет хорошо иллюстрировать принцип:

• выполнение потенциально опасного кода в контексте с ограниченными правами

• гарантированно-надежная передача результата в привилегированный контекст

• безопасное завершение и выдача результата статическим кодом в привилегированном контексте

Итак, пусть у нас имеется таблица сотрудников с банальным названием Employee следующего вида:

CREATE TABLE [dbo].[Employee] (
	[EmployeeId]       [INT] PRIMARY KEY CLUSTERED IDENTITY(1, 1),
	[FirstName]        [NVARCHAR](100) NOT NULL,
  	[LastName]         [NVARCHAR](100) NOT NULL,
	[HireDate]         [DATE] NOT NULL,
	[DismissDate]      [DATE] NULL,
	[DepartmentID]     [INT] NOT NULL,
    [Email]            [VARCHAR(256)] NULL,
     . . .
     . . .
)

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

CREATE PROCEDURE dbo.EmployeeSearch (
	@Predicate	VARCHAR(4096) = NULL,	-- все, что идет после WHERE
	@OrderList	VARCHAR(256) = NULL,	-- все, что идет после ORDER BY
	@Offset		INT = NULL,				-- сколько строк пропустить перед выдачей
	@Count		INT = NULL				-- сколько строк выдать
)
AS
BEGIN

	-- валидируем и приводим параметры к ОДЗ 
    SELECT
		@Predicate		= ISNULL(TRIM(@Predicate), '1 = 1'),
		@OrderList		= ISNULL(TRIM(@OrderList), 'EmployeeID'),
		@Offset			= GREATEST(@Offset, 0),
		@Count			= GREATEST(LEAST(ISNULL(@Count, 10), 100), 5)

    -- составляем выражение..
	DECLARE @DSQL VARCHAR(MAX) = 
		CONCAT_WS(' ',
			'SELECT *',
			'FROM Employee',
			'WHERE', @Predicate,
			'ORDER BY', @OrderList,
			'OFFSET', @Offset, 'ROWS',
			'FETCH NEXT', @Count, 'ROWS ONLY'
		)

	-- .. и выполняем его
    EXEC (@DSQL)

END

Выполняем мы это примерно так:

EXEC dbo.EmployeeSearch 
               'DismissDate IS NULL AND LastName LIKE ''%AB%''', 
               'LastName, FirstName', 
                20, 10

Собственно, все будет работать, пока злой юзер не пропихнет через набор полей ввода условий поиска стандартные кракозябры хакера типа '' & '; drop database', или например, исказит выдачу в свою пользу (поменяв, скажем отдел сотрудника или его дату увольнения). Можно пытаться валидировать входные строковые параметры, чтобы минимизировать вероятность взлома — но на всякую хитрую * найдется болт с резьбой — вспомните document.write('<scr' + 'ipt') на заре Интернета — поэтому мы пойдем другим путем — выполним DSQL in-vitro, чтобы ни одна злая кракозябра не смогла ничего сделать за пределами пробирки.

Сейчас пошагово, что нам для этого нужно:

-- создаем низкоприоритетного юзера
CREATE USER dsql_user WITHOUT LOGIN
-- если нужно, можно отнять у него членство во всех ролях и
-- и забрать доступ ко всей метаинформации БД
-- для этого курите BOL 

-- создаем схему (песочницу), в которой будет барахтаться DSQL
CREATE SCHEMA dsql_sandbox AUTHORIZATION dsql_user

-- это неочевидно, зачем схеме иметь овнера dbo, но об этом ниже
ALTER AUTHORIZATION ON SCHEMA::dsql_sandbox TO dbo

-- тоже необязательно, но позволяет делать меньше правок
-- в существующем DSQL (об этом тоже ниже)
ALTER USER dsql_user WITH DEFAULT_SCHEMA = dsql_sandbox

-- даем песочному юзеру выбирать из всего, что мы в эту схему положим
GRANT SELECT ON SCHEMA::dsql_sandbox TO dsql_user

То есть мы создали специального юзера, который ничего нигде не может, кроме как делать SELECT из тех объектов, которые есть (будут) в схеме. И этот юзер видит объекты в схеме без префикса, как это делает юзер dbo для основной схемы dbo — что дает ему полную иллюзию, что он живет в полноценном мире, пока он не стукнется о стенку аквариума.

Теперь создадим то, что он может видеть в схеме и с чем только сможет работать, чтобы было над чем выполнять DSQL:

CREATE VIEW dsql_sandbox.Employee 
AS
	SELECT E.EmployeeId,
           E.FirstName,
           E.LastName,
           E.HireDate,
           E.DismissDate,
           E.EmploymentTypeID,
           E.Email
	FROM dbo.Employee E

Догадываетесь? Мы имитируем таблицу в защищенной области через вьюху (все равно юзер может делать только SELECT) и заодно экспозим в ней только те поля, по которым может идти поиск и/или сортировка (но не выдача!)

Песочница и объект для работы лопаточкой в ней готовы, теперь нам нужно подумать о том, как извлечь пользу из этой работы и безопасно передать результат в большой мир, так, чтобы его невозможно было подпортить изнутри — очевидно ведь, что даже если наш SELECT выполняется в песочнице только над отображением таблицы Employee, ничто не мешает вредителю подправить выборку так, чтобы возвращались искаженные данные, добавив инъекцией что-нибудь к выводимым данным, или вообще заменив их на свои. Решение есть, и оно основано на двух свойствах:

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

2. Есть замечательная форма обмена большим количеством данных между динамикой и статикой (и это не XML и не Mr. JSON):

INSERT INTO @X (A, B, C ..., Y)
EXEC (@SQL)  | EXEC <stored proc>

Ко второму пункту есть два ограничения —
а) озвращаемый в результате выполнен ия EXEC резалтсет должен по типу и количеству полей соответствовать списку, принимаемому в INSERT (это просто), и
б) все, что выполняется под EXEC, не должно иметь внутри подобных попыток граббинга результата из потока резалтсета — то есть внутри не должно быть ни INSERT .. EXEC, ни OUTPUT-конструкций.

Если вы к этому готовы, то идем дальше. Понятно, что вот этот INSERT ... EXEC и есть наш гематоэнцефалогический барьер — все что в EXEC — потенциально опасное, но принимать из крови в чистый мозг мы будем только то, что можем простым способом отвалидировать — и это — набор первичных ключей. Итого, осталось три шага:

1. выполнить код поиска записей по предикату, упорядоченный по условию сортировки

2. забрать ключи выборки, сохраняя порядок

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

Шаг 1 нужно сделать в песочнице, шаги 2 и 3 — уже в высокоприоритетном окружении. Чтобы не делать две процедуры — одну высокоприоритетную, другую - песочную, сменим контекст безопасности прямо в основной, используя крутые штуки MS SQL:

		EXECUTE AS USER = 'dsql_user'
        -- здесь мы уже в песочнице
        . . . .
        . . . .
        REVERT
        -- а вот здесь уже снова в высокопривилегированном контексте

Если кто сомневается, вот проверка:

Еще соображение — ключи можно забирать в табличную переменную, чтобы сделать код полностью детерминированным в design time — временные таблицы такой детерминации не дают. Это абсолютно нормально, и даже правильно, если размер записи табличной переменной невелик, как и количество записей, плюс мы правильно указываем свойства уникальности и упорядоченности для такой переменной‑таблицы — что облегчает дальнейшую работу с ней. По счастью, наш ключ компактный, а количество записей при постраничной выборке — абсолютно микронное для использования таблиц без статистики в качестве буфера:

	DECLARE @KeyBuff TABLE (
		RowOrder	INT PRIMARY KEY CLUSTERED IDENTITY (1, 1),
		TablePK		INT NOT NULL UNIQUE
	)

Попутно используем свойство identity, чтобы сохранить порядок выдачи через порядок вставки записи, и использовать его потом. Используем все встроенные возможности MS SQL по валидации наших данных — синтаксис табличных переменных позволяет объявлять поля, как уникальные независимо от объявления ПК. И это нам поможет от попытки размножить выходные записи вставкой нескольких одинаковых значений EmployeeID в поле TablePK — поэтому не пренебрегаем UNIQUE.

<off>Один умный человек мне как-то сказал — «используй все возможные средства обозначения характера твоих данных — пусть у SQL сервера будет возможность оперировать ими эффективнее, если он сочтет нужным или сможет использовать твою разметку». С тех пор я следую этому правилу, и всегда ставлю nullability, ключи, уникальность и констрейнты там, где их можно поставить, включая и табличные переменные ;)</off>

Но мы отвлеклись. Пора собирать все вместе в процедуру. Вот она:

CREATE PROCEDURE dbo.EmployeeSearch_Safe (
    @Predicate	VARCHAR(4096) = NULL,	-- все, что идет после WHERE
	@OrderList	VARCHAR(256) = NULL,	-- все, что идет после ORDER BY
	@Offset		INT = NULL,				-- сколько строк пропустить перед выдачей
	@Count		INT = NULL				-- сколько строк выдать
)
AS
BEGIN

	DECLARE @TablePKName	VARCHAR(32) = 'EmployeeID'    -- это наш ПК

    -- валидируем и приводим параметры к ОДЗ 
    SELECT
		@Predicate		= ISNULL(TRIM(@Predicate), '1 = 1'),
		@OrderList		= ISNULL(TRIM(@OrderList), @TablePKName),
		@Offset			= GREATEST(@Offset, 0),
		@Count			= GREATEST(LEAST(ISNULL(@Count, 10), 100), 5)

    -- буфер для приема ключей из песочницы
    DECLARE @KeyBuff TABLE (
		RowOrder	INT PRIMARY KEY CLUSTERED IDENTITY (1, 1),	-- identity помнит порядок вставки
		TablePK		BIGINT NOT NULL UNIQUE
	)

    -- составляем выражение (почти ничего переделывать не нужно
    -- относительно предыдущей небезопасной имплементации)
	DECLARE @DSQL VARCHAR(MAX) = 
		CONCAT_WS(' ',
			'SELECT',
			@TablePKName,
  			'FROM Employee',        -- этот Employee на самом деле вьюха из sandbox
			'WHERE', @Predicate,
			'ORDER BY', @OrderList,
			'OFFSET', @Offset, 'ROWS',
			'FETCH NEXT', @Count, 'ROWS ONLY'
		)

	-- vvvv ---- выполняем под try/catch чтобы переключить обратно контекст при неудаче
	BEGIN TRY	

		-- переходим в песочницу
		EXECUTE AS USER = 'dsql_user'

		-- заполняем таблицу с ключами по результатам выполнения DSQL
		INSERT INTO @KeyBuff ( TablePK )
		EXEC (@DSQL)							

	END TRY
	BEGIN CATCH
		
		-- поймали ошибку, можем ее отправить в лог или еще куда-нибудь для анализа
        -- в самой процедуре можно ничего не делать
        -- просто быть уверенным, что при любой ошибке из песочницы процедура
        -- ничего не возвращает
		DELETE FROM @KeyBuff

	END CATCH

	-- переключаемся обратно на привилегированного юзера
	REVERT
	-- ^^^^ ---- 

	-- теперь мы в опасном контексте, но у нас дальше только
    -- статический SQL и следовательно, все безопасно
    SELECT 
		E.*
	FROM Employee E
	INNER JOIN @KeyBuff KB ON KB.TablePK = e.EmployeeId
	ORDER BY KB.RowOrder
	
END

Вот и все.

PS. Malicious user может нас за DDosить, соорудив через инъекцию какой-нибудь многоэтажный cross join вьюхи Employee с самой собой. Защититься от массовой вставки в таблицу-буфер от этого можно устаревшей, но надежной конструкцией — вставив SET ROWCOUNT @Count непосредственно перед INSERT..EXEC и поставив SET ROWCOUNT 0 после CATCH. В отличие от SELECT TOP количество вставок управляется из безопасного слоя, поэтому ему можно будет доверять.