json_decode returns null instead of NULL on post request, prevents me to set NULL in the DB

Question

I am having the issue that I need to be able to put a NULL value in a mysql database. So from the frontend I send a basic javascript object:

{
 "Info": {
  "Name": "Michelangelo",
  "Date" : null
  }
}

In my PHP file I do get the request correctly and decode it:

if(isset($postdata) && !empty($postdata)) {
  // Extract the data.
  $request = json_decode($postdata);
}

It works for strings, booleans, ints correctly but not for null values. It will output in PHP after decoding like null and not NULL. As far as I know the only correct value in PHP is NULL:https://www.php.net/manual/en/language.types.null.php

I put the values like this in DB. Date column is of type date and it accepts NULL values. However with null it will be converted to 0000-00-00;

  $sql = "UPDATE `users` SET 
  `Username`='$request->Info->Name',
  `Date`='$request->Info->Date' //DB accepts NULL but not null (tested)
  WHERE `id` = '{$id}' LIMIT 1";

So why does it not convert correctly? I know I can loop over the object and replace all the null with NULL values, but since the object has nesting it will be a major headache. How can I solve this and why does this happen? I would prefer to do this with PHP and not in the sql query.

Answer 1

The problem is because you are putting the "null" between single quotes!

`Date`='$request->Info->Date'

Solution: Use bind parameters in your prepared statement. https://www.php.net/manual/en/pdo.prepare.php

$pdo = new PDO($dsn, $user, $pass, $options);
$sth = $pdo->prepare("UPDATE `users` SET
  `Username`=:username, `Date`=:date
  WHERE `id` = :id LIMIT 1");
$sth->bindParam(':username', $request->Info->Name, PDO::PARAM_STR);
$sth->bindParam(':date', $request->Info->Date);
$sth->bindParam(':id', $id);
$sth->execute();

You should build some validation class where it will return the proper (sanitized) values from the Request that you are going to use in the query.

Consider creating some class/logic like

final class InfoRequest
{
    /** @var array */
    private $info;

    public function __construct($request)
    {
        $this->info = $request->Info;
    }

    public function date(): ?string
    {
        $date = $this->info['Date'];
        if (!$this->isValidDate($date)) {
            throw NotValidDateException();
        }
        if (!$date) {
            return null;
        }
        $dateTime = new \DateTime($this->info['Date']);

        return $dateTime->format('Y-m-d');
    }

    private function isValidDate($date): bool
    {
        return true; // TODO: Not implemented yet
    }

    public function name(): string
    {
        $name = $this->info['Name'];
        if (!$this->isValidName($name)) {
            throw NotValidNameException();
        }

        return $name;
    }

    private function isValidName($name): bool
    {
        return true; // TODO: Not implemented yet
    }
}

Usage:

$info = new InfoRequest($request);
$sth->bindParam(':username', $info->name(), PDO::PARAM_STR);

Apart from that, you should never pass the direct input from the request into a raw query to the DB.

Answer 2

It's generally a good idea not to take the values directly from user input (no matter how much you think you should trust the source). See also obligatory xkcd.

Since you're now mapping and cleansing the data as you build your query (probably using prepared statements), it should be trivial to map the value null to the string "NULL" (or just let the prepared statement library handle it).

Answer 3

The problem is not related to the variations of NULL or null. In PHP they are the same thing.

You are probably running in MySQL NO_ZERO_DATE mode, which fills null dates with zeroes. The command below changes the way that null dates are handled by MySQL.

SET sql_mode = 'NO_ZERO_DATE';

More info here.