Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

RB 22.0 - ValidateSQLText calls GetFieldsForSQL StealthMode produces SQL error

Since we updated a client with our software tht uses RB22.0 they now have a report that fails with an SQL error:

Syntax error 'and' on line 38

We found that you have introduced 'StealthMode' which injects code and 'c' <> 'c' which the SQL uses the WITH statement and has multiple unions and the offending section does not have a WHERE clause so falls over.

Our fix for this is to add WHERE 1=1 which works - below is the SQL that shows the inserted line:

WITH vwWebConfirmedJobsDetail AS ( SELECT J.JobNumber, (SELECT TOP 1 L.UserID FROM SYSDATA_SystemAudit2Log L WHERE L.RefKey = J.UniqueKey AND L.TableID = 882 AND L.ColumnID = 33 AND L.UserID NOT IN('WEB') AND CAST(L.DateTime AS DATE) BETWEEN '2022-05-23' AND '2023-05-23' ORDER BY L.DateTime ASC) AS TickedBy, (SELECT TOP 1 L.UserID SplitBy FROM SYSDATA_SystemAudit2Log L WHERE L.RefKey IN (SELECT S.UniqueKey FROM HLG01_HAStageFile S WHERE S.ID = 'J' AND S.JobNumber = J.JobNumber) AND L.Action = 'I' AND L.TableID = 885 AND L.ColumnID = 10 AND L.UserID NOT IN('WEB') AND CAST(L.DateTime AS DATE) BETWEEN '2022-05-23' AND '2023-05-23' ORDER BY L.DateTime ASC) AS SplitBy, CASE WHEN TickedBy IS NULL THEN SplitBy ELSE TickedBy END ConfirmedBy, U.DefaultDepot FROM HLG01_HAJobFile J INNER JOIN SYSDATA_Users U ON (U.User_ID = ConfirmedBy) WHERE J.ID = 'J' AND J.ColDate BETWEEN DATEADD("dd", -5, '2022-05-23') AND DATEADD("dd", 5, '2023-05-23') AND J.CrtdBy = 'WEB' AND ConfirmedBy NOT IN('ALEX', 'ADAMK', 'ASAMUEL', 'ASAMUEL2', 'DAMON', 'GBOYER', 'GREG', 'GREG2', 'SAMB', 'SPENCER', 'SPENCER2', 'SPENCER3', 'WEB', 'SAMB') AND U.DefaultDepot IN ('%') ), vwWebConfirmedJobs AS ( SELECT ConfirmedBy User_ID, DefaultDepot Depot, COUNT(DISTINCT JobNumber) Jobs FROM vwWebConfirmedJobsDetail and (('c' <> 'c' )) GROUP BY ConfirmedBy, DefaultDepot ), vwCreatedJobs AS ( SELECT U.User_ID, U.DefaultDepot Depot, COUNT(DISTINCT J.JobNumber) Jobs FROM HLG01_HAJobFile J INNER JOIN SYSDATA_Users U ON (U.User_ID = J.CrtdBy) WHERE J.ID = 'J' AND J.CrtdDate BETWEEN '2022-05-23' AND '2023-05-23' AND J.CrtdBy NOT IN('ALEX', 'ADAMK', 'ASAMUEL', 'ASAMUEL2', 'DAMON', 'GBOYER', 'GREG', 'GREG2', 'SAMB', 'SPENCER', 'SPENCER2', 'SPENCER3', 'WEB') AND U.DefaultDepot IN ('%') GROUP BY U.User_ID, U.DefaultDepot ), vwTotalJobsPerDepot AS ( SELECT Depot, SUM(Jobs) Jobs FROM ( SELECT Depot, SUM(Jobs) Jobs FROM vwCreatedJobs GROUP BY Depot UNION ALL SELECT Depot, SUM(Jobs) Jobs FROM vwWebConfirmedJobs GROUP BY Depot ) vwDepotTotals GROUP BY Depot ) SELECT DATEFORMAT('2022-05-23', 'dd/MM/yyyy') DateFrom, DATEFORMAT('2023-05-23', 'dd/MM/yyyy') DateTo, U.User_ID, U.UserName || ' (' || U.User_ID || ')' Username, U.DefaultDepot Depot, CASE U.DefaultDepot WHEN 'BAR' THEN 'BARNSLEY' WHEN 'BOD' THEN 'BODMIN' WHEN 'BWN' THEN 'BRIDGWATER NATIONAL' WHEN 'BWL' THEN 'BRIDGWATER LOCAL' WHEN 'DOV' THEN 'DOVER' WHEN 'GLN' THEN 'MOTHERWELL' WHEN 'LIV' THEN 'LIVERPOOL' WHEN 'LUT' THEN 'LUTON' WHEN 'PET' THEN 'PETERBOROUGH' WHEN 'RED' THEN 'REDDITCH' END DepotName, COALESCE(W.Jobs, 0) JobsConfirmed, --DivideDD(JobsConfirmed, TotalJobs, 3)*100 JobsConfirmedPercent, COALESCE(C.Jobs, 0) JobsCreated, --DivideDD(JobsCreated, TotalJobs, 3)*100 JobsCreatedPercent, JobsConfirmed + JobsCreated TotalJobs, DivideDD(TotalJobs, TotalDepotJobs, 3)*100 TotalJobsPercent, (SELECT T.Jobs FROM vwTotalJobsPerDepot T WHERE U.DefaultDepot = T.Depot) TotalDepotJobs FROM SYSDATA_Users U LEFT OUTER JOIN vwWebConfirmedJobs W ON (W.User_ID = U.User_ID) LEFT OUTER JOIN vwCreatedJobs C ON (C.User_ID = U.User_ID) WHERE U.DefaultDepot IN ('%') AND TotalJobs > 0 ORDER BY DepotName, TotalJobs DESC, Username

Comments

  • Hi Steve,

    I emailed you a couple of patches for RB 22.01.

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • Hi Nard

    The two patches worked, thanks.

    Regards
    Steve
Sign In or Register to comment.