Updating database using PHP without empty values from HTML form

Question

I want to update my SQL db without empty values from my form. That's what I've got:

if(isset($_POST['account_details_submit'])) { 
    $user_id = array_values($_SESSION['user_info'])[9];
    $edited = date('d.m.Y h:i a');
    if(isset($_POST['account_details_first_name']) and !empty($_POST['account_details_first_name'])) { $add .= " and `first_name` = '$_POST[account_details_first_name]'"; }
    if(isset($_POST['account_details_last_name']) and !empty($_POST['account_details_last_name'])) { $add .= " and `last_name` = '$_POST[account_details_last_name]'"; }
    if(isset($_POST['account_details_phone_number']) and !empty($_POST['account_details_phone_number'])) { $add .= " and `phone_number` = '$_POST[account_details_phone_number]'"; }
    if(isset($_POST['account_details_address_1']) and !empty($_POST['account_details_address_1'])) { $add .= " and `address_1` = '$_POST[account_details_address_1]'"; }
    if(isset($_POST['account_details_address_2']) and !empty($_POST['account_details_address_2'])) { $add .= " and `address_2` = '$_POST[account_details_address_2]'"; }
    if(isset($_POST['account_details_city']) and !empty($_POST['account_details_city'])) { $add .= " and `city` = '$_POST[account_details_city]'"; }
    if(isset($_POST['account_details_post_code']) and !empty($_POST['account_details_post_code'])) { $add .= " and `post_code` = '$_POST[account_details_post_code]'"; }
    if(isset($_POST['account_details_country']) and !empty($_POST['account_details_country'])) { $add .= " and `country` = '$_POST[account_details_country]'"; }

    $update = "UPDATE `users` SET `edited` = '$edited'".$add." WHERE `id` = '$user_id'";

    if ($conn->query($update) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
}

The message is "Record updated successfully" but only one row that is updating is row called edited and it's always updated to 0

I'm open for other ways how to do it.

P.S I have tried to do it using array value but the result isn't what I wanted

Just a note, you don't need both `isset` and `!empty`. Empty also checks to see if it's set, so only `!empty` is needed. — aynber, Dec 13 '19 at 16:39
the SET assignment list should be separated by commas, not "AND" — aynber, Dec 13 '19 at 16:46
@aynber I assume that's why the updated value is zero. It's interpreting all those ands as a boolean expression — Don't Panic, Dec 13 '19 at 16:47
plenty of possibilities for sql injection here - consider converting to use `prepared statements` — Professor Abronsius, Dec 13 '19 at 16:51

Professor Abronsius · Accepted Answer · 2019-12-14T08:10:16.587

What you could do, though this is not tested, would be to use an array of form field names and database column names to help build your sql dynamically in a much safer manner using a prepared statement

if( !empty( $_SESSION['user_info'] ) && $_SERVER['REQUEST_METHOD']=='POST' ) {

    $fields=array(
        'account_details_first_name'    =>  'first_name',
        'account_details_last_name'     =>  'last_name',
        'account_details_phone_number'  =>  'phone_number',
        'account_details_address_1'     =>  'address_1',
        'account_details_address_2'     =>  'address_2',
        'account_details_city'          =>  'city',
        'account_details_post_code'     =>  'post_code',
        'account_details_country'       =>  'country'
    );

    /* default variables... */
    $user_id = array_values( $_SESSION['user_info'] )[9];
    $edited = date('d.m.Y h:i a');

    /* placeholders used to generate sql statement */
    $params=array();
    $values=array();
    $types=array();

    /* 
        iterate through all submitted POST fields - 
        if they are not empty add them to the placeholders 
    */
    foreach( $_POST as $field => $value ){
        if( !empty( $value ) ){
            $params[]=sprintf( '`%s`=?', $fields[ $field ] );
            $values[]=$value;
            $types[]='s';
        }
    }
    /*
        add semi-static variables to placeholders too
    */
    $values[]=$user_id;
    $types[]='s';


    /* create a sql statement and the use that to create the `prepared statement` */
    $sql = sprintf( 'update `users` set %s where `id`=?', implode( ',', $params ) );
    #echo $sql;
    $stmt=$db->prepare( $sql );

    /* bind the types and assign variables with a SPLAT */
    $stmt->bind_param( implode('',$types), ...$values );
    $result=$stmt->execute();

    echo $result ? 'Record updated successfully' : 'Error updating record';
}

By echoing out the SQL prior to any calls to $db I was able to generate the following SQL which looks fine for use in the prepared statement:

update `users` set `first_name`=?,`last_name`=?,`phone_number`=?,`address_1`=?,`address_2`=?,`city`=?,`post_code`=?,`country`=? where `id`=?

Without the schema and data I can test no further but looks OK. Time now for a glass of wine...

that's good to hear - wish I could have tested before posting! The only part I didn't incorporate was the `$edited` variable - but that would seem like a nonsense if the `user` table had a `timestamp` field set to update on edit etc. — Professor Abronsius, Dec 13 '19 at 20:47

score -5 · Answer 2 · answered Dec 13 '19 at 17:03

You need separate fields by ',' and not 'and', I recommend you use xss protected with the function htmlspecialchars, see How to prevent XSS with HTML/PHP?. Try this:

if(isset($_POST['account_details_submit'])) {
    $valuesToUpdate = [];
    $fields = [
        'first_name'   => 'account_details_first_name',
        'last_name'    => 'account_details_last_name',
        'phone_number' => 'account_details_phone_number',
        'address_1'    => 'account_details_address_1',
        'address_2'    => 'account_details_address_2',
        'city'         => 'account_details_city',
        'post_code'    => 'account_details_post_code',
        'country'      => 'account_details_country'
    ];

    foreach ($fields as $key => $field) {
        $protectedFromXss = trim(htmlspecialchars($_POST[$field]));        
        if ($protectedFromXss) {
            $valuesToUpdate[] = "$key = '$protectedFromXss'";
        }
    }

    if (count($valuesToUpdate)) {
        $values = ', ' . implode(', ', $valuesToUpdate);
    }

    $edited = date('d.m.Y h:i a');
    $user_id = array_values($_SESSION['user_info'])[9];
    $update = "UPDATE `users` SET `edited` = '{$edited}' {$values} WHERE `id` = '$user_id'";

    if ($conn->query($update) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
}

XSS protection should be done on output, not when sent to the database. Database queries should be protected with prepared statements and parameter binding. — aynber, Dec 13 '19 at 17:18
@Matheus you were right. I replaced "and" with "," and everything works. I didn't think about the simplest solution here. Thanks — Freshoy, Dec 13 '19 at 17:39

Updating database using PHP without empty values from HTML form

2 Answers2

Linked