How to escape characters in Azure Table queries?

Question

I want to query for rows where a column named message starts with: metric="foo"

I tried encoding = and " with percentage and hex codes but it did not work.

Microsoft documentations says special characters must be encoded but does not tell how: https://learn.microsoft.com/en-us/rest/api/storageservices/querying-tables-and-entities#query-string-encoding

What should the query look like when value being compared contains special characters?

Is that you want to use the parameter "metric" to filter results — Jim Xu, Sep 06 '19 at 02:52
@IgorGatis, if you're using azure sdk, then the sdk already did the tricky for you, you can see my answer below. if it's not the case, please show us your code and which nuget packages you're using. — Ivan Glasenberg, Sep 09 '19 at 07:03
@IgorGatis, hello, just want to check if you can solve your issue now? — Ivan Glasenberg, Oct 17 '19 at 07:13

score 1 · Accepted Answer · answered Sep 09 '19 at 07:03

If you're using azure sdk, then the sdk already did the tricky for you.

In my test, I'm using the latest azure table storage sdk Microsoft.Azure.Cosmos.Table, version 1.0.4.

The test code:

    static void Main(string[] args)
    {
        string connstr = "xxxx";
        var storageAccount = CloudStorageAccount.Parse(connstr);
        CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
        CloudTable table = tableClient.GetTableReference("myCustomer123");

        TableQuery<CustomerEntity> query = new TableQuery<CustomerEntity>();

        string myfilter = TableQuery.CombineFilters(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "ivan"),
                                  TableOperators.And,
                                  //for metric="foo", like below.                                      
                                  TableQuery.GenerateFilterCondition("PhoneNumber", QueryComparisons.Equal, "metric=\"foo\"")
                                  );

        query.FilterString = myfilter;

        var items = table.ExecuteQuery(query);

        foreach (var item in items)
        {
            Console.WriteLine(item.RowKey);
            Console.WriteLine(item.PhoneNumber);
        }

       Console.WriteLine("*****end******");
       Console.ReadLine();

    }

Test result:

score 0 · Answer 2 · answered Sep 06 '19 at 05:10

If you want to use the parameter to filter results, you can use ?$filter=<your parameter>%20eq%20'<vaule>'. For example

var date = DateTime.Now.ToUniversalTime().AddYears(1).ToString("R");
            var CanonicalizedResource = "/" + StorageAccountName + "/people";
            var StringToSign = date + "\n" + CanonicalizedResource;
            // List the containers in a storage account.
            // ListContainersAsyncREST(StorageAccountName, StorageAccountKey, CancellationToken.None).GetAwaiter().GetResult();
            var hmacsha = new HMACSHA256();
            hmacsha.Key = Convert.FromBase64String(StorageAccountKey);
         var sig=   hmacsha.ComputeHash(UTF8Encoding.UTF8.GetBytes(StringToSign));
            var sig1 = Convert.ToBase64String(sig);

            Console.WriteLine(sig1);
            String uri = "https://jimtestperfdiag516.table.core.windows.net/people" + "?$filter=PartitionKey%20eq%20'Jim'";
            HttpClient client = new HttpClient();
            var httpRequestMessage = new HttpRequestMessage(HttpMethod.Get, uri);


            httpRequestMessage.Headers.Add("x-ms-date", date);

            var str = "SharedKeyLite " + StorageAccountName + ":" + sig1;
            httpRequestMessage.Headers.TryAddWithoutValidation("Authorization", str);
            httpRequestMessage.Headers.Add("x-ms-version", "2017-04-17");
            httpRequestMessage.Headers.Add("Accept", "application/json;odata=fullmetadata");
            var results = client.SendAsync(httpRequestMessage).Result;
            var response = results.Content.ReadAsStringAsync().Result;
           var objs = JsonConvert.DeserializeObject(response);


            Console.WriteLine(objs);

How to escape characters in Azure Table queries?

2 Answers2