PHP MariaDB Insert NULL Value Not Working

Question

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?

Answer 1

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.