PHP How to insert NULL in Database?

Question

I have a normal HTML-File that give a string via. POST to my PHP-file wich will put this to a MySQL-database.

How do I achieve that I can write a "real" NULL in the database and not " " (a empty string) or something like that?

The MySQL column is nullable.

My form:

<form method="post" action="putInDatabase.php">

    <label>Text</label>
    <textarea name="text" ></textarea>

    <label>Picture URL (optional)</label>
    <input name="image" />
    <br>

    <input id="submit" name="submit" type="submit" value="submit">

</form>

My PHP-File:

<?php

  $text = "";
  $image = null;

  if (isset($_POST["submit"]))
  {
    $text = $_POST["text"];

    $image = $_POST["image"];
  }

  $text = strtr ($text, array ('"' => '\"'));


  $con = mysql_connect("censored :)");

  if (!$con)
  {
    die('ERROR' . mysql_error());
  }
  mysql_select_db("_DATABASE_HERE_", $con);


  $insertSQL = "INSERT INTO `_DATABASE_HERE_`.`_NAME_HERE_` (`Text`, `PictureURL`) VALUES ('$text', '$image ');";  

  $res = mysql_query($insertSQL); 
  $res = mysql_query($sql);

  mysql_close($con);

  echo "Success!";

?>

first you need to make sure that the column is set as nullable ,if it does you can just write NULL on the insert statement. — Gal Sisso, Feb 06 '15 at 16:07
you need to switch to `PDO` or `MySQLi` prepared statements!!! — cmorrissey, Feb 06 '15 at 16:08
Instead of using the `mysql` functions, use PDO with prepared statements. This article will show you all you need to know how to do it the right way from the start: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Feb 06 '15 at 16:08
@Fred-ii- No, I add a URL in my "Image" form element. But I want to set it NULL if the user don't write anything in that element. — DominikTV, Feb 06 '15 at 16:09
Use a conditional statement then and use NULL as a "string" in the value. A ternary operator can also be used. — Funk Forty Niner, Feb 06 '15 at 16:09
See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 - the accepted answer shows how NULLs can be easily inserted. — user2864740, Feb 06 '15 at 16:20

Marc B · Accepted Answer · 2015-02-06T16:18:38.957

You'd need to pre-process your text. And since you're vulnerable to sql injection attacks this should be considered MANDATORY:

if (isset($_POST['text']) && !empty($_POST['text'])) {
   $safetext = "'" . mysql_real_escape_string($_POST['text']) . "'";
} else {
   $safetext = 'null';
}

$sql = "INSERT ... VALUES ($safetext, ...)";

Note how the quotes are added inside the if(). If there's some text to be done, the sql-escaped text is surrounded by quotes. if there's no text at all, then the string null is added in. This boils down to the difference between null and 'null'.

null is an sql null, "unknown value". 'null' is a string with the literal characters n, u, l, and l in it. In SQL terms, they're two completely different things.

The above code would produce

INSERT ... VALUES (null, ...)
INSERT ... VALUES ('Miles O\'Brien', ...)

score 0 · Answer 2 · answered Feb 06 '15 at 16:19

0

Try this:

$variable_name = "NULL";

Now, while inserting into the database, use $variable_name.

That should do it.

EDIT: You need to either use prepare statements, if you are gonna switch to PDOs in future or escape the user input.

answered Feb 06 '15 at 16:19

Pawan Kartik

34
2

PHP How to insert NULL in Database?

2 Answers2