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()

Recommended Answers

All 17 Replies

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

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

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

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

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.

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

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()

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

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?

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`)

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

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

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"])) {

                        }

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

commented: +rep +7

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

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.