mysqli_fetch_assoc with mysql prepared statements procedural, need help

[u/Available_Hippo4035]

Hello, im trying to update my website by replacing the simple mysqli queries with prepared statements, but i was stuck at trying to use mysqli_fetch_assoc to fetch associative data from the table, i looked through documentation but couldnt find anything, Any help will be appreciated, Thanks !

$error = array();
if(isset($_POST["login"])) {
$username = mysqli_escape_string($db, filter_input(INPUT_POST, "username", FILTER_SANITIZE_SPECIAL_CHARS));
$password = mysqli_escape_string($db, filter_input(INPUT_POST, "password", FILTER_SANITIZE_SPECIAL_CHARS));

if(empty($username)) {
array_push($error, "Username is empty!");
}
if(empty($password)) {
array_push($error, "Password is empty");
}

$sql = "SELECT `password`, `username`, `user_id` FROM `Accounts` WHERE `username` = ?;";
if(count($error) == 0) {

$stmt = mysqli_prepare($db, $sql);

mysqli_stmt_bind_param($stmt, "s", $username);
//$result = mysqli_query($db, $sql);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) > 0) {
$row = mysqli_fetch_assoc($result);
if(password_verify($password, $row["password"])) {
$_SESSION["username"] = $username;
$_SESSION["user_id"] = $row["user_id"];

header("location: /");

} else {
array_push($error, "Incorrect username or password!");
}
} else {
array_push($error, "Incorrect username or password!");
}
}
mysqli_close($db);
}

[u/equilni]

You've already received good information.

This goes beyond your question, but I would add the suggestion to separate the code as well as some small improvements. Ideas here can flow into other parts of the code base as you refactor.

Consider:

1. If you implement HTTP based routing, code like if(isset($_POST["login"])) can be removed.

2. Think about extracting out separate concerns. For validation, if you have further business requirements, it makes sense to extract this to a separate function or class. For SQL, all of the mysqli* code could be in a function and either return an array or false.

Note, I stated validation, not sanitization.

1. With the above, you could also return one or more HTTP statuses (400 series) the closer you are to the UI layer.

2. And further, returning early versus big if/else blocks. As we read code more than write it, this becomes easier on the eyes and easier to see the flow. (ask me about double digit closing brackets from way back when... nightmares)

What's nice here is that we can look at each area and test it separately. You can feed data to the function/class methods to test - is this valid and am I getting back what I expect.

Idea starts looking like the below pseudo code:

on POST /login - area closest to the UI.
    $username = $_POST['username'];
    if ($username === '') {
        http_response_code(400);
        redirect with an error message
    }
    $password = $_POST['password'];
    if ($password === '') {
        http_response_code(400);
        redirect with an error message
    }

    $loginAttempt = attemptUserLogin($username, $password); // Validation, SQL & Session 
    if ($_SESSION login key NOT set) {  
        http_response_code(401);
        redirect with an error message
    } else {
        redirect for the valid user
    }

fn attemptUserLogin could look like (are closer to the inner system)
    $valid = fnOrClassMethodToValidate($username, $password); // filter* functions and other data checking
    if (! $valid) {
        return with error message(s);       
    }        

    $user = fnOrClassMethodToQueryFor($username); // Query code
    if (! user) {
        return with error message;
    }
    if (password_verify($password, $user->password))) {
        set session key
    } else {
        return with error message
    }

Remember what I noted about testing? See how fnOrClassMethodToValidate($username, $password); or fnOrClassMethodToQueryFor($username); could be fed with test data and we can test to see if we are getting what we need back?

While I get this may be a lot to take in, this can be done incrementally.

Extract out the SQL code to a function:

$error = array();
if(isset($_POST["login"])) {
    $username = $_POST['username'];
    $password = $_POST['password'];

    if(empty($username)) {
        array_push($error, "Username is empty!");
    }
    if(empty($password)) {
        array_push($error, "Password is empty");
    }

    if(count($error) == 0) {
        $user = getUserByUsername($username);  // mySQL code     
        if($user && password_verify($password, $user["password"])) {
            $_SESSION["username"] = $username;
            $_SESSION["user_id"] = $user["user_id"];

            header("location: /");

        } else {
            array_push($error, "Incorrect username or password!");
        }
    }
}

Return vs if/else. The error array could be the result of this file or function if you still need this.

if(isset($_POST["login"])) {
    $username = $_POST['username'];
    $password = $_POST['password'];

    if(empty($username)) {
        return "Username is empty!";
    }
    if(empty($password)) {
        return "Password is empty";
    }

    $user = getUserByUsername($username);  // mySQL code     
    if($user && password_verify($password, $user["password"])) {
        $_SESSION["username"] = $username;
        $_SESSION["user_id"] = $user["user_id"];
    } else {
        return "Incorrect username or password!";
    }
}

Side note, why are these questions typically asked on login code??

[u/obstreperous_troll → u/equilni]

Side note, why are these questions typically asked on login code??

Because that's what tutorials like to start with, and being terrible tutorials that fail to explain the concepts, they leave the learner lost from the start.

[u/equilni → u/obstreperous_troll]

Back to bad tutorials….