[TASK] Refactor BackendUtility::getRecordsByField() to prepared statements 64/50664/6
authorManuel Selbach <manuel_selbach@yahoo.de>
Tue, 15 Nov 2016 14:10:29 +0000 (15:10 +0100)
committerSusanne Moog <susanne.moog@typo3.org>
Sat, 3 Dec 2016 14:07:33 +0000 (15:07 +0100)
According to task #78437 queries should follow the prepared statement
principle.
Thus method BackendUtiltiy::getRecordsByField() has to be refactored
to retrieve a queryBuilder which holds the parameter assigned to e.g.
additional where clause, etc. otherwise the parameter and the dynamically
generated placeholders in the queryQuilder will be reset.

Change-Id: Id66d7b2fcfc5bcdca4d920b645c2285ded0c160a
Resolves: #78704
Releases: master
Reviewed-on: https://review.typo3.org/50664
Reviewed-by: Anja Leichsenring <aleichsenring@ab-softlab.de>
Tested-by: Anja Leichsenring <aleichsenring@ab-softlab.de>
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Susanne Moog <susanne.moog@typo3.org>
Tested-by: Susanne Moog <susanne.moog@typo3.org>
typo3/sysext/backend/Classes/Utility/BackendUtility.php
typo3/sysext/backend/Classes/View/PageLayoutView.php
typo3/sysext/recycler/Classes/Domain/Model/DeletedRecords.php

index 4cc6f56..a49bbbe 100644 (file)
@@ -219,6 +219,7 @@ class BackendUtility
      * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
      * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
      * @param bool $useDeleteClause Use the deleteClause to check if a record is deleted (default TRUE)
