PHP PDO Password Update goes to a coded error screen?

In my current application, I have a table called users which shows a list of users that has access to my app. However, there seems to be a lingering issue whenever I update a user’s password.

My table structure looks like this:

And here is my code. First is the edit user form:

<?php

include('nav/head.php');

if($role_id != '1') {
	header('Location: error.php');
	exit();
}

// Define user data by User ID
if(isset($_GET['edit_id'])) {
	 $id = $_GET['edit_id'];
	 $stmt = $pdo->prepare("SELECT * FROM users WHERE id=:id");
	 $stmt->execute(array(":id" => $id));
	 $rowUser = $stmt->fetch(PDO::FETCH_ASSOC);
} else {
	 $id = null;
	 $rowUser = null;

}

?>

<!DOCTYPE html>
<html lang="en">

<head>

  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <link rel="stylesheet" type="text/css" href="css/toggle.css">

  <title>CascoTax | <?php print($rowUser['first_name'] . " " . $rowUser['last_name']); ?></title>

  <?php include('nav/header.php'); ?>

	<h1 class="h3 mb-2 text-gray-800"> Edit <?php print($rowUser['first_name'] . " " . $rowUser['last_name']); ?></h1>
	<br>
	<form action="api/users/edit.php" method="post">
			<input type="hidden" class="form-control" id="id" name="id" placeholder="" value="<?php print($rowUser['id']); ?>" maxlength="255" autocomplete="off" readonly/>
		<div class="form-group">
			<label for="role_id">User Status</label>
			<!-- <input type="text" class="form-control" id="role_id" name="role_id" placeholder="" value="<?php print($rowUser['role_id']); ?>" maxlength="255" autocomplete="off" /> -->
			<?php

			if($_SESSION['id'] == $rowUser['id']) {
				echo '<select disabled class="form-control" id="status" name="status">
				<option selected value="1">Active</option>
				<option value="0">Inactive</option>
			</select>';
				echo '<p style="color: #a40000;">The user status cannot be changed</p>';
			} else {
			if($rowUser['status'] === '1') {
				echo '<select class="form-control" id="status" name="status">
				<option selected value="1">Active</option>
				<option value="0">Inactive</option>
			</select>';
			} elseif ($rowUser['status'] === '0') {
				echo '<select class="form-control" id="status" name="status">
				<option value="1">Active</option>
				<option selected value="0">Inactive</option>
			</select>';
			}
			}

			?>
		</div>
		<div class="form-group">
			<label for="role_id">User Role</label>
			<!-- <input type="text" class="form-control" id="role_id" name="role_id" placeholder="" value="<?php print($rowUser['role_id']); ?>" maxlength="255" autocomplete="off" /> -->
			<?php

			if($_SESSION['id'] == $rowUser['id']) {
				echo '<select disabled class="form-control" id="role_id" name="role_id">
				<option selected value="1">Administrator</option>
				<option value="2">Operator</option>
			</select>';
			echo '<p style="color: #a40000;">The user role cannot be changed</p>';
			} else {
			if($rowUser['role_id'] === '1') {
				echo '<select class="form-control" id="role_id" name="role_id">
				<option selected value="1">Administrator</option>
				<option value="2">Operator</option>
			</select>';
			} elseif ($rowUser['role_id'] === '2') {
				echo '<select class="form-control" id="role_id" name="role_id">
				<option value="1">Administrator</option>
				<option selected value="2">Operator</option>
			</select>';
			}
			}

			?>
		</div>
		<div class="form-group">
			<label for="first_name">First Name</label>
			<input type="text" class="form-control" id="first_name" name="first_name" placeholder="" value="<?php print($rowUser['first_name']); ?>" maxlength="255" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="last_name">Last Name</label>
			<input type="text" class="form-control" id="last_name" name="last_name" placeholder="" value="<?php print($rowUser['last_name']); ?>" maxlength="14" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="email">Email</label>
			<input type="text" class="form-control" id="email" name="email" placeholder="" value="<?php print($rowUser['email']); ?>" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="username">Username</label>
			<input type="text" class="form-control" id="username" name="username" placeholder="" value="<?php print($rowUser['username']); ?>" autocomplete="off" />
		</div>
		<hr style="background-color: #a40000;">
		<div class="form-group">
			<label for="password">New Password</label>
			<input type="password" class="form-control" id="password" name="password" placeholder="" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="confirm_pwd">Confirm Password</label>
			<input type="password" class="form-control" id="confirm_pwd" name="confirm_pwd" placeholder="" autocomplete="off" />
		</div>
			<input type="submit" name="btn_save" class="btn btn-success" value="Save">
			<input type="submit" name="btn_cancel" class="btn btn-danger" value="Cancel">
	</form>
	&nbsp;
	&nbsp;

        </div>
        <!-- /.container-fluid -->

      </div>
      <!-- End of Main Content -->

	<?php include('nav/footer.php'); ?>

