Re: [Patch] Add support for DB->quoteEntity and Make $__DB = more consistent
| From: | Alan Knowles | Date: | Thu, 11 Dec 2003 09:36:07 +0000 |
| Subject: | Re: [Patch] Add support for DB->quoteEntity and Make $__DB = more consistent | ||
| References: | 1 | Groups: | php.pear.dev |
| Request: | Send a blank email to pear-dev+get-24346@lists.php.net to get a copy of this message | ||
can you re-open the bug in the database about this, and put a link to the marc.theaimsgroup or news.php.net page with the patch on.
Regards
Alan
Justin Patrin wrote:
This patch adds support to DB_DataObject to quote table and column names with the DB function quoteEntity(). Note: I just submitted the quoteEntity patch, so to use this, you must have installed the DB patch. If the DB patch is altered before submission to CVS, I will alter this patch accordingly. This feature is by default off, but can be turned on by setting: quote_entities = 1 in the DataObject.ini. All functions which take SQL code directly will not be quoted. They must be quoted by the user (using DB::quoteEntity() of course). This will have to be mentioned in the docs. I'm not sure about some of the functions. selectAdd() is normally used with columns, but may be, say, a function call, so I removed my quiting code from there. However, selectAs() looks like it is made for columns, so I left my code in. Is this correct? NONE of my code takes into account that the table/column could be pre-quoted. Technically, this should probably go in the DB function (although I didn't add it there, either). While working on this patch, I also noticed that many functions were getting $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] directly instead of using getDatabaseConnection, so I changed all that I could find. I also found two possible bugs while working on this pertaining to joinAdd. Bugs 404 and 405. I have tested my changes with a small test script. If you would like me to send that, just ask. ------------------------------------------------------------------------ Index: DataObject.php =================================================================== RCS file: /repository/pear/DB_DataObject/DataObject.php,v retrieving revision 1.161 diff -u -r1.161 DataObject.php --- DataObject.php 10 Dec 2003 03:06:48 -0000 1.161 +++ DataObject.php 11 Dec 2003 08:56:02 -0000 @@ -34,7 +34,6 @@ - /** * Needed classes */ @@ -364,9 +363,13 @@-- Can you help out? Need Consulting Services or Know of a Job? http://www.akbkhome.com} $this->N = 0; $this->_build_condition($this->table()) ;++ $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection();+$this->_query('SELECT ' . $this->_query['data_select'] . - ' FROM ' . $this->__table . " " . + ' FROM ' . ($quoteEntities ? $__DB->quoteEntity($this->__table) : $this->__table) . " " . $this->_join . $this->_query['condition'] . ' '. $this->_query['group_by'] . ' '.@@ -539,6 +542,27 @@return DB_DataObject::raiseError("orderBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS); } + //SQL can be sent to this function, so no quoting + /*global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection(); + if($quoteEntities) { + //This accounts for multiple entries in the orderBy clause + // it is assumed that the format is: \s*column(\s*|\s+(ASC|DESC)),... + $pieces = explode(',', $order); + $order = ''; + $first = true; + foreach($pieces as $piece) { + if($first) { + $first = false; + } else { + $order .= ', '; + } + $arr = preg_split('/\s+/', trim($piece)); + $order .= $__DB->quoteEntity(trim($arr[0])). + (trim($arr[1]) ? ' '.$arr[1] : ''); + } + }*/ if (!$this->_query['order_by']) { $this->_query['order_by'] = " ORDER BY {$order} "; return;@@ -574,7 +598,13 @@return DB_DataObject::raiseError("groupBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS); } - + //SQL can be passed in, so we can't quote it + /*global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection(); + if($quoteEntities) { + $group = $__DB->quoteEntity($group); + }*/ if (!$this->_query['group_by']) { $this->_query['group_by'] = " GROUP BY {$group} "; return;@@ -699,7 +729,15 @@if (!trim($k)) { return DB_DataObject::raiseError("selectAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS); } - + + //SQL can be passed in, so we can't quote it + /* + global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection(); + if($quoteEntities) { + $k = $__DB->quoteEntity($k); + }*/ if ($this->_query['data_select']) $this->_query['data_select'] .= ', '; $this->_query['data_select'] .= " $k ";@@ -746,8 +784,19 @@$table = $tableName; } + global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection(); + if($quoteEntities) { + $table = $__DB->quoteEntity($table); + } foreach ($from as $k) { - $this->selectAdd(sprintf("%s.%s as {$format}",$table,$k,$k)); + $kFormatted = sprintf($format, $k); + if($quoteEntities) { + $kSql = $__DB->quoteEntity($k); + $kFormatted = $__DB->quoteEntity($kFormatted); + } + $this->selectAdd($table.'.'.$kSql.' as '.$kFormatted); } $this->_query['data_select'] .= "\n"; }@@ -770,10 +819,9 @@function insert() { global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; // connect will load the config! - $this->_connect();-- $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; + $__DB =& $this->getDatabaseConnection(); $items = $this->table(); if (!$items) { DB_DataObject::raiseError("insert:No table definition for {$this->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);@@ -789,7 +837,7 @@list($key,$useNative) = $this->sequenceKey(); $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"]; - + // nativeSequences or Sequences.. // big check for using sequences@@ -818,7 +866,11 @@$leftq .= ', '; $rightq .= ', '; } - $leftq .= "$k "; + if($quoteEntities) { + $leftq = $__DB->quoteEntity($k).' '; + } else { + $leftq .= "$k "; + } if (is_a($this->$k,'db_dataobject_cast')) { $value = $this->$k->toString($v,$dbtype);@@ -852,7 +904,10 @@if ($leftq) { - $r = $this->_query("INSERT INTO {$this->__table} ($leftq) VALUES ($rightq) ");++ $r = $this->_query('INSERT INTO '. + ($quoteEntities ? $__DB->quoteEntity($this->__table) : $this->__table). + ' ('.$leftq.') VALUES ('.$rightq.') '); if (PEAR::isError($r)) { DB_DataObject::raiseError($r); return false;@@ -944,10 +999,19 @@- $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; - $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"]; - + $__DB =& $this->getDatabaseConnection(); + $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype']; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities'];++ if($quoteEntities) { + $table = $__DB->quoteEntity($k); + } foreach($items as $k => $v) { + if($quoteEntities) { + $kSql = $__DB->quoteEntity($k); + } else { + $kSql = $k; + } if (!isset($this->$k)) { continue; }@@ -972,7 +1036,7 @@DB_DataObject::raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG); return false; } - $settings .= "$k = ". $value; + $settings .= "$kSql = ". $value; continue; } @@ -980,21 +1044,21 @@ /* special values ... at least null is handled...*/ if (strtolower($this->$k) === 'null') { - $settings .= "$k = NULL"; + $settings .= "$kSql = NULL"; continue; } if ($v & DB_DATAOBJECT_STR) { - $settings .= $k .' = '. $__DB->quote($this->$k) . ' '; + $settings .= $kSql.' = '.$__DB->quote($this->$k).' '; continue; } if (is_numeric($this->$k)) { - $settings .= "$k = {$this->$k} "; + $settings .= "$kSql = {$this->$k} "; continue; } // at present we only cast to integers // - V2 may store additional data about float/int - $settings .= "$k = " . intval($this->$k) . ' '; + $settings .= "$kSql = " . intval($this->$k) . ' '; } @@ -1005,7 +1069,9 @@ $this->_build_condition($items,$keys); // echo " $settings, $this->condition "; if ($settings && isset($this->_query) && $this->_query['condition']) { - $r = $this->_query("UPDATE {$this->__table} SET {$settings} {$this->_query['condition']} "); + $r = $this->_query('UPDATE '. + ($quoteEntities ? $__DB->quoteEntity($this->__table) : $this->__table). + " SET {$settings} {$this->_query['condition']} "); if (PEAR::isError($r)) { $this->raiseError($r); return false;@@ -1051,7 +1117,8 @@{ global $_DB_DATAOBJECT; // connect will load the config! - $this->_connect(); + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; + $__DB =& $this->getDatabaseConnection(); if (!$useWhere) { @@ -1067,7 +1134,9 @@ // don't delete without a condition if (isset($this->_query) && $this->_query['condition']) { - $r = $this->_query("DELETE FROM {$this->__table} {$this->_query['condition']}"); + $r = $this->_query('DELETE FROM '. + ($quoteEntities ? $__DB->quoteEntity($this->__table) : $this->__table). + " {$this->_query['condition']}"); if (PEAR::isError($r)) { $this->raiseError($r); return false;@@ -1161,6 +1230,8 @@function count($whereAddOnly = false) { global $_DB_DATAOBJECT; + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities'];+$t = $this->__clone(); @@ -1176,7 +1247,7 @@ if (!$whereAddOnly && $items) { foreach ($items as $key => $val) { if (isset($t->$key)) { - $t->whereAdd($key . ' = ' . $__DB->quote($t->$key)); + $t->whereAdd(($quoteEntities ? $__DB->quoteEntity($key) : $key) . ' = ' . $__DB->quote($t->$key)); } } }@@ -1188,8 +1259,14 @@} $r = $t->_query( - "SELECT count({$t->__table}.{$keys[0]}) as DATAOBJECT_NUM - FROM {$t->__table} {$t->_join} {$t->_query['condition']}"); + 'SELECT count('. + ($quoteEntities ? $__DB->quoteEntity($t->__table) : $t->__table). + '.'.($quoteEntities ? $__DB->quoteEntity($keys[0]) : $keys[0]). + ') as '. + ($quoteEntities ? $__DB->quoteEntity('DATAOBJECT_NUM') : 'DATAOBJECT_NUM'). + ' FROM '. + ($quoteEntities ? $__DB->quoteEntity($t->__table) : $t->__table). + " {$t->_join} {$t->_query['condition']}"); if (PEAR::isError($r)) { return false; }@@ -1227,9 +1304,8 @@function escape($string) { global $_DB_DATAOBJECT; - $this->_connect(); + $__DB =& $this->getDatabaseConnection(); - $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; return substr($__DB->quote($string),1,-1); } @@ -1596,7 +1672,7 @@ } if (!$this->_database) { - $this->_database = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["database"]; + $this->_database = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database']; } return; }@@ -1687,7 +1763,7 @@$this->debug($string,$log="QUERY"); } - $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; + $__DB =& $this->getDatabaseConnection(); if (@$options['debug_ignore_updates'] && (strtolower(substr(trim($string), 0, 6)) != 'select') &&@@ -1755,7 +1831,8 @@function _build_condition($keys, $filter = array(),$negative_filter=array()) { global $_DB_DATAOBJECT; - $this->_connect(); + $__DB =& $this->getDatabaseConnection(); + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; // if we dont have query vars.. - reset them. if (!isset($this->_query)) {@@ -1763,7 +1840,6 @@$this->_query= $x->_query; } - $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; foreach($keys as $k => $v) { // index keys is an indexed array@@ -1783,6 +1859,12 @@if (!isset($this->$k)) { continue; }++ if($quoteEntities) { + $kSql = $__DB->quoteEntity($this->__table).'.'.$__DB->quoteEntity($k); + } else { + $kSql = $this->__table.'.'.$k; + } if (is_a($this->$k,'db_dataobject_cast')) { $value = $this->$k->toString($v,$dbtype);@@ -1793,26 +1875,26 @@if ($value == 'NULL') { $value = 'IS NULL'; } - $this->whereAdd(" {$this->__table}.{$k} = $value"); + $this->whereAdd(" {$kSql} = $value"); continue; } if (strtolower($this->$k) === 'null') { - $this->whereAdd(" {$this->__table}.{$k} IS NULL"); + $this->whereAdd(" {$kSql} IS NULL"); continue; } if ($v & DB_DATAOBJECT_STR) { - $this->whereAdd(" {$this->__table}.{$k} = " . $__DB->quote($this->$k) ); + $this->whereAdd(" {$kSql} = " . $__DB->quote($this->$k) ); continue; } if (is_numeric($this->$k)) { - $this->whereAdd(" {$this->__table}.{$k} = {$this->$k}"); + $this->whereAdd(" {$kSql} = {$this->$k}"); continue; } /* this is probably an error condition! */ - $this->whereAdd(" {$this->__table}.{$k} = 0"); + $this->whereAdd(" {$kSql} = 0"); } } @@ -1983,7 +2065,7 @@ list($table,$link) = explode(':', $match); $k = sprintf($format, str_replace('.', '_', $key)); // makes sure that '.' is the end of the key; - if ($p = strpos($key,".")) { + if ($p = strpos($key,'.')) { $key = substr($key, 0, $p); } $this->$k = $this->getLink($key, $table, $link);@@ -2192,7 +2274,7 @@* links are added as where items. * * @param optional $joinAs string if you want to select the table as anther name - * usefull when you want to select multiple columsn + * useful when you want to select multiple columns * from a secondary table. * @param optional $joinCol string The column on This objects table to match (needed@@ -2212,6 +2294,7 @@$this->_join = ''; return; } + $quoteEntities =& $_DB_DATAOBJECT['CONFIG']['quote_entities']; // support for array as first argument // this assumes that you dont have a links.ini for the specified table.@@ -2237,13 +2320,10 @@DB_DataObject::raiseError("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA,PEAR_ERROR_DIE); } - $this->_connect(); /* make sure $this->_database is set. */ + $__DB =& $this->getDatabaseConnection(); $this->table(); /* make sure the links are loaded */ - $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];- -$links = array(); if (isset($_DB_DATAOBJECT['LINKS'][$this->_database])) { $links = &$_DB_DATAOBJECT['LINKS'][$this->_database];@@ -2266,7 +2346,7 @@DB_DataObject::raiseError( "joinAdd: You cannot target a join column in the " . "'link from' table ({$obj->__table}). " . - "Either remove the forth argument to joinAdd() ". + "Either remove the fourth argument to joinAdd() ". "({$joinCol}), or alter your links.ini file.", DB_DATAOBJECT_ERROR_NODATA); return false;@@ -2313,8 +2393,14 @@if ($joinAs === false) { $joinAs = $obj->__table; } + if($quoteEntities) { + $joinAs = $__DB->quoteEntity($joinAs); + } $objTable = $obj->__table; + if($quoteEntities) { + $objTable = $__DB->quoteEntity($objTable); + } if ($obj->_join) { $objTable = "($objTable {$obj->_join})"; }@@ -2323,16 +2409,23 @@$fullJoinAs = "AS {$joinAs}"; } + if($quoteEntities) { + $endSql = $__DB->quoteEntity($ofield).'='. + $__DB->quoteEntity($this->__table).'.'.$__DB->quoteEntity($tfield).' '; + } else { + $endSql = "{$ofield}={$this->__table}.{$tfield} "; + }+switch ($joinType) { case 'INNER': case 'LEFT': case 'RIGHT': // others??? .. cross, left outer, right outer, natural..? $this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}". - " ON {$joinAs}.{$ofield}={$this->__table}.{$tfield} "; + " ON {$joinAs}.{$endSql}"; break; case '': // this is just a standard multitable select.. $this->_join .= "\n , {$objTable} {$fullJoinAs} "; - $this->whereAdd("{$joinAs}.{$ofield}={$this->__table}.{$tfield}"); + $this->whereAdd("{$joinAs}.{$endSql}"); } // if obj only a dataobject - eg. no extended class has been defined..@@ -2359,19 +2452,24 @@} foreach($items as $k => $v) { + if($quoteEntities) { + $kSql = $__DB->quoteEntity($k); + } else { + $kSql = $k; + } if (!isset($obj->$k)) { continue; } if ($v & DB_DATAOBJECT_STR) { - $this->whereAdd("{$joinAs}.{$k} = " . $__DB->quote($obj->$k)); + $this->whereAdd("{$joinAs}.{$kSql} = " . $__DB->quote($obj->$k)); continue; } if (is_numeric($obj->$k)) { - $this->whereAdd("{$joinAs}.{$k} = {$obj->$k}"); + $this->whereAdd("{$joinAs}.{$kSql} = {$obj->$k}"); continue; } /* this is probably an error condition! */ - $this->whereAdd("{$joinAs}.{$k} = 0"); + $this->whereAdd("{$joinAs}.{$kSql} = 0"); } // and finally merge the whereAdd from the child..@@ -2772,7 +2870,6 @@$_DB_DATAOBJECT['CONFIG'] = &PEAR::getStaticProperty('DB_DataObject','options'); - }