[BUGFIX] Use the correct named parameter type in Extbase queries 02/52802/14
authorChristian Toffolo <ian@webian.it>
Sun, 14 May 2017 12:24:25 +0000 (14:24 +0200)
committerBenni Mack <benni@typo3.org>
Sat, 17 Jun 2017 19:12:58 +0000 (21:12 +0200)
In Extbase query expressions the second operand is converted to a plain
value which is either an integer or a string. This type must also be
specified when creating a named parameter in the query.

Resolves: #79473
Resolves: #80978
Resolves: #81487
Resolves: #81019
Resolves: #81056
Releases: master, 8.7
Change-Id: I835643653e03b98bfcd5a8b35b5de750641ecb79
Reviewed-on: https://review.typo3.org/52802
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Tested-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Reviewed-by: Benni Mack <benni@typo3.org>
Tested-by: Benni Mack <benni@typo3.org>
typo3/sysext/extbase/Classes/Persistence/Generic/Mapper/DataMapper.php
typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php
typo3/sysext/extbase/Tests/Unit/Persistence/Generic/Mapper/DataMapperTest.php

index af3c544..fb49f9f 100644 (file)
@@ -695,6 +695,8 @@ class DataMapper implements \TYPO3\CMS\Core\SingletonInterface
 
         if (is_bool($input)) {
             $parameter = (int)$input;
+        } elseif (is_int($input)) {
+            $parameter = $input;
         } elseif ($input instanceof \DateTime) {
             if (!is_null($columnMap) && !is_null($columnMap->getDateTimeStorageFormat())) {
                 $storageFormat = $columnMap->getDateTimeStorageFormat();
index 0793627..b54916a 100644 (file)
@@ -399,12 +399,15 @@ class Typo3DbQueryParser
                     $plainValue = $this->dataMapper->getPlainValue($singleValue);
                     if ($plainValue !== null) {
                         $hasValue = true;
-                        $parameterType = ctype_digit((string)$plainValue) ? \PDO::PARAM_INT : \PDO::PARAM_STR;
-                        $plainValues[] = $this->queryBuilder->createNamedParameter($plainValue, $parameterType);
+                        $plainValues[] = $this->createTypedNamedParameter($singleValue);
                     }
                 }
                 if (!$hasValue) {
-                    throw new Exception\BadConstraintException('The IN operator needs a non-empty value list to compare against. The given value list is empty.', 1484828466);
+                    throw new Exception\BadConstraintException(
+                        'The IN operator needs a non-empty value list to compare against. ' .
+                        'The given value list is empty.',
+                        1484828466
+                    );
                 }
                 $expr = $exprBuilder->comparison($fieldName, 'IN', '(' . implode(', ', $plainValues) . ')');
                 break;
@@ -412,8 +415,8 @@ class Typo3DbQueryParser
                 if ($value === null) {
                     $expr = $fieldName . ' IS NULL';
                 } else {
-                    $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value));
-                    $expr = $exprBuilder->comparison($fieldName, $exprBuilder::EQ, $value);
+                    $placeHolder = $this->createTypedNamedParameter($value);
+                    $expr = $exprBuilder->comparison($fieldName, $exprBuilder::EQ, $placeHolder);
                 }
                 break;
             case QueryInterface::OPERATOR_EQUAL_TO_NULL:
@@ -423,40 +426,86 @@ class Typo3DbQueryParser
                 if ($value === null) {
                     $expr = $fieldName . ' IS NOT NULL';
                 } else {
-                    $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value));
-                    $expr = $exprBuilder->comparison($fieldName, $exprBuilder::NEQ, $value);
+                    $placeHolder = $this->createTypedNamedParameter($value);
+                    $expr = $exprBuilder->comparison($fieldName, $exprBuilder::NEQ, $placeHolder);
                 }
                 break;
             case QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
                 $expr = $fieldName . ' IS NOT NULL';
                 break;
             case QueryInterface::OPERATOR_LESS_THAN:
