Ejemplo n.º 1
0
 /**
  * Regression test
  *
  * @test
  * @todo Define visibility
  */
 public function compileWhereClauseDoesNotDropClauses()
 {
     $clauses = array(0 => array('modifier' => '', 'table' => 'pages', 'field' => 'fe_group', 'calc' => '', 'comparator' => '=', 'value' => array(0 => '', 1 => '\'')), 1 => array('operator' => 'OR', 'modifier' => '', 'func' => array('type' => 'IFNULL', 'default' => array(0 => '1', 1 => '\''), 'table' => 'pages', 'field' => 'fe_group')), 2 => array('operator' => 'OR', 'modifier' => '', 'table' => 'pages', 'field' => 'fe_group', 'calc' => '', 'comparator' => '=', 'value' => array(0 => '0', 1 => '\'')), 3 => array('operator' => 'OR', 'modifier' => '', 'func' => array('type' => 'FIND_IN_SET', 'str' => array(0 => '0', 1 => '\''), 'table' => 'pages', 'field' => 'fe_group'), 'comparator' => ''), 4 => array('operator' => 'OR', 'modifier' => '', 'func' => array('type' => 'FIND_IN_SET', 'str' => array(0 => '-1', 1 => '\''), 'table' => 'pages', 'field' => 'fe_group'), 'comparator' => ''));
     $output = $this->fixture->compileWhereClause($clauses);
     $parts = explode(' OR ', $output);
     $this->assertSame(count($clauses), count($parts));
     $this->assertContains('IFNULL', $output);
 }
Ejemplo n.º 2
0
 /**
  * Debug handler for query execution
  *
  * @param string $function Function name from which this function is called.
  * @param string $execTime Execution time in ms of the query
  * @param array $inData In-data of various kinds.
  * @return void
  * @access private
  */
 public function debugHandler($function, $execTime, $inData)
 {
     // we don't want to log our own log/debug SQL
     $script = \TYPO3\CMS\Core\Utility\PathUtility::stripPathSitePrefix(PATH_thisScript);
     if (substr($script, -strlen('dbal/mod1/index.php')) != 'dbal/mod1/index.php' && !strstr($inData['args'][0], 'tx_dbal_debuglog')) {
         $data = array();
         $errorFlag = 0;
         $joinTable = '';
         if ($this->sql_error()) {
             $data['sqlError'] = $this->sql_error();
             $errorFlag |= 1;
         }
         // if lastQuery is empty (for whatever reason) at least log inData.args
         if (empty($this->lastQuery)) {
             $query = implode(' ', $inData['args']);
         } else {
             $query = $this->lastQuery;
         }
         if ($this->conf['debugOptions']['numberRows']) {
             switch ($function) {
                 case 'exec_INSERTquery':
                 case 'exec_UPDATEquery':
                 case 'exec_DELETEquery':
                     $data['numberRows'] = $this->sql_affected_rows();
                     break;
                 case 'exec_SELECTquery':
                     $data['numberRows'] = $inData['numberRows'];
                     break;
             }
         }
         if ($this->conf['debugOptions']['backtrace']) {
             $backtrace = debug_backtrace();
             unset($backtrace[0]);
             // skip this very method :)
             $data['backtrace'] = array_slice($backtrace, 0, $this->conf['debugOptions']['backtrace']);
         }
         switch ($function) {
             case 'exec_INSERTquery':
             case 'exec_UPDATEquery':
             case 'exec_DELETEquery':
                 $this->debug_log($query, $execTime, $data, $joinTable, $errorFlag, $script);
                 break;
             case 'exec_SELECTquery':
                 // Get explain data:
                 if ($this->conf['debugOptions']['EXPLAIN'] && GeneralUtility::inList('adodb,native', $inData['handlerType'])) {
                     $data['EXPLAIN'] = $this->debug_explain($this->lastQuery);
                 }
                 // Check parsing of Query:
                 if ($this->conf['debugOptions']['parseQuery']) {
                     $parseResults = array();
                     $parseResults['SELECT'] = $this->SQLparser->debug_parseSQLpart('SELECT', $inData['args'][1]);
                     $parseResults['FROM'] = $this->SQLparser->debug_parseSQLpart('FROM', $inData['args'][0]);
                     $parseResults['WHERE'] = $this->SQLparser->debug_parseSQLpart('WHERE', $inData['args'][2]);
                     $parseResults['GROUPBY'] = $this->SQLparser->debug_parseSQLpart('SELECT', $inData['args'][3]);
                     // Using select field list syntax
                     $parseResults['ORDERBY'] = $this->SQLparser->debug_parseSQLpart('SELECT', $inData['args'][4]);
                     // Using select field list syntax
                     foreach ($parseResults as $k => $v) {
                         if ($v === '') {
                             unset($parseResults[$k]);
                         }
                     }
                     if (!empty($parseResults)) {
                         $data['parseError'] = $parseResults;
                         $errorFlag |= 2;
                     }
                 }
                 // Checking joinTables:
                 if ($this->conf['debugOptions']['joinTables']) {
                     if (count(explode(',', $inData['ORIG_from_table'])) > 1) {
                         $joinTable = $inData['args'][0];
                     }
                 }
                 // Logging it:
                 $this->debug_log($query, $execTime, $data, $joinTable, $errorFlag, $script);
                 if (!empty($inData['args'][2])) {
                     $this->debug_WHERE($inData['args'][0], $inData['args'][2], $script);
                 }
                 break;
         }
     }
 }
