Reset a variable's value from inside of a foreach loop

Question

I'm working on someone elses code and it looks like there's some issues with the MySQL Insert statement...

Data truncated for column 'floorSize' at row 1

It seems to do this for quite a few fields, but ideally I want to allow them to be nullable.

So I'm looping through the posted data and looking for an empty string, If it's an empty string, I'd like to reset that variable to NULL. So that the DB can accept a null value instead of a '' string.

At present, I'm using a foreach loop to see if it the value is empty, Then trying to set that value to NULL. But not having much look

Current code snippet is :

function add_user($first_name, $last_name, $gender, $date_of_birth, $address1, $address2, $city, $state, $country, $zip_code, $mobile, $phone, $username, $email, $password, $profile_image, $description, $status, $user_type, $property, $VAN, $agent, $propertyType, $buyerType, $houseName, $street, $postcode, $parish, $zoning, $lat, $lng, $price, $ARV, $tax, $fees, $availableDate, $term, $type, $bedrooms, $bathrooms, $powderrooms, $guestAccom, $furnished, $shortDescription, $floorSize, $plotSize, $pool, $waterfront, $dock, $tennis, $view, $garden, $pets, $fireplace, $laundry, $ac, $patio, $deck, $verandah, $beach, $propertySkipper, $mooring, $gym, $showMap, $display, $videoURL, $garage, $tankSize, $water, $well, $tank, $holiday) {

    global $db;

    $vars = $_POST;

    $count = 0;
    foreach($vars as $k => $v)
    {
        if($v == '')
        {
            $k = NULL;
        }
    }

    //Running Query to add user.
        $query = "INSERT into users VALUES(NULL, '".$first_name."', '".$last_name."', '".$gender."', '".$date_of_birth."', '".$address1."', '".$address2."', '".$city."', '".$state."', '".$country."', '".$zip_code."', '".$mobile."', '".$phone."', '".$username."', '".$email."', '".$password_con."', '".$profile_image."', '".$description."', '".$status."', '', '".date('Y-m-d')."', '".$user_type."', '".$property."', '".$VAN."', '".$agent."', '".$propertyType."', '".$buyerType."', '".$houseName."', '".$street."', '".$postcode."', '".$parish."', '".$zoning."', '".$lat."', '".$lng."', '".$price."', '".$ARV."', '".$tax."', '".$fees."', '".$availableDate."', '".$term."', '".$type."', '".$bedrooms."', '".$bathrooms."', '".$powderrooms."', '".$guestAccom."', '".$furnished."', '".$shortDescription."', '".$floorSize."', '".$plotSize."', '".$pool."', '".$waterfront."', '".$dock."', '".$tennis."', '".$view."', '".$garden."', '".$pets."', '".$fireplace."', '".$laundry."', '".$ac."', '".$patio."', '".$deck."', '".$verandah."', '".$beach."', '".$propertySkipper."', '".$mooring."', '".$gym."', '".$location_id."', '".$showMap."', '".$display."', '".$videoURL."', '".$garage."', '".$tankSize."', '".$water."', '".$well."', '".$tank."', '".$holiday."')";
}

So for example $floorSize is set to '' but ideally I want to overwrite to set it to NULL.

My Query is:

INSERT into users VALUES(NULL, '', '', '', '2000-01-01', '', '', '', '', 'Bermuda', '', '', '', '', 'amy.peniston@gmail.com', '0cc0d896622bca5f24125fd5e5f2fabb', '', '', 'activate', '', '2019-09-13', 'subscriber', 'PW Property', '', 'sue', '1', '0', '', '', '', 'devonshire', '', '32.309851', '-64.781894', '125000', '100', '1', '2', '', '', 'sale', '3', '3', '1', '0', '0', '', '', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '472', '0', '0', '', '0', '', '0', '0', '0', '0')

Thanks

Answer 1

$k is the key, you need to assign the value. You can do this by making $v a reference variable.

    foreach($vars as $k => &$v)
    {
        if($v == '')
        {
            $v = null;
        }
    }

If you're calling the function like this:

add_user($_POST['first_name'], $_POST['last_name'], ...);

then you need to put this loop before you call the function, not inside the function. And you need to loop over $_POST, not another variable that you copy it to (unless you use that variable in the argument list when calling).

Then you need to use a prepared statement. If you just concatenate the variables to the SQL string, null will just turn back into an empty string, it won't become a NULL value in the database. The prepared statement will also protect against SQL injection.

$query = "INSERT into users VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $db->prepare($query);
$date = date('Y-m-d');
$stmt->bind_param("sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss", $first_name,$last_name,$gender,$date_of_birth,$address1,$address2,$city,$state,$country,$zip_code,$mobile,$phone,$username,$email,$password_con,$profile_image,$description,$status,$date,$user_type,$property,$VAN,$agent,$propertyType,$buyerType,$houseName,$street,$postcode,$parish,$zoning,$lat,$lng,$price,$ARV,$tax,$fees,$availableDate,$term,$type,$bedrooms,$bathrooms,$powderrooms,$guestAccom,$furnished,$shortDescription,$floorSize,$plotSize,$pool,$waterfront,$dock,$tennis,$view,$garden,$pets,$fireplace,$laundry,$ac,$patio,$deck,$verandah,$beach,$propertySkipper,$mooring,$gym,$location_id,$showMap,$display,$videoURL,$garage,$tankSize,$water,$well,$tank,$holiday);
$stmt->execute();

Answer 2

You are using function params in your insert query. So you shouldn't check values from $_POST.

// we can use this function inside of your function to get all paramenters
$params = get_defined_vars();

global $db;

foreach($params as $key => $param){
    if(empty($param)){
        ${$key} = NULL;
    }
    // we need to remove special characters to prevent sql injection
    ${$key} = mysqli_real_escape_string($db, $param);
}

// your query here

Anyway this isn't the ideal solution as it may encounter a lot of issues so I would suggest to you to remove all the function parameters since there are a lot and use values directly from $_POST.

function add_user() {
    global $db;

    $values = $_POST;

    foreach ($values as $key => $value) {
        $values[$key] = mysqli_real_escape_string($db, $value);

        if(empty($value)){
            $values[$key] = 'NULL';
        }
    }


    $first_name = $values['first_name'];
    $last_name = $values['last_name'];
    // .... and all your variables

    // be careful to get the right key from $POST variable when setting these variables

}