SQL Injection Prevention Example from MS Press

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Injection Prevention Example from MS Press

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-04-21 : 15:35:09

There is a MS Press book that I read which used the following code to prevent SQL Injection attempts from succeeding within a SProc. However, I have found that this example is too inclusive. For example, this will catch the value "Spanish Moss", when this clearly not an injection attempt. Why is that?


DECLARE @Query NVARCHAR(50);
SET @Query = N'Spanish Moss';
/* Also return true: */
--SET @Query = N'experienced vb.net coder';
--SET @Query = N'executive suite';

IF UPPER(@Query) LIKE UPPER(N'%0x%') 
	OR UPPER(@Query) LIKE UPPER(N'%;%') 
	OR UPPER(@Query) LIKE UPPER(N'%''%') 
	OR UPPER(@Query) LIKE UPPER(N'%--%') 
	OR UPPER(@Query) LIKE UPPER(N'%/*%*/%') 
	OR UPPER(@Query) LIKE UPPER(N'%EXEC%') 
	OR UPPER(@Query) LIKE UPPER(N'%xp_%') 
	OR UPPER(@Query) LIKE UPPER(N'%sp_%') 
	OR UPPER(@Query) LIKE UPPER(N'%SELECT%') 
	OR UPPER(@Query) LIKE UPPER(N'%INSERT%') 
	OR UPPER(@Query) LIKE UPPER(N'%UPDATE%') 
	OR UPPER(@Query) LIKE UPPER(N'%DELETE%') 
	OR UPPER(@Query) LIKE UPPER(N'%TRUNCATE%') 
	OR UPPER(@Query) LIKE UPPER(N'%CREATE%') 
	OR UPPER(@Query) LIKE UPPER(N'%ALTER%') 
	OR UPPER(@Query) LIKE UPPER(N'%DROP%') 
BEGIN
	RAISERROR(N'Possible SQL injection attempt.', 16, 1);
	RETURN;
END
ELSE
BEGIN
	PRINT N'All is well!';
	RETURN;
END

If I remove "Spanish" from the query, or the check for "sp_" from the IF statement, this runs fine. So I know the line where this is caught, but it doesn't make sense to me. I can also duplicate this using the work "experienced" which gets caught by the "xp_" check.

Can someone offer some assistance?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 15:37:12

They should have square brackets around the underscores. Check LIKE in BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-21 : 15:37:45

jesus... what so they write in books these days...

to prevent sql injection all you have to do is use parametrized sql. period.
that's all you need to know.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-04-21 : 15:46:07

Whoa! I didn't expect these responses so quickly...

Well, how would you do the parameterized query when you need to use the LIKE clause?

SELECT * FROM [dbo].[table] WHERE [field1] LIKE '%' + @Param1 + '%';

From my understanding, won't this still expose the query to injection attempts?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-21 : 15:51:42

nope. it won't.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-04-21 : 15:55:16

Good to know. Thanks!

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-21 : 16:13:11

the trouble comes whenever you build the sql to be executed dynamically, and then execute it with EXEC or similar. for example:


declare @param1 nvarchar(100)
set @param1='hackme please ''; select * from sysusers -- '

SELECT * FROM sysobjects WHERE [name] LIKE '%' + @Param1 + '%' -- this is safe

exec ('SELECT * FROM sysobjects WHERE [name] LIKE ''%' + @Param1 + '%''') -- this is not safe

btw, trying to sanitize inputs in the way your book example is doing is not bulletproof. if you ever are dynamically building a sql statement based on client input and then executing it, you are at risk.

elsasoft.org

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-04-21 : 16:40:46

Thanks for the clarification. I appreciate everyone's help.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-21 : 17:57:42

Good god, that was from a MS Press book?
Ridiculous.

e4 d5 xd5 Nf6

dpeharec
Starting Member

1 Post

Posted - 2008-07-10 : 04:56:25

like tkizer mentioned

Change
OR UPPER(@Query) LIKE UPPER(N'%xp_%')
OR UPPER(@Query) LIKE UPPER(N'%sp_%')
To
OR UPPER(@Query) LIKE UPPER(N'%xp[_]%')
OR UPPER(@Query) LIKE UPPER(N'%sp[_]%')

but ofc. this wont work for like anything that has textsp_text or textxp_text.

If its a plain select search query i would use
EXECUTE AS USER = "USERNAME"
Where that user would have read access to tables and columns needed for that specific query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources