/** * Remove parts of a SQL string that contain quoted strings * of values or identifiers. * * @param string $sql * @return string */ protected function _stripQuoted($sql) { // get the character for delimited id quotes, // this is usually " but in MySQL is ` $d = $this->_adapter->quoteIdentifier('a'); $d = $d[0]; // get the value used as an escaped delimited id quote, // e.g. \" or "" or \` $de = $this->_adapter->quoteIdentifier($d); $de = substr($de, 1, 2); $de = str_replace('\\', '\\\\', $de); // get the character for value quoting // this should be ' $q = $this->_adapter->quote('a'); $q = $q[0]; // get the value used as an escaped quote, // e.g. \' or '' $qe = $this->_adapter->quote($q); $qe = substr($qe, 1, 2); $qe = str_replace('\\', '\\\\', $qe); // get a version of the SQL statement with all quoted // values and delimited identifiers stripped out // remove "foo\"bar" $sql = preg_replace("/{$q}({$qe}|\\\\{2}|[^{$q}])*{$q}/", '', $sql); // remove 'foo\'bar' if (!empty($q)) { $sql = preg_replace("/{$q}({$qe}|[^{$q}])*{$q}/", '', $sql); } return $sql; }
/** * Truncate a given table. * * @param \Zend\Db\Adapter\AbstractAdapter $db * @param string $tableName * @return void */ protected function _truncate(\Zend\Db\Adapter\AbstractAdapter $db, $tableName) { $tableName = $db->quoteIdentifier($tableName); if ($db instanceof \Zend\Db\Adapter\Pdo\Sqlite) { $db->query('DELETE FROM ' . $tableName); } else { if ($db instanceof \Zend\Db\Adapter\Db2) { /*if(strstr(PHP_OS, "WIN")) { $file = tempnam(sys_get_temp_dir(), "zendtestdbibm_"); file_put_contents($file, ""); $db->query('IMPORT FROM '.$file.' OF DEL REPLACE INTO '.$tableName); unlink($file); } else { $db->query('IMPORT FROM /dev/null OF DEL REPLACE INTO '.$tableName); }*/ throw \Zend\Test\PHPUnit\Db\Exception\InvalidArgumentException("IBM Db2 TRUNCATE not supported."); } else { if ($this->_isMssqlOrOracle($db)) { $db->query('TRUNCATE TABLE ' . $tableName); } else { if ($db instanceof \Zend\Db\Adapter\Pdo\PgSql) { $db->query('TRUNCATE ' . $tableName . ' CASCADE'); } else { $db->query('TRUNCATE ' . $tableName); } } } } }
/** * _authenticateCreateSelect() - This method creates a Zend_Db_Select object that * is completely configured to be queried against the database. * * @return Zend_Db_Select */ protected function _authenticateCreateSelect() { // build credential expression if (empty($this->_credentialTreatment) || strpos($this->_credentialTreatment, '?') === false) { $this->_credentialTreatment = '?'; } $credentialExpression = new DBExpr('(CASE WHEN ' . $this->_zendDb->quoteInto($this->_zendDb->quoteIdentifier($this->_credentialColumn, true) . ' = ' . $this->_credentialTreatment, $this->_credential) . ' THEN 1 ELSE 0 END) AS ' . $this->_zendDb->quoteIdentifier($this->_zendDb->foldCase('zend_auth_credential_match'))); // get select $dbSelect = clone $this->getDbSelect(); $dbSelect->from($this->_tableName, array('*', $credentialExpression))->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_identity); return $dbSelect; }
/** * Render ORDER clause * * @param string $sql SQL query * @return string */ protected function _renderOrder($sql) { if ($this->_parts[self::ORDER]) { $order = array(); foreach ($this->_parts[self::ORDER] as $term) { if (is_array($term)) { if (is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) { $order[] = (int) trim($term[0]) . ' ' . $term[1]; } else { $order[] = $this->_adapter->quoteIdentifier($term[0], true) . ' ' . $term[1]; } } else { if (is_numeric($term) && strval(intval($term)) == $term) { $order[] = (int) trim($term); } else { $order[] = $this->_adapter->quoteIdentifier($term, true); } } } $sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode(', ', $order); } return $sql; }
/** * Ids-specific sequence id value * * @param string $sequenceName * @return integer */ public function nextSequenceId($sequenceName) { $sql = 'SELECT ' . $this->_adapter->quoteIdentifier($sequenceName) . '.NEXTVAL FROM ' . 'systables WHERE tabid = 1'; $value = $this->_adapter->fetchOne($sql); return $value; }
/** * Returns a quoted schema object. (table name, column name, etc) * * @param string $object * @return string */ public function quoteSchemaObject($object) { return $this->_connection->quoteIdentifier($object); }
/** * Db2-specific sequence id value * * @param string $sequenceName * @return integer */ public function nextSequenceId($sequenceName) { $sql = 'SELECT NEXTVAL FOR ' . $this->_adapter->quoteIdentifier($sequenceName) . ' AS VAL FROM SYSIbm.SYSDUMMY1'; $value = $this->_adapter->fetchOne($sql); return $value; }
/** * Fetches rows by primary key. The argument specifies one or more primary * key value(s). To find multiple rows by primary key, the argument must * be an array. * * This method accepts a variable number of arguments. If the table has a * multi-column primary key, the number of arguments must be the same as * the number of columns in the primary key. To find multiple rows in a * table with a multi-column primary key, each argument must be an array * with the same number of elements. * * The find() method always returns a Rowset object, even if only one row * was found. * * @param mixed $key The value(s) of the primary keys. * @return \Zend\DB\Table\Rowset\AbstractRowset Row(s) matching the criteria. * @throws \Zend\DB\Table\Exception */ public function find() { $this->_setupPrimaryKey(); $args = func_get_args(); $keyNames = array_values((array) $this->_primary); if (count($args) < count($keyNames)) { throw new Exception("Too few columns for the primary key"); } if (count($args) > count($keyNames)) { throw new Exception("Too many columns for the primary key"); } $whereList = array(); $numberTerms = 0; foreach ($args as $keyPosition => $keyValues) { $keyValuesCount = count($keyValues); // Coerce the values to an array. // Don't simply typecast to array, because the values // might be Zend_Db_Expr objects. if (!is_array($keyValues)) { $keyValues = array($keyValues); } if ($numberTerms == 0) { $numberTerms = $keyValuesCount; } else { if ($keyValuesCount != $numberTerms) { throw new Exception("Missing value(s) for the primary key"); } } $keyValues = array_values($keyValues); for ($i = 0; $i < $keyValuesCount; ++$i) { if (!isset($whereList[$i])) { $whereList[$i] = array(); } $whereList[$i][$keyPosition] = $keyValues[$i]; } } $whereClause = null; if (count($whereList)) { $whereOrTerms = array(); $tableName = $this->_db->quoteTableAs($this->_name, null, true); foreach ($whereList as $keyValueSets) { $whereAndTerms = array(); foreach ($keyValueSets as $keyPosition => $keyValue) { $type = $this->_metadata[$keyNames[$keyPosition]]['DATA_TYPE']; $columnName = $this->_db->quoteIdentifier($keyNames[$keyPosition], true); $whereAndTerms[] = $this->_db->quoteInto($tableName . '.' . $columnName . ' = ?', $keyValue, $type); } $whereOrTerms[] = '(' . implode(' AND ', $whereAndTerms) . ')'; } $whereClause = '(' . implode(' OR ', $whereOrTerms) . ')'; } // issue ZF-5775 (empty where clause should return empty rowset) if ($whereClause == null) { $rowsetClass = $this->getRowsetClass(); // if (!class_exists($rowsetClass)) { // end\Loader::loadClass($rowsetClass); // } return new $rowsetClass(array('table' => $this, 'rowClass' => $this->getRowClass(), 'stored' => true)); } return $this->fetchAll($whereClause); }