PDO更新功能出错 - Error in PDO Update Function

Error in PDO Update Function

本文关键字：出错新功能更新 PDO | 更新日期: 2023-09-27

$table="menu_permission";
$field = array('permission'=>$mnuprmis);
$ob->update($table,$field,'staff_id',$stfid);

public function update($table, $fields, $wherefield, $wherefieldvalues) 
    {
        $sql = "update $table set";
        foreach ( $fields as $fieldname => $sfieldvalue )
            $sql .=  $fieldname."= '".$sfieldvalue."',";
            $sql = substr($fldquery,0,strlen($fldquery)-1);
            $sql .=" where $wherefield = '$wherefieldvalues'";
        $q = $this->conn->prepare($sql);
        $q->execute();
        return true;
    }

错误

Fatal error: Uncaught exception 'PDOException' with message '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 'where staff_id = '1'' at line 1' 
in G:'xampp'htdocs'live'Billing Suryas'model'DBConfig.php:171 
Stack trace: #0 G:'xampp'htdocs'live'Billing Suryas'model'DBConfig.php(171): PDOStatement->execute() 
#1 G:'xampp'htdocs'live'Billing Suryas'pages'permission_pages.php(257): Connection->update('menu_permission', Array, 'staff_id', '1') 
#2 {main} thrown in G:'xampp'htdocs'live'Billing Suryas'model'DBConfig.php on line 171

没有像$fldquery这样可怕的事情

$sql = substr($fldquery,0,strlen($fldquery)-1);
              ^^^                  ^^^

因此，您的查询只是

 $sql .=" where $wherefield = '$wherefieldvalues'";

这导致

 where staff_id = '1'   // This is your COMPLETE query

这只是问题之一，当您修复拼写错误并在那里输入正确的变量名称时，它将得到修复。但是，如果您阅读本文，则会出现更大的问题

如何防止 PHP 中的 SQL 注入？

这可能与您在

数值周围加上单引号这一事实有关，这不是必需的，并且可能会破坏您的查询，因为您的数据库可能将其视为字符串而不是数字。

$table="menu_permission";
$field = array('permission'=>$mnuprmis);
$ob->update($table,$field,'staff_id',$stfid);

public function update($table, $fields, $wherefield, $wherefieldvalues) 
    {
        //
        // COMPILE QUERY
        $sql = "update $table set ";
        $col_values_array = array();
        foreach ( $fields as $fieldname => $sfieldvalue ) {
            $value = is_numeric($sfieldvalue) ? $sfieldvalue : "'$sfieldvalue'";
            $col_values_array[] =  "$fieldname = $value";
        }
        $sql .= implode("," , $col_values_array);
        $sql .= " where $wherefield = '$wherefieldvalues'";
        //
        // EXECUTE QUERY
        //$q = $this->conn->prepare($sql); --> not required when not using parametrised queries
        //$q->execute(); --> not required when not using parametrised queries
        $this->conn->query($sql);
        return true;
    }