Appending NULL to a string, used for MySQL insert query

Question

I am trying to add a new entry to a MySQL database, with last 5 parameters being optional, so NULL value is possible. I am having trouble appending NULL value to a query string, based on received POST data.

How to append NULL value to a query string?

//more similar mysqli_real_escape_string rows and if clauses above these lines
$phonenumber=mysqli_real_escape_string($db,$_POST["phonenumber"]);
$gender = NULL;
if($_POST["gender"] != null)
   $gender=(int)mysqli_real_escape_string($db,$_POST["gender"]);
$age = NULL;
if($_POST["age"] != null)
   $age = (int)mysqli_real_escape_string($db,$_POST["age"]);
$qs_registration="insert into uporabnik (ID, up_ime, geslo, email, ime, priimek, naslov_bivanja, posta_foreign_key,
tel_stevilka, spol, starost) VALUES (NULL, '".$username."','".$password."','".$email."','".$name."',
'".$surname."','".$location."',".$postoffice.",'".$phonenumber."',".$gender.",".$age.")";

You can set the default value to `NULL` in MySQL, then just leave those fields out when inserting if those values aren't present; building your query dynamically. — Qirel, Mar 09 '17 at 14:54
Table design is bad, ID field should not be null, either auto increment or specify a value in the insert statement — glant, Mar 09 '17 at 14:57
@glant If `ID` is the primary key, with auto-increment, passing NULL would just make it use the AI, so that could work if its set up right. — Qirel, Mar 09 '17 at 14:59
@Qirel that's my point...Is the ID a pKey or just a column ?? Not sure in this case — glant, Mar 09 '17 at 15:00
We don't know here, but it can be perfectly valid as-is, likely the ID is an auto-increment - then it works ;-) — Qirel, Mar 09 '17 at 15:01
If it was declared as PKey in the designer it would show as null not allowed. — glant, Mar 09 '17 at 15:03

score 0 · Answer 1 · answered Mar 09 '17 at 14:57

If you need a null value eg: are using and auto_increment id ..in a column then you could omit the related values (and you can avoid the string concat because '$yourvar' is enough)

    "insert into uporabnik (up_ime, geslo, email, ime, priimek, 
      naslov_bivanja, posta_foreign_key, tel_stevilka, spol, starost)
    VALUES ('$username','$password','$email','$name',
         '$surname','$location', $postoffice','$phonenumber',$gender,$age.")";

anyway be careful passing var you should eval a paramaetrized query instead passing var for avoid sqlinjection

score 0 · Answer 2 · edited May 23 '17 at 11:46

First of all, you should be using prepared statements - When should I use prepared statements?

However, if you are just looking for an answer, just set your initial variables to the string 'NULL' instead.

That way, when you concat everything, you'll get a query string that MySQL can understand:

$age = 'NULL';
if($_POST["age"] != null) {
    // Wrap each variable in quotes
   $age = "'" . (int)mysqli_real_escape_string($db,$_POST["age"]) . "'";
}
// etc.

$qs_registration="insert into uporabnik (ID, up_ime, geslo, email, ime, priimek, naslov_bivanja, posta_foreign_key,
tel_stevilka, spol, starost) VALUES (NULL, " . $username . "," . $password . "," . $email . "," . $name . ",
" . $surname . "," . $location . "," . $postoffice . "," . $phonenumber . "," . $gender . "," . $age . ")";

NOTE: I think there are a lot of other problems here, from not using prepared statements (as mentioned before) to how you have the table configured in the first place. But this should fix your issue at hand.

What if the variable is a string, then you need singlequotes around it in the query, and then you'd get `'NULL'` as a string, not actual *null*. And since your query has no singlequotes around the variables, it will fail. — Qirel, Mar 09 '17 at 15:01
A solution is to do `$age = $_POST["age"] != null ? "'".(int)mysqli_real_escape_string($db,$_POST["age"])."'" : NULL;`, then you'd get the sniglequote only when there is a value (albeit, this is a bad example, because age here is an integer, not a string - but its an example) ;-) — Qirel, Mar 09 '17 at 15:04

Appending NULL to a string, used for MySQL insert query

2 Answers2