This review is around the misue of EXECUTE\EXEC\SP_EXECUTE in stored procedures.

To find the at risk stored procedures run the query below:

o.name AS Object_Name,
FROM sys.sql_modules m
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like ‘%exec%’;

Then scan the contents of anything returned. 95% will be eliminated with a quick read once you know what you are looking for. Some of the returned results will be calling a stored procedure from another stored procedure using execute these are probably ok. The remaining 5% should be investigate carefully as they may be calling dynamic sql. The dynamic sql is the risk.

The stored procedures calling dynamic sql should ideally have contents something like below i.e. calling dynamic SQL using a fixed command string and parameters.

SET @SQLToRun = “select mycolumn from mytable where myothercolumn = @myparam1”

DECLARE @ParmDefinition  AS NVARCHAR(200)
SET @ParmDefinition = N’@param1  NVARCHAR(40)’;

EXECUTE [sys].[sp_executesql]
@param1 = param1In

Unfortunately this type of functionality can be written poorly which introduces risks. Firstly the @SQLToRun is prone to injection if appended together from different strings rather than using a fixed string and so is still a risk. Below is a high risk approach that needs investigation.

set @SQLToRun = mystring1 + mystring2; <- High risk

In this case you need to examine carefully how the @SQLToRun is constructed and check a hacker cannot influence its component parts. Ideally this should be changed to fixed string with parameters like below

SET @SQLToRun = “select mycolumn from mytable where myothercolumn = @myparam1”

Another strong sign something is wrong is if the parameters are missing completely in the execute call i.e.

EXECUTE [sys].[sp_executesql]  @SQLToRun



If the parameters are missing it is 99% likely there is a problem. Rework and parameterise the statement and explain the risk to your team.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s