DCSIMG
Stored Procedure Is Not A Silver Bullet Against SQL Injection Attacks - אליק לוין

אליק לוין

עולמו של יועץ ממיקרוסופט

Stored Procedure Is Not A Silver Bullet Against SQL Injection Attacks

SQL injection attack  is possible as a result of developer's security bug [vulnerability] - Dynamic SQL. I admit it is easiest way to write code that access DB. Once it is discovered by security expert and presented to the developer most obvious question that comes up "what do I do?". Very often [too often] the answer is that I hear is "Use stored procedure".

Consider this stored proc [recently seen in some project]:

DECLARE @ID NVARCHAR(150)

EXEC ('USE MSPetShop4; SELECT * FROM PRODUCT WHERE CATEGORYID=''' + @ID + ''';');

Consider that  @ID='BIRDS' then the result would be this one:


BD-01 BIRDS Pelican Will sit in your garden and admire nature Birds/icon-pelican.gif
BD-02 BIRDS Penguin Guaranteed to stay by your side Birds/icon-penguin.gif
BD-03 BIRDS Pteranodon Can't let go of the past? This is your bird Birds/icon-pteranodon.gif
BD-04 BIRDS Owl Your personal dictionary û night & day Birds/icon-owl.gif
BD-05 BIRDS Duck Lisps but otherwise sings well Birds/icon-duck.gif

(5 row(s) affected)

Now consider this: @ID='''; EXEC XP_CMDSHELL ''NET USER HACKER qwe123!@# /ADD''--'

Figuring out what result would be?

Do not use dynamic SQL neither in .Net code nor Stored Procs

Here is practical recommendations to How To Protect From SQL Injection in ASP.NET

Enjoy

פורסם: Dec 21 2006, 12:09 AM by alikl | with 5 comment(s)
תגים:, ,

תוכן התגובה

Evgeny Alexandrovich כתב/ה:

Old and very rare code. Its artificial and customized example that has nothing to do with real life. Most people use sp_ExecuteSQL with parameters. Real life use will be something like that: EXEC sp_ExecuteSQL N'USE MSPetShop4; SELECT * FROM PRODUCT WHERE CATEGORYID=@ID', N'@ID NVARCHAR(150)', @ID = @ID
# December 21, 2006 12:31 AM

alikl כתב/ה:

Great tip!

I wish ALL [not most] people would do that...

Seen it couple days ago in some large project.

Cheers

# December 21, 2006 3:13 PM

alik levin's כתב/ה:

. http://msdn2.microsoft.com/en-us/library/ms188354.aspx "In SQL Server 2005 you can define the execution

# February 11, 2007 2:44 AM

alik levin's כתב/ה:

I've recently presented Security Engineering topic during internal Microsoft convention to international

# February 14, 2007 4:53 PM

alik levin's כתב/ה:

Imagine if security was cool like Silverlight .... But security is not that cool, so the biggest challenge

# April 18, 2007 8:49 PM