CRM OData (REST) queries and special characters

In this blog, we will discuss "How to handle special characters in CRM OData queries". Every now an then we have to query the data based on the fields other than entity Ids. Most of the time OData queries works as expected except if the filtering parameter conatins special characters. OData queries does not support special characters as a filter parameters.

For example, if you are searching for a customer with an apostrophe in the  name (“Simon O’ Donnel”),  the OData query will throw a following error message.

If you try to search for a customers with special characters in the email address (abc#123@test.com) , we  encountered the following error message.

The reason is that these special characters has different meaning when used in URLs. The JavaScript “encodeUri” or  “encodeUriComponent” does not solve this problem. Here is the list of the special characters that needs to be replaced when used in the OData queries.
https://msdn.microsoft.com/en-us/library/aa226544(SQL.80).aspx

Special character	Special meaning	Hexadecimal value
+	Indicates a space (spaces cannot be used in a URL).	%2B
/	Separates directories and subdirectories.	%2F
?	Separates the actual URL and the parameters.	%3F
%	Specifies special characters.	%25
#	Indicates bookmarks.	%23
&	Separator between parameters specified in the URL.	%26

The apostrophe is not mentioned in the table. The apostrophe needs to be replaced with double apostrophes.
Now you can write a separate function to replace these characters with the appropriate hexadecimal value.

Note: Do not use the “JavaScript String replace() Method”. It will replace the first occurrence of the special characters. if you  have 2 occurence of the same special characters in the filtering parameter, it will fail. So use the regular expression to replace the characters.

You can create a  separate function and call it for all the filtering parameter before you pass it to the query.

function replaceSpecialCharacters(attribute) {
  // replace the single quotes
     attribute = attribute.replace(/'/g, "''");

     attribute = attribute.replace(/"+"/g, "%2B");
     attribute = attribute.replace(/\//g, "%2F");
     attribute = attribute.replace(/"?"/g, "%3F");
     attribute = attribute.replace(/%/g, "%25");
     attribute = attribute.replace(/#/g, "%23");
     attribute = attribute.replace(/&/g, "%26");
     return attribute;
}

Happy Coding…