Partager via

Announcing SQL Parameterization in DocumentDB

  • Article

We are pleased to announce support for parameterized SQL queries in the Azure DocumentDB REST API and SDKs. Using this feature, you can now write parameterized SQL queries. Parameterized SQL provides robust handling and escaping of user input, preventing accidental exposure of data through “SQL injection” *.

Let's take a look at a sample using the .NET SDK; In addition to plain SQL strings and LINQ expressions, we've added a new SqlQuerySpec class that can be used to build parameterized queries.
Here's a sample that queries a "Books" collection with a single user supplied parameter for author name:

IQueryable<Book> queryable = client.CreateDocumentQuery<Book>(collectionSelfLink, new SqlQuerySpec {
                    QueryText = "SELECT * FROM books b WHERE (b.Author.Name = @name)",
                    Parameters = new SqlParameterCollection()  {
                          new SqlParameter("@name", "Herman Melville")
                     }
});

A few interesting things to note here:

  • SQL parameters in DocumentDB use the familiar @ notation borrowed from T-SQL
  • Parameter values can be any valid JSON (strings, numbers, Booleans, null, even arrays or nested JSON)
  • Since DocumentDB is schema-less, parameters are not validated against any type
  • We could just as easily supply additional parameters by adding additional SqlParameters to the SqlParameterCollection

The DocumentDB REST API also natively supports parameterization. The .NET sample shown above translates to the following REST API call. To use parameterized queries, you need to specify the Content-Type Header as application/query+json and the query as JSON in the body, as shown below.

 POST https://contosomarketing.documents.azure.com/dbs/XP0mAA==/colls/XP0mAJ3H-AA=/docs
HTTP/1.1 x-ms-documentdb-isquery: True 
x-ms-date: Mon, 18 Aug 2014 13:05:49 GMT 
authorization: type%3dmaster%26ver%3d1.0%26sig%3dkOU%2bBn2vkvIlHypfE8AA5fulpn8zKjLwdrxBqyg0YGQ%3d 
x-ms-version: 2014-08-21 
Accept: application/json 
Content-Type: application/query+json 
Host: contosomarketing.documents.azure.com 
Content-Length: 50 
{      
    "query": "SELECT * FROM books b WHERE (b.Author.Name = @name)",     
    "parameters": [          
        {"name": "@name", "value": "Herman Melville"}         
    ] 
}

Queries can be issued against document collections, as well as system metadata collections like Databases, DocumentCollections, and Attachments using the approach shown above. To try this out, download the latest build of the DocumentDB SDK on any of the supported platforms (.NET, Java, Node.js, JavaScript, or Python).

*DocumentDB is not susceptible to the most common kinds of injection attacks that lead to “elevation of privileges” because queries are strictly read-only operations. However, it might be possible for a user to gain access to data they shouldn’t be accessing within the same collection by crafting malicious SQL queries. SQL parameterization support helps prevent these sort of attacks.

Comments

  • Anonymous
    January 15, 2015
    What about the list of updates everyone is voting on like order by? I don't remember this being the priority.  :P

  • Anonymous
    January 18, 2015
    @Mike Everybody does indeed want 'Order By', and for good reason, but SQL Parameterization isn't a feature, its simply a patch to a giant security flaw.

  • Anonymous
    January 20, 2015
    @Mike, remember when we didn't have this important security feature and everyone grabbed their pitch forks and wanted to chase us out of town? :) Order By is being actively worked on but please understand that ordering of resultsets is a little more complex and therefore will take a little longer to implement.