PDO statement updates 0 values instead of null or empty

Question

I am making a PDO update statement but it is entering 0's on empty values.

I want a empty value to enter null
I want a zero value to enter zero

Here is my code:

include 'dbconfig.php';

$id = $_POST['id'];
$data = $_POST['data'];

try {
    $options = [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES => false,
    ];
    $pdo = new PDO("mysql:charset=utf8mb4;host=$servername;dbname=$dbname", $username, $password);
    
    $setStr = "";

    $values = array();

    foreach($data as $field => $value) {
        $setStr .= "$field=:$field,";
        $values[$field] = $value;
    }

    $setStr = rtrim($setStr, ",");


    $values['id'] = $id;
    $stmt = $pdo->prepare("UPDATE products SET $setStr WHERE id = :id");
    $stmt->execute($values);


    $count = $stmt->rowCount();

    if ($count > 0) {
        echo json_encode(array('response'=>'success','message'=>'Product ID # '.$id." successfully updated."));
    }else{
        echo json_encode(array('response'=>'danger','message'=>'Product not successfully updated'));
    }

}catch(PDOException $e){
    
    echo json_encode(array('response'=>'danger','message'=>$e->getMessage()));
}
    $conn = null;

What is the best way to do this? Thank you

Are you sure the columns in your DB are set to allow NULL values? — Dharman, Aug 17 '20 at 21:37
**Warning:** Your code is open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) — Dharman, Aug 17 '20 at 21:38
Welcome to the community Jack. Because `$data` is being posted by the user, they keys of the array *cannot be trusted* and so your `$setStr .= "$field=:$field,";` line is at risk of [SQL injection](https://owasp.org/www-community/attacks/SQL_Injection) — Rudu, Aug 17 '20 at 21:39
Don't expose PDO error messages to the user. Please remove that try-catch — Dharman, Aug 17 '20 at 21:40

score 0 · Accepted Answer · answered Aug 17 '20 at 21:34

0

Check for an empty string in your loop, and convert it to null.

    foreach($data as $field => $value) {
        $setStr .= "$field=:$field,";
        $values[$field] = $value === "" ? null : $value;
    }

answered Aug 17 '20 at 21:34

Barmar

596,455
48
393
495

That's definitely one way to do it, there's not any setting built into PDO to do this? – jack Aug 17 '20 at 21:36
Not that I know of. It's not normal to treat empty strings as `NULL`. – Barmar Aug 17 '20 at 21:38
`NULL` is usually used for missing data, not data that's explicitly set to an empty string. – Barmar Aug 17 '20 at 21:38
So you want 0 to turn into an empty string, and an empty string to turn into `NULL`? – Barmar Aug 17 '20 at 21:46
`if ($value === "0") { $value = ""; } elseif ($value === "") { $value = null; }` – Barmar Aug 17 '20 at 21:47
No I am just wondering how to turn empty to empty and zero to zero and null to null then – jack Aug 17 '20 at 21:48
Right now it makes empty 0 – jack Aug 17 '20 at 21:48
How would I set empty as empty instead of 0? – jack Aug 17 '20 at 21:48
What is the datatype of the column? If it's a numeric type, you can't put an empty string in there. – Barmar Aug 17 '20 at 21:49
Gotcha, yeah, it's a decimal. – jack Aug 17 '20 at 21:50
If you want `0` to become `NULL`, you can do `$values[$field] = $value === "" || $value === "0" ? null : $value;` – Barmar Aug 17 '20 at 21:50

score 0 · Answer 2 · answered Aug 17 '20 at 22:01

PDOStatement::execute considers every parameter to be a string. In some databases the driver deals with this without issue, but if you want to specifically type values you need instead to use bindValue. With this typing, we can type null (or empty) values to the database-null type (instead of whatever the column default is) - see my bindEmptyAsNull function.

Also, since I'm using a trusted source for the column names, I can safely use your $setStr pattern for building the update params, but note you might not want to update a column that isn't included in the $dangerData associative array (as it is, if it isn't submitted, the column will be cleared).

<?php

//... your setup code, including $pdo

// List all the names of the columns in the products table here - you havent' provided enough info so I'm guessing
$keys=["name","cost","weight_grams"];
$dangerId = $_POST['id'];
$dangerData = $_POST['data'];

/**
 * Convert null values into PARAM_NULL types, not
 * @param $type Should be one of the [PDO::PARAM_*](https://www.php.net/manual/en/pdo.constants.php) constants
 */
function bindEmptyAsNull($stmt,$name,$value,$type=PDO::PARAM_STR) {
    //If you want empty string to be considered null too:
    //if ($value===null || $value==="") {
    if ($value===null) {
        //Note the column needs to support null types
        $stmt->Value($name,null,PDO::PARAM_NULL);
    } else {
        $stmt->bindValue($name,$value,$type);
    }
}

//Build the set string
$setStr='';
foreach($keys as $col) {
    $setStr.=$col.'=:'.$col.',';
}
$setStr=substr($set,0,-1);//Remove the trailing comma

//Create the statement
$stmt=$pdo->prepare("UPDATE products SET $setStr WHERE id=:id");

//Bind the danger values
// NOTE: The only way you could automate this (re-read the keys array) is to also have an
// associative array of key:type mapping too
$stmt->bindValue(":id",$dangerId,PDO::PARAM_INT);
bindEmptyAsNull($stmt,":name",$dangerData["name"]);
bindEmptyAsNull($stmt,":cost",$dangerData["cost"]);
bindEmptyAsNull($stmt,":weight_grams",$dangerData["weight_grams"],PDO::PARAM_INT);

$stmt->execute();
// .. Further processing

PDOStatement::execute considers every parameter to be a string. do you have any evidence to support this claim in regard of null values? — Your Common Sense, Aug 18 '20 at 05:04
when you bind a variable using PDO::PARAM_STR, a null value is sent to database as null. so again, do you have any reason for this bindEmptyAsNull of yours? — Your Common Sense, Aug 18 '20 at 06:19
The PDO documentation is ambiguous on the topic. While there was an attempt to move `PARAM_NULL` from PHP in 7.0, it did not happen - meanwhile there are several PDO *drivers* that still require `PARAM_NULL` (including SQLAnywhere and IBM/DB2). On a driver by driver basis NULL maybe acceptable as a string (certainly the language distinguishes between empty string and null - unlike Oracle) and inserted as a NULL in the database (vs coerced), but if you want this to work for any PDO driver you'd need to prove this works everywhere. Have you evidence proving otherwise? — Rudu, Aug 21 '20 at 21:11

PDO statement updates 0 values instead of null or empty

2 Answers2