从POST参数数组变量插入“0”到db

Question

I'm building an android app that uses Phil Sturgeon's RESTful Server for CodeIgniter as a RESTful API.
When the android app makes a POST request to register a user with facebook oauth data the method below is called when it reaches server side. It works, but if one or more of the optional params is empty it will insert a 0 into my database.
How do I prevent this? I'd much prefer it enters nothing at all or null.

 function fb_register_post(){


        if($this->get_request_method() != "POST"){
                $this->response('',406);
            }



        $oauth_email = $this->input->post('OAUTH_EMAIL');       
        $oauth_uid = $this->input->post('OAUTH_UID');
        $oauth_provider = $this->input->post('OAUTH_PROVIDER');
        $first_name = $this->input->post('FIRST_NAME');
        $last_name = $this->input->post('LAST_NAME');

        if(!empty($oauth_provider) and !empty($oauth_uid) and !empty($oauth_email) and !empty($first_name) and !empty($last_name)){

            if(filter_var($oauth_email, FILTER_VALIDATE_EMAIL)){

                $new_member_insert_data = array(
                'first_name' => $first_name,
                'last_name' => $last_name,
                'email' => $oauth_email,
                'OAUTH_EMAIL' => $oauth_email,
                'OAUTH_PROVIDER' => $oauth_provider,
                'OAUTH_UID' => $oauth_uid,


                //OPTIONAL DATA
                'gender' => $this->post('GENDER'),
                'hometown' => $this->post('HOMETOWN'),
                'bio' => $this->post('BIO'),
                'birthday' => $this->post('BIRTHDAY')   
                );
                $this->load->model('membership_model');
                $data['user'] = $register = $this->membership_model->oauth_register($new_member_insert_data);


                $this->response($data, 200);


            }
        }else{
           $message = array('message' => 'FAIL');
           $this->response($message, 201); 
        }

        $message = array('message' => 'FAIL!');
        $this->response($message, 200); // 200 being the HTTP response code    

    }    

The model function being called is :

 function oauth_register($new_member_insert_data)
 {
   $insert = $this->db->insert('users', $new_member_insert_data);
   if($insert){
     $UID = $new_member_insert_data['OAUTH_UID'];

     $q = $this->db->query("SELECT * FROM users WHERE OAUTH_UID = $UID LIMIT 1 ") or die(mysql_error());
     if($q->num_rows() > 0) 
     {
       foreach($q->result() as $row) 
       {
         $data[] = $row;
       }
       return $data;
     }
    }
    else
    {
      return false;
    }
 }

Answer 1

The issue is your post parameters is passing an empty string '' for the value (at least that's what the $_POST array will see it as). Then you try to insert this into an numeric column and Mysql magically is casting it to 0 -- even if you've got another default value set.

The best thing you can do is check the parameters for being empty before adding them to the $new_member_insert_data array any values that are numeric (assuming this array is used to construct the insert statement). Below is an explicit example of not setting array members that have empty values:

 //assuming all non-optional details have values
$new_member_insert_data = array(       
                               'first_name' => $first_name,
                               'last_name' => $last_name,
                               'email' => $oauth_email,
                               'OAUTH_EMAIL' => $oauth_email,
                               'OAUTH_PROVIDER' => $oauth_provider,
                               'OAUTH_UID' => $oauth_uid
                               ) ;
   //OPTIONAL DATA
   $gender =  $this->post('GENDER')? $this->post('GENDER'):null;
   if(!empty($gender)){
          $new_member_insert_data['gender'] = $gender;
   }

   $hometown = $this->post('HOMETOWN')? $this->post('HOMETOWN'):null;
   if(!empty($hometown)){
          $new_member_insert_data['hometown'] = $hometown; 
   }

   ...etc...

You can also prevent this on the client request side by not putting any thing without an value into your post parameters, but I always protect against this on the webservice side, not just the client POST request side.

You'll also see this happen with dates and timestamps... when you try to set them to '' tehy end up like 0000-00-00 00:00:00.

You can turn on a strict mode in Mysql that will cause inserts fail when you try to stuff an empty string into a numeric field or other non-character field (I highly recommend against this though).