Runbook SQL Injection

With our implementation of Orchestrator 2012 we plan to fully implement and utilize the fancy new web tier – specifically we plan to expand our automation to include many more user initiated workflows.  This means that we will need to begin worrying about the dreaded ‘user input’ and all of the security risks that come along with it.  One of the largest problems that we are working on solving at the moment is that of injection.  The most common type of injection attack we need to worry about is SQL injection

What is SQL injection and why do I care

The basic idea of any type of injection attack is that the end user is able to make a program do something unexpected.  Take the following Runbook for example, it starts with initialize data and takes a Computer Name.  We then want to use that computer name and run a SQL query against our SCCM database to figure out some information about its hardware and return that data to whatever called us (another Runbook or the web service).


So what is the issue here?  Normally when our users pass in ‘good’ data for the computer name everything works just wonderfully and we get data about that computer from SCCM and return it up.  The problem becomes what if the user were to enter ‘bad’ data?  For instance, what would happen if the user said that the computer name was

mycomputer’; drop table sms_r_system; —

The resulting query that Orchestrator would run would be

select * from sms_r_system where name = ‘mycomputer’; drop table sms_r_system –‘

Which is a very valid SQL statement that just happens to drop your sms_r_system table from you SCCM database (your SCCM admin’s would not be too happy).  So, what do we do? Really there are a number of strategies for mitigating injection of this sort but the one we are pursuing is parameterized SQL queries.  The community IP for SQL ( was created to support this scenario.  Instead of putting your published data directly into your SQL statement you instead add parameters to your SQL statement then map the published data to those parameters




So, now when the user passes us ‘bad’ information it will not modify our SQL statement at all, rather it will just be passed as a parameter and the SQL statement will fail because there is no computer named “mycomputer’; drop table sms_r_system; –” in our SCCM environment Smile.


How does it work

As this is a community developed Integration Pack you can always go and take a look at the source code (or have one of your developers do so) to ensure that it meets your companies security standards.  If it does not you are always welcome (and encouraged) to post a bug report or submit a code patch.  At its core though, this object is very simple, the code for running the query and adding the parameters is below (the full version is available at


Are there any other benefits of using this object?

Yes!  One of the other benefits of this object (in respect to the built-in SQL object) is how it deals with returned data.  The normal object will return one line of data for each record in the database, called [Full line as a string with fields separated by ‘;’].  So, to parse information out of this we have to do manipulations to that field afterwards (for example to get the data in field 1 or field 3) and we have to do additional workarounds if our data happens to have a ; in it. 


The object in this integration pack still has that functionality (now called QueryResultString) and, in addition, it also splits a pre-defined number of fields out individually


This ends up looking like the following when it actually is run


What Limitations are there?

This integration pack only currently works when doing queries against SQL Server where the default object supports Access, ODBC, Oracle and SQL Server


Final Conclusions

This is a solve for one type of injection (SQL) but there are numerous other types that we need to be cognizant of when designing Runbooks such as ‘Run .NET Script’ injection and ‘Run Program’ injection.

Tagged with: , , , , ,
Posted in General Information, Integration Pack

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: