Post reply

dynamic SQL

  • February 24, 2006 at 6:57 am

    #168473

    Quick question: is there a security concern with using dynamic SQL? I’ve had others raise the question of SQL injection, etc. I really like dynamic SQL within Stored procedures, but don’t want to move that way if it’s not good strategy.

     

    Thanks,

    Sam

  • February 24, 2006 at 9:24 am

    #623250

    Its acceptable to use when it needs to be but there are some extra percautions that need to be taken to ensure security.

    When using arguments that are passed in they have to be checked to be sure that they don't contain code used for an injection attack.  You should limit the length of the fields to the minimum and don't allow large fields that can be typed in via some front end.

    Also, the user running the procedure needs SELECT permissions on any views, functions or tables used in the dynamic SQL.  This should also be properly managed, i.e. don't grant all the rights to public.

  • February 24, 2006 at 10:52 am

    #623276

    Thanks. That really helps.

    Sam

  • March 1, 2006 at 3:55 am

    #623900

    Just a quick one.

    Only pass in the parameters and build the query within the proc.  I have seen on more than one occsasion (unfortunately) someone building dynamic SQL code and passing the whole code into the proc and then executing that.

    I winced in pain when I saw that...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply