没有为准备好的语句MySQLi PHP中的参数提供数据

Question

I've been reworking my website from unprotected mysql queries to mysqli prepared statements and it all went well until I got this: No data supplied for parameters in prepared statement.

I've researched it to no avail and I am requesting the blinding awesomeness of this community to help me out.

if(empty($err)) {
    $pSETQuery  = NULL;
    if(!empty($_POST['password'])) {
        $pSETQuery .= ", password = ?";
    }
    if($session->isSuperuser()) {
        $pSETQuery .= ", usertype = ?";
    }
    if(!($stmt = $database->prepare("UPDATE user SET username = ?, email = ? $pSETQuery WHERE UserId = ?"))) {
        $err[] = "PREPARE FAILED.";
    }
    $stmt->bind_param("s", $_POST['username']);
    $stmt->bind_param("s", $_POST['email']);
    if(!empty($_POST['password'])) {
        $stmt->bind_param("s", $_POST['password']);
    }
    if($session->isSuperuser()) {
        $stmt->bind_param("s", $_POST['usertype']);
    }
    $stmt->bind_param("i", $_POST['userid']);
    if(!$stmt->execute()){
        $err[] = "Execute failed. ERROR: " . $stmt->error;
    }

}

Regards, Harry

Answer 1

Seems like you probably want to validate all of those fields before going ahead and updating their profile.

if(empty($err)) {

    //Check for UserId, otherwise you can't update a profile
    if (empty($_POST['userid'])) {
        //Handle error here...
        exit;
    }

    $userid = $_POST['userid'];

    //Filter out all NULL values and replace with empty strings (safety first!)
    $username = empty($_POST['username']) ? '' : $_POST['username'];
    $password = empty($_POST['password']) ? '' : $_POST['password'];
    $email = empty($_POST['email']) ? '' : $_POST['email'];
    $usertype = empty($_POST['usertype']) ? '' : $_POST['usertype'];

    $pSETQuery  = '';
    $pSETQuery .= !empty($username) ? ", username = ?" : "";
    $pSETQuery .= !empty($password) ? ", password = ?" : "";
    $pSETQuery .= (!empty($usertype) && $session->isSuperuser()) ? ", usertype = ?" : "";
    //This line stops someone from being able to enter a blank username

    //Prepare statement
    if(!($stmt = $database->prepare("UPDATE user SET email = ? $pSETQuery WHERE UserId = ?"))) {
        $err[] = "PREPARE FAILED.";
    }

    //Bind parameters where appropriate
    $stmt->bind_param("s", $email);
    if(!empty($username)) $stmt->bind_param("s", $username);
    if(!empty($password)) $stmt->bind_param("s", $password);
    if($session->isSuperuser() && !empty($usertype)) $stmt->bind_param("s", $usertype);
    $stmt->bind_param("i", $userid);

    //Execute statement
    if(!$stmt->execute()){
        $err[] = "Execute failed. ERROR: " . $stmt->error;
    }
}