mysqli_num_rows = 0 when it should be 1

Question

I've been trying to resolve this problem on my own and for the life of me can't fix it..

//If they are, retreive them from the Person table    
var_dump($_POST['username']);
var_dump($_POST['password']);
$select = $this-> doQuery("SELECT * FROM Person WHERE username = '{$_POST['username']}' AND password = '".md5($_POST['password'])."'");
var_dump($select);  

try {    
    if (mysqli_num_rows($select) > 0){
        var_dump(mysqli_num_rows($select));
        //fetching username and password from database
        $fetch_array = mysqli_fetch_assoc($select);
        var_dump($fetch_array);     
        //Building a session for the user
        $_SESSION['username'] = $fetch_array['username'];

        //var_dump($_SESSION['username']);

        //Redirecting the user to the index page        
        header('Location:index.php');
        return true;
    }

The two var_dumps on the username and password on the 4th and 5th lines returned this:

string(8) "johnny03" 
string(9) "password3" 
object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(8) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }

The values that I entered for both the password and username are both valid (they are in the database under the correct columns, trust me). I have a feeling that something is wrong with my select query because the var dump on mysqli_num_rows($select) doesn't return anything.That being said, I've tried just about every change to the query that I could think of to make it work and still nothing :[ Can anyone help?

Is the username pre- and suffixed by a brace ? That's not a typo ? — Daneo, Nov 07 '12 at 22:23
And does `var_dump(md5($_POST['password']));` yield the same password that is in the database in the password column? Also you are vulnerable to sql-injection in that query. — drew010, Nov 07 '12 at 22:24
+1 to @drew10. If you are sure the username is correct, the encryption of the password in the database has to be verified — janenz00, Nov 07 '12 at 22:26
Also note that md5 hashes of passwords is insufficient for secure password storage but I'm guessing the hash is your problem. Otherwise make sure your columns are case-insensitive if the case enetered differs from that in the database. — drew010, Nov 07 '12 at 22:29
Yes, I put the braces around $_POST['username'] because I was told that it was needed for the $fetch_array — Joanne DeBiasa, Nov 07 '12 at 22:31
Check your database field where the passwords are stored. Those generally consist of 128 bits, or 32 characters. — Daneo, Nov 07 '12 at 22:31
when I var_dump(md5($_POST['password'])); it returns 819b0643d6b89dc9b579fdfc9094f28e, in the database it is 819b0643d6b89dc, does that mean something is wrong — Joanne DeBiasa, Nov 07 '12 at 22:35
@JoanneDeBiasa It probably means that your varchar length for the password column isn't long enough. For md5 it should be 32 bytes, but md5 is insecure for password hashing so increase the length of the password field to something bigger and check out bcrypt. — drew010, Nov 07 '12 at 22:36
Thanks guys! The problem was with my varchar database after all. I probably would have sat here staring at my code for another three hours before even thinking of that. — Joanne DeBiasa, Nov 07 '12 at 22:51

Daneo · Accepted Answer · 2012-12-14T23:16:35.503

First : Try removing the braces from the query :

$select = $this-> doQuery("SELECT * FROM Person WHERE username = '{$_POST['username']}' AND password = '" . md5($_POST['password']) . "'");

Edit : Braces are okay, see comment by drew010.

If that isn't the problem, as noted by drew010, check your resulting hash (output it using a simple echo and compare it to your database.

If those do seem to match, but there is a piece missing, then adjust the capacity of your password field in your database.

Your password field most likely has a size e.g. varchar(20) which is too small for the entire hash to fit in. Adjust the size in your database and store the correct hash result.

Also, at least see into some salting, md5 has been broken since a while now so you should replace it by a hash of the sha-family.

You can verify the hash in the database as well by using an online md5 hashing and get the length by measuring it using strlen

The braces around the variable are okay, it is required to parse certain types of variables. [Source](http://php.net/manual/en/language.types.string.php#language.types.string.parsing). — drew010, Nov 07 '12 at 22:38

score 0 · Answer 2 · answered Nov 07 '12 at 22:41

Try this:

var_dump("SELECT * FROM Person WHERE username = '{$_POST['username']}' AND password = '".md5($_POST['password'])."'");

It will dump out the query. Any obvious errors you should be able to pick out straight away. If the query looks fine, try copying and pasting it into PHPMyAdmin (or similar) to test the query. If it returns zero rows also, then maybe the User/Pass don't match?

mysqli_num_rows = 0 when it should be 1

2 Answers2