[BUGFIX] Fix inSet implementation for sqlite 07/57107/4
authorSusanne Moog <susanne.moog@typo3.org>
Thu, 31 May 2018 19:23:45 +0000 (21:23 +0200)
committerBenni Mack <benni@typo3.org>
Thu, 31 May 2018 23:10:02 +0000 (01:10 +0200)
For SQLite inSet was implemented with a "like" substitution
which had various bugs. That implementation has
now been substituted with an "instr" implementation.

Resolves: #85131
Releases: master
Change-Id: Id4e8f7132800c991e49d4033d2b01f474b6aae3b
Reviewed-on: https://review.typo3.org/57107
Reviewed-by: Benni Mack <benni@typo3.org>
Tested-by: Benni Mack <benni@typo3.org>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: TYPO3com <no-reply@typo3.com>
typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php
typo3/sysext/core/Tests/Unit/Database/Query/Expression/ExpressionBuilderTest.php

index 4d58337..b2c177d 100644 (file)
@@ -347,18 +347,32 @@ class ExpressionBuilder
                         1476029421
                     );
                 }
-
-                return $this->comparison(
-                    implode('||', [
-                        $this->literal(','),
-                        $this->connection->quoteIdentifier($fieldName),
-                        $this->literal(','),
-                    ]),
-                    'LIKE',
-                    $this->literal(
-                        '%,' . $this->unquoteLiteral($value) . ',%'
+                $comparison = sprintf(
+                    'instr(%s, %s)',
+                    implode(
+                        '||',
+                        [
+                            $this->literal(','),
+                            $this->connection->quoteIdentifier($fieldName),
+                            $this->literal(','),
+                        ]
+                    ),
+                    $isColumn ?
+                        implode(
+                            '||',
+                            [
+                                $this->literal(','),
+                                // do not explicitly quote value as it is expected to be
+                                // quoted by the caller
+                                'cast(' . $value . ' as text)',
+                                $this->literal(','),
+                            ]
+                        )
+                        : $this->literal(
+                        ',' . $this->unquoteLiteral($value) . ','
                     )
                 );
+                return $comparison;
                 break;
             default:
                 return sprintf(
index fddd900..10ff586 100644 (file)
@@ -321,7 +321,7 @@ class ExpressionBuilderTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCa
         $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
 
         $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
-        $this->connectionProphet->quote('%,1,%', Argument::cetera())->shouldBeCalled()->willReturn("'%,1,%'");
+        $this->connectionProphet->quote(',1,', Argument::cetera())->shouldBeCalled()->willReturn("'%,1,%'");
         $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
             return '"' . $args[0] . '"';
         });
@@ -330,7 +330,7 @@ class ExpressionBuilderTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCa
 
         $result = $this->subject->inSet('aField', "'1'");
 
-        $this->assertSame('\',\'||"aField"||\',\' LIKE \'%,1,%\'', $result);
+        $this->assertSame('instr(\',\'||"aField"||\',\', \'%,1,%\')', $result);
     }
 
     /**
@@ -343,8 +343,8 @@ class ExpressionBuilderTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCa
         $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
 
         $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
-        $this->connectionProphet->quote('%,\'Some\'Value,%', Argument::cetera())->shouldBeCalled()
-            ->willReturn("'%,''Some''Value,%'");
+        $this->connectionProphet->quote(',\'Some\'Value,', Argument::cetera())->shouldBeCalled()
+            ->willReturn("',''Some''Value,'");
         $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
             return '"' . $args[0] . '"';
         });
@@ -353,7 +353,7 @@ class ExpressionBuilderTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCa
 
         $result = $this->subject->inSet('aField', "'''Some''Value'");
 
-        $this->assertSame('\',\'||"aField"||\',\' LIKE \'%,\'\'Some\'\'Value,%\'', $result);
+        $this->assertSame('instr(\',\'||"aField"||\',\', \',\'\'Some\'\'Value,\')', $result);
     }
 
     /**