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
and How EXEC(@SQL) is prone to SQL injection and sp_executesql @SQL...... isn't?
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'
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'
Your sp_executesql SQL should probably be;
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=@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.
Contrast that to the SQL required for EXEC;
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=' + char(39) + @Status + char(39)
...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'
With sp_executesql
, you don't have to build your query like that. You could declare it like this:
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=@eStatus'
This way if your @Status
value came from a user you can use @eStatus
and not have to worry about escaping '
. sp_executesql gives you the ability to put variables in your query in string form, instead of using concatenation. So you have less to worry about.
The column and table variables are still the same, but that's less likely to be directly from a user.
With Exec You can't have a place holder in your T-Sql statement string.
sp_executesql gives you the advantage of having a place holder and pass the actual value at runtime
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With