RetrieveSalesforceObjects() & Salesforce Activities within Journey Builder does not escape apostrophes
Trailblazer Community

RetrieveSalesforceObjects() & Salesforce Activities within Journey Builder does not escape apostrophes

Marketing Cloud Integrations , Marketing Cloud Journey Builder

Last updated 2020-08-05 ·Reference W-5165122 ·Reported By 6 users

Fixed in version Marketing Cloud Release July 2020

Summary
When using the RetrieveSalesforceObject() AMPScript function, you can pull in personalization strings to filter the object you are retrieving. If any result is returned that has a single quote, it will break the SOQL query generated to return Salesforce results.

When using a Sales & Service activity within Journey Builder and a value with an apostrophes is used to be a lookup value it will error on the value with the apostrophe and will not update any values within the activity.

Repro
-Have an integrated account
-Create a text field on the Contact Object called SF_Attribute
-Create an attribute called SF_Attribute in Marketing Cloud and map it to the Salesforce attribute
-Create a subscriber to be used in a send preview
-Make sure the Marketing Cloud attribute for this subscriber has a value of test's
-Put the below in an email

<body>
%%[
Set @SF = RetrieveSalesforceObjects('Contact', 'ID,SF_Attribute__c','SF_Attribute__c','=',AttributeValue('SF_attribute'))
]%%

%%=v(@SF)=%%

</body>

-Send preview to the subscriber and get the below error


Call to retrieve records for salesforceobject Contact failed! Error in the application.
API Fault: Salesforce.com Fault thrown.
Exception Type:MalformedQueryFault
Exception Code:MALFORMED_QUERY
Exception Message:
Contact WHERE SF_Attribute__c = 'test's'
^
ERROR at Row:1:Column:69
unexpected token: 's'
At row:1 and column: 69

Workaround
Use the Replace() AMPScript function to remove single quotes.

Don't use single quotes in attribute values.

Use Synchronized Sources and the fields from these Synchronized Data Extensions in AMPScript rather than using AMPScript to retrieve the values from Sales Cloud.

Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.