[FEATURE] Execute native prepared queries 31/28231/10
authorXavier Perseguers <xavier@typo3.org>
Mon, 10 Mar 2014 15:41:14 +0000 (16:41 +0100)
committerJigal van Hemert <jigal.van.hemert@typo3.org>
Tue, 18 Mar 2014 10:39:10 +0000 (11:39 +0100)
As TYPO3 is now using mysqli, prepared queries are natively supported
and should be used.

DBAL, however, does not yet actually use native prepared queries but
falls back to standard queries by replacing placeholders and executing
the underlying SQL query.

Change-Id: If50da6e6d27af89e01c0439bcb9d39a85615a75d
Resolves: #27372
Releases: 6.2
Reviewed-on: https://review.typo3.org/28231
Reviewed-by: Andreas Fernandez
Tested-by: Andreas Fernandez
Tested-by: Wouter Wolters
Reviewed-by: Markus Klein
Reviewed-by: Stefan Neufeind
Reviewed-by: Jigal van Hemert
Tested-by: Jigal van Hemert
typo3/sysext/core/Classes/Database/DatabaseConnection.php
typo3/sysext/core/Classes/Database/PreparedStatement.php
typo3/sysext/core/Tests/Unit/Database/PreparedStatementTest.php
typo3/sysext/dbal/Classes/Database/AdodbPreparedStatement.php [new file with mode: 0644]
typo3/sysext/dbal/Classes/Database/DatabaseConnection.php
typo3/sysext/dbal/Tests/Unit/Database/DatabaseConnectionTest.php

index cca1a7d..bab8d54 100644 (file)
@@ -765,19 +765,23 @@ class DatabaseConnection {
        }
 
        /**
-        * Executes a prepared query.
-        * This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
+        * Prepares a prepared query.
         *
         * @param string $query The query to execute
         * @param array $queryComponents The components of the query to execute
-        * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
+        * @return \mysqli_statement|object MySQLi statement / DBAL object
+        * @internal This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
         */
