How to use SP_ExecuteSQL to prevent SQL injection

SP_ExecuteSQL() is a system stored procedure that could be used to execute dynamic SQL same way as Exec() and Execute(). SP_Execute besides exacting a stored procedure it fills a large security hole that the later procedures have for dynamic SQL. If you are not familiar with Exec and Execute functions check out this blog first.

Let see how we could use SP_ExecuteSQL to prevent SQL injection with dynamic SQL in action:

sp_executesql Param1, Param2, Parameter Assignment;
where Param1 = SQLString, Param2 = ParameterDefinition

Well by now you may be asking yourself why in the world you will need to go all this hassle when you could just include everything all the parameters in the string and use Exec or Execute to execute it. Let’s start by example how including parameters in dynamic sql is will make us vulnerable to sql injection. Do not try whatever you learn from this lesson on the system you don’t own or have enough permission to so or you will find yourself in trouble.

--Sample Select
select  FirstName
        ,LastName
        ,UserName
        ,[Password]
from TestDB.dbo.tUsers;
go

Take a close look at username TAbram that will be using that account to demonstrate our case. So we have a stored procedure named spAuthenticateUser that will accept a username and Password then return the users account if it exists.

--Sample stored proc
create proc spAuthenticateUser (@UserName as Nvarchar(25),
                                @PassWord as Nvarchar(25)) as
declare @sql as nvarchar(max);
set @sql = 'select FirstName
                  ,LastName
                  ,UserName
                  ,[Password]
from TestDB.dbo.tUsers
where UserName = ''' + @UserName +
      ''' and [PassWord] = ''' + @PassWord + '''';
 
execute (@sql);
go

Now let’s pass the correct values to the parameter and call the stored procedure

--Call the stored procedure with values that are in the table --
Exec&nbsp; spAuthenticateUser 'TAbram','PassW0rd3';

In the above call we used the values that exist in our table to therefore the stored procedure returned the correct value. Okay what happen if someone passed values that is similar to the bottom script.

--Call the stored procedure wrong values --
Exec&nbsp; spAuthenticateUserModified 'TAbram'' or ''1''=''1', 'WrongPassWord';

Well we get the same exact result as if we passed a valid input. What hat exactly happening here let’s just simply output the sql statement and take a close look.

--Check what is going on with the dynamic sql script--
print 'select FirstName
             ,LastName
             ,UserName
             ,[Password]
from TestDB.dbo.tUsers
where UserName = ''' + 'TAbram'' or ''1''=''1' +
      ''' and [PassWord] = ''' + 'WrongPassWord' + '''';
Go

As you could see from the above interpretation of dynamic sql output 1=1 is always return true and logic valued with true using or operator returns true. This makes using Execute() function very dangerous for dynamic sql. Let’s rewrite our procedure using sp_execute

--rewrite our stored procedure--
create proc spAuthenticateUserModified 
(@UserNameParam as Nvarchar(25),@PassWordParam as Nvarchar(25)) as
begin
Declare @sql&nbsp; Nvarchar(max),
@ParameterDefinition Nvarchar(max);
 
SET @sql = N'select FirstName
                   ,LastName
                   ,UserName
                   ,[Password]
from TestDB.dbo.tUsers
where UserName = @UserName&nbsp; and
      [PassWord] = @PassWord' ;
 
SET @ParameterDefinition = N'@UserName as Nvarchar(25),
                             @PassWord as Nvarchar(25)';
end
go

Now let us pull the same stunt we did above by passing a wrong values.

EXECUTE sp_executesql @sql, @ParameterDefinition, 
                      @UserName = @UserNameParam, @PassWord=@PassWordParam ;

Aha nothing returned which it should be and sp_execute stops any attempt to tricking sql server.