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 


            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;




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" 🙁


  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

Skip to main content