Fending off SQL Injection Attacks plus a nice SP to help you along the way


This topic comes up lots of recent and always surprises me that awareness levels are still quite low.


 


Adrian (thanks Adrian) from DFEEST in Adelaide sent me a link that discusses SQL Injection attacks (this is a universal DB attack); you can find the article at http://www.sitepoint.com/article/sql-injection-attacks-safe.  On this site there is a useful SP you can add to your system for general purpose string cleaning…



 


CREATE FUNCTION dbo.CleanString (@Clean varchar(8000)) 


RETURNS varchar(8000) AS 


BEGIN


            set @Clean=REPLACE(@Clean,””,’ ‘);  — Remove single quote


            set @Clean=REPLACE(@Clean,’^’,’ ‘); — Remove caret


            set @Clean=REPLACE(@Clean,’#’,’ ‘); — Remove hash


            set @Clean=REPLACE(@Clean,’select’,’ ‘); — Remove select


            set @Clean=REPLACE(@Clean,’drop’,’ ‘); — Remove drop


            set @Clean=REPLACE(@Clean,’;’,’ ‘); — Remove semi colon


            set @Clean=REPLACE(@Clean,’–‘,’ ‘); — Remove double dash


            set @Clean=REPLACE(@Clean,’insert’,’ ‘); — Remove insert


            set @Clean=REPLACE(@Clean,’delete’,’ ‘); — Remove delete


            set @Clean=REPLACE(@Clean,’xp_’,’ ‘); — Remove extended stored procedure prefix


            return @Clean;


END


 


 


So this plus the following guidance particularly for web apps:- 




  1. validateRequest is on true by default and most cases this should be fine – it raises an error if a form post contains any HTML whatsoever.  


  2. Use HtmlEncode when reflecting any user input back to the browser


  3. Validate for known good data



    1. Use ASP.NET Validators – remember these act client side in IE 5 and above but always execute server side so you MUST check “Page.Isvalid()” is true.


    2. Use Regular expressions to validate – check out http://www.regxlib.com/ for Regular Expression samples.  If you hate regex then validate with your own code


  4. Validate strings in SQL Server with the above stored proc


  5. Connect your app to the database with the minimum privileges required and ideally just execute rights on the Stored Procs required to get the work done.

Comments (10)

  1. Hi – just wondering why, if you’re using stored procedures with parameters (which I believed then mitigated the risk of any SQL injection attacks, unless you use something like an EXEC command), you’d need to use the sp you describe?

  2. Jason says:

    I am very surprised that people are still using string concatenation to build SQL queries. I’ve always used stored procedures or SQL statements with ‘?’ or ‘@name’ placeholders that are substituted automatically (and safely) via the development platform’s Command and Parameter objects. If I encounter any complex problem where the obvious solution is to use string concatenation, I find a better way to structure the query. Please correct me if I am wrong about all this.

  3. Hi,

    so if the user posts the request string "select the right tool and insert it into box" then the " the right tool and it into box" string will be saved into DB which is wrong (if there is some INSERT SQL code in our page).

    I think it’s better to use some SQL command builder which will check the parameters instead of using this stored proc.

    BTW: I wanted to read your "Why WSE" article but there is shortcut to "d:My ProdinfoSecurityWeb Services Home Why WSE.mht" 🙁

    JJ

  4. Dave Glover says:

    The "Why WSE" link fixed, doh, sorry about that. Alas, the new blog system doesn’t allow for long URLs:-( So you can find the article half way down the page or here http://msdn.microsoft.com/webservices/building/wse/default.aspx?pull=/library/en-us/dnwse/html/whywse.asp

    The article was written by Benjamin Mitchell