Inject this - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Inject this

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-03-03 : 03:56:27

I've been reading about SQL injection attacks recently and am now concerned that I need to re-write some code. What I was wondering is if anyone has any examples of the techniques that they have used to prevent this (and other) sorts of attack. The articles I've seen are quite good at explaining how attacks work but seemed light on prevention techniques.

For example. I have recently written a sproc that takes a single parameter with a default value. If a parameter is passed then it must exist in a table I have. What I've written seems to me to be fairly secure but how can I show this?

This is the code

CREATE PROCEDURE dbo.sproc_SendoutSamples
@Dest VARCHAR(50) = 'GENLYNC (%)'	
AS

-- check for valid parameters in an effort to prevent SQL injection attacks
IF @Dest = 'GENLYNC (%)' -- This bit is here to prevent Injection but is it needed?
or exists(SELECT SENDOUT FROM dbo.SENDOUT_LOCS where SENDOUT = @Dest)
begin
	-- valid parameters so can run query
	SELECT <Lots of stuff>
           FROM <SomeJoins>
	WHERE <Some fixed criteria> AND 
	    (ls.SENDOUTLOC like @Dest) AND 
             <Some more fixed criteria>
 end
 ELSE
 	
	RAISERROR ('Severity 10 error: Invalid parameter in procedure call', 10, 1) with log
	return (0)

Any comments? Any suggestions for improvements etc would all be appreciated

thanks

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-03-03 : 05:16:12

Steve,

There is no injection risk at all in this situtation.
The parameter can only be defined/interpreted by its type. In this case it is a string and will be interperated as column value and nothing else.. Try putting in 'DROP DATABASE Northwind' for education and giggles..

The injection issue arises if EXEC or other "dynamic" sql commands are used and no appropriate security is in place.. ie The infamous "sa blank" application account...

DavidM

A front-end is something that tries to violate a back-end.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-03-03 : 06:21:55

Thanks

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 13:45:15

Where can I get more info on Injecting?
I don't want to Google - I'll probably get a bunch of tree-hugging-hippie-crap

You can't teach an old mouse new clicks.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-03-03 : 13:58:35

And whats wrong with hugging trees

More info here [url]http://www.4guysfromrolla.com/webtech/061902-1.shtml[/url]

Beauty is in the eyes of the beerholder

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 14:36:58

and how about
http://www.justfuckinggoogleit.com/search?query=sql+server+sql+injection

No hippies or tree hugging.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-03-03 : 14:54:01

Nice one nigel

Beauty is in the eyes of the beerholder

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 15:32:27

Nice 1
You got me!

You can't teach an old mouse new clicks.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-03-07 : 07:19:03

Nigel,

This is better than STFW!

So simple, so silly, sooo effective.
I wonder how many hits that site gets.

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources