יום חמישי 23 פברואר 2012 21:20
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,
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!
יום שישי 24 פברואר 2012 11:19Well, 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
I would strongly recommend NOT to use such an practice due to security problems AND sql injection in a pretty simple way.
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.
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.
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
- סומן כתשובה על-ידי Iric WenModerator יום שני 05 מרץ 2012 09:47