Problem with Mysqli (Password-reset script)

Daniii · December 11, 2020

<?php

if (isset($_POST['reset-submit'])) {
    $selector = $_POST['selector'];
    $validator = $_POST['validator'];
    $password = $_POST['password'];
    $password2 = $_POST['password2'];

    // probably better to check this earlier
    if (empty($password) || empty($password2)) {
        header("Location: ../create-new-password.php?newpassword=empty&selector=$selector&validator=$validator");
    } elseif ($password !== $password2) {
        header("Location: ../create-new-password.php?newpassword=passwordsnotmatch");
    }

    $currentDate = date("U");

    require "dbh.inc.php";
    
    $sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= $currentDate";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error 1";
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, 'ss', $selector, $currentDate);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if (!$row = mysqli_fetch_assoc($result)) {
            echo 'You need to re-submit your reset request.';
            exit();
        } else {
            $tokenBin = hex2bin($validator);
            $tokenCheck = password_verify($tokenBin, $row['token']);

            if (!$tokenCheck) {
                echo 'You need to re-submit your reset request.';
                exit();
            } else {
                $email = $row['email'];
                $sql = "SELECT * FROM users WHERE email = $email";
                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo "SQL error 2";
                    exit();
                } else {
                    mysqli_stmt_bind_param($stmt, 's', $email);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if (!$row = mysqli_fetch_assoc($result)) {
                        echo "SQL error 3";
                        exit();
                    } else {
                        $sql = "UPDATE users SET password=? WHERE email=?";
                        $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo "SQL error4 ";
                            exit();
                        } else {
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            mysqli_stmt_bind_param($stmt, 'ss', $hashed_password, $email);
                            mysqli_stmt_execute($stmt);
                            $sql = 'DELETE FROM reset_password WHERE email=?';
                            $stmt = mysqli_stmt_init($conn);
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL error5';
                                exit();
                            } else {
                                mysqli_stmt_bind_param($stmt, 's', $email);
                                mysqli_stmt_execute($stmt);
                                header("Location: ../signup.php?newpassword=updated");
                            }
                        }
                    }
                }
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);

    header('Location: ../reset-password.php?reset=success');
} else {
    header('Location: ../index.php');
}

I always get this errors:

Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\php_login_system-master\includes\reset-password.inc.php on line 26

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in C:\xampp\htdocs\php_login_system-master\includes\reset-password.inc.php on line 30
You need to re-submit your reset request.

But i dont find the mistake in the Code. Can someone help me please

Barand · December 12, 2020

Your query contains only 1 placeholder (?) but you bind 2 parameters

The second parameter is unnecessary if you replace $currentDate in your query with the sql function CURDATE(). EG

$sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= curdate()";

requinix · December 12, 2020

If expires is a Unix timestamp (ie, a number and not a string), which is what date(U) suggests even though the placeholder says it's a 's'tring, then instead of CURDATE() it would be UNIX_TIMESTAMP().

Daniii · December 12, 2020

12 hours ago, Barand said:
Your query contains only 1 placeholder (?) but you bind 2 parameters

The second parameter is unnecessary if you replace $currentDate in your query with the sql function CURDATE(). EG
$sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= curdate()";

<?php

