Issues

ZF-217: placeholder in method query don't work

Description

(sorry for previous report)

About bug:

Code with placeholder ':table'

========= $sql = "UPDATE :table SET ". " {$this->_nsFields['left']}=CASE WHEN {$this->_nsFields['left']}>{$rightId} THEN {$this->_nsFields['left']}+2 ELSE {$this->_nsFields['left']} END, ". " {$this->_nsFields['right']}=CASE WHEN {$this->_nsFields['right']}>={$rightId} THEN {$this->_nsFields['right']}+2 ELSE {$this->_nsFields['right']} END ". " WHERE {$this->_nsFields['right']}>={$rightId}";

    $bind = array(
        'table' => $this->_table
    );

    // try to add
    try {
        $this->e->db->beginTransaction();

        // don't work here
        $res = $this->e->db->query($sql,$bind);         

...
$this->e->db->commit(); } catch ( Exception $e ) { .... }

=== 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 '? SET lft=CASE WHEN lft>6 THEN lft+2 ELSE lft END, rght=CASE WHEN rght>=6 THEN' at line 1

===================

When I'm removing placeholder, works ok:

    $sql = "UPDATE {$this->_table} SET ".
           " {$this->_nsFields['left']}=CASE WHEN {$this->_nsFields['left']}>{$rightId} THEN {$this->_nsFields['left']}+2 ELSE {$this->_nsFields['left']} END, ".
           " {$this->_nsFields['right']}=CASE WHEN {$this->_nsFields['right']}>={$rightId} THEN {$this->_nsFields['right']}+2 ELSE {$this->_nsFields['right']} END ".
           " WHERE {$this->_nsFields['right']}>={$rightId}"; 

Comments

It must be the database server not allowing the table name to be a placeholder. I just tried it with raw PDO and SQLite and got the same result with insert, select, etc. It could vary from RDBMS to RDBMS, but I have no idea. I wouldn't call this a critical bug, in any case.

It is never legal in SQL to use a placeholder for a table name. A placeholder can be used for a single value, not table names or any other syntactic element of a SQL statement.