Portal Engine Questions on portal engine and web parts.
Version 6.x > Portal Engine > Macro |(sqlescape) View modes: 
User avatar
Certified Developer 12
Certified Developer 12
matt-pixelbuilders - 1/29/2014 4:18:36 AM
   
Macro |(sqlescape)
Hi guys,

I have been reading up:
http://devnet.kentico.com/Forums.aspx?forumid=45&threadid=17356

I'm having a similar issue trying to resolve a custom macro below. However the quotes wrapped around the date are causing the SQL injections.

I have tried adding this as an inline macro within the portal repeater web part with the '|(sqlescape)false' parameter but didn't have any luck.

I've also tried adding the this.SQLProperties = ""; line into the cmsrepeater web part in the SetupControl method but didn't have any luck there either. Each load would just reload the default parameters back in.
if (CMSContext.CurrentDocument.DocumentName.ToLower() == "new arrivals")
{
sbResult.AppendFormat(" AND (DateCreated > CONVERT(date, '{0}', 103))", DateTime.Now.AddDays(-14));
}
e.Match = true;
e.Result = sbResult.ToString();

Appreciate any advice

User avatar
Kentico Consulting
Kentico Consulting
Kentico_RichardS - 1/29/2014 4:59:13 AM
   
RE:Macro |(sqlescape)
Hi,

Could you please post the string which your macro is returning so that I can try it on my end?

To summarize it - in the where condition of your repeater webpart you are using condition which is returned by your macro? What error do you get?

Kind regards,
Richard Sustek

User avatar
Certified Developer 12
Certified Developer 12
matt-pixelbuilders - 1/29/2014 5:09:51 AM
   
RE:Macro |(sqlescape)
Hi Richard,

Sure, the string being passed to the macro ultimately results in:
(SKUAvailableItems > 0) AND (DateCreated > CONVERT(date, '15/01/2014 11:02:57', 103))

However after running through the macro resolver results in:
(SKUAvailableItems > 0) AND (DateCreated > CONVERT(date, ''15/01/2014 11:02:57'', 103))

which leads to an error of:

Incorrect syntax near '15'.

Thanks

User avatar
Kentico Consulting
Kentico Consulting
Accepted solutionAccepted solution
Kentico_RichardS - 1/29/2014 5:37:26 AM
   
RE:Macro |(sqlescape)
Hi,

I have been able to reproduce this issue. The fix for this is to use "handlesqlinjection" method instead of sql escape.

Try calling your macro like:
 {% ProcessCustomMacro("macroname", "|(handlesqlinjection)false") %}

Let me know how that works.

Kind regards,
Richard Sustek

User avatar
Certified Developer 12
Certified Developer 12
matt-pixelbuilders - 1/29/2014 5:42:02 AM
   
RE:Macro |(sqlescape)
Perfect, thanks for your help

User avatar
Kentico MVP
Kentico MVP
Accepted solutionAccepted solution
Brenden Kehren - 1/29/2014 8:23:07 AM
   
RE:Macro |(sqlescape)
Matt

I've learned you need to be very specific with the placement of those parameters. For instance with this you cannot have any spaces before or after it.
{% "DocumentName LIKE '%" +  CMSContext.CurrentDocumentName + "%'"|(handlesqlinjection)false%}

Brenden

User avatar
Certified Developer 12
Certified Developer 12
matt-pixelbuilders - 1/29/2014 8:30:13 AM
   
RE:Macro |(sqlescape)
Thanks Brendan. I'm fairly sure I tried it early on with spaces as you mentioned but stuggled, your post clears that up!

Cheers