Login With Prepared Statements Mysqli_Stmt_Num_rows() Function

[borobhaisab]

Hi,

I am trying to create a login page using the sql's mysqli_stmt_num_rows() function.
Issue is, no matter if I give correct password or incorrect, I always get message 'Incorrect user Credentials'.
Why is that ?
The details on Mysql Looks like this:

id|domain|password

0|gmail.com|373b29d2837e83b9ca5cec712a5985843df271cc

Obviously, password is hashed using sha_256.

Here is the php code:

<?php
ini_set("display_errors",1);
ini_set("display_startup_errors",1);
error_reporting(E_ALL);

echo login_form();

if($_SERVER['REQUEST_METHOD'] === 'POST')
{
    echo __LINE__; echo '<br>';//DELETE

    check_user_input();

    echo __LINE__; echo '<br>';//DELETE

    process_login_form();

    echo __LINE__; echo '<br>';//DELETE
}


function login_form()
{
    echo 
    '
    <div name="center pane" id="center pane" align="center" size="50px" width="33%">
    <form method="POST" action="" name="login_form" id="login_form" width="50%">
    <fieldset>
    <label for="domain">Domain</label>
    <input type="text" name="domain" id="domain" size="50" minlength="5" maxlength="253" title="Input your Domain" placeholder="yourdomain.tld">
    <br>
    <label for="password">Password</label>
    <input type="text" name="password" id="password" size="50" minlength="8" maxlength="25" title="Input your Password" placeholder="alpha-numerical-chars">
    <br>
    </fieldset>
    <fieldset>
    <button type="submit" name="login" id="login" title="Submit Form">Login!</button>
    </fieldset>
    </form>
    </div>
    ';
}


