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:
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
I emailed you a couple of patches for RB 22.01.
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
The two patches worked, thanks.
Regards
Steve