How to Insert a value or NULL?

Question

Possible Duplicate:
Insert NULL variable into database

I have a table (log) with Columns:

Seconds: bigint(20) UNSIGNED NULL

IPAddress: varchar(15) NULL

The problem is that whenever the variables are given the value NULL, they are inserted in the columns as an empty string for the IPAddress column and as 0 for the Seconds column, not as NULL.

//PHP 5.3.9//

if(isset($_POST['sec']))
        $seconds = mysql_real_escape_string($_POST['sec']);
else
    $seconds = NULL;

if(isset($_POST['ip']))
    $ipaddress = mysql_real_escape_string($_POST['ip']);
else
    $ipaddress = NULL;

$query = mysql_query("INSERT INTO log (Seconds, IPAddress) VALUES('".$seconds."', '".$ipaddress."')", $DbConnectionMyDb);

I've searched all over google, but all the answers assumes that I already know whether the variables $sec and $ip are going to be NULL or not. In my case it depends on the POST data.

Any help is deeply appreciated. Thanks.

Use placeholders. They will take care of `NULL` handling .. and take care of SQL injection *without* manually needing to call a certain ugly looking function. (The problem is `''`, a string, and not `NULL` is being used as a value in the insert.) — , Nov 13 '12 at 18:24
Because they are strings when you get them, and as such you treat them (mysql_real_escape_STRING) — Damien Pirsy, Nov 13 '12 at 18:25
Your PHP is defining the strings as NULL, which is different from mysql NULL. In your query you should check (IE an if statement) to see if the variable is NULL, if it is then it should look like this: VALUES(NULL, '".$ipaddress."') without quotes. — Ben Ashton, Nov 13 '12 at 18:26
possible duplicate of [Insert NULL variable into database](http://stackoverflow.com/questions/11398135/insert-null-variable-into-database) - gives solution for *obsolete* `mysql_` and example switching to `mysqli_`. http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values (likewise) Search for "tag:php insert null" for more results. — , Nov 13 '12 at 18:29
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — tereško, Nov 13 '12 at 23:53

score 3 · Answer 1 · answered Nov 13 '12 at 18:25

3

"" !== NULL
"NULL" !== NULL
NULL === NULL

You are inserting ""

answered Nov 13 '12 at 18:25

Naftali

144,921
39
244
303

score 1 · Answer 2 · answered Nov 13 '12 at 18:31

At the moment you combine your variables to a string, the NULL value in the variable is casted to an empty string. You need to construct your query that it actually reads:

VALUES (NULL, NULL)

and not

VALUES ("", "")

which you can do by perhaps:

if (is_null($seconds)) $seconds='NULL'; else $seconds='"'.$seconds.'"';
if (is_null($ipaddress)) $ipaddress='NULL'; else $ipaddress='"'.$ipaddress.'"';
$query = mysql_query("... VALUES(".$seconds.", ".$ipaddress.")", ...);

However, using some kind of database library or PDO with prepared statements, which abstracts away these kinds of tasks, would be better in the long run.

How to Insert a value or NULL?

2 Answers2