how to correctly substitute query/route parameter in Azure Function Cosmos DB input binding sqlQuery

user_7093617 0
Viewed 169

New to SQL, function and cosmos db, sorry

I'm using Javascript, try to use some route parameter and query parameter from http trigger to retrieve data from cosmos db use its input binding.

In "sqlQuery" of cosmos db input binding, these route/query parameter can be refered with {key}. When I try to use {key} in SELECT clause, it resolved as string and cause some problem.

  1. I want use TOP n to filter, since the {max} is resolved as a string, I try to use CAST/CONVERT to conver to number, get different errors.

"sqlQuery": "SELECT TOP {max} * FROM c" Error: TOP need a number

"sqlQuery": "SELECT TOP CAST({max} AS int) * FROM c" Error: syntax near

  1. I want to select some properties within JSON, i figure out I should use c[{telemetry}], it does works, but the result are JSON with key name = "$1",

"sqlQuery": "SELECT TOP 10 c[{telemetry}] FROM c"

I get {$1: 25.3} and I expect something like {temperature: 25.3}

  1. If I use AS to convert, I get syntax error.

"sqlQuery": "SELECT TOP 10 c[{telemetry}] AS {telemetry} FROM c"

azure-cosmosdb azure-functions
1 Answers

So firstly, don't forget to sanitize your substitutions - otherwise, you can expose the function to SQL injection exploits: https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-parameterized-queries

An example of how I have seen what you are looking to do:

    const getObjectSql = {
        query: `SELECT TOP @limit * from q
                WHERE q.id = @id
                    AND q.status = @status`,
        parameters: [
            {
                name: "@limit",
                value: parseInt(limit ?? "10"),
            },
            {
                name: "@id",
                value: id,
            },
            {
                name: "@status",
                value: status,
            },
        ],
    };
user_173629140
Related