CODEWITHSUNDEEP
phpmysqlmysqli
ben jay hutton
ben jay hutton

Reputation: 404

mysqli query returns 0 results despite information in MySQL database

I am in the process of making a login screen so once the session has been set a person can access various pages on my site.

It seems, however, that when I send the username and password to be compared with what I have in the MySQL database the results come back empty.

MySQL table:

Id, Username, Password, Email, group
1,  bunbun, hashedpassword, [email protected], admin

PHP code:

<?php
include_once("functions/con-open.php");

if (isset($_POST['username']))
{
    $name = $_POST['username'];

    //$password = password_hash($_POST['password'], PASSWORD_BCRYPT);
    $password = $_POST['password'];
    $name = mysql_real_escape_string($name);
    $password = mysql_real_escape_string($password);
}

function LOGIN($Name, $Password)
{
    $conn = new mysqli(HOST,USER,PASSWORD,DATABASE);

    if ($conn->connect_errno) 
    {
        printf("Connect failed: %s\n", $conn->connect_error);
        exit();
    }   

    if ($result = $conn->query("SELECT * FROM persons WHERE Username='$Name'", MYSQLI_USE_RESULT))
    {
        printf("Select returned %d rows.\n", $result->num_rows);
        echo "<br>Name  Dump<br>";
        var_dump($Name);
        echo "<br>Password  Dump <br>"; 
        var_dump($Password);
        echo "<br>";
        echo "<br>";
        var_dump( $result);
        $result->close();
    }
    else
    {
        echo"no details returned <br>";
        var_dump( $result);
        $result->close();
    }
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Login</title>
<link type="text/css" href="css.css" rel="stylesheet" />
</head>

<body>


<form action="login.php" method="post" name="login-form">
Email: <input type="text" name="username" /><br>
Password: <input type="password" name="password"/>
<input type="submit" value="Login"/>
</form>

<?php

if ($_SERVER['REQUEST_METHOD'] == 'POST') 
{
    LOGIN($NAME, $PASSWORD); 
}

?>

</body>
</html>

The result is 

    Select returned 0 rows.
This is what I have typed in username field: 
This is what I have typed in password field:
Name from DB:
Password from DB:

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(5) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }

Any ideas where I am going wrong?
The column for my hash is Vchar(255) I gave it enough room from the beginning.

Update! This maybe more serious, I have changed the code above to reflect the excellent answer from maytham. however it is still returning 0 results
I took the liberty of adding within the loop, if (isset($_POST['username'])) 

echo "This is what the value name is: ".$name."<br>";

and the same just before i call LOGIN($name, password); to see if $name is set, it is.
however between the function being called and $Name being used in the sql query the value is empty. Not sure why that would be I'll have to debug my php installation to see if there is any bugs.

Upvotes: 1

Views: 3625

Answers (2)

exussum
exussum

Reputation: 18550

You have either edited data or need a trim () for invisible chars.

string(13) "bunbun"

That's not 13 chars in length

There's no reason as it currently stands that the query should dump like it is.

As others have noted. Be aware if SQL injection too

Upvotes: 0

Maytham Fahmi
Maytham Fahmi

Reputation: 33387

Here is what I have observed and fix it for you.

Follow the steps and it should works:

Step A

if ($result = $conn->query("SELECT * FROM persons WHERE Username='$Name'", MYSQLI_USE_RESULT))

To

if ($result = $conn->query("SELECT * FROM persons WHERE Username = '$Name'"))

Step B

The code presented in the question, the function LOGIN need to end with }

function LOGIN($Name, $Password)

Step C (Optional)

When I test I have changed password to plain text to be sure every thing is working, but it is up to you.

$password = $_POST['password'];

Step D

At the beginning of the code I will put if statement

if (isset($_POST['username']))
{
    $name = $_POST['username'];
    // disable for testing
    //$password = password_hash($_POST['password'], PASSWORD_BCRYPT);
    $password = $_POST['password'];
}

Step E (Optional)

I suggest you change all variables like $USERNAME to $username

Step F

Try to protect your inputs from SQL injection by adding mysql_real_escape_string:

$name = mysql_real_escape_string($name);
$password = mysql_real_escape_string($password);
  • thank to user @ibu for noticing that

Finally Here is the full workable code with my modification:

<?php
if (isset($_POST['username']))
{
    $name = $_POST['username'];
    //$password = password_hash($_POST['password'], PASSWORD_BCRYPT);
    $password = $_POST['password'];
    $name = mysql_real_escape_string($name);
    $password = mysql_real_escape_string($password);
}
function LOGIN($Name, $Password)
{
    $conn = new mysqli("localhost", "root", "", "test");

    if ($conn->connect_errno)
    {
        echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error;
    }

    if ($result = $conn->query("SELECT * FROM persons WHERE Username = '$Name'"))
    {
        $row = $result->fetch_assoc();
        printf("Select returned %d rows.\n", $result->num_rows);
        echo "<br />";
        echo "This is what I have typed in username field: " . $Name;
        echo "<br />";
        echo "<b>Name from DB: </b>" . $row['Username'] . "<br />";
        echo "<b>Password from DB: </b>" . $row['Password'] . "<br />";
        echo "<br />";
        var_dump($result);
        $result->close();
    } else
    {
        echo "no details returned <br>";
        var_dump($result);
        $result->close();
    }
}

?>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
    <title>Login</title>
    <link type="text/css" href="css.css" rel="stylesheet"/>
</head>

<body>

<form action="login.php" method="post" name="login-form">
    Email: <input type="text" name="username"/><br>
    Password: <input type="password" name="password"/>
    <input type="submit" value="Login"/>
</form>

<?php

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    LOGIN($name, $password);
}

?>

</body>
</html>

Here is a test shot from my screen: enter image description here

Note: When all this mentioned, this does not mean the code or what is presented is a good or perfect solution. There is a lot you have to think about when building a login mechanism. The code is also vulnerable for SQL injection you need and have to do a lot work. I encourage you to look at:

Link1: http://www.wikihow.com/Create-a-Secure-Login-Script-in-PHP-and-MySQL

Link2: https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project

Link3: http://php.net/manual/en/mysqli.quickstart.statements.php

Upvotes: 1

Related Questions