Dynamic SQL--What is Erland Sommarskog saying?

Question

Post reply

Dynamic SQL--What is Erland Sommarskog saying?

Jacob Pressures

SSCertifiable

Points: 5804
More actions
January 24, 2013 at 1:29 pm

#287949

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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 1

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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!

http://xkcd.com/327/

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

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!