ZF-1: Quote SQL identifiers

Issue Type:
Improvement
Created:
2006-06-15T02:11:03.000+0000
Last Updated:
2007-07-05T14:43:05.000+0000
Status:
Resolved
Fix version(s):
  • 0.9.0 (17/Mar/07)
Reporter:
Crowd Administrator (admin)
Assignee:
Bill Karwin (bkarwin)
Tags:
  • Zend_Db_Table
Related issues:
Attachments:

Description

(copied from TRAC #9 - reported by gwaihir gw h r.n t)

For you guys to ponder: the Zend framework doesn't currently quote the column names in its (My)SQL queries. This gives issues when using "special" characters in those names and thus I found myself haphazardly adding them: From: Zend/Db/Select.php


    public function __toString()
    {
        //unchanged parts
 
        // add columns
        /** WH: Updated to quote column names in back-ticks (`). Needed for '-' in a column name.
         * Note that I am not sure how databases other than MySQL respond to those back-ticks.
         * Note also how - probably inefficiently - the select is for all columns by name, rather than *.
         * @todo: find a way to do this without altering the library code.
         */
        if ($this->_parts['cols']) {
            $sql .= '`'. implode("`,\n\t`", $this->_parts['cols']) . "`\n";
        }
 
        //more unchanged parts
    }

If this affects other database systems differently than MySQL, then I dunno how / where this had best be done. If it doesn't hurt any of them, then please implement right away. :D I'm very exited about this development. PHP really lacks a "master" framework (like Ruby's Rails or some of Java's frameworks). I think the future success of the language rides on it (now that PHP5 already covers most other wishes). With kind regards, Wim Heemskerk

Comments

Posted by Crowd Administrator (admin) on 2006-06-15T02:21:55.000+0000

Previous change history from TRAC:

05/05/06 19:43:49: Modified by gavin

Also, the same problem arises in MySQL when column names use reserved words (e.g. timestamp).

Right or wrong, phpMyAdmin permits this, and some people do this.

06/03/06 04:36:47: Modified by mike

See also #92, issue is not limited to MySQL.

06/06/06 08:00:31: Modified by tony at sealedenvelope.com

The same issue also applies to using reserved words (e.g. 'group') as table names in MySQL. Line 80 of Db/Adaptor/Pdo/Mysql.php should be changed from:


$sql = "DESCRIBE $table";

to:


$sql = "DESCRIBE `$table`";

Posted by Tim Steiner (spam38) on 2006-09-14T11:44:18.000+0000

A more portable solution would be to use the adapter's quoteIdentifier method to quote column and table names:


public function __toString()

...

$cols = $this->_parts['cols'];
foreach($cols as $col_key => $col_val) {
    $cols[$col_key] = $this->_adapter->quoteIdentifier($col_val);
}

...

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:15:30.000+0000

Changing fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2006-12-26T17:28:12.000+0000

Rewording summary.

Posted by Bill Karwin (bkarwin) on 2007-01-05T17:06:25.000+0000

Recategorize as Zend_Db_Table component.

Posted by Bill Karwin (bkarwin) on 2007-03-16T01:16:19.000+0000

As of revision 3897, all SQL generated by Zend_Db classes should quote identifiers.

You can use a Zend_Db_Expr to override an identifier if you don't want it quoted.