Dynamic attribute issue in v24.4

[shivmano]

Hi Team - 

I have 2 dynamic attributes for an endpoint persona and requiredPermission. I have the below queries for each of the dynamic attributes

persona (single selected from SQL query) - select distinct entitlement_value as ID from entitlement_values where entitlementtypekey in (287)

requiredPermission (multi select from sql query)- select distinct displayname as ID from entitlement_values where entitlementtypekey in (289) and customproperty1 like '%${persona}%' order by ID

The issue is persona is showing the values out of the first query just fine but when I select the persona from the list , requiredPermission is not showing up the values as expected although the same works on data analyzer. 

Below is the error I see in logs. Looks like additional quotes are being included to the parent DA value. Can someone please advise why this is happening and how can we handle this? 

2024-07-11T17:21:36+05:30-ecm--null-n5jj4--2024-07-11T11:51:36.545697093Z stdout F 2024-07-11 11:51:36,545 [http-nio-8080-exec-13] DEBUG workflow.WorkflowmanagementController - SQL query is select distinct displayname as ID from entitlement_values where entitlementtypekey in (289) and customproperty1 like '%'Field Service Engineer (3rd Party)'%' order by ID
2024-07-11T17:21:36+05:30-ecm--null-n5jj4--2024-07-11T11:51:36.546970253Z stderr F 11-Jul-2024 11:51:36.546 WARNING [http-nio-8080-exec-13] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: select distinct displayname as ID from entitlement_values where entitlementtypekey in (289) and customproperty1 like '%'Field Service Engineer (3rd Party)'%' order by ID because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Field Service Engineer (3rd Party)'%' order by ID' at line 1
2024-07-11T17:21:36+05:30-ecm--null-n5jj4--2024-07-11T11:51:36.547229017Z stdout F 2024-07-11 11:51:36,546 [http-nio-8080-exec-13] ERROR workflow.WorkflowmanagementController - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Field Service Engineer (3rd Party)'%' order by ID' at line 1

Thank you

[naveenss · Accepted solution]

Hi @shivmano  can you try the below query for requiredPermissions?

select distinct displayname as ID from entitlement_values where entitlementtypekey in (289) and customproperty1 REGEXP ${persona} order by ID

[rushikeshvartak · Accepted solution]

select distinct displayname as ID from entitlement_values where entitlementtypekey in (289) and customproperty1 like '%${persona.replace("'","")}%' order by ID