How to escape special characters from Pre-processor (MODIFYUSERDATAJSON )query in REST connector

[kbandi]

For example 

• When the user attribute has a space in the name. eg - Firstname= Mario Rivero
• When the user attribute uses an apostrophe, eg - Lastname = J'ose

While importing the users for importing Jobs , we are trying to import the users without special characters like above's one.

We are dealing email generation and display generation from MODIFYUSERDATAJSON . Hence need to escape the special characters also from MODIFYUSERDATAJSON query.

We have tried the below method but did't work.

"UPDATE NEWUSERDATA SET firstname = REPLACE(firstname, ‘`’, '')",

"UPDATE NEWUSERDATA SET lastname = REPLACE(lastname, ‘#’, '')",

Can you please suggest here?

Thanks in Advance .

[rushikeshvartak]

Refer : https://forums.saviynt.com/t5/saviynt-knowledge-base/json-handling-special-characters-in-db-connecti...

Example 

{"ROLE"  : ["insert into  SYSADM.PS_AVB_SAVIYNT_ACT(EMPLID , OPRID, FIRST_NAME , MIDDLE_NAME, LAST_NAME, EMAILID, ROLENAME, DATETIME_ADDED, ACTION_MSG, STATUS1 , DATETIME_UPDATED) values ('${user.username}','${task.accountName}' ,'${org.apache.commons.lang.StringEscapeUtils.escapeSql(user.firstname)}', '${user.middlename}', '${org.apache.commons.lang.tringEscapeUtils.escapeSql(user.lastname)}', '${user.email}','${task.entitlement_valueKey.entitlement_value}', sysdate, 'ADD', 'OPEN', sysdate)"]}

[kbandi]

The above is for DB connector insert query . we are trying to escape special characters from preprocessor query . Can you please elaborate what we need to use or test.

for firstname and lastname

[rushikeshvartak]

Use database function to replace characters 

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter02-Identity-Repository/Dat...

[mickeymouse]

I think FN_EIC_REPLACE doesn't work on PREPROCESSOR for 23.10. It should be implement on 23.12, but I didn't test it yet. 

[kbandi]

Hi Team ,

Thanks for the update.I have tried the below ways still not working .

Can you please suggest if anything wrong in the below queries.

1. UPDATE NEWUSERDATA SET lastname = org.apache.commons.lang.StringEscapeUtils.escapeSql(lastname)
2. UPDATE NEWUSERDATA SET lastname = REPLACE(lastname, ‘&’, '')
3. UPDATE NEWUSERDATA SET lastname = FN_EIC_REPLACE(lastname, null)
4. UPDATE NEWUSERDATA SET lastname = REGEXP_REPLACE(lastname,'')

[rushikeshvartak]

Do you have data set with FN_EIC_REPLACE and value in attributes you trying to replace

[kbandi]

Yes , we have data set and value for lastname that we tried

[rushikeshvartak]

Share logs

[rushikeshvartak]

{
"ADDITIONALTABLES": {},
"COMPUTEDCOLUMNS": [
"customproperty4",
"customproperty5",
"EMPLOYEETYPE"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET EMPLOYEETYPE = 'organizational/white-collar/external'",
"UPDATE NEWUSERDATA SET customproperty4 = FN_EIC_REPLACE(firstname,'REPLACE_MAP_CUSTOM')",
"UPDATE NEWUSERDATA SET customproperty5 = FN_EIC_REPLACE(lastname,null)"
]
}

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter03-User-Management/User-Im...