如何防止Update查询中空参数覆盖原有列数据（VB.NET场景）

我使用VB.NET开发API，共有5个参数。执行Update查询时无需更新全部5个参数，可仅更新1至4个。但当我仅传入2个参数、其余留空时，空参数会将对应列的原有数据替换为空字符串，请问如何避免这种情况？

现有代码：

Try
    If String.IsNullOrEmpty(id) Or String.IsNullOrEmpty(name) Or String.IsNullOrEmpty(email) Or String.IsNullOrEmpty(role_id) Or String.IsNullOrEmpty(factory_id) Then
        Dim myResponse As New failResponceClass
        myDataAdapter = New MySqlDataAdapter

        myDataAdapter.UpdateCommand = New MySqlCommand("UPDATE wla_user SET name = '" & name & "', email = '" & email & "', role_id = '" & role_id & "', factory_id = '" & factory_id & "' WHERE id = '" & id & "'")

        myDataAdapter.UpdateCommand.Connection = mySQLConnection
        mySQLConnection.Open()
        myDataAdapter.UpdateCommand.ExecuteNonQuery()
        mySQLConnection.Close()

        myResponse.statusCode = 1
        myResponse.errCode = ""
        myResponse.title = l_Title_TGWLAppUser
        myResponse.desc = "TGWL App User"

        Me.Context.Response.ContentType = "application/json; charset=utf-8"
        Me.Context.Response.Write(JsonConvert.SerializeObject(myResponse))

核心思路 #

• 动态构建UPDATE语句的SET子句，只包含非空的参数，跳过空参数对应的列
• 使用参数化查询，彻底避免SQL注入风险，同时规范参数传递
• 修正逻辑判断：仅当id为空时才判定异常（id是更新的唯一标识，必须传递，其他参数可选）

具体实现代码 #

Try
    ' 仅验证id是否为空，其他参数按需传递
    If String.IsNullOrEmpty(id) Then
        Dim errorResponse As New failResponceClass
        errorResponse.statusCode = 0
        errorResponse.errCode = "INVALID_ID"
        errorResponse.title = l_Title_TGWLAppUser
        errorResponse.desc = "用户ID不能为空"
        
        Me.Context.Response.ContentType = "application/json; charset=utf-8"
        Me.Context.Response.Write(JsonConvert.SerializeObject(errorResponse))
        Return
    End If

    Dim updateSql As New StringBuilder("UPDATE wla_user SET ")
    Dim updateCmd As New MySqlCommand()
    updateCmd.Connection = mySQLConnection

    ' 逐个判断参数，非空则加入更新逻辑
    If Not String.IsNullOrEmpty(name) Then
        updateSql.Append("name = @name, ")
        updateCmd.Parameters.AddWithValue("@name", name)
    End If
    If Not String.IsNullOrEmpty(email) Then
        updateSql.Append("email = @email, ")
        updateCmd.Parameters.AddWithValue("@email", email)
    End If
    If Not String.IsNullOrEmpty(role_id) Then
        updateSql.Append("role_id = @role_id, ")
        updateCmd.Parameters.AddWithValue("@role_id", role_id)
    End If
    If Not String.IsNullOrEmpty(factory_id) Then
        updateSql.Append("factory_id = @factory_id, ")
        updateCmd.Parameters.AddWithValue("@factory_id", factory_id)
    End If

    ' 移除SET子句末尾多余的逗号和空格
    If updateSql.ToString().EndsWith(", ") Then
        updateSql.Remove(updateSql.Length - 2, 2)
    End If

    ' 添加WHERE条件并绑定id参数
    updateSql.Append(" WHERE id = @id")
    updateCmd.Parameters.AddWithValue("@id", id)
    updateCmd.CommandText = updateSql.ToString()

    ' 执行更新操作
    mySQLConnection.Open()
    updateCmd.ExecuteNonQuery()
    mySQLConnection.Close()

    ' 返回成功响应
    Dim successResponse As New failResponceClass
    successResponse.statusCode = 1
    successResponse.errCode = ""
    successResponse.title = l_Title_TGWLAppUser
    successResponse.desc = "TGWL App User 更新成功"

    Me.Context.Response.ContentType = "application/json; charset=utf-8"
    Me.Context.Response.Write(JsonConvert.SerializeObject(successResponse))
Catch ex As Exception
    ' 捕获异常并返回错误信息，确保数据库连接关闭
    Dim errorResponse As New failResponceClass
    errorResponse.statusCode = 0
    errorResponse.errCode = "UPDATE_FAILED"
    errorResponse.title = l_Title_TGWLAppUser
    errorResponse.desc = $"更新失败：{ex.Message}"
    
    Me.Context.Response.ContentType = "application/json; charset=utf-8"
    Me.Context.Response.Write(JsonConvert.SerializeObject(errorResponse))
    If mySQLConnection.State = ConnectionState.Open Then
        mySQLConnection.Close()
    End If
End Try

关键说明 #

1. 动态SQL构建：通过StringBuilder拼接SET部分，只将非空参数对应的列加入更新语句，空参数对应的列不会被修改，保留原有数据。
2. 参数化查询：用@参数名替代直接字符串拼接，既防止SQL注入，也避免了字符串转义问题。
3. 逻辑修正：原代码错误地将所有参数设为必填，现在仅强制验证id，其他参数按需传递即可。
4. 异常处理：添加Catch块捕获执行过程中的异常，确保数据库连接正常关闭，并返回友好的错误提示。

内容的提问来源于stack exchange，提问作者Ratu Batu Hamlo Koto