How the prevent Azure table injection?

Question

Is there a general way to prevent azure storage injection.

If the query contains a user entered string for example his name. Then it is possible to do some injection like: jan + ' or PartitionKey eq 'kees. This will and up getting an object jan and an object with the partitionKey kees.

One option is URLEncoding. In this case ' and " are encoded. And the above injection is not possible anymore.

Is this the best option or are there better ones?

Answer 1

Per my experience, I realize that there is two general ways to prevent azure storage table injection. The one is replace the string ' with the other string such as ; , " or URLEncode string of '. This is your option. The other is storage table key using an encoding format(such as Base64) instread of plain content.

This is my test Java program as follows:

import org.apache.commons.codec.binary.Base64;

import com.microsoft.azure.storage.CloudStorageAccount;
import com.microsoft.azure.storage.table.CloudTable;
import com.microsoft.azure.storage.table.CloudTableClient;
import com.microsoft.azure.storage.table.TableOperation;
import com.microsoft.azure.storage.table.TableQuery;
import com.microsoft.azure.storage.table.TableQuery.QueryComparisons;

public class TableInjectTest {

    private static final String storageConnectString = "DefaultEndpointsProtocol=http;" + "AccountName=<ACCOUNT_NAME>;"
            + "AccountKey=<ACCOUNT_KEY>";

    public static void reproduce(String query) {
        try {
            CloudStorageAccount storageAccount = CloudStorageAccount.parse(storageConnectString);
            CloudTableClient tableClient = storageAccount.createCloudTableClient();
            // Create table if not exist.
            String tableName = "people";
            CloudTable cloudTable = new CloudTable(tableName, tableClient);
            final String PARTITION_KEY = "PartitionKey";
            String partitionFilter = TableQuery.generateFilterCondition(PARTITION_KEY, QueryComparisons.EQUAL, query);
            System.out.println(partitionFilter);
            TableQuery<CustomerEntity> rangeQuery = TableQuery.from(CustomerEntity.class).where(partitionFilter);
            for (CustomerEntity entity : cloudTable.execute(rangeQuery)) {
                System.out.println(entity.getPartitionKey() + " " + entity.getRowKey() + "\t" + entity.getEmail() + "\t"
                        + entity.getPhoneNumber());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*
     * The one way is replace ' with other symbol string
     */
    public static String preventByReplace(String query, String symbol) {
        return query.replaceAll("'", symbol);
    }

    public static void addEntityByBase64PartitionKey() {
        try {
            CloudStorageAccount storageAccount = CloudStorageAccount.parse(storageConnectString);
            CloudTableClient tableClient = storageAccount.createCloudTableClient();
            // Create table if not exist.
            String tableName = "people";
            CloudTable cloudTable = new CloudTable(tableName, tableClient);
            String partitionKey = Base64.encodeBase64String("Smith".getBytes());
            CustomerEntity customer = new CustomerEntity(partitionKey, "Will");
            customer.setEmail("[email protected]");
            customer.setPhoneNumber("400800600");
            TableOperation insertCustomer = TableOperation.insertOrReplace(customer);
            cloudTable.execute(insertCustomer);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // The other way is store PartitionKey using encoding format such as Base64
    public static String preventByEncodeBase64(String query) {
        return Base64.encodeBase64String(query.getBytes());
    }

    public static void main(String[] args) {
        String queryNormal = "Smith";
        reproduce(queryNormal);
        /*
         * Output as follows:
         * PartitionKey eq 'Smith'
         * Smith Ben    [email protected] 425-555-0102
         * Smith Denise [email protected]  425-555-0103
         * Smith Jeff   [email protected]    425-555-0105
         */
        String queryInjection = "Smith' or PartitionKey lt 'Z";
        reproduce(queryInjection);
        /*
         * Output as follows:
         * PartitionKey eq 'Smith' or PartitionKey lt 'Z'
         * Webber Peter [email protected]   425-555-0101             <= This is my information
         * Smith Ben    [email protected] 425-555-0102
         * Smith Denise [email protected]  425-555-0103
         * Smith Jeff   [email protected]    425-555-0105
         */
        reproduce(preventByReplace(queryNormal, "\"")); // The result same as queryNormal
        reproduce(preventByReplace(queryInjection, "\"")); // None result, because the query string is """PartitionKey eq 'Smith" or PartitionKey lt "Z'"""
        reproduce(preventByReplace(queryNormal, "&")); // The result same as queryNormal
        reproduce(preventByReplace(queryInjection, "&")); // None result, because the query string is """PartitionKey eq 'Smith& or PartitionKey lt &Z'"""
        /*
         * The second prevent way
         */
        addEntityByBase64PartitionKey(); // Will Smith
        reproduce(preventByEncodeBase64(queryNormal));
        /*
         * Output as follows:
         * PartitionKey eq 'U21pdGg='
         * U21pdGg= Will    [email protected]  400800600     <= The Base64 string can be decoded to "Smith"
         */
        reproduce(preventByEncodeBase64(queryInjection)); //None result
        /*
         * Output as follows:
         * PartitionKey eq 'U21pdGgnIG9yIFBhcnRpdGlvbktleSBsdCAnWg=='
         */
    }

}

I think that the best option is choose a suitable way to prevent query injection on the basis of application sence.

Any concerns, please feel free to let me know.