-       public function exec_PREPAREDquery($query, array $queryComponents) {
-               $res = $this->query($query);
+       public function prepare_PREPAREDquery($query, array $queryComponents) {
+               if (!$this->isConnected) {
+                       $this->connectDB();
+               }
+               $stmt = $this->link->stmt_init();
+               $success = $stmt->prepare($query);
                if ($this->debugOutput) {
                        $this->debug('stmt_execute', $query);
                }
-               return $res;
+               return $success ? $stmt : NULL;
        }
 
        /**************************************
index 47e68bc..6b3f6d8 100644 (file)
@@ -4,7 +4,7 @@ namespace TYPO3\CMS\Core\Database;
 /***************************************************************
  *  Copyright notice
  *
- *  (c) 2010-2013 Xavier Perseguers <typo3@perseguers.ch>
+ *  (c) 2010-2014 Xavier Perseguers <xavier@typo3.org>
  *  All rights reserved
  *
  *  This script is part of the TYPO3 project. The TYPO3 project is
@@ -127,11 +127,21 @@ class PreparedStatement {
        protected $defaultFetchMode = self::FETCH_ASSOC;
 
        /**
-        * MySQLi result object / DBAL object
+        * MySQLi statement object / DBAL object
         *
-        * @var boolean|\mysqli_result|object
+        * @var \mysqli_stmt|object
         */
-       protected $resource;
+       protected $statement;
+
+       /**
+        * @var array
+        */
+       protected $fields;
+
+       /**
+        * @var array
+        */
+       protected $buffer;
 
        /**
         * Random token which is wrapped around the markers
@@ -160,7 +170,15 @@ class PreparedStatement {
                $this->precompiledQueryParts = $precompiledQueryParts;
                $this->table = $table;
                $this->parameters = array();
-               $this->resource = NULL;
+
+               // Test if named placeholders are used
+               if ($this->hasNamedPlaceholders($query) || count($precompiledQueryParts) > 0) {
+                       $this->statement = NULL;
+               } else {
+                       // Only question mark placeholders are used
+                       $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($this->query, $this->precompiledQueryParts);
+               }
+
                $this->parameterWrapToken = $this->generateParameterWrapToken();
        }
 
@@ -234,6 +252,9 @@ class PreparedStatement {
                                }
                                break;
                }
+               if (!is_int($parameter) && !preg_match('/^:[\\w]+$/', $parameter)) {
+                       throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1395055513);
+               }
                $key = is_int($parameter) ? $parameter - 1 : $parameter;
                $this->parameters[$key] = array(
                        'value' => $value,
@@ -268,13 +289,12 @@ class PreparedStatement {
         *
         * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. The PHP type of each array value will be used to decide which PARAM_* type to use (int, string, boolean, NULL), so make sure your variables are properly casted, if needed.
         * @return boolean Returns TRUE on success or FALSE on failure.
+        * @throws \InvalidArgumentException
         * @api
         */
        public function execute(array $input_parameters = array()) {
-               $query = $this->query;
-               $precompiledQueryParts = $this->precompiledQueryParts;
                $parameterValues = $this->parameters;
-               if (count($input_parameters) > 0) {
+               if (!empty($input_parameters)) {
                        $parameterValues = array();
                        foreach ($input_parameters as $key => $value) {
                                $parameterValues[$key] = array(
@@ -283,15 +303,100 @@ class PreparedStatement {
                                );
                        }
                }
-               $this->replaceValuesInQuery($query, $precompiledQueryParts, $parameterValues);
-               if (count($precompiledQueryParts) > 0) {
-                       $query = implode('', $precompiledQueryParts['queryParts']);
+
+               if ($this->statement !== NULL) {
+                       // The statement has already been executed, we try to reset it
+                       // for current run but will set it to NULL if it fails for some
+                       // reason, just as if it were the first run
+                       if (!$this->statement->reset()) {
+                               $this->statement = NULL;
+                       }
+               }
+               if ($this->statement === NULL) {
+                       // The statement has never been executed so we prepare it and
+                       // store it for further reuse
+                       $query = $this->query;
+                       $precompiledQueryParts = $this->precompiledQueryParts;
+
+                       $this->convertNamedPlaceholdersToQuestionMarks($query, $parameterValues, $precompiledQueryParts);
+                       if (count($precompiledQueryParts) > 0) {
+                               $query = implode('', $precompiledQueryParts['queryParts']);
+                       }
+                       $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($query, $precompiledQueryParts);
+                       if ($this->statement === NULL) {
+                               return FALSE;
+                       }
+               }
+
+               $combinedTypes = '';
+               $values = array();
+               foreach ($parameterValues as $parameterValue) {
+                       switch ($parameterValue['type']) {
+                               case self::PARAM_NULL:
+                                       $type = 's';
+                                       $value = NULL;
+                                       break;
+                               case self::PARAM_INT:
+                                       $type = 'i';
+                                       $value = (int)$parameterValue['value'];
+                                       break;
+                               case self::PARAM_STR:
+                                       $type = 's';
+                                       $value = $parameterValue['value'];
+                                       break;
+                               case self::PARAM_BOOL:
+                                       $type = 'i';
+                                       $value = $parameterValue['value'] ? 1 : 0;
+                                       break;
+                               default:
+                                       throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $parameterValue['type'], $key), 1281859196);
+                       }
+
+                       $combinedTypes .= $type;
+                       $values[] = $value;
+               }
+
+               // ->bind_param requires second up to last arguments as references
+               $bindParamArguments = array();
+               $bindParamArguments[] = $combinedTypes;
+               $numberOfExtraParamArguments = count($values);
+               for ($i = 0; $i < $numberOfExtraParamArguments; $i++) {
+                       $bindParamArguments[] = &$values[$i];
                }
-               $this->resource = $GLOBALS['TYPO3_DB']->exec_PREPAREDquery($query, $precompiledQueryParts);
+               call_user_func_array(array($this->statement, 'bind_param'), $bindParamArguments);
+
+               $success = $this->statement->execute();
+
+               // Store result
+               if (!$success || $this->statement->store_result() === FALSE) {
+                       return FALSE;
+               }
+
+               if (count($this->fields) === 0) {
+                       // Store the list of fields
+                       if ($this->statement instanceof \mysqli_stmt) {
+                               $result = $this->statement->result_metadata();
+                               $fields = $result->fetch_fields();
+                               $result->close();
+                       } else {
+                               $fields = $this->statement->fetch_fields();
+                       }
+                       if (is_array($fields)) {
+                               foreach ($fields as $field) {
+                                       $this->fields[] = $field->name;
+                               }
+                       }
+
+               }
+
+               // New result set available
+               $this->buffer = NULL;
+
                // Empty binding parameters
                $this->parameters = array();
+
                // Return the success flag
-               return $this->resource ? TRUE : FALSE;
+               return $success;
        }
 
        /**
@@ -305,16 +410,43 @@ class PreparedStatement {
                if ($fetch_style == 0) {
                        $fetch_style = $this->defaultFetchMode;
                }
-               switch ($fetch_style) {
-                       case self::FETCH_ASSOC:
-                               $row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($this->resource);
-                               break;
-                       case self::FETCH_NUM:
-                               $row = $GLOBALS['TYPO3_DB']->sql_fetch_row($this->resource);
-                               break;
-                       default:
-                               throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
+
+               if ($this->statement instanceof \mysqli_stmt) {
+                       if ($this->buffer === NULL) {
+                               $variables = array();
+                               $this->buffer = array();
+                               foreach ($this->fields as $field) {
+                                       $this->buffer[$field] = NULL;
+                                       $variables[] = &$this->buffer[$field];
+                               }
+
+                               call_user_func_array(array($this->statement, 'bind_result'), $variables);
+                       }
+                       $success = $this->statement->fetch();
+                       $columns = $this->buffer;
+               } else {
+                       $columns = $this->statement->fetch();
+                       $success = is_array($columns);
                }
+
+               if ($success) {
+                       $row = array();
+                       foreach ($columns as $key => $value) {
+                               switch ($fetch_style) {
+                                       case self::FETCH_ASSOC:
+                                               $row[$key] = $value;
+                                               break;
+                                       case self::FETCH_NUM:
+                                               $row[] = $value;
+                                               break;
+                                       default:
+                                               throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
+                               }
+                       }
+               } else {
+                       $row = FALSE;
+               }
+
                return $row;
        }
 
@@ -326,7 +458,12 @@ class PreparedStatement {
         * @api
         */
        public function seek($rowNumber) {
-               return $GLOBALS['TYPO3_DB']->sql_data_seek($this->resource, (int)$rowNumber);
+               $success = $this->statement->data_seek((int)$rowNumber);
+               if ($this->statement instanceof \mysqli_stmt) {
+                       // data_seek() does not return anything
+                       $success = TRUE;
+               }
+               return $success;
        }
 
        /**
@@ -352,7 +489,7 @@ class PreparedStatement {
         * @api
         */
        public function free() {
-               $GLOBALS['TYPO3_DB']->sql_free_result($this->resource);
+               $this->statement->close();
        }
 
        /**
@@ -362,7 +499,7 @@ class PreparedStatement {
         * @api
         */
        public function rowCount() {
-               return $GLOBALS['TYPO3_DB']->sql_num_rows($this->resource);
+               return $this->statement->num_rows;
        }
 
        /**
@@ -372,7 +509,7 @@ class PreparedStatement {
         * @api
         */
        public function errorCode() {
-               return $GLOBALS['TYPO3_DB']->sql_errno();
+               return $this->statement->errno;
        }
 
        /**
@@ -387,8 +524,8 @@ class PreparedStatement {
         */
        public function errorInfo() {
                return array(
-                       $GLOBALS['TYPO3_DB']->sql_errno(),
-                       $GLOBALS['TYPO3_DB']->sql_error()
+                       $this->statement->errno,
+                       $this->statement->error
                );
        }
 
@@ -431,51 +568,67 @@ class PreparedStatement {
        }
 
        /**
-        * Replaces values for each parameter in a query.
+        * Returns TRUE if named placeholers are used in a query.
+        *
+        * @param string $query
+        * @return boolean
+        */
+       protected function hasNamedPlaceholders($query) {
+               $matches = preg_match('/(?<![\\w:]):[\\w]+\\b/', $query);
+               return $matches > 0;
+       }
+
+       /**
+        * Converts named placeholders into question mark placeholders in a query.
         *
         * @param string $query
-        * @param array $precompiledQueryParts
         * @param array $parameterValues
+        * @param array $precompiledQueryParts
         * @return void
         */
-       protected function replaceValuesInQuery(&$query, array &$precompiledQueryParts, array $parameterValues) {
-               if (count($precompiledQueryParts['queryParts']) === 0) {
-                       $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
-               }
-               foreach ($parameterValues as $key => $typeValue) {
-                       switch ($typeValue['type']) {
-                               case self::PARAM_NULL:
-                                       $value = 'NULL';
-                                       break;
-                               case self::PARAM_INT:
-                                       $value = (int)$typeValue['value'];
-                                       break;
-                               case self::PARAM_STR:
-                                       $value = $GLOBALS['TYPO3_DB']->fullQuoteStr($typeValue['value'], $this->table);
-                                       break;
-                               case self::PARAM_BOOL:
-                                       $value = $typeValue['value'] ? 1 : 0;
-                                       break;
-                               default:
-                                       throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $typeValue['type'], $key), 1281859196);
+       protected function convertNamedPlaceholdersToQuestionMarks(&$query, array &$parameterValues, array &$precompiledQueryParts) {
+               $queryPartsCount = count($precompiledQueryParts['queryParts']);
+               $newParameterValues = array();
+               $hasNamedPlaceholders = FALSE;
+
+               if ($queryPartsCount === 0) {
+                       $hasNamedPlaceholders = $this->hasNamedPlaceholders($query);
+                       if ($hasNamedPlaceholders) {
+                               $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
                        }
-                       if (is_int($key)) {
-                               if (count($precompiledQueryParts['queryParts']) > 0) {
-                                       $precompiledQueryParts['queryParts'][2 * $key + 1] = $value;
-                               } else {
-                                       $parts = explode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $query, 2);
-                                       $parts[0] .= $value;
-                                       $query = implode('', $parts);
+               } elseif (count($parameterValues) > 0) {
+                       $hasNamedPlaceholders = !is_int(key($parameterValues));
+                       if ($hasNamedPlaceholders) {
+                               for ($i = 1; $i < $queryPartsCount; $i += 2) {
+                                       $key = $precompiledQueryParts['queryParts'][$i];
+                                       $precompiledQueryParts['queryParts'][$i] = '?';
+                                       $newParameterValues[] = $parameterValues[$key];
                                }
-                       } else {
-                               $queryPartsCount = count($precompiledQueryParts['queryParts']);
-                               for ($i = 1; $i < $queryPartsCount; $i++) {
-                                       if ($precompiledQueryParts['queryParts'][$i] === $key) {
-                                               $precompiledQueryParts['queryParts'][$i] = $value;
-                                       }
+                       }
+               }
+
+               if ($hasNamedPlaceholders) {
+                       if ($queryPartsCount === 0) {
+                               // Convert named placeholders to standard question mark placeholders
+                               $quotedParamWrapToken = preg_quote($this->parameterWrapToken, '/');
+                               while (preg_match(
+                                       '/' . $quotedParamWrapToken . '(.*?)' . $quotedParamWrapToken . '/',
+                                       $query,
+                                       $matches
+                               )) {
+                                       $key = $matches[1];
+
+                                       $newParameterValues[] = $parameterValues[$key];
+                                       $query = preg_replace(
+                                               '/' . $quotedParamWrapToken . $key . $quotedParamWrapToken . '/',
+                                               '?',
+                                               $query,
+                                               1
+                                       );
                                }
-                               $query = str_replace($this->parameterWrapToken . $key . $this->parameterWrapToken, $value, $query);
                        }
+
+                       $parameterValues = $newParameterValues;
                }
        }
 
index 2e4e292..25dacdd 100644 (file)
@@ -57,18 +57,12 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
        private function setUpAndReturnDatabaseStub() {
                $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
                        'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
-                       array('exec_PREPAREDquery', 'fullQuoteStr'),
+                       array('prepare_PREPAREDquery'),
                        array(),
                        '',
                        FALSE,
                        FALSE
                );
-               $GLOBALS['TYPO3_DB']->expects($this->any())->method('fullQuoteStr')
-                       ->will($this->returnCallback(
-                               function($quoteStr, $table) {
-                                       return "'" . $quoteStr . "'";
-                               }
-                       ));
 
                return $GLOBALS['TYPO3_DB'];
        }
@@ -115,20 +109,11 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
         */
        public function parametersAndQueriesDataProvider() {
                return array(
-                       'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1'),
-                       'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=1'),
-                       'one named integer parameter is replaced multiple times' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
-                       'two named integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:uid', array(':pid' => 1, ':uid' => 10), 'SELECT * FROM pages WHERE pid=1 OR uid=10'),
-                       'two unnamed integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=? OR uid=?', array(1, 1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
-                       'php bool TRUE parameter is replaced with 1' => array('SELECT * FROM pages WHERE deleted=?', array(TRUE), 'SELECT * FROM pages WHERE deleted=1'),
-                       'php bool FALSE parameter is replaced with 0' => array('SELECT * FROM pages WHERE deleted=?', array(FALSE), 'SELECT * FROM pages WHERE deleted=0'),
-                       'php null parameter is replaced with NULL' => array('SELECT * FROM pages WHERE deleted=?', array(NULL), 'SELECT * FROM pages WHERE deleted=NULL'),
-                       'string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('Foo bar'), 'SELECT * FROM pages WHERE title=\'Foo bar\''),
-                       'number as string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('12'), 'SELECT * FROM pages WHERE title=\'12\''),
-                       'question mark as values with unnamed parameters are properly escaped' => array('SELECT * FROM foo WHERE title=? AND name=?', array('?', 'fancy title'), 'SELECT * FROM foo WHERE title=\'?\' AND name=\'fancy title\''),
-                       'parameter name as value is properly escaped' => array('SELECT * FROM foo WHERE title=:name AND name=:title', array(':name' => ':title', ':title' => 'fancy title'), 'SELECT * FROM foo WHERE title=\':title\' AND name=\'fancy title\''),
-                       'question mark as value of a parameter with a name is properly escaped' => array('SELECT * FROM foo WHERE title=:name AND name=?', array(':name' => '?', 'cool name'), 'SELECT * FROM foo WHERE title=\'?\' AND name=\'cool name\''),
-                       'unsubstituted question marks do not contain the token wrap' => array('SELECT * FROM foo WHERE title=:name AND question LIKE "%what?" AND name=:title', array(':name' => 'Title', ':title' => 'Name'), 'SELECT * FROM foo WHERE title=\'Title\' AND question LIKE "%what?" AND name=\'Name\'')
+                       'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=?'),
+                       'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=?'),
+                       'one named integer parameter is replaced multiple times' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=? OR uid=?'),
+                       'two named integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:uid', array(':pid' => 1, ':uid' => 10), 'SELECT * FROM pages WHERE pid=? OR uid=?'),
+                       'two unnamed integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=? OR uid=?', array(1, 1), 'SELECT * FROM pages WHERE pid=? OR uid=?'),
                );
        }
 
