כניסה
 
עמוד הביתספריהלמדהורדותאיתר וטיפל בבעיותקהילהפורומים
משאבים למומחי מחשוב >
> 
> 
Secruity implications of using a table to store the T-SQL

الإجابة Secruity implications of using a table to store the T-SQL

  • יום חמישי 23 פברואר 2012 21:20
     
     
    היכנס כדי להצביע
    0

     

    There is a proposed implementation of SSIS package where, package is making a database call to a table which stores T-SQL statements. For an example,

    Table:

    Create Table SQLStatments (GroupID int, SQLstring varchar (5000))

    Insert into SQLStatments (GroupID,SQLstring) values (2,'Update Table5 set date=getdate()')

    Insert into SQLStatments (GroupID,SQLstring) values (3,'delete from Table6')

     

    SSIS: Select SQLString from SQLStatments where GroupID=@ID

    So SSIS will get and execute the T-SQL above, based on the GroupID that is passed

     

    Can you please give your thoughts/ comments on security of implications of this implementation. 

    There is an obvious risk of a malicious record being inserted to the SQLStatements table.  I am also nervous about string truncation type of a thing that can get rid of a part of the SQL Statement (i.e. Where clause) that can do damage to the data.

    Does this implementation increase the risk of SQL injection in any way?

    Appreciate your feed back!

    Thanks
    Sam



    • נערך על-ידי Sam Hewawasam יום חמישי 23 פברואר 2012 21:21
    • נערך על-ידי Sam Hewawasam יום חמישי 23 פברואר 2012 21:22
    •  
     

כל התגובות

  • יום שישי 24 פברואר 2012 11:19
     
     
    היכנס כדי להצביע
    0
    Well, if you make sure the UPDATE and INSERT permissions on the table are tightly restricted, it should do it. About the truncation, it depends on the size of your SSIS variables. Strings should not cause you any trouble, unless you define some VARCHAR(MAX) in your table.
     
  • יום שני 27 פברואר 2012 15:24
     
     תשובה קוד כלול
    היכנס כדי להצביע
    0

    Hallo Sam,

    I would strongly recommend NOT to use such an practice due to security problems AND sql injection in a pretty simple way.

    1. Problem
    IF it is a problem depends on the account which executes the SSIS. Basically - from my experiences - SSIS packages will be executed with the SQL Server Agent account which is in most cases a sysadmin of the sql server. That means in fact that this account can do EVERYTHING on the server.

    If I have reading and writing access to your table [dbo].[SQLStatements] and I would enter the following statement:

    DROP DATABASE [myVeryImportantData];

    the system will radically drop the database.

    2. Problem

    SQL Injection is in any case a big problem when someone can define his own sql statment.

    To get safe I would strongly recommend the following way (just a suggestion)

    1. Create a schema (e.g. remoteSQL)
    2. Grant a dedicated account EXECUTE permissions to that schema
    3. ALL SQLStatements have to be stored procedures - NO NATIVE SQL
    4. Use a Proxy account instead of the higher privileges sql agent account
    (This account will have the execute permissions)
    5. Use your table and enter the name of the procs instead of native sql
    6. Prevent inserting native sql by using a trigger which checks the input (see following script)

    CREATE TABLE dbo.SQLStatement
(
    GroupId INT,
    SQLString nvarchar(256),

    CONSTRAINT pk_SQLStatement PRIMARY KEY
    (SQLString)
)
GO

CREATE TRIGGER dbo.trg_SQLStatement_InsertUpdate
ON dbo.SQLStatement
FOR INSERT, UPDATE
AS
    SET NOCOUNT ON

    -- check whether all objects are stored procedures
    IF EXISTS (
                SELECT TOP 1 o.*
                FROM insert i LEFT JOIN sys.sysobjects o
                     ON (
                         i.SQLString = o.Name AND
                         'P' = o.Type
                         )
                WHERE o.Id IS NULL
               )
     BEGIN
         RAISERROR ('Not a procedure', 11, 1)
         ROLLBACK TRANSACTION
     END

     SET NOCOUNT OFF
GO

    Please note that the devs are not tested and are "writing on the fly".

    As you can see, the trigger will prevent adding commands which are not stored procedures.
    Now your SSIS package can execute the stored procedures instead of native sql.

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • סומן כתשובה על-ידי Iric WenModerator יום שני 05 מרץ 2012 09:47
    •