PDO PHP UPDATE don´t update Password if not included

Question

I have an updating form to update the users information. Here, I have the password input. And I want to, if left blank, not update the password in the database but to leave the one already set up.

For this I have:

$user_password = inputCleaner($_POST['user_password']);
$user_password_repeat = inputCleaner($_POST['user_password_repeat']);

 // IF filled, check if both match
  if (!empty($user_password) && $user_password != $user_password_repeat) {
        $errors .= "Passwords are not the same." . '<br>';
    } elseif (!empty($user_password) && $user_password == $user_password_repeat) {
        $user_password = hash('sha512', $user_password);  
    } 
 // IF NOT FILLED, leave NULL
     elseif (empty($user_password)) {
        $user_password = '';
    }

If all is good, we run the script:

 if(!$errors) {

        $statement = $connection -> prepare("
        UPDATE users SET
        
        user_nickname = :user_nickname,
        user_password = COALESCE(NULLIF(:user_password, ''),user_password)
        user_pass
        user_name = :user_name,
        user_last_name = :user_last_name,
        user_email = :user_email,
        user_picture = :user_picture,
        role = :role
        
        WHERE
        
        user_id = :user_id

        ");

        $statement -> execute(array(
            ':user_nickname' => $user_nickname,
            ':user_password' => $user_password,
            ':user_name' => $user_name,
            ':user_last_name' => $user_last_name,
            ':user_email' => $user_email,
            ':user_picture' => $user_picture,
            ':role' => $role,
            ':user_id' => $user_id
        ));

Note my inputCleaner() function is a simple:

function inputCleaner($input) {
    $input = trim($input);
    $input = stripslashes($input);
    $input = htmlspecialchars($input);
    return $input;
}

With this, the password is not updated at all, it won´t change it.

Answer 1

Instead of converting '' to NULL and then using COALESCE(), you can simply compare :user_password to ''.

You also had some syntax errors: a missing comma after assigning to user_password and an extra line with user_pass after that.

        $statement = $connection -> prepare("
        UPDATE users SET
        
        user_nickname = :user_nickname,
        user_password = IF(:user_password = '',user_password, :user_password),
        user_name = :user_name,
        user_last_name = :user_last_name,
        user_email = :user_email,
        user_picture = :user_picture,
        role = :role
        
        WHERE
        
        user_id = :user_id

        ");```