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.

Answer 1

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("'", "''"));

Answer 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')"