PDO运行报SQLSTATE[HY093]无效参数数，bindParam行报错如何解决？

问题定位 #

代码报错和第30行的$findU->bindParam(':uid', $uid);本身无关，是两处语法使用错误导致的：

• 预处理SQL的命名占位符不能用单引号包裹，加单引号后PDO会将其识别为普通字符串，无法匹配绑定的参数，直接抛出绑定错误
• execute()方法返回值为布尔类型，不是结果集对象，不能调用fetchColumn()方法，需要通过预处理语句对象本身获取查询结果

修正方案 #

1. 修复用户名查重逻辑 #

原错误代码：

$findU = $pdo->prepare("SELECT * FROM `users` WHERE user_uid = ':uid'");
$findU->bindParam(':uid', $uid);
$result = $findU->execute();
if($result->fetchColumn() > 0)

修正后：

// 去掉占位符外的单引号
$findU = $pdo->prepare("SELECT * FROM `users` WHERE user_uid = :uid");
$findU->bindParam(':uid', $uid);
$findU->execute();
// 从预处理对象直接获取结果
if($findU->fetchColumn() > 0)

2. 修复用户插入逻辑 #

原错误代码：

$newUser = $pdo->prepare("INSERT INTO `users` (user_first, user_last, user_email, 
user_uid, user_pwd) VALUES (':first', ':last', ':email', ':uid', ':hashedPwd')");

修正后：

// 移除所有占位符外的单引号
$newUser = $pdo->prepare("INSERT INTO `users` (user_first, user_last, user_email, 
user_uid, user_pwd) VALUES (:first, :last, :email, :uid, :hashedPwd)");

完整修正后代码 #

<?php
    try {
        $pdo = new \PDO('mysql:host=localhost;dbname=project;charset=utf8', 'root', '', [
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]);
        if($_POST && isset($_POST['submit'])) {
            $first = trim($_POST['first']);
            $last = trim($_POST['last']);
            $email = trim($_POST['email']);
            $uid = trim($_POST['uid']);
            $pwd = trim($_POST['pwd']);
            // 空字段校验
            if(empty($first) || empty($last) || empty($email) || empty ($uid) || empty ($pwd)) {
                header('Location: sign_up.php?signup=empty');
                exit();
            } 
            // 姓名字符合法性校验
            elseif(!preg_match("/^[a-zA-Z]/", $first) || !preg_match("/^[a-zA-Z]/", $last)) {
                header('Location: sign_up.php?signup=invalid');
                exit();
            } 
            // 邮箱格式校验
            elseif(!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                header('Location: sign_up.php?signup=invalidemail');
                exit();
            } 
            // 用户名重复校验
            else {
                $findU = $pdo->prepare("SELECT * FROM `users` WHERE user_uid = :uid");
                $findU->bindParam(':uid', $uid);
                $findU->execute();
                if($findU->fetchColumn() > 0) {
                    header("Location: sign_up.php?signup=usertaken");
                    exit();
                } else {
                    // 密码哈希
                    $hashedPwd = password_hash($pwd, PASSWORD_DEFAULT);
                    // 插入新用户
                    $newUser = $pdo->prepare("INSERT INTO `users` (user_first, user_last, user_email, 
                    user_uid, user_pwd) VALUES (:first, :last, :email, :uid, :hashedPwd)");
                    $newUser->execute(array(':first' => $first, ':last' => $last, ':email' => $email,
                    ':uid' => $uid, ':hashedPwd' => $hashedPwd));
                    header("Location: sign_up.php?signup=success");
                    exit();
                }
            }
        }
    } catch(\PDOException $e) {
        echo "数据库连接错误: " . $e->getMessage();
        echo "<code><pre>".print_r($e,true)."</pre></code>";
        exit();
    }
?>

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