PHP查询数据触发SQL语法错误，请求技术排查（附代码）

问题描述 #

报错信息：

Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '00-0.006 WHERE usersUID = 'test'' at line 1

相关PHP代码：

$QT = $_GET['number'];
$url = $_GET['url'];
$serviceid = $_GET['serviceid'];
$lastprice = $_GET['price'];

$converted_price = sprintf('%.8f', floatval($lastprice));

$devidedamount = $converted_price * $QT;
$currentcredits = $_SESSION['credits'];

$v = (float)$currentcredits - (float)$devidedamount;

if($currentcredits < $devidedamount){
    header("location: ../newOrder.php?error=nobalance");
}
else{
$sqldevidecredits = "UPDATE users SET credits= ? WHERE usersUID = ? ";


$devidestm = mysqli_stmt_init($conn);
mysqli_stmt_prepare($devidestm, $sqldevidecredits);

mysqli_stmt_bind_param($devidestm, "ds",  $v, $_SESSION['useruid']);

mysqli_stmt_execute($devidestm);

mysqli_query($conn, $sqldevidecredits);

}

错误原因 #

核心问题是多余的mysqli_query($conn, $sqldevidecredits);调用：

• 你已经通过预处理语句流程（初始化语句对象→预处理SQL→绑定参数→执行语句）完成了安全的SQL执行，这部分逻辑是正确的。
• 但额外调用的mysqli_query直接传入了带占位符?的原始SQL，MariaDB无法解析占位符，会将其当作普通字符串处理，导致SQL语法混乱，最终触发报错。

修复方案 #

删除最后一行的mysqli_query($conn, $sqldevidecredits);即可，预处理语句已经通过mysqli_stmt_execute($devidestm);完成了数据更新操作。

优化后的代码片段：

else{
$sqldevidecredits = "UPDATE users SET credits= ? WHERE usersUID = ? ";

$devidestm = mysqli_stmt_init($conn);
mysqli_stmt_prepare($devidestm, $sqldevidecredits);

mysqli_stmt_bind_param($devidestm, "ds",  $v, $_SESSION['useruid']);

mysqli_stmt_execute($devidestm);
// 移除错误的重复执行语句
}

额外建议 #

• 增加预处理语句的错误校验，例如：

  if (!mysqli_stmt_prepare($devidestm, $sqldevidecredits)) {
      die("预处理失败: " . mysqli_error($conn));
  }
  

• 对$_GET传入的$QT、$lastprice等参数做类型校验（比如用is_numeric()检查），避免非数字值导致的业务逻辑错误。

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