Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is sp_executesql reliable? Expand / Collapse
Author
Message
Posted Wednesday, December 25, 2013 9:33 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
In my opinion there is no need to checking sys.objects for the code given in the original post
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'Select something from ' + QUOTENAME(@TableName) + ' where 1=1'
EXEC sp_executesql @sql

Guys! In practice no one ask table names as input from final users in their applications
All they do is using it hard coded.

But as I reading from here
http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

You can easily prevent sql injection by using Parameterization

CREATE PROC [sp_select]( @name NVARCHAR(50) )
AS
declare @cmd nvarchar(max)
set @cmd = N'SELECT * FROM [users] WHERE NAME = @name'
EXEC sp_executesql @cmd, @name = @name
go

Now if attacker write:
 set @name = 'Some Name''; GRANT CONTROL TO [Malicious User];--

Parameterization will take care of single quotations for us :-\
 set @cmd = N'SELECT * FROM [users] WHERE NAME = ''''Some Name''; GRANT CONTROL TO [Malicious User];--''''

It will take a while until I test it.


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1525902
Posted Wednesday, December 25, 2013 10:05 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 5:55 PM
Points: 44, Visits: 131
Using sp_executesql with parameters does NOT do a replace like this:
@cmd = REPLACE(@cmd, N'@name', @name);

It generates inline code like this:
set @name = 'Some Name''; GRANT CONTROL TO [Malicious User];--'
SELECT * from [users] WHERE NAME = @name;

sp_executesql used with parameters and no concatenation/REPLACE completely removes the ability to rewrite the SQL inside. Whatever garbage the user provides is just a string, a string that is a value that is evaluated as data, not as code. As long as you never use user provided data as part of the composition of the batch to be executed, sq_executesql will prevent SQL injection.


Sincerely,
Daniel
Post #1525903
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse