다음을 통해 공유

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 https://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 https://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