Prepared statement in PHP 8 / MySQL not working

PHP
1

Hi
I am trying out a script for a login but it appears the prepared statement is not prepared correctly. The thing is it worked fine on my local machine but fails when uploaded to live server. I have added some echoes to assist me in debugging and changed the database credentials. Maybe I messed the syntax up somehow but Ive checked and rechecked and cannot see why it is failing. It connects OK but fails at the point it prepares the statement to authenticate the user / pass. Can anyone see what is wrong, thanks.

Here is the on-screen result of my echoes:-

PHP version is: 8.0.11
About to start session
Session started
Connecting
Connected
Proceeding to authenticate submitted credentials
Preparing statement
Statement not prepared ok

and here is my code:-

<?php
echo 'PHP version is: ' . phpversion().'<br>';
echo 'About to start session<br>';
session_start();
echo 'Session started<br>';
// Change this to your connection info.
$DATABASE_HOST = 'myhost';
$DATABASE_USER = 'myuser';
$DATABASE_PASS = 'mypass';
$DATABASE_NAME = 'mydbname';
// Try and connect using the info above.
echo 'Connecting<br>';
$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if ( mysqli_connect_errno() ) {
	// If there is an error with the connection, stop the script and display the error.
	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}
else {
	echo 'Connected<br>';
	}

// Now we check if the data from the login form was submitted, isset() will check if the data exists.
if ( !isset($_POST['username'], $_POST['password']) ) {
	// Could not get the data that should have been sent.
	exit('Please fill both the username and password fields!');
}

echo 'Proceeding to authenticate submitted credentials<br>';
// Prepare our SQL, preparing the SQL statement will prevent SQL injection.
echo 'Preparing statement<br>';
if ($stmt = $con->prepare('SELECT id, password FROM accounts WHERE username = ?')) {
	echo 'Binding parameters<br>';
	// Bind parameters (s = string, i = int, b = blob, etc), in our case the username is a string so we use "s"
	$stmt->bind_param('s', $_POST['username']);
	$stmt->execute();
	// Store the result so we can check if the account exists in the database.
	
	$stmt->store_result();

	if ($stmt->num_rows > 0) {
	$stmt->bind_result($id, $password);
	$stmt->fetch();
	// Account exists, now we verify the password.
	// Note: remember to use password_hash in your registration file to store the hashed passwords.
	if (password_verify($_POST['password'], $password)) {
		// Verification success! User has logged-in!
		// Create sessions, so we know the user is logged in, they basically act like cookies but remember the data on the server.
		session_regenerate_id();
		$_SESSION['loggedin'] = TRUE;
		$_SESSION['name'] = $_POST['username'];
		$_SESSION['id'] = $id;
		// Original simple welcome with session name
		// echo 'Welcome ' . $_SESSION['name'] . '!'; 
		header('Location: home.php');
	} else {
		// Incorrect password
		echo 'Incorrect username and/or password!';
	}
} else {
	// Incorrect username
	echo 'Incorrect username and/or password!';
}
	
	$stmt->close();
}
else {
	echo 'Statement not prepared ok';
}
?>
2

Sorry guys, my bad, I hadn’t created table correctly in remote db, so worked fine local but not remote. I’m stupid I realise now, but I didn’t realise preparing the statement accessed the table, I just assumed table was only accessed when statement was executed. I know better now!

3

Just a thought. Do you have error reporting enabled? I would have thought that would pick it up.
We all make these silly mistakes, but the error reporting helps you identify and fix them quickly.
This is what I get with PDO:-
PDOException: SQLSTATE[42S02]: Base table or view not found: etc…

1 Like
4

Preparing the sql query involves sending the sql statement to the database server, where the sql syntax is parsed, the database, table, columns, and indexes are found, and the execution/optimization is planned.

You always need error handing for statements that can fail. For database statements, the easiest way of adding error handling for all the statements that can fail - connection, query, prepare, and execute, without adding logic at each statement, is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information, via an uncaught exception error (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) You would then remove any existing database statement error handling logic in your code, simplifying it. To use exceptions for the mysqli extension, add the following line before the point where you make the connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

In your last thread on this forum, you were using the much simpler and more consistent PDO extension. Why have you now taken a step backwards and are using the mysqli extension? About half of the database related statements will go away with PDO. You would just use a prepare, execute, and fetch statement. Example PDO based code to perform the login check -

			$sql = "SELECT id, password from users WHERE username = ?";

			$stmt = $pdo->prepare($sql);
			$stmt->execute([
				$post['username']
				]);
			if(!$row = $stmt->fetch())
			{
				// username was not found
				$errors['login'] = "Invalid Username/Password.";
			} else {
				// username found, verify the password hash
				if(!password_verify($post['password'],$row['password']))
				{
					// password doesn't match
					$errors['login'] = "Invalid Username/Password.";
				} else {
					// password matches
					$_SESSION['user_id'] = $row['id'];
				}
			}

Also, if ( !isset($_POST['username'], $_POST['password']) ) { only tests if the form has been submitted, because these fields will always be set when submitted. It doesn’t test if the variables actually contain anything. You need to trim, then validate each input before using them in your code.

2 Likes
5

Your comment is wrong. It doesn’t check if data exists. It checks if the variable exists and is not null. On a properly coded form, the inputs, save for check boxes will ALWAYS be isset therefore the check is pointless. Blank fields with no data would pass this test. You need to trim the POST array and then check for empty.

Also, do not output internal system errors to the user. That info is only good to hackers.

Edit: LOL @mabismad beat me two it while I was typing.

1 Like
6

and …

Sincere thanks guys. My intention is still to develop a secure login in PDO, however I am finding it a huge task since I am unfamiliar with PDO. I found what appears to be a pretty decent mysqli registration and login script(s) so am concentrating on getting it going and then improving it, so conversion to PDO will follow and internal system errors will not be output in final version.

I really appreciate all your advice guys.

Thanks to mabismad and also to benanamen, even though mabisbad beat ya to it :grinning:

I did not realise this, thanks again, invaluable advice.

@SamA74 I’ll take your excellent advice as well :grinning:

1 Like
7

Here is a good tutorial on PDO.

2 Likes
8

Thanks my friend !

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.