4

Using PHP Version 7.1.9, MariaDB 10.1.26.

I'm submitting form data to a MySQL database, one of my values is NULL however in the database it's empty.

I have ensured that my database table is set to;

  • allow null = yes
  • default - null

My code is below (please ignore any security vulnerabilities this is simplified code);

$id = $_POST['id '];
$name = $_POST['name'] ? $_POST['name'] : NULL ;
$sql = "INSERT INTO staff (id, name) VALUES ('".$id."', '".$name."')
// query runs and inserts successfully

When I var_dump($name) I get NULL, although the name value in my database is empty (i.e. not null)

Any ideas what i'm doing wrong?

Improve this question
7
  • Is it possible that you are inserting an empty string instead of NULL? Commented Sep 12, 2018 at 15:09
  • if you don't have a value for $name your query should be just $sql = "INSERT INTO staff (id) VALUES ('".$id.") to have NULL for the name in the db Commented Sep 12, 2018 at 15:09
  • @GeorgeHanson this is actually what is happening with his query Commented Sep 12, 2018 at 15:10
  • 1
    Also, isn't id an auto increment primary key? Then you shouldn't include it in the query at all. Commented Sep 12, 2018 at 15:14
  • 1
    I want to point out the original poster said "please ignore any security vulnerabilities this is simplified code" ... it seems like everyone ignored this point in their responses. I take that statement to mean "I know about security measures and I am using them." Commented Sep 12, 2018 at 15:58

4 Answers 4

Reset to default
3

Edit

The original poster said

My code is below (please ignore any security vulnerabilities this is simplified code)

I interpret that as "I know about SQL injection and I am taking measures to prevent it in my code. I've simplified my post to make it easier to get an answer."

My response below is following their format. That's why I did not use PDO, mysqli, prepared statements/escape measures in my post. If I were personally writing code to insert data into a database, I would make sure my data is sanitized and I would use an ORM like Doctrine (which is a wrapper for PDO) to interact directly with the database.

My Answer

Referencing the code in the original post:

$id = $_POST['id '];
$name = $_POST['name'] ? $_POST['name'] : NULL ;
$sql = "INSERT INTO staff (id, name) VALUES ('".$id."', '".$name."')
// query runs and inserts successfully

Your query is behaving the way you've written your code. If you echo/print a PHP variable to standard output after it has been set to NULL you won't see a value at all. Null is the absence of value. Since you've wrapped the absence of value (no value, null) in single quotes, you're telling MySQL that you want to insert an empty string into the name column.

I would rewrite the code as follows:

$id = $_POST['id '];
$name = $_POST['name'] ? "'$_POST[name]'" : 'NULL';
$sql = "INSERT INTO staff (id, name) VALUES ('$id', $name)";

Notice how I put NULL in a string for the name variable. When I include the name variable in the query I don't wrap it with quotes. This is the proper way to explicitly add a null value to a column in MySQL.

PHP's double quotes allows variable interpolation. This means you don't have to break your strings down into individual parts and concatenate string values together. This makes the code cleaner and easier to read.

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

1 Comment

Thanks for the great explanation! :)
2

First, you're obviously not using prepared statements. I strongly advice you to use prepared statements in the name of security and stability.

Then, on to the issue at hand. The database doesn't know what a PHP null is and will only see an empty string to be inserted in your code.

"" . null . "" === ""

Keeping your (very dangerous and vulnerable) example code, and modifing the place where you add the "quotes" around the to be inserted string. If the name is null just insert NULL without quotes around it. the databse server will interpret that as having to inserta null value

$name = $_POST['name'] ? "'".$_POST['name']."'" : 'NULL';
$sql = "INSERT INTO staff (id, name) VALUES ('".$id."', ".$name.")";

Now really, investigate how to do prepared queries to prevent SQL injections or at least use mysqli_real_escape_string or something equivalent.

this is the more secure version, using PDO.

$sql = "INSERT INTO staff (id,name) VALUES (:id,:name)";
$stmt= $dpo->prepare($sql);
$stmnt->bindParam(':id', $id, PDO::PARAM_INT);

if(!$POST['name']) {
    $stmnt->bindParam(':name', null, PDO::PARAM_NULL);
}
else {
   $stmnt->bindParam(':name', $POST['name'], PDO::PARAM_STR);
}
$stmt->execute();
Improve this answer

2 Comments

This is great, and works perfect - thanks for the explanation too! I understand your comments about the security and take them on board :)
@TheOrdinaryGeek I added a rough PDO example for you. this does not take in account names of '0' or other thruthy values that represent false. The validation would need to happen up front of course.
0

I would instead use PDO prepared statements. That should set the value to NULL instead of an empty string. Because you are wrapping '".$name"' it is making the query '') - i.e an empty string.

Improve this answer

Comments

0

I'd do like this:

$id = $_POST['id '];
if(isset($_POST['name'])){
    $sql = "INSERT INTO staff (id, name) VALUES ('".$id."', '".$name."')
}else{
    $sql = "INSERT INTO staff (id) VALUES ('".$id."')
}

discriminating the query if I receive the name from the form or not. You should use prepared statements to pass variables (user inputs) to a mysql query. Otherwise you are widely open to SQL injections.

Improve this answer

3 Comments

Then present an answer that is not open to slq injection
I am currently replying to a question posted by the OP that is strictly related to the code that he posted.
It seems like everyone keeps ignoring that the original poster said "My code is below (please ignore any security vulnerabilities this is simplified code)" meaning that they already know about sql injection and they're taking measures in their code to prevent it. I imagine the post was simplified to get a quick answer, in which case makes Lelio's post appropriate.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.