Forums (Obsolete)

Certified Developer 13

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


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

Certified Developer 13

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

Kentico Consulting

Accepted 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


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

Kentico Legend

Accepted 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


Certified Developer 13	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