Stored procedure EXEC vs sp_executesql difference?

Question

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'

Solution 1

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'

Solution 2

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.

Solution 3

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

Comments

[Registered User]

Why where clause isn't enclosed with '?

[Joachim Isaksson]

@user1095881 If you enclose @eStatus with quotes, it will use the string '@eStatus' to search for in the database, if you use an unquoted @eStatus, the value will be used. For example, if @eStatus is 'test', an INSERT INTO myTable VALUES (@eStatus) would insert the string 'test', while INSERT INTO myTable VALUES ('@eStatus') would insert the string '@eStatus'.

[Sam]

I think this is missing a closing ' in the first code example.

[Sankara]

the other difference is when you use temp tables in the dynamic sql , sp_executesql uses the cache plan where as exec does not use it