Invalid SQL Query Error on a Page

Andre Pfanz asked on March 17, 2017 16:37

We are using Kentico v9.0.50 and on some pages are getting this error in the event log:

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_Documents_cmsrepeater.get_WhereCondition() at CMSWebParts_Viewers_Documents_cmsrepeater.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)

I have no idea what is causing this error or how to troubleshoot it. The error message doesn't give me enough details. Does anyone have any suggestions?

Recent Answers


Chetan Sharma answered on March 17, 2017 17:15

Go to settings -> System -> Debug and enable SQL query debug.

Now go to Debug -> SQL queries tab and now you will be able to see detail error log for SQL

Remember to check "Show complete context"

0 votesVote for this answer Mark as a Correct answer

Zachary Perry answered on March 17, 2017 17:51 (last edited on March 17, 2017 17:53)

Have you recently upgrade or hotfixed?

Hotfix 44 changed the SQL Parser, which breaks queries where you use ##WHERE## or another macro in the wrong location.

EDIT* - See Ahmad's last answer on this question

0 votesVote for this answer Mark as a Correct answer

Andre Pfanz answered on March 17, 2017 18:53

I went into the Debug->SQL query tab and found this error when I ran most of the queries in SQL Server Management Studio:

Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid.

Here's an example of a query that gave that error:

DECLARE @NodeSiteID int = 3;
DECLARE @NodeGUID uniqueidentifier = '7eb5661d-816a-4d11-8001-0bf58c6a164d';
DECLARE @DocumentCulture nvarchar(max) = N'en-US';
DECLARE @CMS_C int = 1;

SELECT [NodeID], [NodeAliasPath], [DocumentUrlPath], [DocumentCulture]
FROM (
    SELECT [NodeID], [NodeAliasPath], [DocumentUrlPath], [DocumentCulture], ROW_NUMBER() OVER                (PARTITION BY NodeID ORDER BY CASE WHEN [DocumentCulture] = @DocumentCulture THEN 1 ELSE 2 END,   DocumentCulture) AS [CMS_C]
    FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID
WHERE [NodeSiteID] = @NodeSiteID AND [NodeGUID] = @NodeGUID AND [DocumentCulture] = @DocumentCulture
) AS SubData
WHERE [CMS_C] = @CMS_C

If this is the problem, I don't know where this query is being called from.

0 votesVote for this answer Mark as a Correct answer

Andre Pfanz answered on March 17, 2017 19:06

Zachary, We are using v9.0.50, so I'm thinking we are on hotfix 50.

I looked at the link you sent. However, I'm not using the ##WHERE## macro.

0 votesVote for this answer Mark as a Correct answer

Andre Pfanz answered on March 17, 2017 19:47

I made some progress. I found out the repeater causing the problem is using this for the WHERE field:

ApplicationId in ({% ProductApplications.Replace("|",",") #%})

If I try this it works:

ApplicationId in (1,2,3)

Does anyone have any ideas on what I can do to make the dynamic version work using the {% ... %}?

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on March 18, 2017 16:05

Hi Andre,

If you still have an error with NOEXPAND hint, check the same problem here System.Data.SqlClient.SqlException: Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid

0 votesVote for this answer Mark as a Correct answer

Michal Samuhel answered on March 21, 2017 13:42

Hi Andre, I can see, based on your last reply, that you have trouble with WHERE condition. As Zach had mentioned above, we had tightened security around these conditions. Problem with your where is not macro itself, but the fact that after resolving the macro It creates invalid SQL syntax.

Current requirement is that each of these property has to have valid SQL syntax (it does not have to return values though). It just needs to pass alone as executable in SQLSMS.

0 votesVote for this answer Mark as a Correct answer

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