SQLSTATE[21S01]插入值与列列表不匹配问题求助

已花费数小时尝试排查「插入值列表与列列表不匹配」的错误，相关代码及信息如下：

PHP插入函数代码 #

function insert_data($humidity, $temperature, $sensor_id, $log_time) {
    $conn = db_connect(); //connects to database

    $sql = "INSERT INTO data(humidity, temperature, log_time, sensor_id)\n"
    . "VALUES ($humidity, $temperature, '$log_time', '$sensor_id');";

    $statement = $conn -> prepare($sql);
    $ok = $statement -> execute();

    $conn = null;

    return $ok;
}

函数调用代码 #

<?php 
include("lib_db.php");
include("header.php");

if(isset($_POST["data_send"]) && filter_var($_POST["data_send"], FILTER_VALIDATE_BOOLEAN)) {

$sensor_id = $_POST["sensor_id"] ?? '';
$humidity = $_POST["humidity"] ?? '';
$temperature = $_POST["temperature"] ?? '';
$log_time = $_POST["log_time"] ?? '';

$res = insert_data($humidity, $temperature, $sensor_id, $log_time);

if(!$res) throw new ErrorException;
}

$res = select_data();
?>
<div class="container-xxl bg-danger hero-header py-5">
    <div class="container-xxl py-5">
        <div class="container-xxl py-5 px-lg-5 wow fadeInUp">
         <div class="table-responsive">
         <table class="table table-hover table-dark">
             <thead>
                 <tr>
                     <th>#ID</th>
                     <th>Humidity</th>
                     <th>Temperature</th>
                     <th>Log time</th>
                     <th>#Sensor ID</th>
                 </tr>
             </thead>
             <tbody>
             <?php if(isset($res)) { 
                     foreach($res as $row) {
                     ?>
                     <tr>
                         <td><?= $row["id"]?></td>
                         <td><?= $row["humidity"]?></td>
                         <td><?= $row["temperature"]?></td>
                         <td><?= $row["log_time"]?></td>
                         <td><?= $row["sensor_id"]?></td>
                     </tr>
                     <?php } } ?>
             </tbody>
         </table>
    </div>
  </div>
  </div>
</div>
<?php
include("footer.php");
?>

POST数据由功能正常的外部C#应用发送，data表结构为data(id, humidity, temperature, log_time, sensor_id)，其中id是自增主键。执行插入时出现以下错误：

<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 in C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php:115
Stack trace:
#0 C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php(115): PDOStatement->execute()
#1 C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\sensor_api.php(12): insert_data('48,755359214191', '20,443338532373...', '278e8f10-c667-4...', '2023-12-02 20:0...')
#2 {main}
  thrown in <b>C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php</b> on line <b>115</b><br />

已检查标点、引号、变量顺序及表结构，在PhPmyAdmin中执行对应查询可正常运行，但代码执行仍报错。

解决方案 #

• 核心问题分析
  从错误堆栈的参数可见，humidity的值是'48,755359214191'（用逗号作为小数点分隔符），而你直接将变量拼入SQL语句且未加引号，导致SQL解析时把48,755359214191当成了两个独立值（48和755359214191），最终VALUES里的总数量远超过列列表的4个，触发列数不匹配错误。

  另外，当前代码存在严重的SQL注入风险，直接拼接变量到SQL是不安全的做法。

• 修复后的插入函数
  使用PDO参数绑定来解决这两个问题，代码如下：

  function insert_data($humidity, $temperature, $sensor_id, $log_time) {
      $conn = db_connect(); //connects to database
  
      // 使用占位符代替直接拼接变量
      $sql = "INSERT INTO data(humidity, temperature, log_time, sensor_id)
              VALUES (:humidity, :temperature, :log_time, :sensor_id);";
  
      $statement = $conn->prepare($sql);
      // 绑定参数，PDO会自动处理数据类型和引号
      $statement->bindParam(':humidity', $humidity);
      $statement->bindParam(':temperature', $temperature);
      $statement->bindParam(':log_time', $log_time);
      $statement->bindParam(':sensor_id', $sensor_id);
      
      $ok = $statement->execute();
  
      $conn = null;
  
      return $ok;
  }
  

• 额外优化建议

  • 若你的数据库默认小数点分隔符是点（.），而C#端发送的是逗号（,），可以在插入前替换：

    $humidity = str_replace(',', '.', $_POST["humidity"] ?? '');
    $temperature = str_replace(',', '.', $_POST["temperature"] ?? '');
    

  • 添加参数验证逻辑，确保数值类型参数是有效的数字，避免无效数据插入。

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