Getting Bad request; Syntax error- invalid string literal if backslash is present in query

Author: Ashish Agrawal

Originally Sourced from: https://stackoverflow.com/questions/74371021/getting-bad-request-syntax-error-invalid-string-literal-if-backslash-is-presen

I am trying to search for a record in Cosmos DB using Azure SDK for Python. I have a string in my query that contains double backslash.

Azure functions core tools version - 3
Python 3.7.5

'SELECT * from c where ... "Cardiac Imaging Followup\\unplanned")'

With this Query string, I receive an error like below:

 azure.cosmos.errors.HTTPFailure: Status code: 400
{"code":"BadRequest","message":"Message: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":327,\"end\":355},\"code\":\"SC1012\",\"message\":\"Syntax error, invalid string literal token '\\\"Cardiac Imaging Followup\\\\un'.\"}]}\r\nActivityId: 4155710f-facb-4b96-a6b9-169f6e463f13, Microsoft.Azure.Documents.Common/2.14.0"} 

However, when we use the same query on Azure Cosmos DB Web Data explorer, it works.

I assume this is due to escape characters.

  1. Tried with one backslash - Did not work on SDK, Did not work on Web Data explorer
  2. Tried with two backslash - Did not work on SDK, Worked on Web Data explorer

I tried escaping the string (with double \) in SDK using re.escape, but that also did not work.