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

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

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;
    }
}

"No data supplied for parameters in prepared statement" means statement is ok but at least one of the vars you're providing to bind_param is not there as expected! i would print out $_POST and see whats going on and eventually set $pSETQuery = ''; and not to null!

$_POST['username']
$_POST['email']
$_POST['password']
$_POST['usertype']
$_POST['userid'] // this one is the one i would really watch after, how do you tell the userid if the user is not logged ( i assume that from email, passwrod and might be wrong)

cheers

Do you use Zend Framework ? It could be a version problem between Php and Zend. I got the problem with PHP 5.3 + who got the same error on insert or update with Zend framework 1.8.3.

If you are in that case, one of the solutions is to change the connector to the database. Try this, it works for me :

$db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

I've just found the way to fix the same problem.

It was a value past to MySQL, which was NULL. Whereas this column can't be NULL in table definition...