PHP PDO: Why cause this error in sql statement?

Question

Well I dont understand why dont execute correctly this line:

try {
    $sql = "UPDATE t_perfiles_permisos 
        SET :tipo = :valor
            WHERE Area_Permiso = :area AND Id_Perfil = :idp";
    $result = $this->dbConnect->prepare($sql) or die ($sql);
    $result->bindParam(':tipo',$this->tipo,PDO::PARAM_STR);
    $result->bindParam(':valor',$this->valor,PDO::PARAM_INT); 
    $result->bindParam(':area',$this->area,PDO::PARAM_STR);
    $result->bindParam(':idp',$this->idp,PDO::PARAM_INT);

    $result->execute();
} catch (PDOException $e) {
    echo "Error!! No se puede establecer el permiso: ".$e->getMessage()."<br/>";
    return false;
}

Error:

Error!! No se puede establecer el permiso: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Buscar' = '1' WHERE Area_Permiso = 'Perfiles' AND Id_Perfil = '4'' at line 2

jeroen · Accepted Answer · 2012-09-02 01:13:23Z

2

The problem is:

SET :tipo = :valor

You can only use bound parameters for values, not for column names.

What you need to do in this case, is use a normal variable in your sql statement and check that variable against a white-list of allowed column names.

SET `{$checked_against_whitelist_column_name}` = :valor

answered Sep 2, 2012 at 1:13

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

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

Comments

Francis Bailey · Accepted Answer · 2012-09-02 01:13:35Z

0

You shouldn't be "binding" column names. Where you have

SET :tipo = :valor

this is not proper sytnax. Instead do

SET tipo=:valor

and there you have it

answered Sep 2, 2012 at 1:13

Francis Bailey

632 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP PDO: Why cause this error in sql statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related