Portal Engine Questions on portal engine and web parts.
Version 5.x > Portal Engine > webpart where condition is filtering returned macro text? View modes: 
User avatar
Member
Member
gatsby0121 - 4/11/2011 12:24:15 PM
   
webpart where condition is filtering returned macro text?
I have a webpart, and in the where condition, I'm passing a custom macro to the CMSCustom.cs file.

in the custom file, I'm doing a check if a column is equal to ''

result = "alertHeader <> '' ";

(that's two single quotes after the <>.

But it still shows everything. when I check the query in sql, it works fine, and hides the rows. Am I doing something wrong?

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 4/11/2011 2:11:09 PM
   
RE:webpart where condition is filtering returned macro text?
Hi,

could you please try to use following code instead:
result = "column <> \'\'";

Best regards,
Ivana Tomanickova

User avatar
Member
Member
gatsby0121 - 4/13/2011 4:43:30 AM
   
RE:webpart where condition is filtering returned macro text?
Hi Ivana, thanks for the quick reply.

I tried your suggestion, and unfortunately it didn't work.

And when I debug the repeater and check the where query string, it has columnname <> '''' (that's four single quotes);

the code I used was: result = "[alertHeader] <> \'\'"; (The last quote is a double quote)

User avatar
Member
Member
gatsby0121 - 4/14/2011 12:34:26 AM
   
RE:webpart where condition is filtering returned macro text?
Thanks for your help in this, I just added a separate checkbox and added result = "[showAlert] = 1";

And it ended up working.

User avatar
Member
Member
alarba-osa - 4/22/2011 1:51:25 PM
   
RE:webpart where condition is filtering returned macro text?
Actually I am seeing this same issue. basically any single quote that i return in a string from a custom macro gets doubled for my by something in the kentico macro framework. Any work around to this so that I can pass a string with singles back from the custom macro without have kentico double up my single quotes?

my example is that I am building a dynamic where clause in a custom macro.
my macro returns the string
gradelevel like 'K-2%'
but when it hits the final query sent to SQL server it has been changed to
gradelevel like ''%K-2%'' ... which blows up the queryparser

Any workaround?

User avatar
Member
Member
alarba-osa - 4/24/2011 10:04:42 AM
   
RE:webpart where condition is filtering returned macro text?
Thinking about this more, you are clearly implementing some SQL injection protection. I was doing the injection check in my custom macro too. I guess that I can run my custom macro as-is right now and then pass the result from my custom macro to a replace macro call something like this (just guessing here)

{% ProcessCustomMacro("MyCustomMacroMtd", "|(replace)\'\'(with)\'") %}

does that sound reasonable?

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 4/26/2011 2:47:24 AM
   
RE:webpart where condition is filtering returned macro text?
Hi,

could you please try to use following macro definition:
 switch (expression.ToLower())
{
case "cmacro":
match = true;
result = "\'%teststring%\'";
break;
}

Macro returns '%teststring%' and this can be used as a query parameter.

Best regards,
Ivana Tomanickova

User avatar
Member
Member
gatsby0121 - 4/26/2011 1:14:24 PM
   
RE:webpart where condition is filtering returned macro text?
This didn't work unfortunately, it returns with an error and the code says:

([NodeName] like ''%value_Guide Lorem%''. It turned \' into ''

User avatar
Member
Member
gatsby0121 - 4/26/2011 2:13:58 PM
   
RE:webpart where condition is filtering returned macro text?
Also, just for completionist sake, I tried your code above (I realize I should have added a column that teststrinng is = to, but the real point is that it's double quoting still with your example:


AND (''%teststring%'') : caused exception: An expression of

You can see the double quotes being added.

User avatar
Member
Member
gatsby0121 - 4/26/2011 2:08:11 PM
   
RE:webpart where condition is filtering returned macro text?
Hi Alarba-osa, did you ever get this to work? turns out I need it for another section, and I can't seem to get it to work.

User avatar
Member
Member
gatsby0121 - 4/26/2011 4:00:49 PM
   
RE:webpart where condition is filtering returned macro text?
This answered my question:


http://devnet.kentico.com/Forums/f45/fp5/t17677/Custom-functions-in-Where-condition.aspx

You should clone the webpart, then add this.SQLProperties = ""; to the setupcontrol method.

User avatar
Member
Member
Andy - 4/26/2011 5:09:19 PM
   
RE:webpart where condition is filtering returned macro text?
Thanks for posting this gatsby. As you have realized, my solution using replace in the macro was wrong.... it appears that the Macro framework will always replace ' with ''.... in the course of sql injection removal. I'm just posting this so that anyone who sees my comment does not think that it ever worked ...... and waste time on it.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 4/28/2011 3:43:12 AM
   
RE:webpart where condition is filtering returned macro text?
Hi Andy,

Yes, you are right. The apostrophe is always replaced in the Where property when a macro is resolved (because of the sql injection). In others properties (Content before, etc.) the apostrophe is not replaced.

I am sorry for confusion.

Best regards,
Ivana Tomanickova

User avatar
Member
Member
shahidsalman143-yahoo.co - 9/20/2012 9:04:29 AM
   
RE:webpart where condition is filtering returned macro text?
I have the following SQL macro injected on to the query data source web part and when run in debug mode it always return the dates in double which is wrong, any idea of how to remove the double quotes please.

Macro
{%CurrentUser.IsInRole("LA.CentreManagers")? "Deleted = 0 AND CourseSearchExclude = 0 " : "GetDate() BETWEEN ISNULL(ValidFrom, '19000101') AND ISNULL(ValidTo, '20990101') AND Deleted = 0 AND CourseSearchExclude = 0"%}

value passed to the Where Clause of the datasource control
GetDate() BETWEEN ISNULL(ValidFrom, ''19000101'') AND ISNULL(ValidTo, ''20990101'') AND Deleted = 0 AND CourseSearchExclude = 0

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 9/24/2012 6:23:02 AM
   
RE:webpart where condition is filtering returned macro text?
Hi,

could you please try to rewrite the condition accorting to this example?
GETDATE() between Isnull(DocumentPublishFrom, convert(datetime, '1/23/1970', 101)) and Isnull(DocumentPublishFrom,convert(datetime, '1/23/2099', 101)) 

I used in in Where condition of repeater web part and checked SQL debug. The apostrophies where not quoted.

Best regards,
Ivana Tomanickova