Uncaught pdoexception: SQLSTATE[HY093]: invalid parameter number:...

Question

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 21-Jan-21 10:49am

InnaTS

Updated 23-Jan-21 6:06am

v2

Add a Solution

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.

2 solutions

Add a Solution

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

Christian Graus · Accepted Answer · 2021-01-21T10:57:00

Solution 1

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

The param in your code is :new_password. It does not match

Posted 21-Jan-21 10:57am

Christian Graus

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[^]

InnaTS 23-Jan-21 11:05am

mysqli_stmt::bind_param[^] is a MySQLi statement, while I used PDO. I thought $param_password = password_hash($new_password, PASSWORD_DEFAULT); hashes the password before the statement is executed, which should be an equivalent to your suggestion?

InnaTS · Accepted Answer · 2021-01-23T06:06:00

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

Uncaught pdoexception: SQLSTATE[HY093]: invalid parameter number:...

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Uncaught pdoexception: SQLSTATE[HY093]: invalid parameter number:...

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us