@@ -143,34 +128,13 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
         * @param string $expectedResult Query with all markers replaced
         * @return void
         */
-       public function parametersAreReplacedInQueryByCallingExecute($query, $parameters, $expectedResult) {
+       public function parametersAreReplacedByQuestionMarkInQueryByCallingExecute($query, $parameters, $expectedResult) {
                $statement = $this->createPreparedStatement($query);
-               $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
+               $this->databaseStub->expects($this->any())->method('prepare_PREPAREDquery')->with($this->equalTo($expectedResult));
                $statement->execute($parameters);
        }
 
        /**
-        * @test
-        */
-       public function executeCallsFullQuoteStrForStringParameter() {
-               $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
-                       'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
-                       array('exec_PREPAREDquery', 'fullQuoteStr'),
-                       array(),
-                       '',
-                       FALSE,
-                       FALSE
-               );
-               $query = 'SELECT * FROM pages WHERE title=?';
-               $parameter = "'Foo'";
-               $uniqueQuoteResult = uniqid('quoteResult');
-               $GLOBALS['TYPO3_DB']->expects($this->once())->method('fullQuoteStr')->with($parameter)->will($this->returnValue($uniqueQuoteResult));
-               $GLOBALS['TYPO3_DB']->expects($this->any())->method('exec_PREPAREDquery')->with($this->stringContains($uniqueQuoteResult));
-               $statement = $this->createPreparedStatement($query);
-               $statement->execute(array($parameter));
-       }
-
-       /**
         * Checking if parameters bound to the statement by bindValues()
         * are properly replaced in the query.
         *
@@ -183,7 +147,7 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
         */
        public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult) {
                $statement = $this->createPreparedStatement($query);
-               $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
+               $this->databaseStub->expects($this->any())->method('prepare_PREPAREDquery')->with($this->equalTo($expectedResult));
                $statement->bindValues($parameters);
                $statement->execute();
        }
