Syntax error with PDO Prepared statements

Question

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.

You _cannot_ specify the field names like this. Explicit fieldnames aren't strings or integers, they are identifiers. If you are _really_ sure about column names, the first `$mark` could be `implode(',', array_keys($fields);`. I'd still whitelist those fieldnames... — Wrikken, Mar 09 '12 at 19:59
Also: keep in mind PDO _emulates_ prepares / statements under certain conditions, unless told otherwise. — Wrikken, Mar 09 '12 at 20:01

score 6 · Accepted Answer · edited May 23 '17 at 12:32

6

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.

edited May 23 '17 at 12:32

Community

1
1

answered Mar 09 '12 at 19:59

Your Common Sense

156,878
40
214
345

You're right, I just replaced the ? marks with the actual field names and it works now. I'll need to keep that in mind for now on! – Derek Maciel Mar 09 '12 at 20:10

score 0 · Answer 2 · answered Mar 09 '12 at 19:56

0

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

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

answered Mar 09 '12 at 19:56

Madara's Ghost

172,118
50
264
308

score 0 · Answer 3 · answered Mar 09 '12 at 19:56

0

In your SQL query:

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

You forgot table name:

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

answered Mar 09 '12 at 19:56

Robik

6,047
4
31
41

Sorry, that was a typo, in the var_dump at the bottom you should see that I did include the table name. – Derek Maciel Mar 09 '12 at 20:00

Syntax error with PDO Prepared statements

3 Answers3