Syntax error with PDO Prepared statements

Question

I just started working with prepared instructions, and my first few examples turned out great, but now I came across SQL syntax that I do not understand. I have a function that performs an INSERT by taking an associative array parameter, where the array key is the field and the array value is the value to be inserted. For instance:

$arr = array("field1" => "value1", "field2" => "value2"); $this->insert("table", $arr); 

Performed:

 INSERT INTO table ('field1', 'field2') VALUES ('value1', 'value2') 

However, trying to do this, I get the following error:

PDOException: SQLSTATE [42000]: syntax error or access violation: 1064 You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to '' post_title ',' post_body ') VALUES (' Testing! ',' 1 2 3! ')' at line 1

This is my function:

  /** * insert() * * Performs an insert query * * @param string $table The table to be inserted into * @param array $fields An associative array of the fields to be inserted * and their respective values * @return void * */ function insert($table, $fields) { if (empty($table) || empty($fields)) { trigger_error('insert(): one or more missing parameters', E_USER_ERROR); } if (!is_array($fields)) { trigger_error('insert(): second parameter expected to be array', E_USER_ERROR); } for ($i = 0; $i < count($fields); $i++) { $mark[] = "?"; } //(?, ?, ...) $mark = "(" . implode(", ", $mark) . ")"; $bind = array_merge(array_keys($fields), array_values($fields)); //INSERT INTO table (?, ?, ...) VALUES (?, ?, ...) $query = 'INSERT INTO '.$table.' '.$mark.' VALUES '.$mark; //Prepare and execute $stmt = $this->connection->prepare($query); var_dump($stmt); var_dump($bind); $stmt->execute($bind); } 

I am calling using:

 $this->insert('post', array("post_title"=>"Testing!", "post_body"=>"1 2 3!")); 

And two var_dump () s files at the end result in:

  object(PDOStatement)[7] public 'queryString' => string 'INSERT INTO post (?, ?) VALUES (?, ?)' (length=37) array 0 => string 'post_title' (length=10) 1 => string 'post_body' (length=9) 2 => string 'Testing!' (length=8) 3 => string '1 2 3!' (length=6) 

Maybe I'm wrong, but as far as I understand, there is no way to check the actual request sent to the server, so I honestly don't know where the SQL syntax comes from. If anyone could indicate what might be wrong, I would really appreciate it.

Answer 1

You cannot bind identifiers. A thing unknown to all PDO preaching volunteers.

You need to add identifiers using ol'good query building.

Have them whitelisted and create clusters of fields from this list

See Insert / update helper function using PDO for full implementation.

Answer 2

Field names should be marked with ticks ( `` ), and not with quotation marks ( '' ). It should be

 INSERT INTO (`field1`, `field2`) VALUES ('value1', 'value2') 

Answer 3

In your SQL query:

 INSERT INTO ('field1', 'field2') VALUES ('value1', 'value2') 

You forgot the table name:

 INSERT INTO table('field1', 'field2') VALUES ('value1', 'value2');