Query Reapeater WHERE condition got error after installing hotfix 9.0.44

Ahmad Shah Barhanuddin asked on October 20, 2016 17:53

Hi All,

My page got error after installing Kentico hotfixs 9.0.44. In my query repeater web part, found that it cause by WHERE condition fields property. If I remove the value the web part can work as before. Error log as below. Please help. I'm done search and info in the net but cant find any.

Message: An invalid SQL query was used.

Exception type: System.Security.SecurityException Stack Trace: at CMS.PortalControls.CMSAbstractWebPart.GetValueInternal(String propertyName, Boolean resolveMacros) at CMSWebParts_Viewers_Query_queryrepeater.get_WhereCondition() at CMSWebParts_Viewers_Query_queryrepeater.SetupControl() at CMS.PortalControls.CMSWebPartZone.OnContentLoaded() at CMS.PortalControls.CMSAbstractLayout.OnContentLoaded() at CMS.PortalControls.CMSAbstractLayout.LoadWebPartsContent(Boolean reloadData) at CMS.PortalControls.CMSAbstractLayout.LoadContent(PageInfo pageInfo, Boolean reloadData, Boolean allowOrphanedZones) at CMS.PortalControls.CMSPagePlaceholder.LoadContent(PageInfo pageInfo, Boolean reloadData) at CMS.PortalControls.CMSPagePlaceholder.LoadContent(PageInfo pageInfo, Boolean reloadData) at CMS.PortalControls.CMSPortalManager.LoadPage(Boolean reloadData) at CMS.PortalControls.CMSPortalManager.PageHelper_InitComplete(Object sender, EventArgs e) at CMS.Helpers.PageContext.CurrentPage_InitComplete(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Page.OnInitComplete(EventArgs e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Recent Answers


Joshua Adams answered on October 20, 2016 17:58

Is it a complex where clause, or basic? If complex, it may be a specific part of it...maybe try recreating it bit by bit and see what creates the error.

0 votesVote for this answer Mark as a Correct answer

Ahmad Shah Barhanuddin answered on October 20, 2016 18:08

Just basic. Like "d.DocumentPublishFrom >= getdate()"

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on October 20, 2016 19:55

Can you get the entire query generated by web part and try to run it with SQL Management Studio?

0 votesVote for this answer Mark as a Correct answer

Jan Šedo answered on October 21, 2016 14:10

The parser changed with the hotfix. I would recommend to follow Roman's advise and try to run the query generated by the webpart directly in SQL Management Studio - it is possible that your WHERE condition wasn't valid even before the hotfix, but it was tolerated and "fixed" by the previous parser.

What stands out for me in your condition is the d.DocumentPublishedFrom part. What does the 'd' stand for? Is it part of some macro?

0 votesVote for this answer Mark as a Correct answer

Ahmad Shah Barhanuddin answered on October 26, 2016 12:41 (last edited on October 26, 2016 12:48)

I found the problem.

SELECT ##TOPN## ##COLUMNS## FROM customtable_test WHERE (##WHERE##) ORDER BY ##ORDERBY##

Base on above syntax. Before the hotfix, my CMS version was 9.0.0. During that, ##WHERE## macro i can put it anywhere on the query. But after the hotfix, version 9.0.44, ##WHERE## macro can only exists after SQL WHERE.

Example.

SELECT TOP 10 * FROM customtable_test t
INNER JOIN CMS_Document d on d.DocumentForeignKey = t.ItemID 
WHERE d.DocumentPublishedFrom >= getdate() ##WHERE##

or

SELECT TOP 10 * FROM customtable_test t
INNER JOIN CMS_Document d on d.DocumentForeignKey = t.ItemID ##WHERE##
WHERE d.DocumentPublishedFrom >= getdate() 

Before hotfix query above works. But after hotfix. The query can only works as below

SELECT TOP 10 * FROM customtable_test t
INNER JOIN CMS_Document d on d.DocumentForeignKey = t.ItemID 
WHERE ##WHERE## and d.DocumentPublishedFrom >= getdate()

Same with ##ORDERBY## macro. I only found ##COLUMN## macro that can be put in anywhere.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.