</html>

and here is the API that processes the request:

<?php

include ('../dbconnect.php');

// Update
if(!empty($_POST['password'])) {
	$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, password = :password, status = :status WHERE id = :id");
	$stmt->bindParam(':role_id', $role_id);
	$stmt->bindParam(':first_name', $first_name);
	$stmt->bindParam(':last_name', $last_name);
	$stmt->bindParam(':email', $email);
	$stmt->bindParam(':username', $username);
	$stmt->bindParam(':password', $password);
	$stmt->bindParam(':status', $status);
	$stmt->bindParam(':id', $id);



	// Update User Info
	if(isset($_POST['btn_save'])) {
		if ($_POST['password'] != $_POST['confirm_pwd']) {
    		die ('The two password provided do not match.');
		}

  		$role_id = $_POST["role_id"];
  		$first_name = $_POST["first_name"];
  		$last_name = $_POST["last_name"];
  		$email = $_POST["email"];
  		$username = $_POST["username"];
  		$password = password_hash($_POST['password'], PASSWORD_DEFAULT);
  		$status = $_POST["status"];
  		$id = $_POST["id"];
  		$stmt->execute();
  		header('Location: ../../users.php');
	}

	// Return to Users Page
	if(isset($_POST['btn_cancel'])) {
  	header('Location: ../../users.php');
	}
} else {
	$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, status = :status WHERE id = :id");
	$stmt->bindParam(':role_id', $role_id);
	$stmt->bindParam(':first_name', $first_name);
	$stmt->bindParam(':last_name', $last_name);
	$stmt->bindParam(':email', $email);
	$stmt->bindParam(':username', $username);
	$stmt->bindParam(':status', $status);
	$stmt->bindParam(':id', $id);



	// Update User Info
	if(isset($_POST['btn_save'])) {
  		$role_id = $_POST["role_id"];
  		$first_name = $_POST["first_name"];
  		$last_name = $_POST["last_name"];
  		$email = $_POST["email"];
  		$username = $_POST["username"];
  		$status = $_POST["status"];
  		$id = $_POST["id"];
  		$stmt->execute();
  		header('Location: ../../users.php');
	}

	// Return to Users Page
	if(isset($_POST['btn_cancel'])) {
  		header('Location: ../../users.php');
	}
}

?>

Originally, I had an error screen which would appear if a specific user without the proper user role could not access the page. I can update another user’s password and the password changes without any problems. However, when I go to change the password of the logged in user, the role_id in the table goes from 1 to NULL, the status value goes from 1 to 0 and the error page in my script appears out of format.

This prevents the user from being able to log in again unless the values are manually changed in the database. Why does this happen?

“:” - prefix for placeholder in SQL-string. In bindParam() or execute() methods it couldn’t be.

