Issues

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

Issue Type: Bug Created: 2006-07-09T17:22:58.000+0000 Last Updated: 2007-07-05T14:43:13.000+0000 Status: Resolved Fix version(s): - 0.2.0 (29/Oct/06)

Reporter: Eugene Myazin (meniam) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db

Related issues: - ZF-216

Attachments:

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

Posted by Matt Schmidt (sloat) on 2006-07-16T01:42:39.000+0000

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.

Posted by Bill Karwin (bkarwin) on 2006-11-04T07:13:34.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts