准备语句使用int表字段进行插入

Question

I am working a personal project to learn OOP. I created this generic method to do an insert to a table, but I could not. $dbAttributes is a list of fields in the table without the id. I pass an object instance, for example, product with properties set such as category_id,product_name, price. The problem comes when I try to bind the values using foreach loop.

I get errors:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value

then after I removed single quote from the placeholder I get:

SQLSTATE[42000]: Syntax error or access violation: 1064

public static function create($obj) {
    global $db;

    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $dbAttributes = $obj->db_fields;
    $colonValues = array();

    for($i=0;$i<count(array_values($dbAttributes));$i++){
        $colonValues[] = ':'.$dbAttributes[$i];
}

   $sql ="INSERT INTO ".$obj::$tableName."(";
   $sql .=join(",", array_values($dbAttributes));
   $sql .= ") VALUES('";
   $sql .= join("','",array_values($colonValues));
   $sql .= "')";

   try {
        $stmt = $db->prepare($sql);

        foreach($colonValues as $k=>$value){

            $rs = $obj->$dbAttributes[$k];
            //if($k!=0){
            $stmt->bindParam($value, $rs); 
            echo gettype($rs)." ". $rs."<br>";//}

    }

    var_dump($sql);
    $numRowsAffected = $db->exec($sql);

   } catch (Exception $ex) {
      echo $ex->getMessage();
   }

}

As you see I am trying to dynamically create SQL and also dynamically bind values. I tried everything and I also read the forum on using back-ticks for table names, it did not solve the problem.

Answer 1

You prepare the statement, but you don't execute the prepared statement. You exec($sql) as a non-prepared statement. This method doesn't support query parameters, it executes the SQL verbatim. So it's not surprising that MySQL complains when it expects an integer literal and finds :columnname.

$numRowsAffected = $db->exec($sql);

This should be:

$stmt->execute();
$numRowsAffected = $stmt->rowCount();

Parameter placeholders must not go inside quotes in SQL.

Also, you don't have to use bindValue(), you can just pass an array to execute().

Here's a simpler way of writing your function:

public static function create($obj) {
    global $db;

    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $dbAttributes = $obj->db_fields;
    $columns = array_map(function ($col) { return "`" . $col . "`"; },
        array_values($dbAttributes));
    $paramPlaceholders = array_map(function ($col) { return ":" . $col; },
        array_values($dbAttributes));
    $paramValues = array_intersect_key(get_object_vars($obj), array_flip($dbAttributes));

    $sql ="INSERT INTO `".$obj::$tableName."` (".implode(",",$columns).")"
        . " VALUES (".implode(",",$paramPlaceholders).")";

    try {
        $stmt = $db->prepare($sql);
        $stmt->execute($paramValues);
        $numRowsAffected = $stmt->rowCount();
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }

}