-                $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT);
-                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LT, $value);
+                $placeHolder = $this->createTypedNamedParameter($value);
+                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LT, $placeHolder);
                 break;
             case QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
-                $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT);
-                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LTE, $value);
+                $placeHolder = $this->createTypedNamedParameter($value);
+                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LTE, $placeHolder);
                 break;
             case QueryInterface::OPERATOR_GREATER_THAN:
-                $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT);
-                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GT, $value);
+                $placeHolder = $this->createTypedNamedParameter($value);
+                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GT, $placeHolder);
                 break;
             case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
-                $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT);
-                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GTE, $value);
+                $placeHolder = $this->createTypedNamedParameter($value);
+                $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GTE, $placeHolder);
                 break;
             case QueryInterface::OPERATOR_LIKE:
-                $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value));
-                $expr = $exprBuilder->comparison($fieldName, 'LIKE', $value);
+                $placeHolder = $this->createTypedNamedParameter($value, \PDO::PARAM_STR);
+                $expr = $exprBuilder->comparison($fieldName, 'LIKE', $placeHolder);
                 break;
             default:
-                throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Unsupported operator encountered.', 1242816073);
+                throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception(
+                    'Unsupported operator encountered.',
+                    1242816073
+                );
         }
         return $expr;
     }
 
     /**
+     * Maps plain value of operand to PDO types to help Doctrine and/or the database driver process the value
+     * correctly when building the query.
+     *
+     * @param mixed $value The parameter value
+     * @return int
+     * @throws \InvalidArgumentException
+     */
+    protected function getParameterType($value): int
+    {
+        $parameterType = gettype($value);
+        switch ($parameterType) {
+            case 'integer':
+                return \PDO::PARAM_INT;
+            case 'string':
+                return \PDO::PARAM_STR;
+            default:
+                throw new \InvalidArgumentException(
+                    'Unsupported parameter type encountered. Expected integer or string, ' . $parameterType . ' given.',
+                    1494878863
+                );
+        }
+    }
+
+    /**
+     * Create a named parameter for the QueryBuilder and guess the parameter type based on the
+     * output of DataMapper::getPlainValue(). The type of the named parameter can be forced to
+     * one of the \PDO::PARAM_* types by specifying the $forceType argument.
+     *
+     * @param mixed $value The input value that should be sent to the database
+     * @param int|null $forceType The \PDO::PARAM_* type that should be forced
+     * @return string The placeholder string to be used in the query
+     * @see \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper::getPlainValue()
+     */
+    protected function createTypedNamedParameter($value, int $forceType = null): string
+    {
+        $plainValue = $this->dataMapper->getPlainValue($value);
+        $parameterType = $forceType ?? $this->getParameterType($plainValue);
+        $placeholder = $this->queryBuilder->createNamedParameter($plainValue, $parameterType);
+
+        return $placeholder;
+    }
+
+    /**
      * @param Qom\DynamicOperandInterface $operand
      * @param Qom\SourceInterface $source The source
      * @return string
index 6a298c5..88e3fb4 100644 (file)
@@ -323,11 +323,13 @@ class DataMapperTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
             'datetime to timestamp' => ['1365866253', new \DateTime('@1365866253')],
             'boolean true to 1' => [1, true],
             'boolean false to 0' => [0, false],
-            'NULL is handled' => ['NULL', null],
-            'plain value is returned unchanged' => ['RANDOM string', 'RANDOM string'],
+            'NULL is handled as string' => ['NULL', null],
+            'string value is returned unchanged' => ['RANDOM string', 'RANDOM string'],
             'array is flattened' => ['a,b,c', ['a', 'b', 'c']],
             'deep array is flattened' => ['a,b,c', [['a', 'b'], 'c']],
             'traversable domain object to identifier' => [1, $traversableDomainObject->reveal()],
+            'integer value is returned unchanged' => [1234, 1234],
+            'float is converted to string' => ['1234.56', 1234.56],
         ];
     }