Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: MS SQL Server Management Studio - permissions and stored procedures

    Hi

    My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.

    I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!

    Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables?

    many thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your best bet is to avoid dynamic code within your stored procedure. Failing that, you need to avoid actually executing any submitted parameters within you stored procedure. Failing that, you need to thoroughly verify parameter strings before including them in any executed sql.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    hi blindman

    I am not using any dynamic code, i am just passing in variables to my stored proc.

    I'm not sure what you mean by:

    Failing that, you need to avoid actually executing any submitted parameters within you stored procedure.
    I'm using SELECT statements only in my stored proc, for example:

    Code:
    SELECT t3.sub_id, t2.SIC_id, t1.business_name, t1.venue_id, t1.address1, t1.address2, t1.address3, t1.address4, t1.county, t1.town, t1.postcode, t1.tel, t1.img_thumb
            FROM VENUE AS t1 INNER JOIN SIC AS t2 ON t1.venue_id = t2.venue_id INNER JOIN SUBSCRIPTION AS t3 ON t1.venue_id = t3.venue_id INNER JOIN SIC_TYPE AS t4 ON t2.SIC_id = t4.SIC_id
            WHERE (t3.sub_id = 1) AND (t2.SIC_id = 8 OR t2.SIC_id = 9) AND (t1.town = @city OR @city = '0') AND (postcode LIKE @postcode + '%' OR @postcode = '0') AND (county = @county OR @county = '0')
    Can you see anything wrong with that with regards to injection attacks?

    thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The code you posted is not susceptible to SQL injection attacks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •