[SOLVED] Filtering on data that has single quotes in them '


#1

Hei,

Currently in the web ui when passing this $filter=data/title/iv eq 'Test alex's article' I get the following error
50

I am guessing that single quotes are not possible to use in the filter value? Is there any workaround to filter or let Squidex sanitise data that contains single quotes '?

The example above was just to show you the issue, but we experienced the error first when using the API on a schema we call testimonials that is user inputed data that we get from a third party. The fields we were using triggered a similar query error.

 { 
ok: false,
url:
  'https://[...]/api/content/[...]/articles?%24top=1&%24filter=data%2Ftitle%2Fiv%20eq%20\'Test%20alex\'s%20article\'',
  status: 400,
  statusText: 'Bad Request',
  headers:
  { server: 'nginx/1.14.1',
date: 'Wed, 14 Aug 2019 08:35:18 GMT',
      'content-type': 'application/json; charset=utf-8',
      'transfer-encoding': 'chunked',
      connection: 'close',
      'strict-transport-security': 'max-age=31536000' },
  text:
    '{"message":"Query $filter clause not valid.","details":["Syntax error at position 29 in \'data/title/iv eq \'Test alex\'s article\'\'."],"statusCode":400}',
  data:
    '{"message":"Query $filter clause not valid.","details":["Syntax error at position 29 in \'data/title/iv eq \'Test alex\'s article\'\'."],"statusCode":400}',
  body:
  { message: 'Query $filter clause not valid.',
details: [Array],
         statusCode: 400 },
  obj:
  { message: 'Query $filter clause not valid.',
details: [Array],
         statusCode: 400 } 
}

For now we have restricted which fields we use for filtering to not contain special characters. The way we achieve this is only use numbers or a separate field which is a hash of the fields that contain single quotes. This does put an overhead on us so was hopping to find a workaround.

Other things we tried is escaping the single quote in the value but that did not help.

Thanks.


#2

Have you tried to escape it with \'


#3

Yes, tried it both in the API request and the web ui. Showing it below in case I misunderstood you.

$filter=data/title/iv eq 'Test alex\'s article'

and the api result

   { ok: false,
     url:
      'https://[...]/api/content/[...]/articles?%24top=1&%24filter=data%2Ftitle%2Fiv%20eq%20\'Test%20alex\'s%20article\'',
     status: 400,
     statusText: 'Bad Request',
     headers:
      { server: 'nginx/1.14.1',
        date: 'Wed, 14 Aug 2019 08:42:35 GMT',
        'content-type': 'application/json; charset=utf-8',
        'transfer-encoding': 'chunked',
        connection: 'close',
        'strict-transport-security': 'max-age=31536000' },
     text:
      '{"message":"Query $filter clause not valid.","details":["Syntax error at position 29 in \'data/title/iv eq \'Test alex\'s article\'\'."],"statusCode":400}',
     data:
      '{"message":"Query $filter clause not valid.","details":["Syntax error at position 29 in \'data/title/iv eq \'Test alex\'s article\'\'."],"statusCode":400}',
     body:
      { message: 'Query $filter clause not valid.',
        details: [Array],
        statusCode: 400 },
     obj:
      { message: 'Query $filter clause not valid.',
        details: [Array],
        statusCode: 400 } } }

#4

http://docs.oasis-open.org/odata/odata/v4.01/cs01/part2-url-conventions/odata-v4.01-cs01-part2-url-conventions.html#sec_URLComponents

Here is says double single quote


#5

Double single quote works and seeing no errors in API or UI.

Thanks a lot.


#6

I added the info to the docs, thank you very much.