Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Checking for SQL injection attacks in parameters.

T-SQL Hints and Tips

Checking for SQL injection attacks in parameters.

by  donutman  Posted    (Edited  )
If you use dynamic SQL that is constructed from user input, then it would be wise to check the input strings for potential SQL injection attacks. The following stored procedure returns the objectionable keywords in a comma separated string. If the SP returns an empty string then no injection attack was detected.
Admittedly the inclusion of all system SPs and XPs is overly conservative especially since most if not all of the ways to actually execute them have been guarded against. If you think it's overkill, then you can easily eliminate the Select statement that adds them to the temporary WMD table. However, there are many other possible injection attacks depending on what your application is doing. For an explanation of various techniques follow this link, http://www.nextgenss.com/papers/advanced_sql_injection.pdf.
Code: 
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spInjectionAttack
   @Sql [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
   @BadKeywords [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray] OUTPUT
[Blue]AS[/Blue]
   [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #WMD [Gray]([/Gray]Weapon [Blue]varchar[/Blue][Gray]([/Gray]128[Gray])[/Gray][Gray])[/Gray]
   [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
      [Blue]SELECT[/Blue] [red]' alter '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' bcp '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' delete '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' drop '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' exec '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' exec('[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' execute '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' execute('[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' insert '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' openquery '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' restore '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' shutdown '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' truncate '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' update '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue] 
      [Blue]SELECT[/Blue] [red]' sp_executesql '[/red] [Blue]UNION[/Blue] 
      [Blue]SELECT[/Blue] [red]' xp_cmdshell '[/red]  
   [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
      [Blue]SELECT[/Blue] [red]';'[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray] [Blue]FROM[/Blue] #WMD [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [Name] [Blue]FROM[/Blue] Master.dbo.sysobjects 
         [Blue]WHERE[/Blue] xType[Gray]=[/Gray][red]'P'[/red] [Gray]OR[/Gray] xType[Gray]=[/Gray][red]'X'[/red]

   [Blue]SET[/Blue] @BadKeywords[Gray]=[/Gray][red]''[/red]                         
   [Blue]SELECT[/Blue] @BadKeywords[Gray]=[/Gray]@BadKeywords[Gray]+[/Gray]
         [Blue]CASE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Weapon[Gray],[/Gray][red]' '[/red][Gray]+[/Gray]@Sql[Gray]+[/Gray][red]' '[/red][Gray])[/Gray]
           [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]''[/red] 
           [Blue]ELSE[/Blue] [red]', '[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray][Gray])[/Gray] 
         [Blue]END[/Blue] 
      [Blue]FROM[/Blue] #WMD
   [Blue]DROP[/Blue] [Blue]TABLE[/Blue] #WMD
   [Blue]SET[/Blue] @BadKeywords[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BadKeywords[Gray],[/Gray]3[Gray],[/Gray]8000[Gray])[/Gray]
[Blue]GO[/Blue]

To include this SP within a SP that uses dynamic SQL, do something similar to the following:
Code: 
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spYourDynamicSqlSP[green]
 --String data that will become part of dynamic SQL statement.
[/green]   @UserInput1 [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
   @UserInput2 [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
[Blue]AS[/Blue]
   [Blue]DECLARE[/Blue] @BadKeywords [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][green]
   --Be sure to place a space between each parameter to be tested.
[/green]   [Blue]EXEC[/Blue] spInjectionAttack 
      @UserInput1 [Gray]+[/Gray] [red]' '[/red] [Gray]+[/Gray] @UserInput2[Gray],[/Gray]
      @BadKeywords OUTPUT
   [Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@BadKeywords[Gray])[/Gray][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      RAISEERROR [Gray]([/Gray][red]'Possible SQL injection attack:'[/red]
         [Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray]@BadKeywords[Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
      RETURN
   [Blue]END[/Blue]
   {continue [Blue]WITH[/Blue] your T[Gray]-[/Gray]SQL here}
-Karl
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top