Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

T-SQL Hints and Tips

Checking for SQL injection attacks in parameters. by donutman
Posted: 1 Jul 04 (Edited 18 Jul 04)

   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.


CREATE PROCEDURE spInjectionAttack
   @Sql AS varchar(8000),
   @BadKeywords AS varchar(8000) OUTPUT
   CREATE TABLE #WMD (Weapon varchar(128))
      SELECT ' alter ' UNION
      SELECT ' bcp ' UNION
      SELECT ' delete ' UNION
      SELECT ' drop ' UNION
      SELECT ' exec ' UNION
      SELECT ' exec(' UNION
      SELECT ' execute ' UNION
      SELECT ' execute(' UNION
      SELECT ' insert ' UNION
      SELECT ' openquery ' UNION
      SELECT ' restore ' UNION
      SELECT ' shutdown ' UNION
      SELECT ' truncate ' UNION
      SELECT ' update ' UNION
      SELECT ' sp_sqlexec ' UNION
      SELECT ' sp_executesql ' UNION
      SELECT ' xp_cmdshell '  
      SELECT ';'+LTrim(Weapon) FROM #WMD UNION
      SELECT [Name] FROM Master.dbo.sysobjects
         WHERE xType='P' OR xType='X'

   SET @BadKeywords=''                         
   SELECT @BadKeywords=@BadKeywords+
         CASE CharIndex(Weapon,' '+@Sql+' ')
           WHEN 0 THEN ''
           ELSE ', '+LTrim(Rtrim(Weapon))
      FROM #WMD
   SET @BadKeywords=SubString(@BadKeywords,3,8000)

   To include this SP within a SP that uses dynamic SQL, do something similar to the following:


 --String data that will become part of dynamic SQL statement.
   @UserInput1 AS varchar(8000),
   @UserInput2 AS varchar(8000)
   DECLARE @BadKeywords AS varchar(8000)
   --Be sure to place a space between each parameter to be tested.
   EXEC spInjectionAttack
      @UserInput1 + ' ' + @UserInput2,
      @BadKeywords OUTPUT
   IF Len(@BadKeywords)>0 BEGIN
      RAISEERROR ('Possible SQL injection attack:'
   {continue WITH your T-SQL here}

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close