Ejemplo n.º 3
0
 /**
  * @test
  * @dataProvider trimSqlReallyTrimsAllWhitespaceDataProvider
  * @param string $sql The SQL to trim
  * @param string $expected The expected trimmed SQL with single space at the end
  */
 public function trimSqlReallyTrimsAllWhitespace($sql, $expected)
 {
     $result = $this->fixture->_call('trimSQL', $sql);
     $this->assertSame($expected, $result);
 }
Ejemplo n.º 4
0
 /**
  * Implodes an array of WHERE clause configuration into a WHERE clause.
  *
  * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are:
  * - the bitwise logical and (&)
  * - the addition (+)
  * - the substraction (-)
  * - the multiplication (*)
  * - the division (/)
  * - the modulo (%)
  *
  * @param array $clauseArray
  * @param bool $functionMapping
  * @return string WHERE clause as string.
  * @see \TYPO3\CMS\Core\Database\SqlParser::parseWhereClause()
  */
 public function compileWhereClause($clauseArray, $functionMapping = TRUE)
 {
     $output = '';
     switch ((string) $this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
         case 'native':
             $output = parent::compileWhereClause($clauseArray);
             break;
         case 'adodb':
             // Prepare buffer variable:
             $output = '';
             // Traverse clause array:
             if (is_array($clauseArray)) {
                 foreach ($clauseArray as $v) {
                     // Set operator:
                     $output .= $v['operator'] ? ' ' . $v['operator'] : '';
                     // Look for sublevel:
                     if (is_array($v['sub'])) {
                         $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
                     } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
                         $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
                     } else {
                         if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
                             $output .= ' ' . trim($v['modifier']);
                             switch (TRUE) {
                                 case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
                                     $output .= ' CHARINDEX(';
                                     $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
                                     $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                     $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
                                     $output .= ')';
                                     break;
                                 case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
                                     $output .= ' INSTR(';
                                     $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                     $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
                                     $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
                                     $output .= ')';
                                     break;
                                 default:
                                     $output .= ' LOCATE(';
                                     $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
                                     $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                     $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
                                     $output .= ')';
                             }
                         } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
                             $output .= ' ' . trim($v['modifier']) . ' ';
                             switch (TRUE) {
                                 case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
                                     $output .= 'ISNULL';
                                     break;
                                 case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
                                     $output .= 'NVL';
                                     break;
                                 default:
                                     $output .= 'IFNULL';
                             }
                             $output .= '(';
                             $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                             $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
                             $output .= ')';
                         } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {
                             $output .= ' ' . trim($v['modifier']) . ' ';
                             if ($functionMapping) {
                                 switch (TRUE) {
                                     case $this->databaseConnection->runningADOdbDriver('mssql'):
                                         $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         if (!isset($v['func']['str_like'])) {
                                             $v['func']['str_like'] = $v['func']['str'][0];
                                         }
                                         $output .= '\',\'+' . $field . '+\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
                                         break;
                                     case $this->databaseConnection->runningADOdbDriver('oci8'):
                                         $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         if (!isset($v['func']['str_like'])) {
                                             $v['func']['str_like'] = $v['func']['str'][0];
                                         }
                                         $output .= '\',\'||' . $field . '||\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
                                         break;
                                     case $this->databaseConnection->runningADOdbDriver('postgres'):
                                         $output .= ' FIND_IN_SET(';
                                         $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
                                         $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         $output .= ') != 0';
                                         break;
                                     default:
                                         $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         if (!isset($v['func']['str_like'])) {
                                             $v['func']['str_like'] = $v['func']['str'][0];
                                         }
                                         $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
                                 }
                             } else {
                                 switch (TRUE) {
                                     case $this->databaseConnection->runningADOdbDriver('mssql'):
                                     case $this->databaseConnection->runningADOdbDriver('oci8'):
                                     case $this->databaseConnection->runningADOdbDriver('postgres'):
                                         $output .= ' FIND_IN_SET(';
                                         $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
                                         $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         $output .= ')';
                                         break;
                                     default:
                                         $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
                                         if (!isset($v['func']['str_like'])) {
                                             $v['func']['str_like'] = $v['func']['str'][0];
                                         }
                                         $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
                                 }
                             }
                         } else {
                             // Set field/table with modifying prefix if any:
                             $output .= ' ' . trim($v['modifier']) . ' ';
                             // DBAL-specific: Set calculation, if any:
                             if ($v['calc'] === '&' && $functionMapping) {
                                 switch (TRUE) {
                                     case $this->databaseConnection->runningADOdbDriver('oci8'):
                                         // Oracle only knows BITAND(x,y) - sigh
                                         $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
                                         break;
                                     default:
                                         // MySQL, MS SQL Server, PostgreSQL support the &-syntax
                                         $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
                                 }
                             } elseif ($v['calc']) {
                                 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
                                 if (isset($v['calc_table'])) {
                                     $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
                                 } else {
                                     $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
                                 }
                             } elseif (!($this->databaseConnection->runningADOdbDriver('oci8') && preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']) && $functionMapping)) {
                                 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
                             }
                         }
                         // Set comparator:
                         if ($v['comparator']) {
                             $isLikeOperator = preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']);
                             switch (TRUE) {
                                 case $this->databaseConnection->runningADOdbDriver('oci8') && $isLikeOperator && $functionMapping:
                                     // Oracle cannot handle LIKE on CLOB fields - sigh
                                     if (isset($v['value']['operator'])) {
                                         $values = array();
                                         foreach ($v['value']['args'] as $fieldDef) {
                                             $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
                                         }
                                         $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
                                     } else {
                                         $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
                                     }
                                     if (GeneralUtility::isFirstPartOfStr($v['comparator'], 'NOT')) {
                                         $output .= 'NOT ';
                                     }
                                     // To be on the safe side
                                     $isLob = TRUE;
                                     if ($v['table']) {
                                         // Table and field names are quoted:
                                         $tableName = substr($v['table'], 1, strlen($v['table']) - 2);
                                         $fieldName = substr($v['field'], 1, strlen($v['field']) - 2);
                                         $fieldType = $this->databaseConnection->sql_field_metatype($tableName, $fieldName);
                                         $isLob = $fieldType === 'B' || $fieldType === 'XL';
                                     }
                                     if (strtoupper(substr($v['comparator'], -6)) === 'BINARY') {
                                         if ($isLob) {
                                             $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
                                         } else {
                                             $output .= '(instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
                                         }
                                     } else {
                                         if ($isLob) {
                                             $output .= '(dbms_lob.instr(LOWER(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
                                         } else {
                                             $output .= '(instr(LOWER(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
                                         }
                                     }
                                     break;
                                 default:
                                     if ($isLikeOperator && $functionMapping) {
                                         if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) {
                                             // Remap (NOT)? LIKE to (NOT)? ILIKE
                                             // and (NOT)? LIKE BINARY to (NOT)? LIKE
                                             switch ($v['comparator']) {
                                                 case 'LIKE':
                                                     $v['comparator'] = 'ILIKE';
                                                     break;
                                                 case 'NOT LIKE':
                                                     $v['comparator'] = 'NOT ILIKE';
                                                     break;
                                                 default:
                                                     $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
                                             }
                                         } else {
                                             // No more BINARY operator
                                             $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
                                         }
                                     }
                                     $output .= ' ' . $v['comparator'];
                                     // Detecting value type; list or plain:
                                     $comparator = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $v['comparator']));
                                     if (GeneralUtility::inList('NOTIN,IN', $comparator)) {
                                         if (isset($v['subquery'])) {
                                             $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
                                         } else {
                                             $valueBuffer = array();
                                             foreach ($v['value'] as $realValue) {
                                                 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
                                             }
                                             $dbmsSpecifics = $this->databaseConnection->getSpecifics();
                                             if ($dbmsSpecifics === NULL) {
                                                 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
                                             } else {
                                                 $chunkedList = $dbmsSpecifics->splitMaxExpressions($valueBuffer);
                                                 $chunkCount = count($chunkedList);
                                                 if ($chunkCount === 1) {
                                                     $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
                                                 } else {
                                                     $listExpressions = array();
                                                     $field = trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
                                                     switch ($comparator) {
                                                         case 'IN':
                                                             $operator = 'OR';
                                                             break;
                                                         case 'NOTIN':
                                                             $operator = 'AND';
                                                             break;
                                                         default:
                                                             $operator = '';
                                                     }
                                                     for ($i = 0; $i < $chunkCount; ++$i) {
                                                         $listPart = trim(implode(',', $chunkedList[$i]));
                                                         $listExpressions[] = ' (' . $listPart . ')';
                                                     }
                                                     $implodeString = ' ' . $operator . ' ' . $field . ' ' . $v['comparator'];
                                                     // add opening brace before field
                                                     $lastFieldPos = strrpos($output, $field);
                                                     $output = substr_replace($output, '(', $lastFieldPos, 0);
                                                     $output .= implode($implodeString, $listExpressions) . ')';
                                                 }
                                             }
                                         }
                                     } elseif (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {
                                         $lbound = $v['values'][0];
                                         $ubound = $v['values'][1];
                                         $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];
                                         $output .= ' AND ';
                                         $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];
                                     } elseif (isset($v['value']['operator'])) {
                                         $values = array();
                                         foreach ($v['value']['args'] as $fieldDef) {
                                             $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
                                         }
                                         $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
                                     } else {
                                         $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
                                     }
                             }
                         }
                     }
                 }
             }
             break;
     }
     return $output;
 }
Ejemplo n.º 5
0
 /**
  * @test
  * @dataProvider parseSQLDataProvider
  * @param string $sql The SQL to trim
  * @param array $expected The expected trimmed SQL with single space at the end
  */
 public function parseSQL($sql, $expected)
 {
     $result = $this->subject->_callRef('parseSQL', $sql);
     $this->assertSame($expected, $result);
 }