Click here to Skip to main content
15,917,709 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi everyone.

I've run into a problem with updating of my table. I keep getting
Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in
on the line, where I try to execute the statement.

The code I'm trying to execute is:

$sql = 'UPDATE users SET password = :new_password WHERE email = "$email"';

        if($stmt = $pdo->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bindParam(":password", $param_password, PDO::PARAM_STR);
            $stmt->bindParam(":email", $param_email, PDO::PARAM_STR);

            // Set parameters
            $param_password = password_hash($new_password, PASSWORD_DEFAULT);
            $param_email = $email;

            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Password updated successfully. Destroy the session, and redirect to login page
                header("location: login.php");
                exit();
            } 


What I have tried:

I have tried to remove
$stmt->bindParam(":email", $param_email, PDO::PARAM_STR);
and
$param_email = $email;
, which obviously didn't do anything.

I've tried to search for solution other places online, and it looks like the variables are bound and executed according to suggestions, incl. php.net. But I'm very new to php, and it can well be that I misread or misunderstand those suggestions. I would very much appreciate if anyone could point out, what is wrong with my code.
Posted
Updated 23-Jan-21 6:06am
v2
Comments
Richard Deeming 22-Jan-21 4:37am    
$sql = 'UPDATE users SET password = :new_password WHERE email = "$email"';

You almost avoided the SQL Injection[^] vulnerability. But you've injected the email directly into the query, rather than using the parameter.
$sql = 'UPDATE users SET password = :new_password WHERE email = :email';
InnaTS 23-Jan-21 8:57am    
Thank you, Richard! I have corrected it now. My database wouldn't update (still doesn't...) because of 'PHP Notice: Undefined index: email', which I tried to correct changing different things in my code. That's why I stuck the function directly into SQL, failing to realize that it made my code vulnerable to SQL injections.

$stmt->bindParam(":password", $param_password, PDO::PARAM_STR);


The param in your code is :new_password. It does not match
 
Share this answer
 
Comments
InnaTS 21-Jan-21 17:10pm    
Thank you, Christian! It's amazing for how long I was staring at this code and had not seen it! I have now corrected it, but I'm still getting the same issue. I've cleared my cache just in case, but the error persists.
Christian Graus 21-Jan-21 17:15pm    
Happens to me all the time :)
Christian Graus 21-Jan-21 17:16pm    
I don't see how you can get the error. IF you do, remove all params (hard code them) then add one at a time until you work out which one is not working
InnaTS 21-Jan-21 17:54pm    
Thanks for your help! I will try that. :)
Richard Deeming 22-Jan-21 4:35am    
$new_password is the plain text password, which should never be stored in the database.

$param_password is updated on the following line to the hashed password. As far as I can see, that's the correct way to do it in PHP:
PHP: mysqli_stmt::bind_param[^]
Hi guys! Thanks a lot to both of you for your help! I've used both inputs as a solution. I set parameters before binding them and it fixed everything! So strange, but everything works now. :)

if(empty($new_password_err) && empty($confirm_password_err)){
        // Prepare an update statement
        $sql = 'UPDATE users SET password = :password WHERE email = :email';

        if($stmt = $pdo->prepare($sql)){

          // Set parameters
          $param_password = password_hash($new_password, PASSWORD_DEFAULT);
          $param_email = $email;

            // Bind variables to the prepared statement as parameters
            $stmt->bindParam(":password", $param_password, PDO::PARAM_STR);
            $stmt->bindParam(":email", $param_email, PDO::PARAM_STR);



            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Password updated successfully. Destroy the session, and redirect to login page
                header("location: login.php");
                exit();
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
        unset($stmt);
        }
    }

    // Close connection
  unset($pdo);
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900