MySQLi Num Rows Doesnt Work

Question

i have a simple login system and i get nothing when trying to fetch the number of rows, the same method used to work all the time, i dont know what is going on today.

Code:

<?php
class LoginClass {
    public $User;
    public $Pass;
    public $Query;
    function Init() {
        $User = $this->User;
        $Pass = $this->Pass;
        if($User != '')
        {
            if($Pass != '')
            {
                $this->HashPass();
            }
            else
            {
                echo 'Please Enter A Password.';
            }
        }
        else
        {
            echo 'Please Enter A Username or E-Mail.';
        }
    }

    function HashPass() {
        $Pass = $this->Pass;
        $this->Pass = hash('sha256', $Pass);
        $this->CheckUser();
    }

    function CheckUser() {
        $User = $this->User;
        if(!filter_var($User, FILTER_VALIDATE_EMAIL))
        {
            $this->Query = 'SELECT * FROM Users WHERE User = "'.$User.'" AND Pass = "'.$this->Pass.'"';
        }
        else
        {
            $this->Query = 'SELECT * FROM Users WHERE EMail = "'.$User.'" AND Pass = "'.$this->Pass.'"';
        }
        $this->CheckDB();
    }

    function CheckDB() {
        $Query = $this->Query;
        $Connect = new mysqli("127.0.0.1", "root", "", "Data");
        $Stmt = $Connect->prepare($Query)
        $Stmt->execute();
        $Stmt->store_result();
        echo $Stmt->num_rows;
        $Stmt->close();
        $Connect->close();
    }

    function SetupSession() {
        echo 'Test';
    }
}

the Check DB is the problem here and im able to echo out the query variable in that function everything is fine, here is exactly what i get

SELECT * FROM Users WHERE User = "Test" AND Pass = "532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25"

I also checked my DB and all my tables are setup correctly and there is no password.

Only hashing a password with SHA256 is not enough. Use [bcrypt](http://stackoverflow.com/questions/4795385/how-do-you-use-bcrypt-for-hashing-passwords-in-php) instead. — Marcel Korpel, Apr 21 '13 at 15:32
what is `var_dump($Stmt);` outputting (just before closing)? — bwoebi, Apr 21 '13 at 15:39
@Marcel - Given that his database has no root password, I doubt this is a production environment. — Glitch Desire, Apr 21 '13 at 15:39
@Oshawott: He might easily copy this to a production environment and there are more SO visitors that see this code and think it's ok. — Marcel Korpel, Apr 21 '13 at 15:42
Are you sure that you even enter the if-part? .. When var_dump doesn't output nothing? — bwoebi, Apr 21 '13 at 15:43
@Marcel, this is true. However, SHA256 is hardly the worst thing I've seen people use (how often do we see unsalted MD5?). Salted SHA256 is fairly good. `bcrypt` has performance trade-offs that aren't always worth it, especially on shared or VPS hosting. — Glitch Desire, Apr 21 '13 at 15:46
@MoussaHarajli - Test by putting `die('running');` inside the `if` block. If your page dies correctly, the `if` block is being reached. — Glitch Desire, Apr 21 '13 at 15:46
What is `var_dump($Connect)` (before the `if` block)? @Oshawott: I don't see a *salted* SHA256. — Marcel Korpel, Apr 21 '13 at 15:47
@Moussa, then the issue (or at least, first issue) is that you're not reaching the `if` block. — Glitch Desire, Apr 21 '13 at 15:49
i am able to enter phpmyadmin, insert, delete, and update rows in my database i have another form that submits a post, and that works perfectly. — Moussa Harajli, Apr 21 '13 at 15:53
After the `$Connect` line and before the `if` block try this (will tell you if you have a connection issue): `if ($Connect->connect_errno) { echo "Failed to connect to MySQL: (" . $Connect->connect_errno . ") " . $Connect->connect_error; }` — Glitch Desire, Apr 21 '13 at 15:54
@Oshawott nothing happens and i also just did a fresh install of windows 8, thats what i have been using before and i have never encountered any problems with it before. — Moussa Harajli, Apr 21 '13 at 15:56
Do you have `mysqli` extension installed? (If PHP is newer than 5.3 answer is yes) — Glitch Desire, Apr 21 '13 at 15:59
Try replacing the `if` block, just assign `$Stmt` then run the code without the block. See if that works. — Glitch Desire, Apr 21 '13 at 16:07
@Oshawott Fatal error: Call to a member function execute() on a non-object in C:\Server\www\Assets\PHP\Login\Login.php on line 49 — Moussa Harajli, Apr 21 '13 at 16:12

score 0 · Answer 1 · answered Apr 21 '13 at 16:35

OK, need more space than the comments area, the issue is clearly in this block:

function CheckDB() {
    $Query = $this->Query;
    $Connect = new mysqli("127.0.0.1", "root", "", "Data");
    $Stmt = $Connect->prepare($Query)
    $Stmt->execute();
    $Stmt->store_result();
    echo $Stmt->num_rows;
    $Stmt->close();
    $Connect->close();
}

I think it's because you aren't binding parameters to the prepared statement, you've already included them inline in your earlier statement. Therefore, you probably want to:

Switch to non-prepared statement

The easy option here will be to switch to a non-prepared statement. Replace your block with:

function CheckDB() {
    $Query = $this->Query;
    $Connect = new mysqli("127.0.0.1", "root", "", "Data");
    $Stmt = $Connect->query($Query)
    echo $Stmt->num_rows;
    $Stmt->close();
    $Connect->close();
}

A word of caution with this approach: you need to sanitize your user input in the block which defines $User, otherwise you're leaving yourself open to mysql injection. In that block, change this line:

$User = $this->User;

To the following:

$User = mysql_real_escape_string($this->User);

MySQLi Num Rows Doesnt Work

1 Answers1