mysqli bind_param not setting string to null

Question

I'm trying to use the mysqli bind_param function in my website for the process of registering users. I want to do two things:

If the user registers with the registration form I provide, set the users password in the database to their desired password (hashed and salted, of course);
if they use Facebook registration, set the password field to NULL.

I realize that I can set the default of the password column in the database to NULL and simply not specify it in the statement upon registering, but this problem also carries on to other aspects of the site where this would not be a practical resolution.

$password = null;

$stmt->bind_param('ssssss', $email, $password, $fullname,
         $prof_image_url, $splash_image_url, $facebook_id);

For testing purposes, I set the variable $password to null directly before the statement is called and it still occurs. When the query is executed, it results in a row with the password column empty rather than saying NULL when I view it via command line.

All help is greatly appreciated!

EDIT: Here's the structure of the password column... Field: password, Type: varchar(255), Null: YES, Key:, Default: NULL, Extra:

function user_add($mysqli, $email, $password, $fullname, $prof_image_url = null, $splash_image_url = null, $facebook_id = null) {
    if ($mysqli == null) throw new Exception('Could not connect to database.');
    if ($email == null || $email == '') throw new Exception('Email is required.', 1);
    if ($password != null && $password == '') throw new Exception('Password is required.', 1);
    if ($fullname == null || $fullname == '') throw new Exception('Name is required.', 1);
    if ($prof_image_url == null) $prof_image_url = IMAGE_PATH . '/profile_default.png';
    if ($splash_image_url == null) $splash_image_url = IMAGE_PATH . '/splash_bg_1.jpg';

    $email = $mysqli->real_escape_string($email);
    $password = $mysqli->real_escape_string($password);
    $fullname = $mysqli->real_escape_string($fullname);
    $facebook_id = $mysqli->real_escape_string($facebook_id);

    $stmt = $mysqli->prepare('INSERT INTO users(`email`, `password`, `full_name`, `prof_image_url`, `splash_image_url`, `facebook_id`) VALUES(?, ?, ?, ?, ?, ?);');

    if (!$stmt) {
        throw new Exception('Could not connect to database.');
    }

    $stmt->bind_param('ssssss', $email, $password, $fullname, $prof_image_url, $splash_image_url, $facebook_id);

    if (!$stmt->execute()) {
        throw new Exception('Could not register user.', 1);
    }

    return $mysqli->insert_id;
}

And this is where I call the function:

function account_facebook_register($mysqli, $code){
    $token_url = "https://graph.facebook.com/oauth/access_token?"
    . "client_id=" . FACEBOOK_APP_ID
    . "&redirect_uri=" . urlencode(FACEBOOK_REDIRECT_URL)
    . "&client_secret=" . FACEBOOK_APP_SECRET
    . "&code=" . $code
    . "&scope=email";

    $response = @file_get_contents($token_url);

    $params = null;
    parse_str($response, $params);
    $token = $params['access_token'];

    $graph_url = "https://graph.facebook.com/me?access_token=" . $token;

    $user = json_decode(file_get_contents($graph_url));

    if (!isset($user->email)){
        $_SESSION['error'] = 'Invalid email on Facebook account.';
        $_SESSION['error_level'] = 1;
        header('Location: ' . WEB_ROOT . '/account/register.php');
        die;
    }

    $user_exists = user_getByEmail($mysqli, $user->email);

    if ($user_exists == null){
        $mysqli = mysql_create();
        $password = null;
        $uid = user_add($mysqli, $user->email, $password, $user->name, 'https://graph.facebook.com/' . $user->id . '/picture?type=large', null, $user->id);

        $token = facebook_get_long_token($token);
        token_add($mysqli, $uid, 'facebook', $token, $user->id);

        $_SESSION['fb_token'] = $token;
        $_SESSION['uid'] = $uid;
        $_SESSION['success'] = "Registration Successful!";
    }else{
        account_facebook_login($mysqli, $user_exists->uid);
    }
}

The mysql_create function just sets up the database for accessing and works as intended. Thank you!

Answer: mysqli->real_escale_string makes null values empty. Check if variable is null before you escape it.

It is completely normal to display an empty column instead of null, even if it is... — chility, Jun 16 '14 at 04:05
@chility Even when I use "Select * from users where password is null;" it doesn't display any rows which have empty password columns. — terpak, Jun 16 '14 at 04:08
You will need to show more code, though -- the DB Schema will help along with the actual code where the query is executed. — , Jun 16 '14 at 05:16

score 6 · Answer 1 · answered Jun 17 '14 at 06:25

When binding values, you shouldn't use real_escape_string at all.

Which renders your initial question not a real one - if you were indeed passing null value to this function, without any intermediate actions, there would be no issue at all.

Edited version is not legitimate either, as it asks to review your code, find the bug, ask a question and answer it. While first two actions you are got to perform yourself.

Thus, to answer your question, one just need the code from it:

$password = null;
$stmt->bind_param('ssssss', $email, $password, $fullname,
         $prof_image_url, $splash_image_url, $facebook_id);

and it will bind null value all right.

score 3 · Answer 2 · edited May 23 '17 at 12:30

After further research, PHP bind_params with null and mysqli prepared statements, insert NULL using bind params both indicate that your question should be addressed as presented so far, so there is something else which is not present in your question about your code which is causing the unexpected behavior.

Your update gives the answer. When you real_escape_string your null value, you cause your empty result.

Take this test DB structure:

CREATE TABLE `example` (
  `example_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `non_null_val` char(64) NOT NULL,
  `null_val` char(64) DEFAULT NULL,
  PRIMARY KEY (`example_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Then, run this PHP code:

<?php
  $mysqli = new mysqli('localhost', 'demo', 'exampledemo', 'demo');
  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

  $stmt = $mysqli->prepare("INSERT INTO example (non_null_val, null_val) VALUES (?, ?)");
  $non_null_val = 'ABC';
  $null_val = null;
  $stmt->bind_param('ss', $non_null_val, $null_val);
  $stmt->execute();

  $non_null_val = 'DEF';
  $null_val = null;
  $stmt->bind_param('ss', $non_null_val, $mysqli->real_escape_string($null_val));
  $stmt->execute();

  $stmt->close();

  $mysqli->close();
?>

You will see that the ABC row has a NULL entry while the DEF has a blank entry. The only difference is the real_escape_string.

So on elaboration,

$password = (is_null($password)?null:$mysqli->real_escape_string($password))

Gives what you need.

You should never ever use `real_escape_string` when binding the values with parameters. This answer has multiple issues! — Dharman, Feb 12 '20 at 16:21

mysqli bind_param not setting string to null

2 Answers2