Edit

Parameterized queries in Azure Cosmos DB

  • Article

APPLIES TO: NoSQL

Azure Cosmos DB supports queries with parameters expressed by the familiar @ notation. Parameterized SQL provides robust handling and escaping of user input, and prevents accidental exposure of data through SQL injection.

Examples

For example, you can write a query that takes lastName and address.state as parameters, and execute it for various values of lastName and address.state based on user input.

    SELECT *
    FROM Families f
    WHERE f.lastName = @lastName AND f.address.state = @addressState

You can then send this request to Azure Cosmos DB as a parameterized JSON query like the following:

    {
        "query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND f.address.state = @addressState",
        "parameters": [
            {"name": "@lastName", "value": "Wakefield"},
            {"name": "@addressState", "value": "NY"},
        ]
    }

The following example sets the TOP argument with a parameterized query:

    {
        "query": "SELECT TOP @n * FROM Families",
        "parameters": [
            {"name": "@n", "value": 10},
        ]
    }

Parameter values can be any valid JSON: strings, numbers, Booleans, null, even arrays or nested JSON. Since Azure Cosmos DB is schemaless, parameters aren't validated against any type.

Here are examples for parameterized queries in each Azure Cosmos DB SDK:

Next steps