PHP PDO UPDATE user details - skip if input is blank

Question

I have written some PHP PDO code to let my website users update their details.

The code is meant to skip any blank input fields and only update those that the user has entered details into.

It's working fine, apart from the password field. When I leave all the form blank and press 'Save', everything stays the same apart from the password that still changes.

Please see my code below.

I want the code to update the password if the user has entered a new one, otherwise I want to it keep the one that is already in the mysql table (The password is hashed).

Is anyone able to advise me what the correct code would be for this?

PHP

    <?php
require('../../../private_html/db_connection/connection.php');

session_start();
    $ID = $_SESSION['ID'];

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "UPDATE user_accounts SET first_name = COALESCE(NULLIF(:fname, ''),first_name), surname = COALESCE(NULLIF(:sname, ''),surname), display_name = COALESCE(NULLIF(:dname, ''),display_name), email = COALESCE(NULLIF(:email, ''),email), password = COALESCE(NULLIF(:password, ''),password) WHERE account_number='$ID'";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':fname', $_POST['fname']);
    $stmt->bindParam(':sname', $_POST['sname']);
    $stmt->bindParam(':dname', $_POST['dname']);
    $stmt->bindParam(':email', $_POST['email']);
    $stmt->bindParam(':password', password_hash($_POST['password'], PASSWORD_DEFAULT));
    $stmt->execute();

    $query = $conn->prepare("SELECT * FROM user_accounts WHERE account_number='$ID'");
    $query->execute();

        if(($row = $query->fetch())){
            $_SESSION['ID'] = $row['account_number'];
            $_SESSION['first_name'] = $row['first_name'];
            $_SESSION['surname'] = $row['surname'];
            $_SESSION['display_name'] = $row['display_name'];
            $_SESSION['email'] = $row['email'];
            header("Location: ../../myaccount/mydetails/mydetails.php");
        }
    }
catch(PDOException $e)
    {
        echo 'Connection failed: ' . $e->getMessage();
        exit();
    }
?>

I think somethings like this will help you https://stackoverflow.com/a/20866691/1804068 — Hoàng Vũ Tgtt, Apr 03 '20 at 16:15

Juan Bonnett · Accepted Answer · 2015-09-19T23:21:00.413

0

Easy. A blank space can also be hashed, that's why, so... we're gonna set an empty value so it can be detected as empty by the NULLIF function:

$stmt->bindParam(':dname', $_POST['dname']);
$stmt->bindParam(':email', $_POST['email']);

//If the password IS NOT '' or 0 or '0' or NULL
if(!empty($_POST['password'])) {
    $pass = password_hash($_POST['password'], PASSWORD_DEFAULT);
} else {
    $pass = '';
}

$stmt->bindParam(':password', $pass);

$stmt->execute();

Or do it like a PRO with the ternary!

//...

$pass = !empty($_POST['password']) ? password_hash($_POST['password'], PASSWORD_DEFAULT) : '';

$stmt->bindParam(':password', $pass);

$stmt->execute();

Important Note

Remember to trim with the trim() function values because empty spaces are considered 'something' and will pass the empty() or !== '' filters and the NULLIF(val, '')

edited Sep 19 '15 at 23:21

answered Sep 19 '15 at 23:02

Juan Bonnett

1,823
1
15
33

I feel silly now! Thanks for these answers, both work. Which one is the best one to use Juan? – Sep 19 '15 at 23:16
Both are the same, ternary operation are used to avoid 3 lines of - if else- – Juan Bonnett Sep 19 '15 at 23:18
1

Excellent, think I'll use the ternary method then if it's shorter and just as good. Thanks again for the help! – Sep 19 '15 at 23:22

PHP PDO UPDATE user details - skip if input is blank

1 Answers1