Is this prepared statement?

Question

Here is my code.

function login($username,$password){
    global $db;
    
    $sql = "SELECT * FROM users133 WHERE username=:username";
    $stmt = $db->prepare($sql);
    $stmt->execute(array(':username' => $username));
    if ($stmt->rowCount() > 0){
        $result = $stmt->fetchAll();
        $hash = $result[0]['password'];
        if (password_verify($password, $hash)) {
            $_SESSION['loggedIn'] = $result[0]['id'];
        header("location: ?page=profile"); /*<----AFTER LOGING IN YOU GET TO THIS PAGE*/
        }else{
            header("location: ?page=loginfailed");
        }
        
    }
    else{
        header("location: ?page=loginfailed");
    }
}

Yeah i know this post is duplicate but there is additional things that i need to ask!! I spend like 6 hours today reading how to do prepared statements. i read about the $stmt->bindParam command that makes the database check if the input value is int, string and so on(in case the user played with the inspect element option or putted malicious code in the form). Is that necessary for SELECT prepared statement? I am planning to copy the code from this login function and use it elsewhere in my site. That's why i need to ask if it is 100% safe the way it is right now.

Answer 1

Yes you are using a prepared statement with a parameter. That's the right thing to do. Parameters are the best way to write safe SQL statements in the majority of cases. There are just a few edge cases where they don't help (see my answer to how safe are PDO prepared statements)

I can suggest some small changes based on how I would write the code.

$sql = "SELECT id, password FROM users133 WHERE username=:username";

Avoid SELECT *, always spell out your columns explicitly. See

$stmt = $db->prepare($sql);
$stmt->execute(['username' => $username]);

If you have enabled PDO exceptions, this is okay, because any SQL error will interrupt the code and throw an exception. But if you have not enabled exceptions, you should always check the return value of both prepare() and execute(). See http://php.net/manual/en/pdo.error-handling.php and http://php.net/manual/en/pdo.errorinfo.php

The syntax of array() is from old PHP versions, and since PHP 5.4 you can use the shorter syntax with square brackets.

You don't need to use : in your key for the PDO param. Only in the SQL string. In old versions of PDO you needed : in both places, but not anymore.

while (row = $stmt->fetch()) {
    $hash = $row['password'];
    if (password_verify($password, $hash)) {
        $_SESSION['loggedIn'] = $row['id'];
        header("location: ?page=profile");
    }else{
        header("location: ?page=loginfailed");
    }
}
header("location: ?page=loginfailed");

The above avoids calling rowCount(). If there are no rows, then while() naturally finishes without doing one loop, and then it falls through to the last header() call.

I prefer to avoid calling rowCount() because it's confusing to remember when it works and when it doesn't work. The rowCount() will return 0 before the client has fetched all rows from the MySQL server. Sometimes executing the query implicitly fetches all rows into client memory, then calling fetch() just iterates over them. This is called a buffered query. But non-buffered queries are useful if your result will have too many rows to buffer. So it's not always clear when rowCount() will return the accurate count.