存储过程 EXEC 与 sp_executesql 的区别? [英] Stored procedure EXEC vs sp_executesql difference?

问题描述

我写了两个存储过程，一个是 sp_executesql，另一个没有 sp_executesql两者都正确执行相同的结果，我不明白这里有什么区别

I've written two stored procedure one with sp_executesql and other doesn't have sp_executesql both are executing properly same results, I didn't get what is the difference here between

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

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

以及 EXEC(@SQL) 如何容易发生 SQL 注入和 sp_executesql @SQL ......不是吗?

and How EXEC(@SQL) is prone to SQL injection and sp_executesql @SQL...... isn't?

下面没有 sp_executesql 的存储过程

Below Stored Procedure without 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 的存储过程

Below stored procedure with 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'

推荐答案

你的 sp_executesql SQL 应该是；

Your sp_executesql SQL should probably be;

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

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

This will allow you to call sp_executesql with @eStatus as a parameter instead of embedding it into the SQL. That will give the advantage that @eStatus can contain any characters and it will be properly escaped automatically by the database if required to be secure.

对比 EXEC 所需的 SQL；

Contrast that to the SQL required for EXEC;

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 将是;

...where a char(39) embedded in @Status will make your SQL invalid and possibly create an SQL injection possibility. For example, if @Status is set to O'Reilly, your resulting SQL would be;

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

这篇关于存储过程 EXEC 与 sp_executesql 的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！