Passing additional parameters to SQL input bindings on function-level

[maersk96]

Let's say i have a function with an HTTP trigger for an SQL input binding at /api/groups.

I know i can pass parameters to an endpoint through the HTTP request body/query. But i would really like to be able to do this from inside the function. So my question/idea is:

• How can i pass parameters to an SQL input binding from inside a function, like context.bindings.groups.parameters.CreatorID = req.user.id?

• How can i perform server-side auth checks inside a function to disallow/modify/overrule an SQL input binding, e.g. blocking certain user roles? I know i can setup access permissions in staticwebapp.config.json, but sometimes i want to make more fine-grained decisions based on the read SQL data.

If this is already possible, could someone please document it.

[Charles-Gagnon]

1. Could you show an example of what the function might look like? You're saying you have a separate function that you want to have call your function with the SQL Input binding? Or is this all the same function?
2. A sample for this would also be good, what kind of "read SQL data" would you be using to make these decisions?

@dzsquared Any ideas here?

[dzsquared]

An input binding is defined to run when the function starts and doesn't take parameters from content of the function. However, 3 options come to mind:

1. If the parameter you're looking to use on the input binding is part of the HTTP request body, you can pass the HTTP trigger a specific C# object that represents the request instead of a generic HttpRequest object. The attributes of the specific C# request object can then be used in the SQL input binding.
2. Imperative bindings (binding during runtime), which I am unfamiliar with, might offer a middle ground. https://learn.microsoft.com/en-us/azure/azure-functions/functions-dotnet-class-library?tabs=v4%2Ccmd#binding-at-runtime
3. Chained Azure Functions, where some input is being processed during the first function, then passed to the 2nd function where it is used in a SQL input binding - likely in conjunction with option 1.

As far as structuring your authorization between SWA, functions, and SQL - if you want to perform additional security checks, there's 2 concepts I would raise for consideration:

1. Before passing the data from your SQL input binding back to the user, parse/filter the dataset and further examine the user's role. If the datasets will be large, this isn't ideal - you'll want to do large set-based operations at the database.
2. SQL's row level security has a few options, including an avenue to define a SQL function that resolves whether a user should have visibility to a specific row based on input parameters to the SQL function. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security

[maersk96]

1. Could you show an example of what the function might look like? You're saying you have a separate function that you want to have call your function with the SQL Input binding? Or is this all the same function?

Optimally, i think, this would be the same function. I think my primary need is to be able to pass the req.user.id to the SQL text/stored command (this would go a long way in terms of querying the correct user-related data, making look-ups in permission tables etc.).

This is how it could look like (shortened):

// index.js
module.exports = async function (context, req, userItems) {
  context.res = {
     body: userItems
  }
}

// function.json
...
{
  "name": "userItems",
  "type": "sql",
  "direction": "in",
  "commandText": "select id, name from Items where creator_id = @UserId",
  "commandType": "Text",
  "parameters": "@UserId = {Request.user.id}",
  "connectionStringSetting": "SqlConnectionString"
}

2. A sample for this would also be good, what kind of "read SQL data" would you be using to make these decisions?

Here is a made up example that combines an input and output binding to read the users permissions from Permissions table and uses this result to determine whether the user is allowed to write to the Groups table.

// index.js
module.exports = async function (context, req, userPermissions) {
  if ( * check if action is allowed using userPermissions * ) {
       context.bindings.groups = req.body
  } else {
      context.res = {
         status: 401
      }
  }
}

// function.json
...
{
  "name": "userPermissions",
  "type": "sql",
  "direction": "in",
  "commandText": "select id, permission from Permissions where user_id = @UserId",
  "commandType": "Text",
  "parameters": "@UserId = {Request.user.id}",
  "connectionStringSetting": "SqlConnectionString"
},
{
  "name": "groups",
  "type": "sql",
  "direction": "out",
  "commandText": "dbo.Groups",
  "commandType": "Text",
  "connectionStringSetting": "SqlConnectionString"
}

I hope it makes sense.

[Charles-Gagnon]

1. You should be able to do that directly - the parameters field is auto-resolved so can reference JSON payloads as described here

https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-expressions-patterns#json-payloads

e.g. using the Products samples we have in our repo:

function.json (note the HTTP method should be post)

    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "post"
      ],
      "route": "getproducts"
    },
    {
      "name": "products",
      "type": "sql",
      "direction": "in",
      "commandText": "select * from Products where Cost = @Cost",
      "commandType": "Text",
      "parameters": "@Cost={payload.cost}",
      "connectionStringSetting": "SqlConnectionString"
    }

and then in the body of the post request you put your payload (which would be the user info in your case)

Note: In this example I could have just had cost directly, but I embedded it in the payload object to better simulate your scenario

{
    "payload": {
        "cost": 100
    }
}

2. For this the example you gave you should be able to do something similar, except the payload would contain multiple sets of data

{
    "user": {
        "id": 1
        // other user info
    },
   "data": {
        // data fields to upsert here
   }
}

function.json (note parameters is using user.id directly)

{
  "name": "userPermissions",
  "type": "sql",
  "direction": "in",
  "commandText": "select id, permission from Permissions where user_id = @UserId",
  "commandType": "Text",
  "parameters": "@UserId = {user.id}",
  "connectionStringSetting": "SqlConnectionString"
},
{
  "name": "groups",
  "type": "sql",
  "direction": "out",
  "commandText": "dbo.Groups",
  "commandType": "Text",
  "connectionStringSetting": "SqlConnectionString"
}

and then in your function you'd have something like you had above (except using req.body.data):

module.exports = async function (context, req, userPermissions) {
  if ( * check if action is allowed using userPermissions * ) {
       context.bindings.groups = req.body.data
  } else {
      context.res = {
         status: 401
      }
  }
}