Skip to main content

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:

 

The only this that sucks about doing this is that the ? 
gets annoying with multiple parameters and tracking what you are using as input’s 

@clecky I find a tidy way to deal with that is declare a SQL variable for each parameter at the top of your query then they’re all in one place and in order. Makes it easier for other people reviewing your logic down the track. Saves scrolling up and down longer queries to find / count the ‘?’. Also makes it quick to transfer to another tool (like SQL Management Studio) for authoring and testing the query outside of the workflow editor. 

In all honesty, but this exactly why we preferably use the API as we've seen some pretty bad mistakes being made directly on the database. But for the ones who know what they are doing and how are pretty aware of the data model running queries directly on the database (views) can be very beneficial. 

@clecky I find a tidy way to deal with that is declare a SQL variable for each parameter at the top of your query then they’re all in one place and in order. Makes it easier for other people reviewing your logic down the track. Saves scrolling up and down longer queries to find / count the ‘?’. Also makes it quick to transfer to another tool (like SQL Management Studio) for authoring and testing the query outside of the workflow editor. 

This is how I do it, i am just complaining

Reply


Terms & ConditionsCookie settings