How to query contact with apostrophe in SOQL using REST?

It's the same question as https://developer.salesforce.com/forums/?id=906F0000000MMbqIAG, but with a twist: I need to do this via REST.

For example, the REST API documentation uses the following example:

curl https://yourInstance.salesforce.com/services/data/v20.0/query/?q=SELECT+name+from+Account -H "Authorization: Bearer token"

but my SOQL uses email address to lookup a Contact. And this email happens to contain an apostrophe.

What should the query parameter look like for the following SOQL?

SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email=john.o'connor@skynet.com+AND+AccountId='001W010000HnaZhiAJ'

Using this format returns:
'No viable alternative at character \"'

Using "john.o\'connor@skynet.com" returns:
'Bind variables only allowed in Apex code'

So does URI encoding the backslash to %5C (john.o%5C'connor@skynet.com).

I am stuck. Can anyone help?

September 16, 2016
·
Like
0
·
Follow
2

Best Answer chosen by benwalk

benwalk
Finally found the solution: wrap URL in double quotes, and email address in single quotes, with escaping:
curl "https://yourInstance.salesforce.com/services/data/v20.0/query/?q=SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email='john.o\'connor@skynet.com'" -H "Authorization: Bearer token"

September 19, 2016
·
Like
0
·
Dislike
0

karthikeyan perumal
Hello Benwalk,

you can use 2 ways to escape characters in you SOQL Query Via RestAPI call

Way1:
StringEmail='john.o\'connor@skynet.com';
StringEmail= StringEmail.replace('\'','%27');

Or use can use:

StrQuert=StrQuert.replace('\'','%27');

Way2:

you can Use Encoding and Decoding Concept:

String stringToEncode = ''john.o'connor@skynet.com';
String encodedUrl = EncodingUtil.urlEncode(stringToEncode, 'UTF-8');
System.debug(encodedUrl);

Hope This will help you.

Thanks
karthik

September 16, 2016
·
Like
0
·
Dislike
0

benwalk
This is not a solution for curl. Using the following template:
curl https://yourInstance.salesforce.com/services/data/v20.0/query/?q=<SOQL> -H "Authorization: Bearer token"

I've tried the following values for <SOQL> (in the above template):
SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email=john.o'connor@skynet.com

SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email=john.o%27connor@skynet.com

SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email=john.o\'connor@skynet.com

SELECT+Id,+Email,+Name,+Phone+FROM+Contact+WHERE+Email=john.o%5C%27connor@skynet.com

ALL of these return:
ERROR at Row:1:Column:55\nBind variables only allowed in Apex code","errorCode":"MALFORMED_QUERY"}]

I've also tried all of the above variations while wrapping the email address in double quotes. No luck.