Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to replace special character(') in CREATEACCOUNTJSON (DB Connector)

axelB
New Contributor III
New Contributor III

Hello,

there was already a topic started around this request, but i don't see a sollution, can someone please provide me the correct way how to do this?

How to replace special character(') in update acco... - Saviynt Forums - 42103

We are using as Connection  : 

axelB_0-1715852030503.png

I have tested all, but all proposals are not working :

Example Lastname = O'Connor Firstname = Rahmel

test 1:
{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}','${org.apache.commons.lang.StringEscapeUtils.escapeSql(user.lastname)}')"
}
-> Provisioning Comments : SAV - Error while creating account - OCONNRD- Exception occured in Create Account Query - Incorrect syntax near 'Connor'. SAV - Error while creating account - OCONNRD-Error parsing JSON


test 2:
{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}',"${user.lastname}")"
}
-> Provisioning Comments : SAV - Error while creating account - OCONNRD-Error parsing JSON

test 3:
{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}','${user.lastname.replaceAll('[^a-zA-Z0-9 ]','')}')"
}
-> Provisioning Comments : SAV - Error while creating account - OCONNRD- Exception occured in Create Account Query - Incorrect syntax near 'Connor'.

Test 4:
{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}','${user.lastname.replaceAll(/'/, "")}')"
}
-> Provisioning Comments : SAV - Error while creating account - OCONNRD- Exception occured in Create Account Query - Incorrect syntax near 'Connor'.

Test 5:
{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}','${user.lastname.replaceAll(''','')}')"
}
-> Provisioning Comments : SAV - Error while creating account - OCONNRD- Exception occured in Create Account Query - Incorrect syntax near 'Connor'.

 

 

13 REPLIES 13

axelB
New Contributor III
New Contributor III

We want to transition from Sailpoint to Saviynt, and in sailpoint this was working and the sql database was accepting the special character. So we need also Saviynt to be able to write that Lastname to the SQL database (now it is just not processing it and failing the task):

example in our prod sql database populated by sailpoint:

axelB_0-1715853560609.png

 

naveenss
All-Star
All-Star

Hi @axelB can you try below?

{
"createaccountqry": "insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values ('${user.firstname}','${user.lastname.replaceAll(\"'\", \"\\\\'\")}')"
}
Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

axelB
New Contributor III
New Contributor III

Hi Naveen, 

i just tried, also not working : 

I guess we are not the first customers for Saviynt that need this working, as a lot of Last and First Names have that character in it.

I found some other documentation, but this i already tried and is not working:

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

 

 

axelB_0-1715855023489.png

 

{
"CreateAccountQry":
[
"insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname,eco_emp_lastname) values '('${user.firstname}','${user.lastname.replaceAll(''','')}')"

]
}


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Hello, 

This syntax is exact the same as i posted above in my first post (Test 5) , and is not working: is there no test environment that you can test this so we know the exact syntax. 

Can you please ask someone who tested it in a test environment and provide the correct syntax?

axelB_0-1715930827599.png

 

 

Nagateja_K
New Contributor II
New Contributor II

Hi @axelB ,

Only use replace user.lastname.replace(''','') I have used this in one of create account Json and it works.

Regards,
Nagateja Ksheerasgar

axelB
New Contributor III
New Contributor III

Hello,

i just tried with 2 users, 1 user with a special character, and 1 user without. Both failing with this syntax, and i do not see any Provisioning Comments:

axelB_0-1715932166635.pngaxelB_1-1715932178109.png

 

 

{
"CreateAccountQry": [
"insert into dbo.TEST_ESSUSERBROKENOUT (eco_emp_firstname, eco_emp_lastname) values ('${user.firstname}','${user.lastname.replaceAll(\"'\", \"\")}')"
]
}


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Nagateja_K
New Contributor II
New Contributor II

Hi @axelB ,

Please use this method user.lastname.replace(\"'\", \"''\") it will replace single quotes.

Regards
Nagateja Ksheerasagar

axelB
New Contributor III
New Contributor III

Sorry to all , but all the following i tried, and none of them are working:

'${org.apache.commons.lang.StringEscapeUtils.escapeSql(user.lastname)}'
'${user.lastname.replaceAll('[^a-zA-Z0-9 ]','')}'
'${user.lastname.replaceAll(/'/, "")}'
'${user.lastname.replaceAll(''','')}'
'${user.lastname.replace(''','')}'
'${user.lastname.replace(\"'\", \"''\")'

is this replace or replaceAll even a command that works , because when creating a user without a special character, then create json fails.

Test user Emma Whyte, and as soon as i place a syntax with Replace or ReplaceAll, it is not doing anything. Even id the LAstname has no ' in the last name.

 

@axelB  can you try below please :

${user.lastname.replaceAll("'", "''")}

or

'${user.lastname.replaceAll("[^a-zA-Z0-9]","")}'


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

axelB
New Contributor III
New Contributor III

Hello ,

last 3 syntax tries failed as well , below all the syntax that already has been tried:

'${org.apache.commons.lang.StringEscapeUtils.escapeSql(user.lastname)}'
'${user.lastname.replaceAll('[^a-zA-Z0-9 ]','')}'
'${user.lastname.replaceAll(/'/, "")}'
'${user.lastname.replaceAll(''','')}'
'${user.lastname.replace(''','')}'
'${user.lastname.replace(\"'\", \"''\")'
'${user.lastname.replaceAll("'", "''")}'
'${user.lastname.replaceAll("[^a-zA-Z0-9]","")}'
'${user.lastname.replace("'", "''")}'
'${user.lastname.replace("[^a-zA-Z0-9]","")}'

axelB_1-1716276471510.png

 

 

 

 

axelB
New Contributor III
New Contributor III

Hello, 

after another proposal (from john.blood@saviynt.com ) to use the following syntax, it is still not the right syntax, so not working:

'${user.lastname.replaceAll(\"'\", \"\\\\'\")}'
'${user.lastname.replace(\"'\", \"\\\\'\")}'

 

All tested syntax, which are no sullution and not working :

'${org.apache.commons.lang.StringEscapeUtils.escapeSql(user.lastname)}'
'${user.lastname.replaceAll('[^a-zA-Z0-9 ]','')}'
'${user.lastname.replaceAll(/'/, "")}'
'${user.lastname.replaceAll(''','')}'
'${user.lastname.replace(''','')}'
'${user.lastname.replace(\"'\", \"''\")'
'${user.lastname.replaceAll("'", "''")}'
'${user.lastname.replaceAll("[^a-zA-Z0-9]","")}'
'${user.lastname.replace("'", "''")}'
'${user.lastname.replace("[^a-zA-Z0-9]","")}'

'${user.lastname.replaceAll(\"'\", \"\\\\'\")}'
'${user.lastname.replace(\"'\", \"\\\\'\")}'

 

when testing the last 2 syntax, there is also no propivsionning comment visible, it just fails :

axelB_0-1716356766496.png