PDO database select not returning expected data

[Mike Askew]

I have the following

                        $subUsername = trim($_POST["user"]);
                        $sql = "SELECT userID, username, password FROM user WHERE username = ':user')";
                        $q = $conn->prepare($sql);
                        $q->bindParam(':user', $subUsername);
                        $q->execute();

                        $result = $q->fetch(PDO::FETCHASSOC);
                        print_r($result);
                        echo "</p>";
                        print_r($subUsername);
                        echo "</p>";
                        print_r($subPassword);

However $result is always just an empty array, if I run the SQL on the database it works fine. Is anything obviously wrong?

The username and password print fine using print_r()

[jkon]

I use question mark placeholders with PDO but why do you have single quotes around :user in your SQL statement ?

[Mike Askew]

was just trying it, didnt work without them either.

Have also tried

                        $sql = "SELECT userID, username, password FROM user WHERE username = ?)";
                        $q = $conn->prepare($sql);
                        $q->execute(array($subUsername));

[jkon]

Next I can see is that there is PDO::FETCH_ASSOC and not PDO::FETCHASSOC

@see
http://php.net/manual/en/pdostatement.fetch.php

[Mike Askew]

I did also try $result = $q->fetchAll(); which again returned an empty array, so I dont think the FETCH_ASSOC is the route cause :(

[pritaeas]

Does a simple SELECT without a WHERE return results?

[Mike Askew]

Yes it does Prit, returns one row, which is all I expect to see. So something on the where must be off...

[jkon]

Could be a charset issue ? For example if the username field is UTF-8 and the connection in the PDO isn't defined as UTF-8 (or with SET NAMES) then this problem could arise.

[Mike Askew]

EDITED

Ok so I get data if I use:

                        $sql = "SELECT * FROM user WHERE username = 'mike'";
                        $q = $conn->prepare($sql);
                        //$q->bindParam(':user', $subUsername);
                        $q->execute();

                        $result = $q->fetchAll();
                        print_r($result);

But as soon as I try changing it to a param it fails to work

                        $sql = "SELECT * FROM user WHERE username = :user";
                        $q = $conn->prepare($sql);
                        $q->bindParam(':user', $subUsername);
                        $q->execute();

                        $result = $q->fetchAll();
                        print_r($result);

[Mike Askew]

Ok putting this thread back on topic as to where I am, partially going mad been looking at this all day.

                        $sql = "SELECT * FROM user WHERE username = :user";
                        $q = $conn->prepare($sql);
                        $q->bindParam(':user', $subUsername);
                        $q->execute();

                        $result = $q->fetchAll();
                        print_r($result);

                        if (count($result) > 0 && password_verify($subPassword, $result[0]["password"])) {

The above code works however password_verify appears to be failing when the password is correct and the database version is hashed using password_hash()

[Mike Askew]

password_hash($regPassword, PASSWORD_DEFAULT)

`$2y$10$fVVIZoq1WrgW6trHhp0hCe7jeAeMExKjOJH3dx92rHznCOL2BAsuC`

is generating a different hash for the same password

password_verify($subPassword, $result[0]["password"])

`$2y$10$IHO5FhB62/LrIFyAf0K2quDFe4yN6Jv9z1unzf2EHVL`

Same password entered on both pages but echoing password_hash on the same page as the verify returns the 2nd hash, that differs from the first

[jkon]

What do you mean that now it is working ? Do you finaly got results and the next thing is that you can't verify the password?

[Gideon_1]

Try this Mike.

    $subUsername = trim($_POST["user"]);

    $sql = "SELECT userID, username, password FROM user WHERE username = :user";

    $q = $conn->prepare($sql);
    $q->bindParam(':user', $subUsername);
    $q->execute();

    $result = $q->fetchAll(PDO::FETCH_ASSOC);

    print_r($result);
    echo "</p>";
    print_r($subUsername);
    echo "</p>";
    print_r($subPassword);?>

You were adding a parenthesis to the Param and aslo you did not use the right argument for the fetch(). Its

`fetch(PDO::FETCH_ASSOC)` NOT `fetch(PDO::FETCHASSOC`)

[Mike Askew]

jkon, yeah the data is coming through now, just the password hashing that isn't lining up for some reason..

[Gideon_1]

Maybe you are reharshing the password from the database. Can you show the codes performing the hashing and verification.

[Mike Askew]

Just done some reading on stack overflow. It is correct that I don't see the same as password_hash won't give the same response twice.

So just have to figure out the code error.

Hashing code:

                        $conn = new PDO($dsn, $user, $pass);

                        $sql = "INSERT INTO user (username,password,staff) 
                                VALUES (:username,:password,:staff)";
                        $q = $conn->prepare($sql);
                        $q->execute(array(':username'=>$regUsername, 
                                          ':password'=>password_hash($regPassword, PASSWORD_DEFAULT),
                                          ':staff'=>false));

Compare code:

                        $conn = new PDO($dsn, $user, $pass);

                        $sql = "SELECT * FROM user WHERE username = :user";
                        $q = $conn->prepare($sql);
                        $q->bindParam(':user', $subUsername);
                        $q->execute();

                        $result = $q->fetchAll();
                        echo  $result[0]["password"];

                        if (count($result) > 0 && password_verify($subPassword, $result[0]["password"])) {

                        }

[jkon]

Are both trimmed ? Also what is the password type of the table field (could be smaller)?

Irrelevant but Notice: That the PASSWORD_DEFAULT might change from one PHP version to other

[Mike Askew]

Oh my days.

Simple things... I didn't update the field size in db from 50 when moving to hashings. Set to 255 and re-created user and now it works. Issues solved! ++rep