Worklows with CommServDBQuery - SQL Injection risk

Hello all, 

I recently found out something that I missed, docs have been updated at some point, so wanted to share this with you all as well. (https://documentation.commvault.com/11.24/essential/131495_predefined_activities_for_workflows.html)

So take the CommServDBQuery predefined Activity. You have an input defined as a client computer group and then want to get a list of clients within the group via this activity type. I have seen many people do this...

SELECT c.id, c.displayName
FROM APP_ClientGroupAssoc cg WITH(NOLOCK)
INNER JOIN APP_Client c WITH(NOLOCK) ON c.id = cg.clientId
WHERE cg.clientGroupId = xpath:{/workflow/inputs/vmGroup/clientGroupId}

Note that the where clause contains the input as a variable.

So this is a bad thing! Have a read through the doc link above, you can see that there is a risk that this could be used to inject SQL code into the workflow and do something you did not intend to happen.

Instead, change it to below…

SELECT c.id, c.displayName
FROM APP_ClientGroupAssoc cg WITH(NOLOCK)
INNER JOIN APP_Client c WITH(NOLOCK) ON c.id = cg.clientId
WHERE cg.clientGroupId = ?

Notice the variable is replaced with the "?"

Once that is done add the variable you defined in to the Parameters tab: