Ajek Ajek - 9 months ago 27
MySQL Question

2 simple SQL queries within PHP throwing errors

Still relatively new to PHP so I might be making some easy mistakes here.

The code below throws warnings:

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/unn_w14017307/public_html/Assignment3/JakeUploads/11March/logonProcess.php on line 87

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/unn_w14017307/public_html/Assignment3/JakeUploads/11March/logonProcess.php on line 88

Warning: mysqli_stmt_store_result() expects parameter 1 to be mysqli_stmt, boolean given in /home/unn_w14017307/public_html/Assignment3/JakeUploads/11March/logonProcess.php on line 89

Warning: mysqli_stmt_bind_result() expects parameter 1 to be mysqli_stmt, boolean given in /home/unn_w14017307/public_html/Assignment3/JakeUploads/11March/logonProcess.php on line 90


I assume this has something to do with the first query that I execute, as the first one works fine and then the second throws these errors.

Isn't a connection problem as the $conn works fine for the first query.

Here's the code:

<?php
$username = filter_has_var(INPUT_POST, 'username') ? $_POST['username']: null;
$password = filter_has_var(INPUT_POST, 'password') ? $_POST['password']: null;

include 'database_conn.php'; // make db connection

/* Query the users database table to get the password hash for the username entered by the user in the logon form */

$sql = "SELECT password FROM Users WHERE username = ?";
$typeSql = "SELECT type FROM Users WHERE username = $username";

$stmt = mysqli_prepare($conn, $sql); // prepare the sql statement

/* Bind the $username entered by the user to the prepared statement. Note the ā€œsā€ part indicates the data type used ā€“ in this case a string */

mysqli_stmt_bind_param($stmt, "s", $username);

mysqli_stmt_execute($stmt); // execute the query
mysqli_stmt_store_result($stmt); //store result so second query can be used
/* Get the password hash from the query results for the given username and store it in the variable indicated */

mysqli_stmt_bind_result($stmt, $returnedPass);

/* Check if a record was returned by the query. If yes, then there was a username matching what was entered in the logon form and we can now test to see if the password entered in the logon form is the same as the stored (correct) one in the database. */

if (mysqli_stmt_fetch($stmt)) {
if($password === $returnedPass)
{
mysqli_stmt_close($stmt);
$stmt = mysqli_prepare($conn, $typeSql);
mysqli_stmt_bind_param($stmt, "s", $username);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
mysqli_stmt_bind_result($stmt, $accountType);


$_SESSION['uName'] = $username;
$_SESSION['logged-in'] = true;
$_SESSION['type'] = $type;

echo "<p>Password valid</p>";
echo "<p>Username: $username</p>";
echo "<p>Account type: $type</p>";


}
else
{
echo "<p>Password invalid</p>";
}
}
else {
echo "<p>Sorry we don't seem to have that username.</p>";
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

Answer Source

The culprit is that mysqli can only bind parameters to a ?.

$sql = "SELECT password FROM Users WHERE username = ?";
$typeSql = "SELECT type FROM Users WHERE username = ?";

However, you should be preparing your statements only once:

if(! $typestmt =  mysqli_prepare($conn, $typeSql)){
  die('error in sql syntax');
}

if (mysqli_stmt_fetch($stmt)) {
  if($password === $returnedPass)
  {
    mysqli_stmt_bind_param($typestmt, "s", $username);
    mysqli_stmt_execute($typestmt);
    mysqli_stmt_store_result($typestmt);
    mysqli_stmt_bind_result($typestmt, $accountType);
  }
}

On a side note as @Fred is saying storing plain text passwords is not safe.

Use password_hash() to generate a hash, store it as password and retrieve that value and use password_verify() with the hash stored into the database with the value the user provided to verify a login.

Another point is that you can combine the query to this:

SELECT password, type FROM Users WHERE username = ?

However for a secure login, I would do something like:

SELECT id FROM users WHERE username = ? AND password = ?"