I’m running into an issue while trying to execute a BAQ query, and I could use some assistance to troubleshoot and resolve it.
I’ve created a BAQ to retrieve order details, including a concatenated list of part numbers for each order. However, when I attempt to run or save the query, I encounter an error that says:
Possible SQL injection.
Review the server event logs for details.
My query:
select
[OrderHed].[Company] as [OrderHed_Company],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderHed].[PONum] as [OrderHed_PONum],
[OrderHed].[VoidOrder] as [OrderHed_VoidOrder],
[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
[OrderHed].[OrderHeld] as [OrderHed_OrderHeld],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[CreditHold] as [Customer_CreditHold],
[SubQuery2].[Calculated_PartNum] as [Calculated_PartNum]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.BTCustNum = Customer.CustNum
inner join (select
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
(CASE
WHEN COUNT(PartNum) = 1
THEN MAX(PartNum)
WHEN COUNT(PartNum) = 2
THEN (
SELECT STUFF(
(SELECT ’ | ’ + PartNum
FROM OrderDtl AS OD
WHERE OD.OrderNum = OrderDtl.OrderNum
FOR XML PATH(‘’)),
1, 3, ‘’
)
)
ELSE (
SELECT STUFF(
(SELECT ’ | ’ + PartNum
FROM OrderDtl AS OD
WHERE OD.OrderNum = OrderDtl.OrderNum
FOR XML PATH(‘’)),
1, 3, ‘’
) + ’ | —’
)
END) as [Calculated_PartNum]
from Erp.OrderDtl as OrderDtl1
group by [OrderDtl1].[OrderNum]) as SubQuery2 on
OrderHed.OrderNum = SubQuery2.OrderDtl1_OrderNum
where (OrderHed.OrderNum = 30265)
Has anyone encountered a similar issue with BAQ errors related to SQL injection, especially when using string concatenation? Any workarounds or best practices would be greatly appreciated. I am currently on Epicor 10.2.
Thanks in advance for any help or insights you can provide!
If you don’t have access to view the compatibility level of the server, you can create a simple BAQ that uses string_agg in a calculated field. If the BAQ runs then you are on Compatibility 14+, if not you will receive an error.
You have to make a Sub-Query, then supply in the Sub-Query SubQuery Criteria, then instead of using it in your Main Query as a JOIN, you can use it in a Calculated field with {SubQueryNameHere}
Epicor will then insert that Sub-Query as a Sub-Select into that field wherever {SubQueryNameHere} is used, you can even use it in Expressions on JOINs etc…
In Epicor, SQL injection checks can sometimes be triggered by certain patterns in BAQ subqueries, particularly when using string manipulation functions like STUFF or complex conditional logic.
Try updating the inner subquery by removing FOR XML PATH or using a simpler aggregation if feasible:
where i can concatenate OrderNums together in one column.
below is the query:
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
(CAST(OrderHed.OrderNum AS VARCHAR) + ‘,’) as [Calculated_OrderNum]
from Erp.OrderHed as OrderHed
where (OrderHed.CustNum = Customer.CustNum FOR XML PATH(‘’)))) , ‘</Calculated_OrderNum>’,‘’),‘<Calculated_OrderNum>’,‘’)) as [Calculated_Orders]
from Erp.Customer as Customer
it is also giving bad sql error. but there is no syntax error.
