Proper way to escape single quote in Kentico REST API "where" condition

Christian Nickel asked on October 12, 2017 17:05

When searching a field using the REST API on a Kentico 9 (hotfix 50) instance, I can no longer search for a value using single quotes in the "where" condition. This use to work on Kentico 8.2.50.

Here are the URLs I'm trying to hit (proprietary information changed)...

This one does not work, it returns a 403 (forbidden) error due to existence of single quotes in where condition:

https://localhost/rest/content/currentsite/en-us/childrenof/Friends ?classnames=MyNS.Friend&format=json&selectonlypublished=false&version=last &where=ShortTitle+=+%27My+name%27%27s+Tom%27

If I change it to search without the single quote, it returns a 200 and proper JSON:

https://localhost/rest/content/currentsite/en-us/childrenof/Friends ?classnames=MyNS.Friend&format=json&selectonlypublished=false&version=last &where=ShortTitle+=+%27His+name+is+Tom%27

How can I properly escape the single-quote in a where statement? Or is there maybe a setting that I need to turn off to allow this?

Thanks for your help!

rest api k9

Correct Answer

Trevor Fayas answered on October 12, 2017 17:34

Have you tried removing the single quote from the Settings -> URLs and SEO -> Forbidden character replacement in url? This may have nothing to do with it, but it may be trying to sanitize the content, but it also may only affect the Portal stuff.

Another possible attempt (although not ideal) is to adjust the where to be where=ShortTitle+LIKE+(%27His+name_s+Tom%27)

leveraging the wildcard _ in like statements.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Christian Nickel answered on October 12, 2017 18:29

I marked your post as the answer because using a LIKE statement worked just fine for me, didn't know about the _ wildcard in SQL.

where=ShortTitle+LIKE+(%27His+name_s+Tom%27)

In case anyone wonders, the forbidden character replacement setting had no effect on this.

In reality, this still isn't answered, can a where statement in the REST API contain single quotes?

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 12, 2017 19:12

you just do url encode: SQL: Where shorttitle = 'My Name Tom' should be where=shorttitle+%3D+%27My+Name+Tom%27

In the doc it says: To avoid ambiguity, ensure that where parameter values are URL encoded. For example, occurrences of the % character should be replaced by the encoded equivalent – %25.

0 votesVote for this answer Mark as a Correct answer

Christian Nickel answered on October 12, 2017 19:31

@Peter - Thanks, but I think you missed the question, URL encoding of = is not the issue, the issue is using single quotes in the where statement.

The statement I'm trying to run (which does not work) is: where=ShortTitle = 'My name''s Tom'

The statement that does work is: where=ShortTitle = 'My Name Top'

The addition of the two single-quotes makes the service return a 403 error.

BTW, the URL was being encoded properly but just ended up in the link I copied from the Chrome's network tab, the only thing that didn't get converted when I pasted was the = sign. Using %3D didn't change anything.

Encoded versions of the where statements:

where=ShortTitle+%3D+%27My+name%27%27s+Tom%27 (doesn't work, returns 403)

where=ShortTitle+3D+%27His+name+is+Tom%27 (works)

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 12, 2017 20:25

Sorry @Christian didn't get it correctly. My bad :(. Now I see the issue. I think you just need to experiment here., for ex.: 'My name''s Tom' = 'My name'+char(39)+'s Tom.'

'My name''s Tom' = "My name's Tom."

1 votesVote for this answer Mark as a Correct answer