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());

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
– comprex, Commented Jul 9, 2012 at 15:27
Have you tried using a prepared statement? See [stackoverflow.com/questions/5329542/… [1]: stackoverflow.com/questions/5329542/… — jle
– jle, Commented Jul 9, 2012 at 15:28

Naftali · Accepted Answer · 2012-07-09 16:18:29Z

5

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}";
        }

edited Jul 9, 2012 at 16:18

answered Jul 9, 2012 at 15:26

Naftali

147k41 gold badges247 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonah Katz Over a year ago

Im talking about the first one

Naftali Over a year ago

@JonahKatz are you sure that it is on the mysql_* line?

NDBoost Over a year ago

are you missing a semi colon at the end of a line somewhere

ircmaxell Over a year ago

-1 for not fixing the SQL injection vulnerability in the first example. A warning is no excuse for leaving insecure code...

Mahesh Meniya · Accepted Answer · 2012-07-09 15:50:16Z

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 9, 2012 at 15:50

answered Jul 9, 2012 at 15:26

Mahesh Meniya

2,6053 gold badges20 silver badges17 bronze badges

1 Comment

Jonah Katz Over a year ago

That works, but i need it to be a variable because sometimes the variable isnt null

craig1231 · Accepted Answer · 2012-07-09 15:24:33Z

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 9, 2012 at 15:24

craig1231

3,8964 gold badges33 silver badges34 bronze badges

2 Comments

Jonah Katz Over a year ago

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

craig1231 Over a year ago

Well it works on my server... so you must be typing something incorrectly

Collectives™ on Stack Overflow

Insert NULL variable into database

3 Answers 3

Warning:

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

4 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Warning:

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

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related