function check_user_input()
{
    if(!EMPTY($_POST['domain']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain = trim($_POST['domain']);
    }
    elseif(!EMPTY($_POST['domain_email']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain_email = trim($_POST['domain_email']);
    }
    elseif(!EMPTY($_POST['password']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $password = trim($_POST['password']);
    }
}


function process_login_form()
{
    echo __LINE__; echo '<br>';//DELETE

    Global $domain;
    Global $password; //DELETE
    Global $hashed_password;

    //Query DB.
    //Check if User already registered or not.
    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
    $conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");
    $stmt = mysqli_stmt_init($conn);
    $sql = "SELECT id FROM domains WHERE domain = ? AND password = ?";

    if(!mysqli_stmt_prepare($stmt,$sql))
    {
        echo __LINE__; echo '<br>';//DELETE

        echo 'Mysqli Error: ' .mysqli_stmt_error(); //DEV MODE.
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_stmt_errno(); //DEV MODE.
        echo '<br>';
        die('Registration a Failure!');
    }
    else
    {
        echo __LINE__; echo '<br>';//DELETE

        mysqli_stmt_bind_param($stmt,"ss",$domain,$hashed_password);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt,$id);
        mysqli_stmt_fetch($stmt);

        if($num_rows = mysqli_stmt_num_rows($stmt)<1) //User not registered.
        {
            echo __LINE__; echo '<br>';//DELETE
            echo 'password: '.$password; echo '<br>';
            echo 'hashed password: '.$hashed_password; echo '<br>';

            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            die('Incorrect User Credentials!');
        }
        mysqli_stmt_close($stmt);
        mysqli_close($conn);

        echo __LINE__; echo '<br>';//DELETE
        echo 'password: ' .$password; echo '<br>';
        echo 'hashed password: ' .$hashed_password; echo '<br>';

        echo 'You are logged in!';
        exit;
    }
}

[Dani]

Perhaps I'm missing something, but you are setting the unencrypted password that the end user types in into $password. You're then using an SQL query to select a row where the password is the value of that unencrypted password. If your passwords are stored in the database encrypted, it would result in no rows being retrieved.

Instead, please look into PHP's password_hash and password_verify functions.

What I have experience doing is save the password to the database with the password_hash() function. This encrypts the password with a unique salt. Then, when it comes time to logging in, I retrieve the row from the database that has the correct username (or in your case, domain). Then, use password_verify() to compare the password field retrieved from the database with the unencrypted one retrieved via POST, and check for a match.

[borobhaisab]

@dani

I am checking against a hashed password:

mysqli_stmt_bind_param($stmt,"ss",$domain,$hashed_password);

However. Oops! It seesm I forgot to hash the password before checking against the hashed version.
Anyway, fixed the code but I still get 'Incorrect User Credentials!' error.

<?php
ini_set("display_errors",1);
ini_set("display_startup_errors",1);
error_reporting(E_ALL);

echo login_form();

if($_SERVER['REQUEST_METHOD'] === 'POST')
{
    echo __LINE__; echo '<br>';//DELETE

    check_user_input();

    echo __LINE__; echo '<br>';//DELETE

    process_login_form();

    echo __LINE__; echo '<br>';//DELETE
}


function login_form()
{
    echo 
    '
    <div name="center pane" id="center pane" align="center" size="50px" width="33%">
    <form method="POST" action="" name="login_form" id="login_form" width="50%">
    <fieldset>
    <label for="domain">Domain</label>
    <input type="text" name="domain" id="domain" size="50" minlength="5" maxlength="253" title="Input your Domain" placeholder="yourdomain.tld">
    <br>
    <label for="password">Password</label>
    <input type="text" name="password" id="password" size="50" minlength="8" maxlength="25" title="Input your Password" placeholder="alpha-numerical-chars">
    <br>
    </fieldset>
    <fieldset>
    <button type="submit" name="login" id="login" title="Submit Form">Login!</button>
    </fieldset>
    </form>
    </div>
    ';
}


function check_user_input()
{
    if(!EMPTY($_POST['domain']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain = trim($_POST['domain']);
    }
    elseif(!EMPTY($_POST['domain_email']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain_email = trim($_POST['domain_email']);
    }
    else
    {
        die('Input your Domain');
    }

    if(!EMPTY($_POST['password']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $hashed_password = hash('sha256',$_POST['password']); 
    }
}


function process_login_form()
{
    echo __LINE__; echo '<br>';//DELETE

    Global $domain;
    Global $password; //DELETE
    Global $hashed_password;

    //Query DB.
    //Check if User already registered or not.
    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
    $conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");
    $stmt = mysqli_stmt_init($conn);
    $sql = "SELECT id FROM domains WHERE domain = ? AND password = ?";

    if(!mysqli_stmt_prepare($stmt,$sql))
    {
        echo __LINE__; echo '<br>';//DELETE

        echo 'Mysqli Error: ' .mysqli_stmt_error(); //DEV MODE.
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_stmt_errno(); //DEV MODE.
        echo '<br>';
        die('Registration a Failure!');
    }
    else
    {
        echo __LINE__; echo '<br>';//DELETE

        mysqli_stmt_bind_param($stmt,"ss",$domain,$hashed_password);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt,$id);
        mysqli_stmt_fetch($stmt);

        //if($num_rows = mysqli_stmt_num_rows($stmt)<1) //User not registered. //FIRST ATTEMPT
        //{
        //if(!$num_rows = mysqli_stmt_num_rows($stmt)) //User not registered. //SECOND ATTEMPT
        //{
        if(!mysqli_stmt_num_rows($stmt)) //User not registered. //3RD ATTEMPT
        {

            echo __LINE__; echo '<br>';//DELETE
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            die('Incorrect User Credentials!');
        }
        mysqli_stmt_close($stmt);
        mysqli_close($conn);

        echo __LINE__; echo '<br>';//DELETE
        echo 'password: ' .$password; echo '<br>';
        echo 'hashed password: ' .$hashed_password; echo '<br>';

        echo 'You are logged in!';
        exit;
    }
}
?>

Notice the 3 attempts I made to check if there is any matching rows or not:

        //if($num_rows = mysqli_stmt_num_rows($stmt)<1) //User not registered.
        //{

        //if(!$num_rows = mysqli_stmt_num_rows($stmt)) //User not registered.
        //{

        if(!mysqli_stmt_num_rows($stmt)) //User not registered.
        {

All of them show same result. Incorrect User Credentials. Even if I typ ethe right password.
Even tried with this but no luck. Same result.

        if(!mysqli_stmt_fetch($stmt))
        {   
            echo __LINE__; echo '<br>';//DELETE
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            die('Incorrect User Credentials!');
        }

[borobhaisab]

Programmers,

I am opening another thread where I attempt with the password_verify() function. And so, do not advise me here to use that. This thread is a different function issue. Let's resolve this thread too.

[borobhaisab]

Corrected typo but no luck!

ini_set("display_errors",1);
ini_set("display_startup_errors",1);
error_reporting(E_ALL);

echo login_form();

if($_SERVER['REQUEST_METHOD'] === 'POST')
{
    echo __LINE__; echo '<br>';//DELETE

    check_user_input();

    echo __LINE__; echo '<br>';//DELETE

    process_login_form();

    echo __LINE__; echo '<br>';//DELETE
}


function login_form()
{
    echo 
    '
    <div name="center pane" id="center pane" align="center" size="50px" width="33%">
    <form method="POST" action="" name="login_form" id="login_form" width="50%">
    <fieldset>
    <label for="domain">Domain</label>
    <input type="text" name="domain" id="domain" size="50" minlength="5" maxlength="253" title="Input your Domain" placeholder="yourdomain.tld">
    <br>
    <label for="password">Password</label>
    <input type="text" name="password" id="password" size="50" minlength="8" maxlength="25" title="Input your Password" placeholder="alpha-numerical-chars">
    <br>
    </fieldset>
    <fieldset>
    <button type="submit" name="login" id="login" title="Submit Form">Login!</button>
    </fieldset>
    </form>
    </div>
    ';
}


function check_user_input()
{
    if(!EMPTY($_POST['domain']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain = trim($_POST['domain']);
    }
    elseif(!EMPTY($_POST['domain_email']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $domain_email = trim($_POST['domain_email']);
    }
    else
    {
        die('Input your Domain');
    }

    if(!EMPTY($_POST['password']))
    {
        echo __LINE__; echo '<br>';//DELETE

        $hashed_password = hash('sha256',$_POST['password']); 
    }
    else
    {
        die('Input your Password');
    }
}


function process_login_form()
{
    echo __LINE__; echo '<br>';//DELETE

    Global $domain;
    Global $password; //DELETE
    Global $hashed_password;

    //Query DB.
    //Check if User already registered or not.
    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
    $conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");
    $stmt = mysqli_stmt_init($conn);
    $sql = "SELECT id FROM domains WHERE domain = ?  OR domain_email = ? AND password = ?";

    if(!mysqli_stmt_prepare($stmt,$sql))
    {
        echo __LINE__; echo '<br>';//DELETE

        echo 'Mysqli Error: ' .mysqli_stmt_error(); //DEV MODE.
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_stmt_errno(); //DEV MODE.
        echo '<br>';
        die('Registration a Failure!');
    }
    else
    {
        echo __LINE__; echo '<br>';//DELETE

        mysqli_stmt_bind_param($stmt,"sss",$domain,$domain_email,$password);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt,$id);
        //mysqli_stmt_fetch($stmt);

        //if($num_rows = mysqli_stmt_num_rows($stmt)<1) //User not registered.
        //{
        //if(!$num_rows = mysqli_stmt_num_rows($stmt)) //User not registered.
        //{
        //if(!mysqli_stmt_num_rows($stmt)) //User not registered.
        //{
        if(!mysqli_stmt_fetch($stmt))
        {   
            echo __LINE__; echo '<br>';//DELETE
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            die('Incorrect User Credentials!');
        }
        mysqli_stmt_close($stmt);
        mysqli_close($conn);

        echo __LINE__; echo '<br>';//DELETE
        echo 'password: ' .$password; echo '<br>';
        echo 'hashed password: ' .$hashed_password; echo '<br>';

        echo 'You are logged in!';
        exit;
    }
}

[borobhaisab]

@dani

Do you know how to sue the mysqli_stmt_num_rows() function since you use pdo instead ?

[Dani]

I don't use PDO.

I use MySQLi but I don't use prepared statements. I just use manual queries and make sure to properly escape all user-input passed into the queries. I also use my own ORM that I wrote.