Login system, converting mysqli to pdo error

Question

I am trying to convert some MySQLi code to PDO for a Unity project. I am making a login system and I have already changed some of the MYSQLi into PDO, however I am getting an error on line 33:

Fatal Error: Call to undefined method PDOStatement::fetch_assoc()

I tried to find PDO's version of fetch_assoc and found this:

fetch(PDO::FETCH_ASSOC);

So I changed the line to

while($row = $result->fetch(PDO::FETCH_ASSOC)) {

And now my Unity DBcontroller which has a "Debug.Log(www.downloadHandler.text" isn't returning anything and the login message isn't appearing... Could you wizards please advise me on where to go next? I guess I should say the script worked fine when I was using MySQLi, but I needed to convert it to PDO for this project. Here is the code below:

<?php
$servername = "***";
$username = "***";
$password = "***";
$dbname = "***";

//variables submitted by user
$loginUser = $_POST["loginUser"];
$loginPass = $_POST["loginPass"];

// Create Connection
$conn = new PDO("mysql:host=***;dbname=***",
"***",
"***",
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

//Check Connection
if(!$conn){
    die("Connection failed.");
}

$sql = "SELECT password FROM users WHERE username = '" . $loginUser . "'";

$result = $conn->query($sql);

if ($result->fetchAll() > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if($row["password"] == $loginPass){
            echo "Login Success. Welcome ", $loginUser, "!";        
        }
        else{
            echo "Wrong Credentials.";
        }
    }
} else {
    echo "Username does not exist.";
}
$conn = null;

?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 11 '19 at 18:12
**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Nov 11 '19 at 18:13
@YourCommonSense why do you think this is a duplicate of that question? — Nick, Nov 12 '19 at 11:22
Because the OP is trying to use a function from the different API, which is clearly explained in the duplicated post as not possible. — Your Common Sense, Nov 12 '19 at 11:25

Nick · Accepted Answer · 2019-11-11T02:23:03.193

When you call fetchAll you fetch all the result rows from the query, so there is no data left when you call fetch. Since you only expect one row to be returned, you can just call fetch once instead:

if ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if($row["password"] == $loginPass){
        echo "Login Success. Welcome ", $loginUser, "!";        
    }
    else{
        echo "Wrong Credentials.";
    }
}
else {
    echo "Username does not exist.";
}

Notes:

You are wide open to SQL injection and should use a prepared statement
You should not store passwords in plain text, instead use PHP's password_hash and password_verify to store and verify them
It is generally considered bad practice to distinguish between incorrect password and an invalid username. Use the same error message for both.

You should use something like this code to avoid these issues:

$sql = "SELECT password FROM users WHERE username = :username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $loginUser);
$stmt->execute();
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if(password_verify($loginPass, $row["password"])) {
        echo "Login Success. Welcome ", $loginUser, "!";        
    }
    else{
        echo "Wrong Credentials.";
    }
}
else {
    echo "Wrong Credentials.";
}

Just tested the changes you made and it's working again now! Thanks so much. I appreciate the heads up about the SQL injection and will look into using the prepared statements. Thanks! - Just seen the edit, I've now made those changes too :) — Dizlen, Nov 11 '19 at 02:23
@Dizlen see my edit - I've added some code to use a prepared statement — Nick, Nov 11 '19 at 02:24

Login system, converting mysqli to pdo error

1 Answers1