Login With Prepared Statements Mysqli_Stmt_Num_rows() Function

Question

borobhaisab 117 Posting Whiz

3 Years Ago

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

mysql php

2 Contributors
6 Replies
134 Views
2 Days Discussion Span
Latest Post 3 Years Ago Latest Post by Dani

Dani AI

Generated 9 Months Ago

Short answer: mysqli_stmt_num_rows() will usually report zero unless you buffer the statement result first — and there are also a couple of small bugs in the posted SQL/binding that will always make the WHERE clause miss. The immediate fixes are: call mysqli_stmt_store_result() after execute, fix the placeholder order and variable you bind (use the hashed password), and be careful with AND/OR precedence in the SQL.

Minimal example of the missing step (add this right after execute):

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);          // ← required before num_rows for SELECT
$rows = mysqli_stmt_num_rows($stmt);
if ($rows > 0) {
    mysqli_stmt_bind_result($stmt, $id);
    mysqli_stmt_fetch($stmt);
    // success
} else {
    // incorrect credentials
}

See the docs for mysqli_stmt_store_result and mysqli_stmt_num_rows for details: mysqli_stmt_store_result and mysqli_stmt_num_rows.

Other concrete issues seen in the thread:

SQL precedence: domain = ? OR domain_email = ? AND password = ? parses as domain = ? OR (domain_email = ? AND password = ?). Use parentheses so the logic is (domain = ? OR domain_email = ?) AND password = ?.
Binding order / variable: bind the hashed value (not the raw $password) as the third parameter. Example: bind_param('sss', $domain, $domain_email, $hashed_password).
Input/variable scope: ensure your input-parsing sets $hashed_password every run (don’t use chained elseif that prevents the password from being set when domain is present).
Hash algorithm mismatch: the sample DB string looks like a 40-hex SHA-1 digest, not SHA-256 (which is 64 hex chars). Verify the DB actually stores the same algorithm/format you compute.

Extra troubleshooting: echo the trimmed $domain and $hashed_password before execute to confirm values, inspect mysqli_stmt_error($stmt) after execute, or use mysqli_stmt_get_result() (requires mysqlnd) and fetch an associative row instead: mysqli_stmt_get_result. This addresses the immediate mysqli_num_rows behavior; ’s higher-level notes about secure password storage are relevant but are on the other thread.

All 6 Replies

Dani 5,664 The Queen of DaniWeb

3 Years Ago

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.

Dani 5,664 The Queen of DaniWeb

3 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

borobhaisab 117 Posting Whiz · Answer 1 · 2022-12-05T18:35:53+00:00

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 117 Posting Whiz · Answer 2 · 2022-12-05T19:05:47+00:00

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 117 Posting Whiz · Answer 3 · 2022-12-05T19:46:43+00:00

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 117 Posting Whiz · Answer 4 · 2022-12-05T19:47:34+00:00

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

Login With Prepared Statements Mysqli_Stmt_Num_rows() Function

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers