January 24, 2013 at 1:29 pm
I'm reading the article The Curse and Blessings of Dynamic SQL found at http://www.sommarskog.se/dynamic_sql.html.
Quote
Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().
The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts.
But it is the third point that is the actual protection, and that we will look a little closer at. The procedure search_orders above should be coded as:
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE @custid '
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE @shipname '
EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',
@custid, @shipname
Since the SQL string does not include any user input, there is no opening for SQL injection. It's as simple as that. (Jacob: WHATT?????) By the way, note that since we can include parameters in the parameter list, even if they don't actually appear in the SQL string, we don't need any complicated logic to build the parameter list, but can keep it static (Jacob: Huh??? And which parameter list??? the Stored Proc or the one at EXEC ep_executesql @sql? I assume the latter but i still don't understand.) . In the same vein, we can always pass all input parameters to the SQL string.
He doesn't explain why this piece of code is better than the previous example, here:
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
EXEC(@sql)
the difference between these two statements is not apparent to me adn I don't believe he has explained this as clearly as he did previous examples.
Thanks!
January 24, 2013 at 1:49 pm
Well I guess
EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',
@custid, @shipname
Gives you something similar to Static SQL without having the user input interpolated into the SQL statement.
Thus when using variables all those crazy quote marks and other symbols used to break the code simply won't work.
January 24, 2013 at 2:24 pm
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
EXEC(@sql)
EXEC search_orders NULL, '''; DROP TABLE dbo.Orders; --'
Try that on both the concatenated and the parameterised versions of the proc (parameterised first). On a dev/test server of course. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2013 at 2:35 pm
GilaMonster (1/24/2013)
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
EXEC(@sql)
EXEC search_orders NULL, '''; DROP TABLE dbo.Orders; --'
Try that on both the concatenated and the parameterised versions of the proc (parameterised first). On a dev/test server of course. 🙂
Little Bobby Tables strikes again!
January 24, 2013 at 2:40 pm
Thanks Gila! I'll have to do it on my laptop when i get home. i don't have any test databases I can play with here at work.
I guess SEEING IS BELIEVING!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply