When building websites using Kentico’s Portal Engine, it is often inevitable to write custom Macro methods which perform some task and return the expected result back into the transformation. A valid scenario would be dynamically building a Where condition for a Repeater with custom query web part.
One issue you can run into with that is that Kentico by default escapes single quote characters in the provided Where condition before query execution and that will actually fail the query execution! That’s by design a security feature in order to prevent SQL injection attacks and usually that’s a good thing. But what’s even better, Kentico allows us to disable that behavior explicitly for a certain macro expression with the built-in macro expression parameter handlesqlinjection.
Example:
{% ... |(handlesqlinjection)false #%}
This little addition works great, but still we ran into an issue on an acceptance environment yesterday. The executed query failed and looking at the Kentico event log revealed that the query was indeed invalid. Our macro expression returned 0 (zero) instead of the expected value and SQL doesn’t like that in an AND condition. Makes sense…
It was entirely unclear to us what was happening. We tried several things without any real result and what’s even more disturbing, all other environments did not show this behavior. We compared the SQL query, the transformation and it all looked the same. With one tiny difference and it is definitely hard to spot!
An EMPTY SPACE
{% ... | (handlesqlinjection)false %}
between the pipe character and the actual macro expression parameter. I cannot explain the difference since no changes are manually done on any target environment, but something did happen here.
We learned the hard way what an empty space in the macro expression can cause, and usually it doesn’t matter at all. Here it does though. Kentico helps by highlighting the added parameter, but to be honest, if your laptop screen isn’t bright enough or the angle is too steep, then you won’t really notice it.
Watch out for empty spaces!
