none
Secruity implications of using a table to store the T-SQL

    שאלה

  •  

    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:22
    יום חמישי 23 פברואר 2012 21:20

תשובות

  • 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
    יום שני 27 פברואר 2012 15:24

כל התגובות

  • 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.
    יום שישי 24 פברואר 2012 11:19
  • 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
    יום שני 27 פברואר 2012 15:24