Are you using Custom SQL Queries in BizTalk360? Are you planning on upgrading to BizTalk360 version 8.7? Then you should read this post before starting the upgrade.

A new version and increased security

On the 12^th of March, Kovai Ltd. has released their latest version of BizTalk360, version 8.7.1138.0903. This time, it comes with a bundle of bug fixes, and some enhancements.

After I successfully updated the UAT environment at a customer, I noticed that a lot of things were quickly going in Error.
Multiple severity alarms / issues were triggered and test users were complaining that they received errors in BizTalk360. This scared me, especially since we’re in an important test phase and I received my configured SEV 1 issues, which require immediate action.

The CREATE issue

Let’s have a look at this error. It contains a simple query which monitors the Spool Size of the environment. Nothing fancy about it. But it also contains some context, some descriptive values.

And that’s where the problem lies. When we look at the changelog of BizTalk360 8.7, we notice the following.

I thought this was a little weird, since the word CREATE is not in the query itself, but merely in the (commented) description. Thus, I asked BizTalk360 what they meant with this. This is there reply:

We are validating the pattern for Database Whole keywords (Create, Alter, SET etc ) in custom SQL queries.

So, they just parse the whole message content and look for these words. You can easily resolve this by modify the comment from “Create” to “Created” or “Creation”.

Missing the SELECT or FROM clause

After changing all the queries, I stumbled on the second issue we have received.

Invalid SQL query, missing either ‘SELECT’ or ‘FROM’ clause of SELECT statement

This error seems logic, we are using the EXEC command to call our stored procedure, which contains more logic (and Create Date formatting).

So, we should be able to implement another fix in here, shouldn’t we? The fix of adding a comment – SELECT 1 FROM didn’t work. We received the second error:

Found the match of database reserved keyword: EXEC which concerns the SQL injection in the query Kovai.BizTalk360.BusinessService.EntityObjects.CustomSQLQuery 

That’s a pity. We are using a lot of stored procedures in our BizTalk360. Why? Because it’s easier to maintain than the SQL code in 360. Plus, we also deploy this from Visual Studio, but that’s a story for another time. This means that, I need to copy and paste all the SQL code into the Custom SQL queries.

I’ve also raised this issue to BizTalk360 and they answered the following:

We will allow DB reserved keywords in the comments section as part of the stored procedure execution fix and let you know.

We have to work on to allow EXEC | EXECUTE keyword in secure SQL Queries.  Once the fix is available for the public release we will let you know.

Conclusion

I’ve also learned something from this BizTalk360 release.

• Please read the release notes carefully, they might contains some useful information;
• Always test a new release before upgrading on production. Luckily, I could do this on our UAT environment;
• Have a backup plan nearby. You can and should take a backup of your database before upgrading it;
• BizTalk360 does care about their customers, and they do reply fast. In this case they will resolve the following incidents in the next release(s):
  • Allow DB reserved keywords in the comments;
  • Allow the keyword EXEC | EXECUTE in the SQL Queries.

Preventing SQL Injection is a great thing to do and I think more application should rethink their application. You often need to shift your strategy between making your application secure and maintaining functionality.