if (isset($_POST['reset-submit'])) {
    $selector = $_POST['selector'];
    $validator = $_POST['validator'];
    $password = $_POST['password'];
    $password2 = $_POST['password2'];

    // probably better to check this earlier
    if (empty($password) || empty($password2)) {
        header("Location: ../create-new-password.php?newpassword=empty&selector=$selector&validator=$validator");
    } elseif ($password !== $password2) {
        header("Location: ../create-new-password.php?newpassword=passwordsnotmatch");
    }
    function curdate() {  
        date_default_timezone_set('Europe/Berlin'); 
        return date('Y-m-d H:i:s');
    }
    $currentDate = curdate();

    require "dbh.inc.php";
    
    $sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= $currentDate";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error 1";
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, 'ss', $selector, $currentDate);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if (!$row = mysqli_fetch_assoc($result)) {
            echo 'You need to re-submit your reset request.';
            exit();
        } else {
            $tokenBin = hex2bin($validator);
            $tokenCheck = password_verify($tokenBin, $row['token']);

            if (!$tokenCheck) {
                echo 'You need to re-submit your reset request.';
                exit();
            } else {
                $email = $row['email'];
                $sql = "SELECT * FROM users WHERE email = $email";
                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo "SQL error 2";
                    exit();
                } else {
                    mysqli_stmt_bind_param($stmt, 's', $email);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if (!$row = mysqli_fetch_assoc($result)) {
                        echo "SQL error 3";
                        exit();
                    } else {
                        $sql = "UPDATE users SET password=? WHERE email=?";
                        $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo "SQL error4 ";
                            exit();
                        } else {
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            mysqli_stmt_bind_param($stmt, 'ss', $hashed_password, $email);
                            mysqli_stmt_execute($stmt);
                            $sql = 'DELETE FROM reset_password WHERE email=?';
                            $stmt = mysqli_stmt_init($conn);
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL error5';
                                exit();
                            } else {
                                mysqli_stmt_bind_param($stmt, 's', $email);
                                mysqli_stmt_execute($stmt);
                                header("Location: ../signup.php?newpassword=updated");
                            }
                        }
                    }
                }
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);

    header('Location: ../reset-password.php?reset=success');
} else {
    header('Location: ../index.php');
}

I have tried to fix it I now still get SQL error 1

Barand · December 12, 2020

curdate() is a mysql function.

Read the replies.

Daniii · December 12, 2020

Yes i used it but i get the errors again

Daniii · December 12, 2020

<?php

if (isset($_POST['reset-submit'])) {
    $selector = $_POST['selector'];
    $validator = $_POST['validator'];
    $password = $_POST['password'];
    $password2 = $_POST['password2'];

    // probably better to check this earlier
    if (empty($password) || empty($password2)) {
        header("Location: ../create-new-password.php?newpassword=empty&selector=$selector&validator=$validator");
    } elseif ($password !== $password2) {
        header("Location: ../create-new-password.php?newpassword=passwordsnotmatch");
    }

    $currentDate = date("U");

    require "dbh.inc.php";
    
    $sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= curdate();";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error 1";
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, 'ss', $selector, $currentDate);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if (!$row = mysqli_fetch_assoc($result)) {
            echo 'You need to re-submit your reset request.';
            exit();
        } else {
            $tokenBin = hex2bin($validator);
            $tokenCheck = password_verify($tokenBin, $row['token']);

            if (!$tokenCheck) {
                echo 'You need to re-submit your reset request.';
                exit();
            } else {
                $email = $row['email'];
                $sql = "SELECT * FROM users WHERE email = $email";
                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo "SQL error 2";
                    exit();
                } else {
                    mysqli_stmt_bind_param($stmt, 's', $email);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if (!$row = mysqli_fetch_assoc($result)) {
                        echo "SQL error 3";
                        exit();
                    } else {
                        $sql = "UPDATE users SET password=? WHERE email=?";
                        $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo "SQL error4 ";
                            exit();
                        } else {
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            mysqli_stmt_bind_param($stmt, 'ss', $hashed_password, $email);
                            mysqli_stmt_execute($stmt);
                            $sql = 'DELETE FROM reset_password WHERE email=?';
                            $stmt = mysqli_stmt_init($conn);
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL error5';
                                exit();
                            } else {
                                mysqli_stmt_bind_param($stmt, 's', $email);
                                mysqli_stmt_execute($stmt);
                                header("Location: ../signup.php?newpassword=updated");
                            }
                        }
                    }
                }
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);

    header('Location: ../reset-password.php?reset=success');
} else {
    header('Location: ../index.php');
}

Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\php_login_system-master\includes\reset-password.inc.php on line 26

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in C:\xampp\htdocs\php_login_system-master\includes\reset-password.inc.php on line 30
You need to re-submit your reset request.

