Proper use of NULL in mysql query from php

Question

$ETA_time =  strtotime($arrivals[$i]["operationalTimes"]["estimatedGateArrival"]["dateLocal"]);
if (!$ETA_time) {
    $ETA = 'NULL';
} else {
    $ETA = strftime("%Y-%m-%d %H:%M:%S", $ETA_time);
}

$STA_time   = strtotime($arrivals[$i]["operationalTimes"]["scheduledGateArrival"]["dateLocal"]);
if (!$STA_time) {
    $STA = 'NULL';
} else {
    $STA = strftime("%Y-%m-%d %H:%M:%S", $STA_time);
}

$ATA_time = strtotime($arrivals[$i]["operationalTimes"]["actualGateArrival"]["dateLocal"]);
if (!$ATA_time) {
    $ATA = 'NULL';
} else {
    $ATA = strftime("%Y-%m-%d %H:%M:%S", $ATA_time);
}

$query="INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`)
            VALUES('".$ETA."','".$STA."','".$ATA."');";
$result=run_query($query);

The related MySQL DB table:

CREATE TABLE `Schedule` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `STA` datetime DEFAULT NULL,
  `ETA` datetime DEFAULT NULL,
  `ATA` datetime DEFAULT NULL,
);

Query var_dump:

INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`) VALUES('2013-08-28 12:30:00','NULL','NULL');

The error message is:

Incorrect datetime value: 'NULL' for column 'STA' at row 1

`NULL` is a keyword, not a string and should not be single quoted. `VALUES('2013-08-28 12:30:00',NULL,NULL);` — Michael Berkowski, Aug 28 '13 at 10:51
Because passing NULL as a string is not NULL, pass empty string or the keyword itself — Royal Bg, Aug 28 '13 at 10:51
See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks for quoting advice — Michael Berkowski, Aug 28 '13 at 10:51
Each SQL error has a number. What is the error number you've got (if not two numbers)? And which part of the error message (which you have posted), is not clear to you? — hakre, Aug 28 '13 at 10:53
`NULL != 'NULL'` and even `NULL` will not work if your field is not nullable. — kapa, Aug 28 '13 at 10:55
possible duplicate of [Passing NULL from PHP to MySQL for auto increment](http://stackoverflow.com/questions/10892577/passing-null-from-php-to-mysql-for-auto-increment) — hakre, Aug 28 '13 at 11:01

score 2 · Accepted Answer · edited Aug 28 '13 at 11:24

NULL is a specific value, not a string literal. It should be passed to query directly, i.e.

INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`)
    VALUES ('2013-08-28 12:30:00', NULL, NULL);

that means your PHP code should handle that and not enclose NULL-s:

$timeFormatAndNull = function ($format) {
    return function($time) use ($format) {
        $time = strtotime($time);
        return $time ? strftime($format, $time) : 'NULL';
    };
};


$operationalLocalDate = function($arrivals, $callback) {
    return function($i, $date) use ($arrivals, $callback) {
        return $callback(
           $arrivals[$i]["operationalTimes"][$date]["dateLocal"])
        );
    };
};

and

$formatTime = $operationalLocalDate(
    $arrivals
    $timeFormatAndNull("'%Y-%m-%d %H:%M:%S'")
);

$query = sprintf(
    "INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`) VALUES (%s, %s, %s);"
    , $formatTime($i, "estimatedGateArrival")
    , $formatTime($i, "scheduledGateArrival")
    , $formatTime($i, "actualGateArrival")
);

@harke - thanks for great edit. To be honest - I'm ashamed by yours edit. — Alma Do, Aug 28 '13 at 11:29
Actually as the question states, NULL is also the default value, so the more correct way - in my eyes - is to not pass the NULL values with the INSERT at all as they are not needed (I can not imagine this was the reason of the downvote though). But this would need a different way to create the SQL query. — hakre, Aug 28 '13 at 11:31

score 2 · Answer 2 · edited May 23 '17 at 11:57

The reason for having that error message is because you are passing a string of NULL (which is impossible to convert into datetime format) in the datetime datatype.

When you want the value of column STA and ATA to be NULL, pass NULL value without single quotes. The single quote around the value makes it a literal string.

INSERT INTO `Schedule` (ETA, STA, ATA) 
VALUES ('2013-08-28 12:30:00', NULL, NULL)

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Proper use of NULL in mysql query from php

2 Answers2