存储过程EXEC与sp_executesql的区别

Question

我已经编写了两个存储过程，一个使用sp_executesql，另一个没有sp_executesql两者都正确地执行相同的结果，我不知道之间有什么区别

EXEC（@SQL）vs EXEC sp_executesql@SQL，N'@eStatus varchar（12）'，@eStatus=@状态

以及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'

Answer 1

您的sp_executesqlSQL可能应该是；

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

这将允许您使用@eStatus作为参数来调用sp_executesql，而不是将其嵌入到SQL中。这将提供这样的优势，即@eStatus可以包含任何字符，并且如果需要安全，它将由数据库自动正确转义。

与EXEC所需的SQL形成对比；

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

其中，@Status中嵌入的char（39）将使您的SQL无效，并可能产生SQL注入的可能性。例如，如果@Status设置为O'Reilly，则生成的SQL将为；

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

Answer 2

使用sp_executesql，您不必像那样构建查询。你可以这样声明：

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

这样，如果@Status值来自用户，则可以使用@eStatus，而不必担心转义'。sp_executesql使您能够以字符串形式将变量放入查询中，而不是使用串联。这样你就不用担心了。

列和表变量仍然相同，但不太可能直接来自用户。

Answer 3

使用Exec在t-Sql语句字符串中不能有占位符。

sp_executesql为您提供了一个占位符的优势，并在运行时