Sneppy Sneppy - 1 month ago 9
MySQL Question

"datetime-local" null input causes MySQL query to fail

On my website, hosted on a LAMP stack, I have a simple html POST form with a bunch of inputs, and two datetime-local inputs

<form>
...
<input id="PublicationDate" type="datetime-local" name="PublicationDate"/>
<input id="ExpirationDate" type="datetime-local" name="ExpirationDate"/>
...
</form>


These input are matched against two Datetime field inside a MySQL table:

...
PublicationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ExpirationDate DATETIME,


Using this php code:

// Prepare query
$Query = $this->DB->prepare("
INSERT INTO Article(Title, Subtitle, Author, PublicationDate, ExpirationDate, Body)
VALUES(?, ?, ?, IFNULL(?, NOW()), ?, ?)");
$Query->bindParam(1, $Data["Title"]);
$Query->bindParam(2, $Data["Subtitle"]);
$Query->bindParam(3, $Username);
$Query->bindParam(4, $Data["PublicationDate"]); // <-
$Query->bindParam(5, $Data["ExpirationDate"]); // <-
$Query->bindParam(6, $Data["Body"]);


At the beginning, with the same MySQL/Php setup but without the actual fields in the html form, everything worked fine: PublicationDate was set to
NOW()
and ExpirationDate to
NULL
.

After I added the fields, if I leave those fields empty (in order to have
NOW()
and
NULL
as values) the query fails.

I think this problem is caused by the datetime-local inputs, because I have no problem leaving Subtitle field empty in order to have the corresponding MySQL row set to
NULL
. Any idea how I can solve this problem?

EDIT:



The
$Data
array is the
$_POST
array passed as a parameter to a function

Answer Source

You are checking one of the values for NULL when you insert it and the other one not at all.

However, when a form is sent in, the values will not be NULL, they will be empty strings so you probably want to set them to NULL if they are empty:

$Query->bindParam(4, $Data["PublicationDate"] ?: null);
$Query->bindParam(5, $Data["ExpirationDate"] ?: null);

Note that this is very basic, you should add real validation to your fields to check if they match what you are expecting.