@@ -225,27 +189,12 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
        }
 
        /**
-        * Checking if formerly bound values are replaced by the values passed to execute().
-        *
-        * @test
-        * @return void
-        */
-       public function parametersPassedToExecuteOverrulesFormerlyBoundValues() {
-               $query = 'SELECT * FROM pages WHERE pid=? OR uid=?';
-               $expectedResult = 'SELECT * FROM pages WHERE pid=30 OR uid=40';
-               $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
-               $statement = $this->createPreparedStatement($query);
-               $statement->bindValues(array(10, 20));
-               $statement->execute(array(30, 40));
-       }
-
-       /**
         * Data Provider for invalid marker names.
         *
         * @see passingInvalidMarkersThrowsExeption
         * @return array
         */
-       public function passingInvalidMarkersThrowsExeptionDataProvider() {
+       public function passingInvalidMarkersThrowsExceptionDataProvider() {
                return array(
                        'using other prefix than colon' => array('SELECT * FROM pages WHERE pid=#pid', array('#pid' => 1)),
                        'using non alphanumerical character' => array('SELECT * FROM pages WHERE title=:stra≠e', array(':stra≠e' => 1)),
@@ -260,14 +209,14 @@ class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
         *
         * @test
         * @expectedException \InvalidArgumentException
-        * @dataProvider passingInvalidMarkersThrowsExeptionDataProvider
+        * @dataProvider passingInvalidMarkersThrowsExceptionDataProvider
         * @param string $query Query with unreplaced markers
         * @param array  $parameters Array of parameters to be replaced in the query
         * @return void
         */
-       public function passingInvalidMarkersThrowsExeption($query, $parameters) {
+       public function passingInvalidMarkersThrowsException($query, $parameters) {
                $statement = $this->createPreparedStatement($query);
-               $statement->execute($parameters);
+               $statement->bindValues($parameters);
        }
 
 }
diff --git a/typo3/sysext/dbal/Classes/Database/AdodbPreparedStatement.php b/typo3/sysext/dbal/Classes/Database/AdodbPreparedStatement.php
new file mode 100644 (file)
index 0000000..f93d83c
--- /dev/null
@@ -0,0 +1,275 @@
+<?php
+namespace TYPO3\CMS\Dbal\Database;
+
+/***************************************************************
+ *  Copyright notice
+ *
+ *  (c) 2014 Xavier Perseguers <xavier@typo3.org>
+ *  All rights reserved
+ *
+ *  This script is part of the TYPO3 project. The TYPO3 project is
+ *  free software; you can redistribute it and/or modify
+ *  it under the terms of the GNU General Public License as published by
+ *  the Free Software Foundation; either version 2 of the License, or
+ *  (at your option) any later version.
+ *
+ *  The GNU General Public License can be found at
+ *  http://www.gnu.org/copyleft/gpl.html.
+ *  A copy is found in the text file GPL.txt and important notices to the license
+ *  from the author is found in LICENSE.txt distributed with these scripts.
+ *
+ *
+ *  This script is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *  GNU General Public License for more details.
+ *
+ *  This copyright notice MUST APPEAR in all copies of the script!
+ ***************************************************************/
+
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
+/**
+ * MySQLi Prepared Statement-compatible implementation for ADOdb.
+ *
+ * Notice: Extends \TYPO3\CMS\Dbal\Database\DatabaseConnection to be able to access
+ * protected properties solely (thus would be a "friend" class in C++).
+ *
+ * @author     Xavier Perseguers <xavier@typo3.org>
+ */
+class AdodbPreparedStatement extends \TYPO3\CMS\Dbal\Database\DatabaseConnection {
+
+       /**
+        * @var \TYPO3\CMS\Dbal\Database\DatabaseConnection
+        */
+       protected $databaseConnection;
+
+       /**
+        * @var string
+        */
+       protected $query;
+
+       /**
+        * @var array
+        */
+       protected $queryComponents;
+
+       /**
+        * @var array
+        */
+       protected $parameters;
+
+       /**
+        * @var \ADORecordSet_array
+        */
+       protected $recordSet;
+
+       /**
+        * Default constructor.
+        *
+        * @param string $query
+        * @param array $queryComponents
+        * @param \TYPO3\CMS\Dbal\Database\DatabaseConnection $databaseConnection
+        */
+       public function __construct($query, array $queryComponents, \TYPO3\CMS\Dbal\Database\DatabaseConnection $databaseConnection) {
+               $this->databaseConnection = $databaseConnection;
+               $this->query = $query;
+               $this->queryComponents = $queryComponents;
+               $this->parameters = array();
+       }
+
+       /**
+        * Prepares an SQL statement for execution.
+        *
+        * @return boolean TRUE on success or FALSE on failure
+        */
+       public function prepare() {
+               // TODO: actually prepare the query with ADOdb, if supported by the underlying DBMS
+               // see: http://phplens.com/lens/adodb/docs-adodb.htm#prepare
+               return TRUE;
+       }
+
+       /**
+        * Transfers a result set from a prepared statement.
+        *
+        * @return TRUE on success or FALSE on failure
+        */
+       public function store_result() {
+               return TRUE;
+       }
+
+       /**
+        * Binds variables to a prepared statement as parameters.
+        *
+        * @param string $types
+        * @param mixed $var1 The number of variables and length of string types must match the parameters in the statement.
+        * @param mixed $_ [optional]
+        * @return boolean TRUE on success or FALSE on failure
+        * @see \mysqli_stmt::bind_param()
+        */
+       public function bind_param($types, $var1, $_ = NULL) {
+               $numberOfVariables = strlen($types);
+               if (func_num_args() !== $numberOfVariables + 1) {
+                       return FALSE;
+               }
+
+               $this->parameters = array(
+                       array(
+                               'type' => $types{0},
+                               'value' => $var1
+                       ),
+               );
+               for ($i = 1; $i < $numberOfVariables; $i++) {
+                       $this->parameters[] = array(
+                               'type' => $types{$i},
+                               'value' => func_get_arg($i + 1),
+                       );
+               }
+
+               return TRUE;
+       }
+
+       /**
+        * Resets a prepared statement.
+        *
+        * @return boolean TRUE on success or FALSE on failure
+        */
+       public function reset() {
+               return TRUE;
+       }
+
+       /**
+        * Executes a prepared query.
+        *
+        * @return boolean TRUE on success or FALSE on failure
+        */
+       public function execute() {
+               $queryParts = $this->queryComponents['queryParts'];
+               $numberOfParameters = count($this->parameters);
+               for ($i = 0; $i < $numberOfParameters; $i++) {
+                       $value = $this->parameters[$i]['value'];
+                       switch ($this->parameters[$i]['type']) {
+                               case 's':
+                                       if ($value !== NULL) {
+                                               $value = $this->databaseConnection->fullQuoteStr($value, $this->queryComponents['ORIG_tableName']);
+                                       }
+                                       break;
+                               case 'i':
+                                       $value = (int)$value;
+                                       break;
+                               default:
+                                       // Same error as in \TYPO3\CMS\Core\Database\PreparedStatement::execute()
+                                       throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $this->parameters[$i]['type'], $i + 1), 1281859196);
+                       }
+
+                       $queryParts[$i * 2 + 1] = $value;
+               }
+
+               // Standard query from now on
+               $query = implode('', $queryParts);
+
+               $limit = $this->queryComponents['LIMIT'];
+               if ($this->databaseConnection->runningADOdbDriver('postgres')) {
+                       // Possibly rewrite the LIMIT to be PostgreSQL-compatible
+                       $splitLimit = GeneralUtility::intExplode(',', $limit);
+                       // Splitting the limit values:
+                       if ($splitLimit[1]) {
+                               // If there are two parameters, do mapping differently than otherwise:
+                               $numRows = $splitLimit[1];
+                               $offset = $splitLimit[0];
+                               $limit = $numrows . ' OFFSET ' . $offset;
+                       }
+               }
+               if ($limit !== '') {
+                       $splitLimit = GeneralUtility::intExplode(',', $limit);
+                       // Splitting the limit values:
+                       if ($splitLimit[1]) {
+                               // If there are two parameters, do mapping differently than otherwise:
+                               $numRows = $splitLimit[1];
+                               $offset = $splitLimit[0];
+                       } else {
+                               $numRows = $splitLimit[0];
+                               $offset = 0;
+                       }
+                       $this->recordSet = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->SelectLimit($query, $numRows, $offset);
+                       $this->databaseConnection->lastQuery = $this->recordSet->sql;
+               } else {
+                       $this->databaseConnection->lastQuery = $query;
+                       $this->recordSet = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->_Execute($this->databaseConnection->lastQuery);
+               }
+
+               if ($this->recordSet !== FALSE) {
+                       $success = TRUE;
+                       $this->recordSet->TYPO3_DBAL_handlerType = 'adodb';
+                       // Setting handler type in result object (for later recognition!)
+                       //$this->recordSet->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
+               } else {
+                       $success = FALSE;
+               }
+
+               return $success;
+       }
+
+       /**
+        * Returns an array of objects representing the fields in a result set.
+        *
+        * @return array
+        */
+       public function fetch_fields() {
+               return $this->recordSet !== FALSE ? $this->recordSet->_fieldobjects : array();
+       }
+
+       /**
+        * Fetches a row from the underlying result set.
+        *
+        * @return array Array of rows or FALSE if there are no more rows.
+        */
+       public function fetch() {
+               $row = $this->databaseConnection->sql_fetch_assoc($this->recordSet);
+               return $row;
+       }
+
+       /**
+        * Seeks to an arbitrary row in statement result set.
+        *
+        * @param integer $offset Must be between zero and the total number of rows minus one
+        * @return boolean TRUE on success or FALSE on failure
+        */
+       public function data_seek($offset) {
+               return $this->databaseConnection->sql_data_seek($this->recordSet, $offset);
+       }
+
+       /**
+        * Closes a prepared statement.
+        *
+        * @return boolean TRUE on success or FALSE on failure
+        */
+       public function close() {
+               return $this->databaseConnection->sql_free_result($this->recordSet);
+       }
+
+       /**
+        * Magic getter for public properties of \mysqli_stmt access
+        * by \TYPO3\CMS\Core\Database\PreparedStatement.
+        *
+        * @param string $name
+        * @return mixed
+        */
+       public function __get($name) {
+               switch ($name) {
+                       case 'errno':
+                               $output = $this->databaseConnection->sql_errno();
+                               break;
+                       case 'error':
+                               $output = $this->databaseConnection->sql_error();
+                               break;
+                       case 'num_rows':
+                               $output = $this->databaseConnection->sql_num_rows($this->recordSet);
+                               break;
+                       default:
+                               throw new \RuntimeException('Cannot access property ' . $name, 1394631927);
+               }
+               return $output;
+       }
+
+}
index 6097077..b1aedc3 100644 (file)
@@ -368,8 +368,9 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                        if (is_array($tdef['fields'])) {
                                foreach ($tdef['fields'] as $field => $fdefString) {
                                        $fdef = $this->SQLparser->parseFieldDef($fdefString);
-                                       $this->cache_fieldType[$table][$field]['type'] = $fdef['fieldType'];
-                                       $this->cache_fieldType[$table][$field]['metaType'] = $this->MySQLMetaType($fdef['fieldType']);
+                                       $fieldType = isset($fdef['fieldType']) ? $fdef['fieldType'] : '';
+                                       $this->cache_fieldType[$table][$field]['type'] = $fieldType;
+                                       $this->cache_fieldType[$table][$field]['metaType'] = $this->MySQLMetaType($fieldType);
                                        $this->cache_fieldType[$table][$field]['notnull'] = isset($fdef['featureIndex']['NOTNULL']) && !$this->SQLparser->checkEmptyDefaultValue($fdef['featureIndex']) ? 1 : 0;
                                        if (isset($fdef['featureIndex']['DEFAULT'])) {
                                                $default = $fdef['featureIndex']['DEFAULT']['value'][0];
@@ -851,7 +852,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                        }
                        $this->debugHandler('exec_SELECTquery', GeneralUtility::milliseconds() - $pt, $data);
                }
-               // Return result handler.
+               // Return handler.
                return $sqlResult;
        }
 
@@ -1501,66 +1502,43 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
        }
 
        /**
-        * Executes a prepared query.
-        * This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
+        * Prepares a prepared query.
         *
         * @param string $query The query to execute
         * @param array $queryComponents The components of the query to execute
-        * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
+        * @return boolean|\mysqli_statement|\TYPO3\CMS\Dbal\Database\AdodbPreparedStatement
+        * @throws \RuntimeException
+        * @internal This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
         */
