PHP/MySQL存在记录时无法执行UPDATE的代码排查请求

问题描述 #

编写的save_inventory函数预期实现记录不存在则执行INSERT，记录已存在则执行UPDATE的逻辑，但实际仅能触发INSERT操作，无法执行UPDATE。

相关代码 #

function save_inventory(){
        extract($_POST);
        $data = "";
        foreach($_POST as $k =>$v){
            if(!in_array($k,array('id'))){
                if(!empty($data)) $data .= ",";
                $v = htmlspecialchars($this->conn->real_escape_string($v));
                $data .= " `{$k}`='{$v}' ";
            }
        }
        $check = $this->conn->query("SELECT * FROM `product_list` where `code` = '{$code}' and delete_flag = 0 ".(!empty($id) ? " and id != {$id} " : "")." ")->num_rows;
        if($this->capture_err())
            return $this->capture_err();
        if($check > 0){
            $resp['status'] = 'failed';
            $resp['msg'] = "Product Code already exists. Code must be unique";
            return json_encode($resp);
            exit;
        }
        if(empty($id)){
            $sql = "INSERT INTO `product_list` set {$data} ";
        }else if(!empty($id)){
            $sql = "UPDATE `product_list` set {$data} where  id = '{$id}' ";
        }
            $save = $this->conn->query($sql);
        if($save){
            $cid = !empty($id) ? $id : $this->conn->insert_id;
            $resp['cid'] = $cid;
            $resp['status'] = 'success';
            if(empty($id))
                $resp['msg'] = "New Inventory successfully saved.";
            else
                $resp['msg'] = " Inventory successfully updated.";
        }else{
            $resp['status'] = 'failed';
            $resp['err'] = $this->conn->error."[{$sql}]";
        }
        // if($resp['status'] == 'success')
        //  $this->settings->set_flashdata('success',$resp['msg']);
            return json_encode($resp);
    }

注：MySQL查询中的code代表产品ID。

问题根源分析 #

• id参数未正确传递：函数依赖extract($_POST)获取$id，若更新操作时$_POST未携带非空id，会触发empty($id)判断，走INSERT分支而非UPDATE分支。
• 不必要的htmlspecialchars处理：数据库存储前调用该函数会转义特殊字符（如<转为<），导致数据失真，甚至引发UPDATE语句的语法错误。该函数仅应在前端输出HTML时使用，存储时只需用real_escape_string做SQL注入防护。
• SQL语句潜在语法错误：若$_POST中无非id字段，$data为空，会生成UPDATE product_list set where id = '{$id}' 这类语法错误的语句。
• 变量安全问题：extract($_POST)易导致变量覆盖、未定义变量等问题，可能引发校验或SQL逻辑异常。

修复方案 #

1. 安全获取参数，替代extract($_POST) #

// 单独获取核心参数，避免变量覆盖
$id = isset($_POST['id']) && is_numeric($_POST['id']) ? $_POST['id'] : '';
$code = isset($_POST['code']) ? $this->conn->real_escape_string($_POST['code']) : '';

2. 移除存储时的htmlspecialchars处理 #

// 原代码
// $v = htmlspecialchars($this->conn->real_escape_string($v));
// 修改为
$v = $this->conn->real_escape_string($v);

3. 增加$data非空校验 #

避免生成无效SQL语句：

if(empty($data)){
    $resp['status'] = 'failed';
    $resp['msg'] = "No valid data to save or update";
    return json_encode($resp);
}

4. 优化唯一性校验逻辑 #

明确区分新增/更新场景的错误提示：

if($check > 0){
    $resp['status'] = 'failed';
    $resp['msg'] = empty($id) ? "Product Code already exists" : "Product Code is used by another record";
    return json_encode($resp);
}

5. 修正UPDATE语句的id处理 #

若id为数字类型，无需加单引号，保持类型一致：

$sql = "UPDATE `product_list` set {$data} where id = {$id} ";

优化后代码示例 #

function save_inventory(){
    // 安全获取参数
    $id = isset($_POST['id']) && is_numeric($_POST['id']) ? $_POST['id'] : '';
    $code = isset($_POST['code']) ? $this->conn->real_escape_string($_POST['code']) : '';
    
    $data = "";
    foreach($_POST as $k =>$v){
        if($k === 'id') continue;
        $v = $this->conn->real_escape_string($v);
        $data .= !empty($data) ? ", `{$k}`='{$v}'" : "`{$k}`='{$v}'";
    }

    // 校验数据是否为空
    if(empty($data)){
        $resp['status'] = 'failed';
        $resp['msg'] = "No valid data to process";
        return json_encode($resp);
    }

    // 唯一性校验
    $check_sql = "SELECT * FROM `product_list` WHERE `code` = '{$code}' AND delete_flag = 0 ";
    if(!empty($id)){
        $check_sql .= " AND id != {$id}";
    }
    $check_query = $this->conn->query($check_sql);
    if($this->capture_err()){
        return $this->capture_err();
    }
    if($check_query->num_rows > 0){
        $resp['status'] = 'failed';
        $resp['msg'] = empty($id) ? "Product Code already exists" : "Product Code is used by another record";
        return json_encode($resp);
    }

    // 生成SQL语句
    if(empty($id)){
        $sql = "INSERT INTO `product_list` SET {$data}";
    }else{
        $sql = "UPDATE `product_list` SET {$data} WHERE id = {$id}";
    }

    $save = $this->conn->query($sql);
    if($save){
        $cid = !empty($id) ? $id : $this->conn->insert_id;
        $resp['cid'] = $cid;
        $resp['status'] = 'success';
        $resp['msg'] = empty($id) ? "New Inventory saved successfully" : "Inventory updated successfully";
    }else{
        $resp['status'] = 'failed';
        $resp['err'] = $this->conn->error . "[{$sql}]";
    }

    return json_encode($resp);
}

内容的提问来源于stack exchange，提问作者Emeka Okoye