Сохраненная процедура EXEC vs sp_executesql разница?

Я написал две хранимые процедуры с sp_executesql, а у других нет sp_executesql оба выполняются правильно одинаковые результаты, я не понял, в чем разница между

EXEC (@SQL) vs EXEC sp_executesql @SQL, N '@eStatus varchar (12)', @eStatus = @Status

и как EXEC (@SQL) подвержен SQL-инъекциям и sp_executesql @SQL...... не?

Ниже хранимой процедуры без sp_executesql

ALTER proc USP_GetEmpByStatus
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print (@SQL)
EXEC (@SQL)
END

EXEC USP_GetEmpByStatus 'Active'

Ниже хранимой процедуры с sp_executesql

create proc USP_GetEmpByStatusWithSpExcute
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'JProCo.dbo.Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print @SQL
exec sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
END

EXEC USP_GetEmpByStatusWithSpExcute 'Active'

Ответы

Ответ 1

Вероятно, ваш SQL sp_executesql должен быть:

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where [email protected]'

Это позволит вам вызвать sp_executesql с @eStatus в качестве параметра, а не встраивать его в SQL. Это даст преимущество в том, что @eStatus может содержать любые символы, и он будет автоматически автоматически экранироваться базой данных, если это необходимо для обеспечения безопасности.

Сравните это с SQL, необходимым для EXEC;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=' + char(39) + @Status + char(39)

... где a char (39), встроенный в @Status, сделает ваш SQL недействительным и, возможно, создаст возможность SQL-инъекции. Например, если для параметра @Status установлено значение O'Reilly, результатом будет SQL:

select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'

Ответ 2

Помимо использования, существуют некоторые важные отличия:

sp_executesql позволяет параметризировать операторы Поэтому он более безопасен, чем EXEC с точки зрения SQL-инъекции
sp_executesql может использовать кэшированные планы запросов. Строка TSQL создается только один раз, после этого каждый раз, когда тот же запрос вызывается с помощью sp_executesql, SQL Server извлекает план запроса из кеша и повторно использует его
Таблицы Temp, созданные в EXEC, не могут использовать механизм кэширования таблицы temp

Ответ 3

С sp_executesql вам не нужно строить свой запрос таким образом. Вы можете объявить это следующим образом:

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
@TableName + ' where [email protected]'

Таким образом, если ваше значение @Status пришло от пользователя, вы можете использовать @eStatus и не беспокоиться об экранировании '. sp_executesql дает возможность помещать переменные в ваш запрос в строковой форме, а не использовать конкатенацию. Поэтому вам не о чем беспокоиться.

Переменные столбца и таблицы все те же, но менее вероятно, что они будут непосредственно от пользователя.

Ответ 4

С Exec У вас не может быть владелец места в строке оператора T-Sql.

sp_executesql дает вам преимущество наличия держателя места и передачи фактического значения в время выполнения