Insert NULL variable into database

Question

I have variable set to NULL that im trying to insert into a database but for some reason they keep getting submitted as '0'. Im positive that column im trying to inset into allows NULL and that the default is set to to NULL. Heres my code:

$insert = NULL;
$query = mysql_query("INSERT INTO `table1` (column1) VALUES ('$insert')") or die(mysql_error());

Have you tried using a prepared statement? See [http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values][1] [1]: http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values — jle, Jul 09 '12 at 15:28
Try to put default value as NULL in database table. When you create a new record column1 will be NULL automatically. Or try to set $insert variable as null $insert = null. And the question is - why you need it as NULL? — comprex, Jul 09 '12 at 15:27

Naftali · Accepted Answer · 2012-07-09T16:18:29.143

Warning:

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi.

IF you want it to be NULL (and you really really still want to use mysqli_*) in the database you can do the following:

$insert = NULL;
$query = mysql_query("INSERT INTO `table1` (column1) VALUES ("
                         .(($insert===NULL)?
                                 "NULL":
                                 "'".mysql_real_escape_string($insert)."'").
                     ")") or die(mysql_error());

But this could lead to nefarious SQL injection and is not recommended.

See Bobby Tables

So: all in all you should be using prepared statements.

You can use MySQLi like so:

        $dbHandle = new mysqli(...);
        $query = "INSERT INTO `table1` (column1) VALUES (?)";
        $statement = $dbHandle->prepare($query);
        if($statement){
            $statement->bind_param('s', $insert);
            if(!$statement->execute()){
                echo "Statement insert error: {$statement->error}";
            }
            $statement->close();
        }
        else {
            echo "Insert error: {$dbHandle->error}";
        }

-1 for not fixing the SQL injection vulnerability in the first example. A warning is no excuse for leaving insecure code... — ircmaxell, Jul 09 '12 at 15:46

Mahesh Meniya · Answer 2 · 2012-07-09T15:50:16.583

1

Try this for static query:

$query = mysql_query("INSERT INTO `table1` (column1) VALUES (NULL)")  or die(mysql_error());

Using Variable :

$insert= NULL;
$insert = ($insert===NULL)? 'NULL' : "'$insert'";
mysql_query("INSERT INTO `table1` (column1) VALUES ($insert)") or die(mysql_error());

edited Jul 09 '12 at 15:50

answered Jul 09 '12 at 15:26

Mahesh Meniya

2,627
3
18
17

That works, but i need it to be a variable because sometimes the variable isnt null – Jonah Katz Jul 09 '12 at 15:30

score 0 · Answer 3 · answered Jul 09 '12 at 15:24

0

Try without the quotes;

$query = mysql_query("INSERT INTO `table1` (`column1`) VALUES (".$insert.")") or die(mysql_error());

The query should be;

INSERT INTO table1 (column1) VALUES (NULL);

answered Jul 09 '12 at 15:24

craig1231

3,769
4
31
34

`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '., '24')' at line 1` – Jonah Katz Jul 09 '12 at 15:33
Well it works on my server... so you must be typing something incorrectly – craig1231 Jul 09 '12 at 15:52

Insert NULL variable into database

3 Answers3

Warning:

But this could lead to nefarious SQL injection and is not recommended.

Linked

Related