Edited December 12, 2020 by Daniii

Barand · December 12, 2020

40 minutes ago, Daniii said:

Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\php_login_system-master\includes\reset-password.inc.php on line 26

I would refer you to earlier replies.

Daniii · December 12, 2020

@Barand

you wrote that I should replace $currentDate with curdate()

Daniii · December 12, 2020

And i replaced

  mysqli_stmt_bind_param($stmt, 'ss', $selector, $currentDate);

with

 mysqli_stmt_bind_param($stmt, 's', $selector);

and get You need to re-submit your reset request.

mac_gyver · December 12, 2020

21 minutes ago, Daniii said:

and get You need to re-submit your reset request.

you need to read @requinix's reply about the data type of the expires column.

actually, you need to slow down, define what you want each part of the code to do, then test and observe the result you get at each step so that you are actually learning by doing. the php error message you most recently got was the same and for the same reason as at the start of this thread, a different number of prepared query place-holders vs the number of bound inputs in the php code. you also have a case later in the code that will produce the same error due to the use of an $email variable in an sql query vs correctly using a place-holder in a prepared query.

next, you have have a serious functionality problem in that your code will allow empty password/password2 inputs to reset the user's password, due to not having exit; statements after every redirect. this is made worse by the form and the form processing code being on different pages, which requires the user to keep reentering these values every time there is a validation error. you should put the form and the form processing code on the same page, the only redirect you should have in your form processing code is to the exact same url as the current page upon successful completion of the form processing code, and you should always have an exit; statement after every redirect.

Barand · December 12, 2020

What is the data type of the "expires" column in your table?

Daniii · December 12, 2020

8 minutes ago, Barand said:

What is the data type of the "expires" column in your table?

expires: 1607789810

request.inc.php:

$expires = date("U") + 1800;

Barand · December 12, 2020

Then you definitely need to read @requinix's reply.

Daniii · December 12, 2020

5 minutes ago, Barand said:

Dann müssen Sie unbedingt lesen @requinixAntwort.

do i have to change something in the database?

Barand · December 12, 2020

No, just the query to use UNIX_TIMESTAMP() instead of CURDATE() since that is how your expire date is stored.

Daniii · December 12, 2020

<?php

if (isset($_POST['reset-submit'])) {
    $selector = $_POST['selector'];
    $validator = $_POST['validator'];
    $password = $_POST['password'];
    $password2 = $_POST['password2'];

    // probably better to check this earlier
    if (empty($password) || empty($password2)) {
        header("Location: ../create-new-password.php?newpassword=empty&selector=$selector&validator=$validator");
    } elseif ($password !== $password2) {
        header("Location: ../create-new-password.php?newpassword=passwordsnotmatch");
    }

    $currentDate = date("U");

    require "dbh.inc.php";
    
    $sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= UNIX_TIMESTAMP();";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error 1";
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, 's', $selector);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if (!$row = mysqli_fetch_assoc($result)) {
            echo 'You need to re-submit your reset request.';
            exit();
        } else {
            $tokenBin = hex2bin($validator);
            $tokenCheck = password_verify($tokenBin, $row['token']);

            if (!$tokenCheck) {
                echo 'You need to re-submit your reset request.';
                exit();
            } else {
                $email = $row['email'];
                $sql = "SELECT * FROM users WHERE email = $email";
                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo "SQL error 2";
                    exit();
                } else {
                    mysqli_stmt_bind_param($stmt, 's', $email);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if (!$row = mysqli_fetch_assoc($result)) {
                        echo "SQL error 3";
                        exit();
                    } else {
                        $sql = "UPDATE users SET password=? WHERE email=?";
                        $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo "SQL error4 ";
                            exit();
                        } else {
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            mysqli_stmt_bind_param($stmt, 'ss', $hashed_password, $email);
                            mysqli_stmt_execute($stmt);
                            $sql = 'DELETE FROM reset_password WHERE email=?';
                            $stmt = mysqli_stmt_init($conn);
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL error5';
                                exit();
                            } else {
                                mysqli_stmt_bind_param($stmt, 's', $email);
                                mysqli_stmt_execute($stmt);
                                header("Location: ../signup.php?newpassword=updated");
                            }
                        }
                    }
                }
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);

    header('Location: ../reset-password.php?reset=success');
} else {
    header('Location: ../index.php');
}

