How to query azure table storage with single quote in PartitionKey

Question

I am migrating some code from the older azure table storage client to the latest release, and have run into an issue that has me stumped: I cannot seem to send a query with a single quote in a partition key without getting a 400 bad request. For example:

public class TestEntity : TableEntity
{
    public string TestProperty { get; set; }
}

public class StorageTester
{
    public static void TestInsert()
    {
        CloudStorageAccount acct = CloudStorageAccount.DevelopmentStorageAccount;
        CloudTableClient client = acct.CreateCloudTableClient();
        CloudTable table = client.GetTableReference("testtable");
        table.CreateIfNotExists();

        // insert a test entity -- this works fine
        TestEntity entity = new TestEntity();
        entity.PartitionKey = "what's up";
        entity.RowKey = "blah";
        entity.TestProperty = "some dataz";

        TableOperation op = TableOperation.Insert(entity);
        table.Execute(op);

        // now query the entity -- explicit query constructed for clarity
        string partitionFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "what's up");
        string rowFilter = TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, "blah");
        string finalFilter = TableQuery.CombineFilters(partitionFilter, TableOperators.And, rowFilter);

        TableQuery<TestEntity> query = new TableQuery<TestEntity>().Where(finalFilter);

        // THIS THROWS 400 ERROR, does not properly encode partition key
        var entities = table.ExecuteQuery(query, new TableRequestOptions { RetryPolicy = new NoRetry() });
        entity = entities.FirstOrDefault();

    }
}

I have tried everything... I tried explicitly setting the FilterString property of the TableQuery, but it performs URL encoding after setting that property, so if I replace the single quote with %27, the % gets double-escaped.

Does anyone have a workaround that would allow me to use the new table storage library without falling back to the old StorageClient library? Note that I have a lot of data already in an existing database, so solutions like "just don't use single quotes in your queries" would be an absolute last resort, as it would require scanning and updating every single record in every existing table -- a maintenance task that I would like to avoid.

Sandrino Di Mattia · Accepted Answer · 2012-11-29 00:06:12Z

5

You need to escape the single quote, but only when filtering (by adding a single quote before the original single quote):

string partitionFilter = TableQuery.GenerateFilterCondition("PartitionKey", 
          QueryComparisons.Equal, "what''s up");

This is because GenerateFilterCondition and CombineFilters turn the filter in a simple string (OData format):

(PartitionKey eq 'what''s up') and (RowKey eq 'blah')

A safer way to use filters would be like this:

string partitionFilter = TableQuery.GenerateFilterCondition("PartitionKey", 
          QueryComparisons.Equal, partitionKey.Replace("'", "''"));

answered Nov 29, 2012 at 0:06

Sandrino Di Mattia

24.9k2 gold badges63 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yellowfive Over a year ago

Thanks -- that is what I was looking for! I actually just figured this out as well by using the old StorageClient and looking at the dev storage logs to see what url it would generate for the same query. I'm pretty sure that the TableQuery code should be handling this automatically though. For example, the InsertOrReplace TableOperation fails on an entity with a single quote in the partition key -- you probably need to manually alter the partition key on entities prior to using that operation. I'll see if I can find the right place to submit this as a bug report to MS.

Yang Xia - Microsoft Over a year ago

An update to this answer, since Windows Azure Storage 2.1, you don't need to escape the single quote in the query. The library will do it. The changelog is here: github.com/Azure/azure-storage-net/blob/master/…

Thomas · Accepted Answer · 2016-04-10 23:20:18Z

2

I am using Windows Azure Storage 7.0.0 and you can use Linq query so that you don't have to escape single quotes :

// Get the cloudtable ...
var table = GetCloudTable();

// Create a query: in this example I use the DynamicTableEntity class
var query = cloudTable.CreateQuery<TestEntity>()
    .Where(d => d.PartitionKey == "what's up" && d.RowKey == "blah");

var entities = query.ToList();

If you inspect the query.FilterString property, you can see that the single quote has been escaped:

"(PartitionKey eq 'what''s up') and (RowKey eq 'blah')"

answered Apr 10, 2016 at 23:20

Thomas

30.5k6 gold badges99 silver badges143 bronze badges

Collectives™ on Stack Overflow

How to query azure table storage with single quote in PartitionKey

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related