PHP/mySQL INSERT NULL if variable is empty [duplicate]

Question

i want to check if a variable from $_POST is empty and INSERT a NULL to my Database.

But when I do it my way i always get a String called NULL and not a real NULL in my data set.
This is how I tried it:

if(isset($_POST['folge'])){
    $comment = !empty($_POST['comment']) ? "'".$_POST['comment']."'" : null;

    $sqlstring = "INSERT INTO eventstest (comment) VALUES (".$comment.")";
    echo $sqlstring;
    if ($mysqli->query($sqlstring) === TRUE) {
      printf("Table myCity successfully created.\n");
    }else{
      printf("Errorcode: %d\n", $mysqli->errno);
        printf("Error: %d\n", $mysqli->error);
    }

if I send the form without making inputs to "comment" page output is:

INSERT INTO eventstest (comment) VALUES ()Errorcode: 1136 Error: 0

Whats wrong? Or whats the better way to check for empty inputs and add NULL to DB?

PS: The database cell has STANDARD: NULL

Your Common Sense · Accepted Answer · 2024-04-21 03:11:33Z

21

It should also be noted that this query is exposed to SQL injection attacks, and you should use an API that supports prepared statements - such as PDO or MySQLi, and utilize those to secure your database against these kinds of attacks. Using a prepared statement would look something like this. See how we assign a PHP null to $comment variable when $_POST['comment'] is not set.

if (isset($_POST['folge'])) {
    $comment = $_POST['comment'] ?? null;
    $sql = "INSERT INTO eventstest (comment) VALUES (?)";
    $stmt = $conn->prepare($sql)->execute([$comment]);
}

edited Apr 21, 2024 at 3:11

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered May 7, 2017 at 12:08

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

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

13 Comments

Sylogista Over a year ago

The other option is ternary operator inside $sql = "INSERT INTO table (comment) VALUES (".$comment.")";

Qirel Over a year ago

What do you mean by ternary operator inside the $sql variable..?

Sylogista Over a year ago

Yeah. I don't like ternary operator because of bad looking, but yes – that's what I'm talking about ;)

Qirel Over a year ago

Well, that's opinionated. They aren't that hard to read, but again - it's really up to what you prefer. At least its shown as an example, then OP can choose which one he likes best ;-)

pfmd86 Over a year ago

I did it that way. When my comment hast content everything works fine. But if comment is empty i get errno 1136 from mysql back...

|

e4c5 · Accepted Answer · 2017-05-07 12:09:48Z

1

Several things wrong here. First is that you are using string concatenation instead of prepared statements. This leaves you open to SQL injection. I suggest you stop this project right now and return after learning to use PDO and prepared statements.

Secondly, 'NULL' != null you need to specify it as null

Last but not least, generally there isn't a need to explicitly check for null in postvars and then pass a null again. If the column type allows null and you do not pass in a non null value. null will be stored in it anyway

answered May 7, 2017 at 12:09

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

2 Comments

pfmd86 Over a year ago

I know about that. But at the moment I am far away from productive enviroment. But I will change to prepared statements as soon as i know my project works as i imagine.

e4c5 Over a year ago

If you already know about it you should know that it's more than 3x effort make that change later on

Collectives™ on Stack Overflow

PHP/mySQL INSERT NULL if variable is empty [duplicate]

2 Answers 2

13 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

2 Comments

Linked

Related