Flexible PDO param binding is not working with numbers

Question

I'm trying to create a flexible PUT method for my database, in which I pass a JSON = { col1 : val1, col2 : val2, ... } where coln is the table column name and voln is the new value for that column. What I tried to do is iterate through the json object in PHP and adding each of the columns into the sql query, and finally I bind the parameters into PDO prepared solution:

$sql = "UPDATE table SET ";

$editableColumns = ["name", "description", "address", "latitude", "longitude"];
foreach ($changes as $key => $val) {
    if (!in_array($key, $editableColumns))
    {
        return FALSE;
    }
    $sql .= $key . " = :" . $key . ", ";
}
$sql = preg_replace('/, $/', ' ', $sql) . "WHERE id = :rKey";

$query = $pdo->prepare($sql);
$query->bindParam(":rKey", $id, PDO::PARAM_INT, 6);

foreach ($changes as $key => $val)
{
    // Here I tried the following methods.
    $query->bindParam(":".$key, $val);
    // $query->bindParam(":".$key, $val, PDO::PARAM_STR);
    // $query->bindParam(":".$key, $val, is_numeric($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
}

$query->execute();

When I send only text (name, description, address) it works as expected. But as soon as I set latitude or longitude, the query sets the longitude value to all the requested columns.

For example if I send:

{
    name : "Castle",
    description : "Dummy description",
    address : "addr",
    latitude: 40.730610,
    longitude: -73.935242
}

$sql would be equal to "UPDATE table SET name = :name, description = :description, address = :address, latitude = :latitude, longitude = :longitude WHERE id = :rKey", but the database result is:

name,description,address,latitude,longitude
-73.935242,-73.935242,-73.935242,-73.935242,-73.935242

UPDATE

The table structure is:

CREATE TABLE `table` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `name` varchar(24) NOT NULL,
    `description` text NOT NULL,
    `address` varchar(64) NOT NULL,
    `latitude` double NOT NULL,
    `longitude` double NOT NULL,
    PRIMARY KEY(id)
)

What error are you getting and what datatypes are your lat/long columns defined as? — waterloomatt, Jun 21 '21 at 19:49
You're binding the actual variable reference, which at this point is `$val`, not the value of `$val`. You want `bindValue` instead. — aynber, Jun 21 '21 at 19:51
Or just `$query->execute($changes)` would work fine. No binding needed. — Bill Karwin, Jun 21 '21 at 19:58

Flexible PDO param binding is not working with numbers

UPDATE

0 Answers0