Parameterized queries - Query language in Cosmos DB (in Azure and Fabric)

Cosmos DB (in Azure and Fabric) 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 upperPriceLimit as a parameter, and execute it for various values of price based on user input.

SELECT
    *
FROM
    p
WHERE
    (NOT p.onSale) AND
    (p.price BETWEEN 0 AND @upperPriceLimit)

You can then send this request to Cosmos DB as a parameterized JSON query object.

{
  "query": "SELECT * FROM p WHERE (NOT p.onSale) AND (p.price BETWEEN 0 AND @upperPriceLimit)",
  "parameters": [
    {
      "name": "@upperPriceLimit",
      "value": 100
    }
  ]
}

This next example sets the TOP argument with a parameterized query:

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

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

Note

Parameter names in the parameters array must include the leading @ symbol in the name field (for example, "name": "@upperPriceLimit"). Parameters are not type-validated at query time.

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

• .NET SDK
• Java
• Node.js
• Python

Related content

• SELECT clause
• WHERE clause