INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

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.

CODE

CREATE PROCEDURE spInjectionAttack
   @Sql AS varchar(8000),
   @BadKeywords AS varchar(8000) OUTPUT
AS
   CREATE TABLE #WMD (Weapon varchar(128))
   INSERT INTO #WMD
      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 '  
   INSERT INTO #WMD
      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))
         END
      FROM #WMD
   DROP TABLE #WMD
   SET @BadKeywords=SubString(@BadKeywords,3,8000)
GO
 

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

CODE

CREATE PROCEDURE spYourDynamicSqlSP
 --String data that will become part of dynamic SQL statement.
   @UserInput1 AS varchar(8000),
   @UserInput2 AS varchar(8000)
AS
   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:'
         +Char(13)+@BadKeywords,15,-1)
      RETURN
   END
   {continue WITH your T-SQL here}
-Karl

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

My Archive

Resources

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