+     * @param null|QueryBuilder $queryBuilder The queryBuilder must be provided, if the parameter $whereClause is given and the concept of prepared statement was used. Example within self::firstDomainRecord()
      * @return mixed Multidimensional array with selected records (if any is selected)
      */
     public static function getRecordsByField(
@@ -229,10 +230,14 @@ class BackendUtility
         $groupBy = '',
         $orderBy = '',
         $limit = '',
-        $useDeleteClause = true
+        $useDeleteClause = true,
+        $queryBuilder = null
     ) {
         if (is_array($GLOBALS['TCA'][$theTable])) {
-            $queryBuilder = static::getQueryBuilderForTable($theTable);
+            if (null === $queryBuilder) {
+                $queryBuilder = static::getQueryBuilderForTable($theTable);
+            }
+
             // Show all records except versioning placeholders
             $queryBuilder->getRestrictions()
                 ->removeAll()
@@ -388,11 +393,10 @@ class BackendUtility
             $tcaCtrl = $GLOBALS['TCA'][$table]['ctrl'];
 
             $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
-                          ->getQueryBuilderForTable($table);
-            $expressionBuilder = $queryBuilder->expr();
+                ->getQueryBuilderForTable($table);
 
-            $constraint = $expressionBuilder->andX(
-                $expressionBuilder->eq(
+            $constraint = $queryBuilder->expr()->andX(
+                $queryBuilder->expr()->eq(
                     $tcaCtrl['languageField'],
                     $queryBuilder->createNamedParameter($language, \PDO::PARAM_INT)
                 ),
@@ -406,7 +410,9 @@ class BackendUtility
                 (string)$constraint,
                 '',
                 '',
-                1
+                1,
+                true,
+                $queryBuilder
             );
         }
         return $recordLocalization;
@@ -3965,13 +3971,29 @@ class BackendUtility
      */
     public static function firstDomainRecord($rootLine)
     {
-        $expressionBuilder = $queryBuilder = static::getQueryBuilderForTable('sys_domain')->expr();
-        $constraint = $expressionBuilder->andX(
-            $expressionBuilder->eq('redirectTo', $expressionBuilder->literal('')),
-            $expressionBuilder->eq('hidden', 0)
+        $queryBuilder = static::getQueryBuilderForTable('sys_domain');
+        $constraint = $queryBuilder->expr()->andX(
+            $queryBuilder->expr()->eq(
+                'redirectTo',
+                $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
+            ),
+            $queryBuilder->expr()->eq(
+                'hidden',
+                $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
+            )
         );
         foreach ($rootLine as $row) {
-            $dRec = self::getRecordsByField('sys_domain', 'pid', $row['uid'], (string)$constraint, '', 'sorting');
+            $dRec = self::getRecordsByField(
+                'sys_domain',
+                'pid',
+                $row['uid'],
+                (string)$constraint,
+                '',
+                'sorting',
+                '',
+                true,
+                $queryBuilder
+            );
             if (is_array($dRec)) {
                 $dRecord = reset($dRec);
                 return rtrim($dRecord['domainName'], '/');
index 8550f9b..fc48b1e 100644 (file)
@@ -451,18 +451,23 @@ class PageLayoutView extends \TYPO3\CMS\Recordlist\RecordList\AbstractDatabaseRe
         }
         if ($userCanEditPage) {
             $languageOverlayId = 0;
-            $overlayExpressionBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
                 ->getConnectionForTable('pages_language_overlay')
-                ->getExpressionBuilder();
-            $constraint = $overlayExpressionBuilder->eq(
+                ->createQueryBuilder();
+            $constraint = $queryBuilder->expr()->eq(
                 'sys_language_uid',
-                (int)$this->tt_contentConfig['sys_language_uid']
+                $queryBuilder->createNamedParameter($this->tt_contentConfig['sys_language_uid'], \PDO::PARAM_INT)
             );
             $pageOverlayRecord = BackendUtility::getRecordsByField(
                 'pages_language_overlay',
                 'pid',
                 (int)$this->id,
-                $constraint
+                $constraint,
+                '',
+                '',
+                '',
+                true,
+                $queryBuilder
             );
             if (!empty($pageOverlayRecord[0]['uid'])) {
                 $languageOverlayId = $pageOverlayRecord[0]['uid'];
index 6af8b34..0d512ea 100644 (file)
@@ -156,12 +156,21 @@ class DeletedRecords
         if (trim($filter) !== '') {
             $labelConstraint = $queryBuilder->expr()->like(
                 $tcaCtrl['label'],
-                $queryBuilder->quote('%' . $queryBuilder->escapeLikeWildcards($filter) . '%')
+                $queryBuilder->createNamedParameter(
+                    $queryBuilder->quote('%' . $queryBuilder->escapeLikeWildcards($filter) . '%'),
+                    \PDO::PARAM_STR
+                )
             );
             if (MathUtility::canBeInterpretedAsInteger($filter)) {
                 $filterConstraint = $queryBuilder->expr()->orX(
-                    $queryBuilder->expr()->eq('uid', (int)$filter),
-                    $queryBuilder->expr()->eq('pid', (int)$filter),
+                    $queryBuilder->expr()->eq(
+                        'uid',
+                        $queryBuilder->createNamedParameter($filter, \PDO::PARAM_INT)
+                    ),
+                    $queryBuilder->expr()->eq(
+                        'pid',
+                        $queryBuilder->createNamedParameter($filter, \PDO::PARAM_INT)
+                    ),
                     $labelConstraint
                 );
             } else {
@@ -176,7 +185,7 @@ class DeletedRecords
                 ->count('*')
                 ->from($table)
                 ->where(
-                    $queryBuilder->expr()->neq($deletedField, 0),
+                    $queryBuilder->expr()->neq($deletedField, $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                     $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)),
                     $filterConstraint
                 )
@@ -246,15 +255,23 @@ class DeletedRecords
         }
         // query for actual deleted records
         if ($allowQuery) {
+            $where = $queryBuilder->expr()->andX(
+                $queryBuilder->expr()->eq(
+                    'pid',
+                    $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)
+                ),
+                $filterConstraint
+            );
             $recordsToCheck = BackendUtility::getRecordsByField(
                 $table,
                 $deletedField,
                 '1',
-                ' AND ' . $queryBuilder->expr()->andX($queryBuilder->expr()->eq('pid', (int)$id), $filterConstraint),
+                ' AND ' . $where,
                 '',
                 '',
                 $limit,
-                false
+                false,
+                $queryBuilder
             );
             if ($recordsToCheck) {
                 $this->checkRecordAccess($table, $recordsToCheck);