You need to re-submit your reset request.

Barand · December 12, 2020

Then it didn't find a record matching your data conditions.

Daniii · December 12, 2020

2.png.dcf3cd87b6a3a1edaa69e13ea3639ba8.png 1.png.2464fd4aad8dc433f6b1cd14f00d4264.png

the records are available

Daniii · December 12, 2020

Now i get SQL error 2

Barand · December 12, 2020

Why don't you output mysql's error messages and make it easier for you (and us)

Daniii · December 12, 2020

10 minutes ago, Barand said:

Why don't you output mysql's error messages and make it easier for you (and us)

the problem is there is no error from i got error reporting on

   echo 'SQL error 2';
                    ini_set('display_errors', 1);
                    ini_set('display_startup_errors', 1);
                    error_reporting(E_ALL);
                    echo "$mysqli->error";

Barand · December 12, 2020

That will only report php errors.

Put this at the beginning of your code, preferably just before you connect to mysql

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

Daniii · December 12, 2020

<?php

if (isset($_POST['reset-submit'])) {
    $selector = $_POST['selector'];
    $validator = $_POST['validator'];
    $password = $_POST['password'];
    $password2 = $_POST['password2'];

    // probably better to check this earlier
    if (empty($password) || empty($password2)) {
        header("Location: ../create-new-password.php?newpassword=empty&selector=$selector&validator=$validator");
    } elseif ($password !== $password2) {
        header("Location: ../create-new-password.php?newpassword=passwordsnotmatch");
    }

    $currentDate = date("U");

    require "dbh.inc.php";
    
    $sql = "SELECT * FROM reset_password WHERE selector=? AND expires >= UNIX_TIMESTAMP();";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error 1";
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, 's', $selector);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if (!$row = mysqli_fetch_assoc($result)) {
            echo 'You need to re-submit your reset request.';
            exit();
        } else {
            $tokenBin = hex2bin($validator);
            $tokenCheck = password_verify($tokenBin, $row['token']);

            if (!$tokenCheck) {
                echo 'You need to re-submit your reset request.';
                $my = mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); 
                    echo "- $my (1)";
                exit();
            } else {
                $email = $row['email'];
                $sql = "SELECT * FROM users WHERE email = $email";
                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error 2';
                    $my = mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); 
                    echo "- $my (2)";
                    exit();
                } else {
                    mysqli_stmt_bind_param($stmt, 's', $email);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if (!$row = mysqli_fetch_assoc($result)) {
                        echo "SQL error 3";
                        exit();
                    } else {
                        $sql = "UPDATE users SET password=? WHERE email=?";
                        $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo "SQL error4 ";
                            exit();
                        } else {
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            mysqli_stmt_bind_param($stmt, 'ss', $hashed_password, $email);
                            mysqli_stmt_execute($stmt);
                            $sql = 'DELETE FROM reset_password WHERE email=?';
                            $stmt = mysqli_stmt_init($conn);
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL error5';
                                exit();
                            } else {
                                mysqli_stmt_bind_param($stmt, 's', $email);
                                mysqli_stmt_execute($stmt);
                                header("Location: ../signup.php?newpassword=updated");
                            }
                        }
                    }
                }
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);

    header('Location: ../reset-password.php?reset=success');
} else {
    header('Location: ../index.php');
}

Output: SQL error 2- 1 (2)

Daniii · December 12, 2020

can it be because of that $expires = date("U") + 1800;

Sign In

Problem with Mysqli (Password-reset script)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information