PHP+MySQLi注册功能报错：SQLSTATE[HY093]参数未定义求助

嘿，我是PHP和PDO（本来以为自己用的MySQLi，结果写的是PDO）的新手，最近在开发登录和注册表单。跟着网上的资料实现功能时，用户注册环节插入MySQL数据库一直报错：

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

以下是我的相关代码：

require_once 'dbconfig.php';
class USER {
    private $conn;
    public function __construct() {
        $database = new Database();
        $db = $database->dbConnection();
        $this->conn = $db;
    }
    public function runQuery($sql) {
        $stmt = $this->conn->prepare($sql);
        return $stmt;
    }
    public function lasdID() {
        $stmt = $this->conn->lastInsertId();
        return $stmt;
    }
    public function register($uname,$email,$upass,$code,$spol) {
        try {
            $password = md5($upass);
            $stmt = $this->conn->prepare("INSERT INTO tbl_users(userName,userEmail,userPass,tokenCode,spol) VALUES(:user_name, :user_mail, :user_pass, :active_code, :spol)");
            $stmt->bindparam(":user_mail",$email);
            $stmt->bindparam(":user_pass",$password);
            $stmt->bindparam(":spol",$spol);
            $stmt->bindparam(":active_code",$code);
            $stmt->execute();
            return $stmt;
        } catch(PDOException $ex) {
            echo $ex->getMessage();
        }
    }
    public function login($email,$upass) {
        try {
            $stmt = $this->conn->prepare("SELECT * FROM tbl_users WHERE userEmail=:email_id");
            $stmt->execute(array(":email_id"=>$email));
            $userRow=$stmt->fetch(PDO::FETCH_ASSOC);
            if($stmt->rowCount() == 1) {
                if($userRow['userStatus']=="Y") {
                    if($userRow['userPass']==md5($upass)) {
                        $_SESSION['userSession'] = $userRow['userID'];
                        return true;
                    } else {
                        header("Location: index.php?narobe");
                        exit;
                    }
                } else {
                    header("Location: index.php?inactive");
                    exit;
                }
            } else {
                header("Location: index.php?error");
                exit;
            }
        } catch(PDOException $ex) {
            echo $ex->getMessage();
        }
    }
    public function is_logged_in() {
        if(isset($_SESSION['userSession'])) {
            return true;
        }
    }
    public function redirect($url) {
        header("Location: $url");
    }
    public function logout() {
        session_destroy();
        $_SESSION['userSession'] = false;
    }
}

我的tbl_users表结构包含这些字段：userID（自增主键，int类型）、userName、userEmail、userPass、tokenCode、userStatus（默认值为N）、spol（其余均为varchar类型）。不确定是表的问题还是代码写错了，麻烦帮忙排查一下！

问题排查与解决 #

这个错误的核心原因是SQL语句中的占位符和你实际绑定的参数数量不匹配：

你在INSERT语句里定义了5个占位符：:user_name, :user_mail, :user_pass, :active_code, :spol，但在bindparam的时候，只绑定了4个参数，漏掉了:user_name对应的$uname变量！

只需要在注册函数的bindparam部分加上这一行即可：

$stmt->bindparam(":user_name",$uname);

修正后的register函数片段：

public function register($uname,$email,$upass,$code,$spol) {
    try {
        $password = md5($upass);
        $stmt = $this->conn->prepare("INSERT INTO tbl_users(userName,userEmail,userPass,tokenCode,spol) VALUES(:user_name, :user_mail, :user_pass, :active_code, :spol)");
        $stmt->bindparam(":user_name",$uname); // 补上这一行
        $stmt->bindparam(":user_mail",$email);
        $stmt->bindparam(":user_pass",$password);
        $stmt->bindparam(":spol",$spol);
        $stmt->bindparam(":active_code",$code);
        $stmt->execute();
        return $stmt;
    } catch(PDOException $ex) {
        echo $ex->getMessage();
    }
}

另外顺便提两个小建议：

1. 不要用md5加密密码了，安全性很低，推荐用password_hash()和password_verify()来处理密码；
2. 你的lasdID方法拼写错了，应该是lastID，不过这和当前错误无关。

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