So, if I understand correctly, these:

	$stmt->bindParam(':role_id', $role_id);
	$stmt->bindParam(':first_name', $first_name);
	$stmt->bindParam(':last_name', $last_name);
	$stmt->bindParam(':email', $email);
	$stmt->bindParam(':username', $username);
	$stmt->bindParam(':status', $status);
	$stmt->bindParam(':id', $id);

need to be this?

	$stmt->bindParam('role_id', $role_id);
	$stmt->bindParam('first_name', $first_name);
	$stmt->bindParam('last_name', $last_name);
	$stmt->bindParam('email', $email);
	$stmt->bindParam('username', $username);
	$stmt->bindParam('status', $status);
	$stmt->bindParam('id', $id);

Yes.

But, actually, I have read PDO manual again… https://www.php.net/manual/de/pdostatement.execute.php

Format ‘:name’ also possible.

That didn’t do anything…

<?php

include ('../dbconnect.php');

// Update
if(!empty($_POST['password'])) {
	$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, password = :password, status = :status WHERE id = :id");
	$stmt->bindParam('role_id', $role_id);
	$stmt->bindParam('first_name', $first_name);
	$stmt->bindParam('last_name', $last_name);
	$stmt->bindParam('email', $email);
	$stmt->bindParam('username', $username);
	$stmt->bindParam('password', $password);
	$stmt->bindParam('status', $status);
	$stmt->bindParam('id', $id);



	// Update User Info
	if(isset($_POST['btn_save'])) {
		if ($_POST['password'] != $_POST['confirm_pwd']) {
    		die ('The two password provided do not match.');
		}

  		$role_id = $_POST["role_id"];
  		$first_name = $_POST["first_name"];
  		$last_name = $_POST["last_name"];
  		$email = $_POST["email"];
  		$username = $_POST["username"];
  		$password = password_hash($_POST['password'], PASSWORD_DEFAULT);
  		$status = $_POST["status"];
  		$id = $_POST["id"];
  		$stmt->execute();
  		header('Location: ../../users.php');
	}

	// Return to Users Page
	if(isset($_POST['btn_cancel'])) {
  	header('Location: ../../users.php');
	}
} else {
	$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, status = :status WHERE id = :id");
	$stmt->bindParam('role_id', $role_id);
	$stmt->bindParam('first_name', $first_name);
	$stmt->bindParam('last_name', $last_name);
	$stmt->bindParam('email', $email);
	$stmt->bindParam('username', $username);
	$stmt->bindParam('status', $status);
	$stmt->bindParam('id', $id);



	// Update User Info
	if(isset($_POST['btn_save'])) {
  		$role_id = $_POST["role_id"];
  		$first_name = $_POST["first_name"];
  		$last_name = $_POST["last_name"];
  		$email = $_POST["email"];
  		$username = $_POST["username"];
  		$status = $_POST["status"];
  		$id = $_POST["id"];
  		$stmt->execute();
  		header('Location: ../../users.php');
	}

	// Return to Users Page
	if(isset($_POST['btn_cancel'])) {
  		header('Location: ../../users.php');
	}
}

?>

disabled form fields are not submitted, so your role_id will not be present. Send it as a hidden value, and instead of displaying the control, just put the word “Administrator” there instead.

Also please tell me your API has some form of authentication on it, otherwise your database is fully open to attack and you’ve just provided the blueprint of how to attack it.

Why do that? I want the ability to change the user role should I need to do it for different users. If a user tries to do it on themselves (if they are logged in), they can’t:

image

Yes, my database has authentication. It is also hosted on a computer, not a server, so this is not live right now.

Fine… so send it as a hidden input and remove the name from your select box when the user is modifying themselves…

Well, wait… how can I keep the role_id value if it isn’t changed? This is what happens when the password fields are not filled in.

If the user is modifying themselves, the targetted user must be role_id 1. Because that’s what line 3 of your page says.

Your form does not allow a user to remove themselves from being an Administrator.

if($role_id != '1') {
	header('Location: error.php');
	exit();
}

I used this to prevent users who are not administrators having access to the page. My goal is say I’m logged in as an admin, then I should be able to edit any user including myself.

