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 queryable = client.CreateDocumentQuery(
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.
