Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to prevent SQL Injection Attack from SQL server side Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 10:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:39 AM
Points: 101, Visits: 489
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.
Post #1468254
Posted Thursday, June 27, 2013 11:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 7:53 PM
Points: 823, Visits: 103
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.



Post #1468280
Posted Thursday, June 27, 2013 12:35 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 3,999, Visits: 3,022
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
Post #1468292
Posted Thursday, June 27, 2013 12:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:39 AM
Points: 101, Visits: 489
Humm....

How do you "clean" a hex input ? convert it to string and also check the resulting string ?
Post #1468295
Posted Thursday, June 27, 2013 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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)
Post #1468300
Posted Thursday, June 27, 2013 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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)
Post #1468301
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse