CODEWITHSUNDEEP
phppdoprepared-statement
Derek Maciel
Derek Maciel

Reputation: 1315

Syntax error with PDO Prepared statements

I've only just started working with prepared statements, and my first few examples worked out great, but now I'm running into an SQL syntax that I don't understand. I have a function that performs an INSERT, taking a parameter of an associative array, where the key of the array is the field and the value of the array is the value to be inserted. For example:

$arr = array("field1" => "value1",
             "field2" => "value2");

$this->insert("table", $arr);

Would perform:

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

However, when trying to do it, I get the following error:

PDOException: 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 ''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'm calling it with:

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

And the two var_dump()s 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)

I may be wrong, but as I understand it, there is no way to check the actual query being sent to the server, so I honestly don't know where the SQL syntax is coming from. If anyone could point out what could be wrong, I would appreciate it tremendously.

Upvotes: 4

Views: 1276

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157839

You can't bind identifiers. A thing unknown to all volunteer PDO evangelists.

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

Have them whitelisted and make field names clause out of that list

See Insert/update helper function using PDO for the complete implementation.

Upvotes: 6

Robik
Robik

Reputation: 6127

In your SQL query:

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

You forgot table name:

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

Upvotes: 0

Madara&#39;s Ghost
Madara&#39;s Ghost

Reputation: 174957

Field names should be surronded with ticks (``) not quotes (''). It should be

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

Upvotes: 0

Related Questions