If I am editing myself, I cannot change the status or the user role of myself.

Role ID 1 = Administrator
Role ID 2 = Operator (or just Standard User)

You have successfully restated my last post.
Was there something else?

I don’t think I’m fully understanding what you are saying. How does that line interfere with the user editing process I am describing?

It doesn’t. It defines it.

Right. Let me try saying it again, but with different words.

Your problem is because you have disabled the form control for role_id when the user is modifying themselves. A disabled form control DOES NOT SEND ITS DATA. It shows up on the page, lets the user see it, but thats all it does.

Your code expects the data for role_id to be sent. So…when the user is modifying themselves, you need to send the data for role_id by another means…

Okay, so ideally, it should be something like this:

if($_SESSION['id'] == $rowUser['id']) {
	echo '<input type="hidden" class="form-control" id="role_id" name="role_id" 
           placeholder="" value="<?php print($rowUser['role_id']); ?>" maxlength="255" 
           autocomplete="off" readonly/>
			</select>';
}

Because external data can be altered and set to anything and cannot be trusted, you cannot safely pass the role_id, status, and id through the form when the user is not an administrator. You must leave these out of the form, out of the SET part of the UPDATE query and you must use $_SESSION['id'] as the value for the :id place-holder in the WHERE clause, i.e. a non-administrator can only edit those fields that they originally provided the values for when they registered.

When the user is an administrator, who is trusted and has the ability to edit all the fields for any user, you would pass the role_id, status, and id through the form, include the role_id and status in the SET part of the query, and use the id from the form in the WHERE clause.

All of your form processing code will be simpler if your dynamically build one UPDATE query with only those parts that it should have, using conditional logic only for the parts of it that are conditional. You would also build a $params array with the corresponding values for the place-holders, then just supply this array to the ->execute($params) method call.

I recommend that you use a defined constant in your code instead of literal numbers and numbers should not be quoted anyway. Use something like -

define(ROLE_ADMIN, 1);

The logic tests then will look like -

if($role_id != ROLE_ADMIN) {

Ok, I’m lost…

First it was an API issue and how the code is processing the form, now it’s a query problem?

If I can make it more simple, how can I modify this then?

UPDATE users 
SET role_id = :role_id, 
first_name = :first_name, 
last_name = :last_name, 
email = :email, 
username = :username, 
password = :password, /* This is conditional */
status = :status 
WHERE id = :id

I tried that, and it does the same thing:

if($_SESSION['id'] == $rowUser['id']) {
echo '<input type="hidden" class="form-control" id="status" name="status" 
placeholder="" value="<?php print($rowUser['.'status'.']); ?>" 
maxlength="255" autocomplete="off" readonly/>';
echo '<p style="color: #a40000;">The user status cannot be changed</p>';
}

image

Back to the basics. Your processing code starts with:

// Update
if(!empty($_POST['password'])) {
    // Execute update query

It could be the case where you are just not showing all the code but where are you getting values like role_id from? Later on you extract them from _POST but for the first update they don’t seem to even exist unless you have register globals enabled.

By default, when a new user is created, that user is automatically given the operator role in the database. The database sees this as a 2. If I want to change it in the user edit form, I have to simply edit that user and change it to Administrator (or 1 in the database).

image

The first update query will only work if the password field is not blank. If it is blank, then this query is executed:

	$stmt = $pdo->prepare("UPDATE users 
SET role_id = :role_id, 
first_name = :first_name, 
last_name = :last_name, 
email = :email, 
username = :username, 
status = :status 
WHERE id = :id");
	$stmt->bindParam(':role_id', $role_id);
	$stmt->bindParam(':first_name', $first_name);
	$stmt->bindParam(':last_name', $last_name);
	$stmt->bindParam(':email', $email);
	$stmt->bindParam(':username', $username);
	$stmt->bindParam(':status', $status);
	$stmt->bindParam(':id', $id);