repeater where clause string literal

Kàren Vaganyan asked on April 13, 2016 00:41

I'm having trouble specifying a where clause in a repeater using a string. The where clause in my repeater has this:

{% strPostedBy = Querystring.GetValue("p"); return#%}
{% if (strPostedBy == "RB") { %} PostedBy = 'Ray Block' {% } #%}

Those are single quotes around Ray Block and double quotes around RB. When I browse to the page with the p query string parameter, it changes the query to

SELECT * FROM View_custom_ExecutiveMessages_Joined WHERE [NodeSiteID] = @NodeSiteID AND (
PostedBy = ''Ray Block''

Those are 2 single quotes on either side of Ray Block. Is there a way to correct that so it only puts 1 single quote on either side of Ray Block?

Correct Answer

Chetan Sharma answered on April 13, 2016 13:05

Hello Kàren, The only thing extra that I would suggest is to escape query suggested by Brendon like this, which he has already mentioned in his answer. I reached out to Kentico recently for a case where value of parameter "p" could have apostrophe like "Kàren's". You may get an error/incorrect results as it will get evaluated. This is primarily dropped to avoid any SQL injection attacks.

{% if(SQLEscape(QueryString.GetValue("p")) == "RB") { "PostedBy = 'Ray Block'" } |(handlesqlinjection)false @%}

Laura's Article is an excellent premier on best parctices while creating secure filters

Kentico's article on SQL Injection attack

Let us know if it helps

2 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on April 13, 2016 01:36

The way to do this, which exposes you to some sql injection to a point, is by simply building string like so:

{% if(QueryString.GetValue("p") == "RB") { "PostedBy = 'Ray Block'" } @%}

2 votesVote for this answer Mark as a Correct answer

Laura Frese answered on April 13, 2016 06:40

You may find a post I recently wrote - Creating secure filters with Kentico - helpful when using the where condition in repeaters to filter things

1 votesVote for this answer Mark as a Correct answer