variable from array of POST params inserting "0" into 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;
    }
 }

make the database default null instead of 0

nathan hayfield
– nathan hayfield

2012-12-31 19:50:42 +00:00
Commented Dec 31, 2012 at 19:50 — nathan hayfield
– nathan hayfield, Commented Dec 31, 2012 at 19:50

Ray · Accepted Answer · 2012-12-31 22:30:47Z

2

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).

edited Dec 31, 2012 at 22:30

answered Dec 31, 2012 at 19:51

Ray

41.6k22 gold badges110 silver badges144 bronze badges

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

3 Comments

ChuckKelly Over a year ago

still seems like a inefficient headache if you ask me , but ill give it a try

ChuckKelly Over a year ago

How do I "check the parameters for being empty before adding them to the $new_member_insert_data array", adding the values that are present while not being stopped if one or more value is empty?

Ray Over a year ago

@user1861766 see my example of not creating array indexes if no value is provided for gender and hometown.

Collectives™ on Stack Overflow

variable from array of POST params inserting "0" into db

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related