Is it possible to use a K# macro in the WHERE condition of a Custom table data source?

Joseph Dietrich asked on March 10, 2017 21:42

I'm trying to create a page that uses a custom table data source and a repeater to show a list of dictionary terms from a custom table by first letter or numeral. The page has a wildcard URL that I'm using to pass in the variable I want to filter by. I have tried using the following macro in the WHERE condition of the custom table data source:

{%
    if ("" == QueryString.DictionaryLetter) {
        return
    } else {
        "Term LIKE '" + QueryString.DictionaryLetter + "%'"
    }
#%}

However, this is throwing an error in the event log when I pass a value such as "A" in:

Query:
SELECT [Term], [Abbreviation], [Definition]
FROM customtable_DictionaryTerms
WHERE (Term LIKE ''A%'')

Caused exception:
Incorrect syntax near 'A'.

It looks to me like the single quotes are getting doubled up when the web part does its thing and constructs the SQL query, but I don't know why this is or how to get around it. Any suggestions?

Correct Answer

Zach Perry answered on March 10, 2017 22:00

You would have to disable handle sql injection for the macro, but you are setting yourself up for sql injection passing a querystring result directly into a query like that.

Your macro would be something like this {% ... |(handlesqlinjection)false |(identity)GlobalAdministrator%} from the docs

I wouldn't recommend doing it that way though.

2 votesVote for this answer Unmark Correct answer

Recent Answers

Joseph Dietrich answered on March 10, 2017 22:08

Good point. I was migrating an old custom ASP.NET web site over to Kentico pretty directly, so I wasn't even thinking about that. Thanks!

0 votesVote for this answer Mark as a Correct answer