-       public function exec_PREPAREDquery($query, array $queryComponents) {
+       public function prepare_PREPAREDquery($query, array $queryComponents) {
                $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
                // Get handler key and select API:
-               $sqlResult = NULL;
+               $preparedStatement = NULL;
                switch ($queryComponents['handler']) {
                        case 'native':
                                $this->lastQuery = $query;
-                               $sqlResult = $this->query($this->lastQuery);
-                               $this->resourceIdToTableNameMap[serialize($sqlResult)] = $queryComponents['ORIG_tableName'];
+                               $preparedStatement = parent::prepare_PREPAREDquery($this->lastQuery, $queryComponents);
+                               $this->resourceIdToTableNameMap[serialize($preparedStatement)] = $queryComponents['ORIG_tableName'];
                                break;
                        case 'adodb':
-                               $limit = $queryComponents['LIMIT'];
-                               if ($this->runningADOdbDriver('postgres')) {
-                                       // Possibly rewrite the LIMIT to be PostgreSQL-compatible
-                                       $splitLimit = GeneralUtility::intExplode(',', $limit);
-                                       // Splitting the limit values:
-                                       if ($splitLimit[1]) {
-                                               // If there are two parameters, do mapping differently than otherwise:
-                                               $numrows = $splitLimit[1];
-                                               $offset = $splitLimit[0];
-                                               $limit = $numrows . ' OFFSET ' . $offset;
-                                       }
-                               }
-                               if ($limit != '') {
-                                       $splitLimit = GeneralUtility::intExplode(',', $limit);
-                                       // Splitting the limit values:
-                                       if ($splitLimit[1]) {
-                                               // If there are two parameters, do mapping differently than otherwise:
-                                               $numrows = $splitLimit[1];
-                                               $offset = $splitLimit[0];
-                                       } else {
-                                               $numrows = $splitLimit[0];
-                                               $offset = 0;
-                                       }
-                                       $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($query, $numrows, $offset);
-                                       $this->lastQuery = $sqlResult->sql;
-                               } else {
-                                       $this->lastQuery = $query;
-                                       $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_Execute($this->lastQuery);
+                               /** @var \TYPO3\CMS\Dbal\Database\AdodbPreparedStatement $preparedStatement */
+                               $preparedStatement = GeneralUtility::makeInstance('TYPO3\\CMS\\Dbal\\Database\\AdodbPreparedStatement', $query, $queryComponents, $this);
+                               if (!$preparedStatement->prepare()) {
+                                       $preparedStatement = FALSE;
                                }
-                               $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
-                               // Setting handler type in result object (for later recognition!)
-                               $sqlResult->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
                                break;
                        case 'userdefined':
+                               throw new \RuntimeException('prepare_PREPAREDquery is not implemented for userdefined handlers', 1394620167);
+                               /*
                                $queryParts = $queryComponents['queryParts'];
-                               $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
-                               if (is_object($sqlResult)) {
-                                       $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
+                               $preparedStatement = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
+                               if (is_object($preparedStatement)) {
+                                       $preparedStatement->TYPO3_DBAL_handlerType = 'userdefined';
                                        // Setting handler type in result object (for later recognition!)
-                                       $sqlResult->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
+                                       $preparedStatement->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
                                }
                                break;
+                               */
                }
                if ($this->printErrors && $this->sql_error()) {
                        debug(array($this->lastQuery, $this->sql_error()));
@@ -1571,13 +1549,10 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                'args' => $queryComponents,
                                'ORIG_from_table' => $queryComponents['ORIG_tableName']
                        );
-                       if ($this->conf['debugOptions']['numberRows']) {
-                               $data['numberRows'] = $this->sql_num_rows($sqlResult);
-                       }
-                       $this->debugHandler('exec_PREPAREDquery', GeneralUtility::milliseconds() - $pt, $data);
+                       $this->debugHandler('prepare_PREPAREDquery', GeneralUtility::milliseconds() - $pt, $data);
                }
                // Return result handler.
-               return $sqlResult;
+               return $preparedStatement;
        }
 
        /**************************************
index 4f254ba..cb7f4f5 100644 (file)
@@ -226,9 +226,24 @@ class DatabaseConnectionTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
                $statement = new $className($sql, 'cache');
                $statement->bindValues($parameterValues);
                $parameters = $statement->_get('parameters');
-               $statement->_callRef('replaceValuesInQuery', $query, $precompiledQueryParts, $parameters);
-               $expected = 'SELECT * FROM cache WHERE tag = \'tag-one\' OR tag = \'tag-two\' OR tag = \'tag-three\'';
-               $this->assertEquals($expected, $query);
+               $statement->_callRef('convertNamedPlaceholdersToQuestionMarks', $query, $parameters, $precompiledQueryParts);
+               $expectedQuery = 'SELECT * FROM cache WHERE tag = ? OR tag = ? OR tag = ?';
+               $expectedParameterValues = array(
+                       0 => array(
+                               'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
+                               'value' => 'tag-one',
+                       ),
+                       1 => array(
+                               'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
+                               'value' => 'tag-two',
+                       ),
+                       2 => array(
+                               'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
+                               'value' => 'tag-three',
+                       ),
+               );
+               $this->assertEquals($expectedQuery, $query);
+               $this->assertEquals($expectedParameterValues, $parameters);
        }
 
 }