pdo中的特殊字符

Question

When I try to update my values it gives me the following error: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined. This only occurs with special characters, for instance: (hello;) and not with (hello) For some reason it can't find values with special characters and I can only insert them and not update or remove it from my database.

And the database is already set on utf8.

public function update($data, $key, $value) {
    try { 

        $this->query = 'UPDATE ' . $this->table . ' SET ';

        array_walk($data, function($index, $key) {
            $this->query .= $key . ' = ' . ':' . $key . ', ';
        });

        $this->query = rtrim($this->query, " ,");

        $this->query .= " WHERE $key = :$value"; 

        $stmt = $this->db->prepare($this->query);

        $stmt->execute(array_merge($data, array($value => $value)));

        return true;

    } catch(PDOException $e) {
        echo $e->getMessage();
    }
}

An example how I use it:

if(isset($oldValue, $newValue)) {
$data['database']->update(array("name" => $newValue), "name", $oldValue);
}

The array is to give a key and a new value to update and after the comma is to search for the value in the table 'name'.

If someone knows a solution for this then I would be very happy, thanks in advance.

Answer 1

You’re parameterizing partly, and that’s good, but you should probably be parameterizing fully. To demonstrate more lucidly, let us assume we are calling update(array("occupation" => "poet"), "name", "Horace") with $this->table set to "persons". We’d then be composing this SQL:

UPDATE persons SET occupation = :occupation WHERE name = : Horace

We’d then execute it with these parameter values:

occupation => poet
:Horace => Horace

The occupation = :occupation part is excellent and correct; but name = : Horace and :Horace => Horace is wrong. You instead should probably be composing a query like this:

UPDATE persons SET occupation = :occupation WHERE name = :name

And these parameter values:

occupation => poet
name => Horace

If you ever have a case where you call it like update(array("occupation" => "unemployed"), "occupation", "poet"), however, you might have some issues, as you’d need to have two different parameters for occupation (one old, one new), and you’d be calling both occupation. One would win out over the other and your UPDATE would do nothing. So you’ll have to deal with that case, but otherwise, it should be fairly straightforward.