.NET 7.0独立进程Azure Function传递含=参数至SQL存储过程

问题说明 #

基于.NET 7.0独立进程的Azure Function，通过HttpTrigger读取URL参数传递给SQL存储过程时，加密后的CustomerKey参数因包含=符号，触发Sql扩展运行时异常。官方规则明确：参数名称和值都不能包含逗号(,)或等号(=)。

原Function代码：

[Function("GetCustomers")]
public static async Task<HttpResponseData>  Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "customers/{CustomerKey}/{batchsize:int}")] HttpRequestData req,
    ILogger log,
     [SqlInput(commandText: "storedprocName", commandType: System.Data.CommandType.StoredProcedure, 
        parameters: "@CustomerKey={CustomerKey},@BatchSize={batchsize}", connectionStringSetting: "SqlConnectionString")]              
        IAsyncEnumerable<customer> customers)
{ ...}

触发的异常信息：

System.Private.CoreLib: Exception while executing function: Functions.GetCustomers. Microsoft.Azure.WebJobs.Extensions.Sql: Parameters must be separated by "," and parameter name and parameter value must be separated by "=", i.e. "@param1=param1,@param2=param2". To specify a null value, use null, as in "@param1=null,@param2=param2".To specify an empty string as a value, simply do not add anything after the equals sign, as in "@param1=,@param2=param2".

可行解决方案 #

方法1：手动构建SQL命令（绕过绑定参数解析限制） #

放弃使用SqlInput绑定的parameters属性，在函数体内直接创建SQL连接和命令，完全控制参数传递逻辑，不受绑定格式限制。

示例代码：

[Function("GetCustomers")]
public static async Task<HttpResponseData> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "customers/{CustomerKey}/{batchsize:int}")] HttpRequestData req,
    ILogger log,
    IConfiguration configuration)
{
    var customerKey = req.RouteValues["CustomerKey"].ToString();
    var batchSize = int.Parse(req.RouteValues["batchsize"].ToString());
    var connectionString = configuration.GetConnectionString("SqlConnectionString");
    
    var customers = new List<customer>();
    using (var conn = new SqlConnection(connectionString))
    {
        await conn.OpenAsync();
        using (var cmd = new SqlCommand("storedprocName", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            // 直接添加参数，无需担心值中的特殊字符
            cmd.Parameters.Add("@CustomerKey", SqlDbType.NVarChar).Value = customerKey;
            cmd.Parameters.Add("@BatchSize", SqlDbType.Int).Value = batchSize;
            
            using (var reader = await cmd.ExecuteReaderAsync())
            {
                // 读取数据并映射到customer对象（根据实际字段调整）
                while (await reader.ReadAsync())
                {
                    customers.Add(new customer
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1)
                    });
                }
            }
        }
    }
    
    var response = req.CreateResponse(HttpStatusCode.OK);
    await response.WriteAsJsonAsync(customers);
    return response;
}

方法2：参数编码+手动传递 #

在前端请求时对CustomerKey进行URL编码（将=替换为%3D），函数体内先解码再通过手动命令传递参数（同方法1的命令构建逻辑）。

编码示例（前端）：

const encodedKey = encodeURIComponent(customerKey);
// 拼接URL时使用encodedKey

解码示例（函数内）：

var customerKey = Uri.UnescapeDataString(req.RouteValues["CustomerKey"].ToString());

方法3：改用POST请求传递参数 #

将参数放在请求体中而非URL路径，避免URL参数的格式限制，同时更适合传递加密字符串。修改HttpTrigger为POST，从请求体读取参数后手动传递给存储过程。

示例代码：

// 定义请求模型
public class CustomerRequest
{
    public string CustomerKey { get; set; }
    public int BatchSize { get; set; }
}

[Function("GetCustomers")]
public static async Task<HttpResponseData> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "customers")] HttpRequestData req,
    ILogger log,
    IConfiguration configuration)
{
    var requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var requestData = JsonSerializer.Deserialize<CustomerRequest>(requestBody);
    
    var connectionString = configuration.GetConnectionString("SqlConnectionString");
    var customers = new List<customer>();
    
    using (var conn = new SqlConnection(connectionString))
    {
        await conn.OpenAsync();
        using (var cmd = new SqlCommand("storedprocName", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerKey", SqlDbType.NVarChar).Value = requestData.CustomerKey;
            cmd.Parameters.Add("@BatchSize", SqlDbType.Int).Value = requestData.BatchSize;
            
            using (var reader = await cmd.ExecuteReaderAsync())
            {
                // 数据映射逻辑
                while (await reader.ReadAsync())
                {
                    customers.Add(new customer
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1)
                    });
                }
            }
        }
    }
    
    var response = req.CreateResponse(HttpStatusCode.OK);
    await response.WriteAsJsonAsync(customers);
    return response;
}

内容的提问来源于stack exchange，提问作者Shridevi rao