THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Bad Habits to Kick : Using EXEC() instead of sp_executesql

Today in my T-SQL: Bad Habits to Kick session at SQL Saturday #84 in Kalamazoo, a user asked if SQL Server supported anything like bind variables in Oracle when using dynamic SQL.

When using dynamic SQL, you have two choices: EXEC() / EXECUTE(), or sp_executesql. Early on my career, I used EXEC() a lot, because it was much easier. I've since learned that sp_executesql is a lot better, for two main reasons:

  1. You substantially reduce the risk of SQL injection by continuing to pass strongly-typed variables throughout the whole chain. Which do you find more questionable?
    DECLARE @foo NVARCHAR(32) = N'string''; DROP TABLE dbo.table; --''';

    DECLARE @sql NVARCHAR(MAX) = N'SELECT foo FROM dbo.table WHERE foo = ''' + @foo + '''';
    EXEC(@sql);
    ... or ...
    DECLARE @foo NVARCHAR(32) = N'string''; DROP TABLE dbo.table; --''';

    DECLARE @sql NVARCHAR(MAX) = N'SELECT foo FROM dbo.table WHERE foo = @foo;';
    EXEC sp_executesql @sql, N'@foo NVARCHAR(32)', @foo;
    In the latter case, @foo is a parameter all the way through, making it quite difficult to expose yourself to SQL injection. The results will literally look for the following value:
    string'; DROP TABLE dbo.table; --'
    Which will return an empty result instead of trying to execute two commands on the server.

  2. In older versions of SQL Server, it was much more likely that a plan will get re-used, even for dynamic SQL, when using sp_executesql. This is because the statement itself always looks the same to the optimizer - however you can get the same effect using EXEC() in modern versions with things like Forced Parameterization (SQL Server is getting better and better at detecting parameters on its own, even if you don't explicitly declare them).

    Now, can you get bitten by parameter sniffing still in either case? Of course. But you can't solve that by switching between EXEC and sp_executesql; it is something you'll need to use OPTIMIZE FOR or RECOMPILE query hints, or maybe even the optimize for ad hoc workloads setting. This behavior is quite similar to how bind variables in Oracle work.

There are going to be cases where your parameters can't simply be passed in directly, such as variable table names. The threat for SQL injection still exists there, but the performance issues are not really relevant - if the query is going to be pulling from a different table potentially each time, you should definitely be using RECOMPILE.

The bottom line is this: query plan reuse is one of your very best friends; concatenation is the long-lost acquaintance from high school that just might steal your lunch.

For some more background on EXEC vs. sp_executesql, see this MSDN topic and also Denis Gobo's post reminding us that simply changing EXEC to sp_executesql is not a silver bullet.

 

Published Saturday, September 17, 2011 1:50 PM by AaronBertrand

Comments

 

giantism_strikes said:

Since this is doing a soft parse and the query plan will be reused, is there a performance gain between using a stored procedure and dynamic, parameterized SQL?

September 17, 2011 2:56 PM
 

AaronBertrand said:

I don't believe so, but I would still opt for stored procedures for other reasons (manageability, maintenance, modularization, consistency).

September 17, 2011 4:48 PM
 

jchang said:

my preference is to employ stored procedures for the reason AB cited

and then within a stored procedure, employ sp_executesql to handle multiple optional search arguments

example:

instead of this code

SELECT xx

FROM TableA JOIN TableB etc

WHERE (@P1 IS NULL OR Col1 = @P1)

 AND (@P2 IS NULL OR Col2 = @P2)

etc

use sp_executesql instead

DECLARE @SQL nvarchar(xxx), @b tinyint = 0

SET @SQL = N'SELECT xx FROM TableA JOIN TableB etc WHERE 1=1 '

IF @P1 IS NOT NULL

SET @SQL = @SQL + N' AND Col1 = @P1 '

IF @P2 IS NOT NULL

SET @SQL = @SQL + N' AND Col2 = @P2 '

I would just declare and apply all the parameters, regardless if they are null

as in

exec sp_executesql @SQL, N'@P1 type, @P2 type, etc', @P1, @P2, etc

September 18, 2011 12:22 PM
 

Jack Corbett said:

I agree with Aaron and Joe about still using SP's and the method Joe espouses for multiple optional search arguments.  It can get a bit ugly looking with a lot of parameters, but is still easier to maintain and expand.  I have blogged about it as well.

September 23, 2011 10:40 AM
 

krismaly said:

AB said right.

Always use Stored Procs let it be simple or complicated since Maintenance, Manageability, Modularity, and other uses

May 27, 2015 9:22 AM
 

Sandy said:

I am sorry.

I don't understand. why the latter won't execute ?

Is it not replacing @foo ?

It should execute like the former one but defineltely, I am missing something. Please help.

March 24, 2016 6:00 AM
 

AaronBertrand said:

@Sandy @foo remains a string throughout. The difference is the first one ends up as this:

   SELECT foo FROM dbo.table WHERE foo = 'string'; DROP TABLE dbo.table; --''

Which will obviously try to execute both commands (and will succeed if the user has permissions, which is all too common with dynamic SQL).

When you pass @foo as a parameter, no, it does not simply replace the string and execute. SQL Server builds a plan where the parameter is a token and it is passed to the engine to execute.  So SQL Server literally looks for a string in dbo.table that matches the entire parameter value. It is much like this:

   DECLARE @foo NVARCHAR(32) = N'string''; DROP TABLE dbo.table; --''';

   SELECT foo FROM dbo.table WHERE foo = @foo;

No string replacement ever happens here.

March 30, 2016 9:49 AM
 

Juan Hernandez said:

Why has Microsoft enchanced EXEC() instruccion in SQL 2012  (see WITH RESULT SET) and not done the same with sp_executsql ??

April 20, 2016 4:22 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement