How to escape a single quote to be used in an OData query?

Question

I am using OData to query my database. The following line of code works fine when “adapterName” just contains text.

ds.query('/DataAdapters?$filter=Name eq \'' + adapterName + '\'', ifmgr_CreateAdapter_Step1, onGenericFailure, '');

If “adapterName” contains a single quote it fails. I tried escaping the single quote by using the following code:

adapterName = adapterName.replace(/\'/g, '\\\'');

Although this correctly escapes the user defined text the function still fails. Can anyone tell me what the correct format is for text in the query?

Answer 1

Actually %27 is not a solution. The correct way to escape is to place two single quotes into the string instead one. In example "o''clock"

Answer 2

I want to expand upon the answer a bit so that it also applies to calling an oData Service Operation Action. The answer posted answer is correct, but there is a specific order in which the parameters to a service operation must encoded.

oData Service Operations receive primitive type parameters where strings are enclosed in a ' such that a valid url (pre encoding) will be as such

AddString?value='o''clock'

This will cause the server to see

AddString?value='o'

and

'clock'

will produce "Bad Request - Error in query syntax."

To correct this, you must double escape the ' and UrlEncode it prior to insertion into the url.

Do not UrlEncode the url itself.

Here's an example that will work.

// value passed as "o'clock"
public async Task AddString(string value)
{
    // Escape ' with '' and UrlEncode value
    value = HttpUtility.UrlEncode(value.Replace("'", "''"));

    string url = String.Format("AddString?value='{0}'", value);

    // No need to UrlEncode url here as dynamic content has already been escaped 

    // Execute .....
}

[WebGet]
public void AddString(string value) 
{
    // here value will be "o'clock"
}

Answer 3

It's actually described in oData docs: http://docs.oasis-open.org/odata/odata/v4.01/cs01/part2-url-conventions/odata-v4.01-cs01-part2-url-conventions.html#sec_URLComponents

For example, one of these rules is that single quotes within string literals are represented as two consecutive single quotes.

Example 3: valid OData URLs:

http://host/service/People('O''Neil')

http://host/service/People(%27O%27%27Neil%27)

http://host/service/People%28%27O%27%27Neil%27%29

http://host/service/Categories('Smartphone%2FTablet')

Example 4: invalid OData URLs:

http://host/service/People('O'Neil')

http://host/service/People('O%27Neil')

http://host/service/Categories('Smartphone/Tablet')

The first and second examples are invalid because a single quote in a string > literal must be represented as two consecutive single quotes. The third example is invalid because forward slashes are interpreted as path segment separators and Categories('Smartphone is not a valid OData path segment, nor is Tablet').

Answer 4

When using wit substringof it needs to be escaped by having 4 instead of 1 apostrophe:

a'b -> $filter=(substringof('a''''b', FirstName))

Answer 5

Instead of using $filter=Title eq 'text'

I am using the oData startswith() function.

$filter=startswith(Title, key)

and then I pass in as much of key as I can.

var pos = key.indexOf("'");
if(pos > -1) {
key = key.substring(0, pos);
}