Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to prevent SQL Injection Attack from SQL server side


How to prevent SQL Injection Attack from SQL server side

Author
Message
j-1064772
j-1064772
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 1202
Even trying to parse the incoming string for DELETE, DROP TABLE, etc. is doomed to fail.

A sneakier attack uses HEX, such as 0x77616974666f722064656c61792027303a303a323027

What does that unreadable string mean ?

DECLARE @x varchar(99)
SET @x=0x77616974666f722064656c61792027303a303a323027
SELECT @x

==> waitfor delay '0:0:20'


Waiting for 20 seconds is a standard trick for hackers to check if an application can transmit commands to the database engine.

Always use parameters, not string concatenation.
Ed Thompson
Ed Thompson
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 122
I must say that I have never heard of a hex code attack; so, thank you for causing me to look into that.

My recommendation is layering. If you do an internet search on SQL Injection you will come up with all the same recommendations previously mentioned: use stored procs, tighten security with lowest level permissions, use sp_executesql with ad hoc query execution, and force developers to use parameterized queries.

But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.

The only way to be truly protected is to operate disconnected in a vacuum which is pointless; so, do the best that you can and be prepared for the worst--backup your databases and practice recovery from time to time.



Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 9604
Ed Thompson (6/27/2013)
But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.


Well-stated, Ed. I view the first layer of defense as being to treat everything as suspect. Make sure the quotes are in order by building and using a standard library of functions to clean every string you pass to SQL.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
j-1064772
j-1064772
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 1202
Humm....

How do you "clean" a hex input ? convert it to string and also check the resulting string ?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
Ed Wagner (6/27/2013)
Ed Thompson (6/27/2013)
But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.


Well-stated, Ed. I view the first layer of defense as being to treat everything as suspect. Make sure the quotes are in order by building and using a standard library of functions to clean every string you pass to SQL.


I would disagree with this. Don't try to clean the input, protect yourself from malicious input by parameterizing your queries. DO NOT EVER execute user entered values. That means you do not create some code to build a sql string and then run that string against your database.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
j-1064772 (6/27/2013)
Humm....

How do you "clean" a hex input ? convert it to string and also check the resulting string ?


I would say you don't, that is kind of like polishing a turd. Now matter how much cleaning, it still stinks.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search