[TASK] Convert BackendAuthentication to Doctrine API 76/47576/5
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Fri, 4 Mar 2016 16:11:53 +0000 (17:11 +0100)
committerSusanne Moog <typo3@susannemoog.de>
Wed, 13 Apr 2016 19:27:09 +0000 (21:27 +0200)
Convert all SQL statements in backend authentication classes
to the new Doctrine DBAL based API.

Releases: master
Resolves: #75546
Change-Id: I2450a9cb8947673285763b475bcf25a2dc284ee8
Reviewed-on: https://review.typo3.org/47576
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
Reviewed-by: Susanne Moog <typo3@susannemoog.de>
Tested-by: Susanne Moog <typo3@susannemoog.de>
typo3/sysext/core/Classes/Authentication/AbstractUserAuthentication.php
typo3/sysext/core/Classes/Authentication/BackendUserAuthentication.php
typo3/sysext/core/Tests/Unit/Authentication/AbstractUserAuthenticationTest.php
typo3/sysext/core/Tests/Unit/Authentication/BackendUserAuthenticationTest.php

index c5a1017..abec38e 100644 (file)
@@ -15,11 +15,15 @@ namespace TYPO3\CMS\Core\Authentication;
  */
 
 use TYPO3\CMS\Backend\Utility\BackendUtility;
+use TYPO3\CMS\Core\Crypto\Random;
+use TYPO3\CMS\Core\Database\Connection;
+use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Database\DatabaseConnection;
+use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Core\Exception;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 use TYPO3\CMS\Core\Utility\MathUtility;
-use TYPO3\CMS\Core\Crypto\Random;
 
 /**
  * Authentication of users in TYPO3
@@ -824,11 +828,10 @@ abstract class AbstractUserAuthentication
         $oldSessionId = $this->id;
         $this->id = $this->createSessionId();
         // Update session record with new ID
-        $this->db->exec_UPDATEquery(
+        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
             $this->session_table,
-            'ses_id = ' . $this->db->fullQuoteStr($oldSessionId, $this->session_table)
-                . ' AND ses_name = ' . $this->db->fullQuoteStr($this->name, $this->session_table),
-            array('ses_id' => $this->id)
+            ['ses_id' => $this->id],
+            ['ses_id' => $oldSessionId, 'ses_name' => $this->name]
         );
         $this->newSessionID = true;
     }
@@ -851,16 +854,17 @@ abstract class AbstractUserAuthentication
             GeneralUtility::devLog('Create session ses_id = ' . $this->id, AbstractUserAuthentication::class);
         }
         // Delete session entry first
-        $this->db->exec_DELETEquery(
+        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table);
+        $connection->delete(
             $this->session_table,
-            'ses_id = ' . $this->db->fullQuoteStr($this->id, $this->session_table)
-                . ' AND ses_name = ' . $this->db->fullQuoteStr($this->name, $this->session_table)
+            ['ses_id' => $this->id, 'ses_name' => $this->name]
         );
+
         // Re-create session entry
         $insertFields = $this->getNewSessionRecord($tempuser);
-        $inserted = (bool)$this->db->exec_INSERTquery($this->session_table, $insertFields);
+        $inserted = (bool)$connection->insert($this->session_table, $insertFields);
         if (!$inserted) {
-            $message = 'Session data could not be written to DB. Error: ' . $this->db->sql_error();
+            $message = 'Session data could not be written to DB. Error: ' . $connection->errorInfo();
             GeneralUtility::sysLog($message, 'core', GeneralUtility::SYSLOG_SEVERITY_WARNING);
             if ($this->writeDevLog) {
                 GeneralUtility::devLog($message, AbstractUserAuthentication::class, 2);
@@ -868,14 +872,15 @@ abstract class AbstractUserAuthentication
         }
         // Updating lastLogin_column carrying information about last login.
         if ($this->lastLogin_column && $inserted) {
-            $this->db->exec_UPDATEquery(
+            $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->user_table);
+            $connection->update(
                 $this->user_table,
-                $this->userid_column . '=' . $this->db->fullQuoteStr($tempuser[$this->userid_column], $this->user_table),
-                array($this->lastLogin_column => $GLOBALS['EXEC_TIME'])
+                [$this->lastLogin_column => $GLOBALS['EXEC_TIME']],
+                [$this->userid_column => $tempuser[$this->userid_column]]
             );
         }
 
-        return $inserted ? $insertFields : array();
+        return $inserted ? $insertFields : [];
     }
 
     /**
@@ -912,13 +917,8 @@ abstract class AbstractUserAuthentication
         }
 
         // Fetch the user session from the DB
-        $statement = $this->fetchUserSessionFromDB();
+        $user = $this->fetchUserSessionFromDB();
 
-        if ($statement) {
-            $statement->execute();
-            $user = $statement->fetch();
-            $statement->free();
-        }
         if ($user) {
             // A user was found
             $user['ses_tstamp'] = (int)$user['ses_tstamp'];
@@ -934,8 +934,11 @@ abstract class AbstractUserAuthentication
             if ($timeout > 0 && $GLOBALS['EXEC_TIME'] < $user['ses_tstamp'] + $timeout) {
                 $sessionUpdateGracePeriod = 61;
                 if (!$skipSessionUpdate && $GLOBALS['EXEC_TIME'] > ($user['ses_tstamp'] + $sessionUpdateGracePeriod)) {
-                    $this->db->exec_UPDATEquery($this->session_table, 'ses_id=' . $this->db->fullQuoteStr($this->id, $this->session_table)
-                        . ' AND ses_name=' . $this->db->fullQuoteStr($this->name, $this->session_table), array('ses_tstamp' => $GLOBALS['EXEC_TIME']));
+                    GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
+                        $this->session_table,
+                        ['ses_tstamp' => $GLOBALS['EXEC_TIME']],
+                        ['ses_id' => $this->id, 'ses_name' => $this->name]
+                    );
                     // Make sure that the timestamp is also updated in the array
                     $user['ses_tstamp'] = $GLOBALS['EXEC_TIME'];
                 }
@@ -971,8 +974,12 @@ abstract class AbstractUserAuthentication
                 }
             }
         }
-        $this->db->exec_DELETEquery($this->session_table, 'ses_id = ' . $this->db->fullQuoteStr($this->id, $this->session_table) . '
-                                               AND ses_name = ' . $this->db->fullQuoteStr($this->name, $this->session_table));
+
+        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->delete(
+            $this->session_table,
+            ['ses_id' => $this->id, 'ses_name' => $this->name]
+        );
+
         $this->user = null;
         // Hook for post-processing the logoff() method, requested and implemented by andreas.otto@dkd.de:
         if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_userauth.php']['logoff_post_processing'])) {
@@ -1008,11 +1015,14 @@ abstract class AbstractUserAuthentication
      */
     public function isExistingSessionRecord($id)
     {
-        $statement = $this->db->prepare_SELECTquery('COUNT(*)', $this->session_table, 'ses_id = :ses_id');
-        $statement->execute(array(':ses_id' => $id));
-        $row = $statement->fetch(\TYPO3\CMS\Core\Database\PreparedStatement::FETCH_NUM);
-        $statement->free();
-        return (bool)$row[0];
+        $conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table);
+        $count = $conn->count(
+            '*',
+            $this->session_table,
+            ['ses_id' => $id]
+        );
+
+        return (bool)$count;
     }
 
     /**
@@ -1038,25 +1048,104 @@ abstract class AbstractUserAuthentication
      * then don't evaluate with the hashLockClause, as the client/browser is included in this hash
      * and thus, the flash request would be rejected
      *
-     * @return \TYPO3\CMS\Core\Database\PreparedStatement
+     * @return array|false
      * @access private
      */
     protected function fetchUserSessionFromDB()
     {
-        $statement = null;
-        $ipLockClause = $this->ipLockClause();
-        $statement = $this->db->prepare_SELECTquery('*', $this->session_table . ',' . $this->user_table, $this->session_table . '.ses_id = :ses_id
-                                       AND ' . $this->session_table . '.ses_name = :ses_name
-                                       AND ' . $this->session_table . '.ses_userid = ' . $this->user_table . '.' . $this->userid_column . '
-                                       ' . $ipLockClause['where'] . '
-                                       ' . $this->hashLockClause() . '
-                                       ' . $this->user_where_clause());
-        $statement->bindValues(array(
-                ':ses_id' => $this->id,
-                ':ses_name' => $this->name
-            ));
-        $statement->bindValues($ipLockClause['parameters']);
-        return $statement;
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+            ->getQueryBuilderForTable($this->session_table);
+        $queryBuilder->select('*')
+            ->from($this->session_table)
+            ->from($this->user_table)
+            ->where(
+                $queryBuilder->expr()->eq(
+                    $this->session_table . '.ses_id',
+                    $queryBuilder->createNamedParameter($this->id)
+                )
+            )
+            ->andWhere(
+                $queryBuilder->expr()->eq(
+                    $this->session_table . '.ses_name',
+                    $queryBuilder->createNamedParameter($this->name)
+                )
+            )
+            // Condition on which to join the session and user table
+            ->andWhere(
+                $queryBuilder->expr()->eq(
+                    $this->session_table . '.ses_userid',
+                    $queryBuilder->quoteIdentifier($this->user_table . '.' . $this->userid_column)
+                )
+            )
+            ->andWhere(
+                $queryBuilder->expr()->eq(
+                    $this->session_table . '.ses_hashlock',
+                    $queryBuilder->createNamedParameter($this->hashLockClause_getHashInt())
+                )
+            )
+            ->andWhere($this->userConstraints($queryBuilder->expr()));
+
+        if ($this->lockIP) {
+            $queryBuilder->andWhere(
+                $queryBuilder->expr()->in(
+                    $this->session_table . '.ses_iplock',
+                    $queryBuilder->createNamedParameter(
+                        [$this->ipLockClause_remoteIPNumber($this->lockIP), '[DISABLED]'],
+                        Connection::PARAM_STR_ARRAY // Automatically expand the array into multiple named parameters
+                    )
+                )
+            );
+        }
+
+        // Force the fetch mode to ensure we get back an array independently of the default fetch mode.
+        return $queryBuilder->execute()->fetch(\PDO::FETCH_ASSOC);
+    }
+
+    /**
+     * @param ExpressionBuilder $expressionBuilder
+     * @param string $tableAlias
+     * @return \Doctrine\DBAL\Query\Expression\CompositeExpression
+     * @internal
+     */
+    protected function userConstraints(
+        ExpressionBuilder $expressionBuilder,
+        string $tableAlias = ''
+    ): \Doctrine\DBAL\Query\Expression\CompositeExpression {
+        if ($tableAlias === '') {
+            $tableAlias = $this->user_table;
+        }
+
+        $constraints = $expressionBuilder->andX();
+        if ($this->enablecolumns['rootLevel']) {
+            $constraints->add(
+                $expressionBuilder->eq($tableAlias . '.pid', 0)
+            );
+        }
+        if ($this->enablecolumns['disabled']) {
+            $constraints->add(
+                $expressionBuilder->eq($tableAlias . '.' . $this->enablecolumns['disabled'], 0)
+            );
+        }
+        if ($this->enablecolumns['deleted']) {
+            $constraints->add(
+                $expressionBuilder->eq($tableAlias . '.' . $this->enablecolumns['deleted'], 0)
+            );
+        }
+        if ($this->enablecolumns['starttime']) {
+            $constraints->add(
+                $expressionBuilder->lte($tableAlias . '.' . $this->enablecolumns['starttime'], $GLOBALS['EXEC_TIME'])
+            );
+        }
+        if ($this->enablecolumns['endtime']) {
+            $constraints->add(
+                $expressionBuilder->orX(
+                    $expressionBuilder->eq($tableAlias . '.' . $this->enablecolumns['endtime'], 0),
+                    $expressionBuilder->gt($tableAlias . '.' . $this->enablecolumns['endtime'], $GLOBALS['EXEC_TIME'])
+                )
+            );
+        }
+
+        return $constraints;
     }
 
     /**
@@ -1065,9 +1154,12 @@ abstract class AbstractUserAuthentication
      *
      * @return string
      * @access private
+     * @deprecated since TYPO3 v8, will be removed in TYPO3 v9
      */
     protected function user_where_clause()
     {
+        GeneralUtility::logDeprecatedFunction();
+
         $whereClause = '';
         if ($this->enablecolumns['rootLevel']) {
             $whereClause .= 'AND ' . $this->user_table . '.pid=0 ';
@@ -1093,9 +1185,11 @@ abstract class AbstractUserAuthentication
      *
      * @return array
      * @access private
+     * @deprecated since TYPO3 v8, will be removed in TYPO3 v9
      */
     protected function ipLockClause()
     {
+        GeneralUtility::logDeprecatedFunction();
         $statementClause = array(
             'where' => '',
             'parameters' => array()
@@ -1183,7 +1277,7 @@ abstract class AbstractUserAuthentication
      * You can fetch the data again through $this->uc in this class!
      * If $variable is not an array, $this->uc is saved!
      *
-     * @param array|string $variable An array you want to store for the user as session data. If $variable is not supplied (is blank string), the internal variable, ->uc, is stored by default
+     * @param array|string $variable An array you want to store for the user as session data. If $variable is not supplied (is null), the internal variable, ->uc, is stored by default
      * @return void
      */
     public function writeUC($variable = '')
@@ -1193,9 +1287,16 @@ abstract class AbstractUserAuthentication
                 $variable = $this->uc;
             }
             if ($this->writeDevLog) {
-                GeneralUtility::devLog('writeUC: ' . $this->userid_column . '=' . (int)$this->user[$this->userid_column], AbstractUserAuthentication::class);
+                GeneralUtility::devLog(
+                    'writeUC: ' . $this->userid_column . '=' . (int)$this->user[$this->userid_column],
+                    AbstractUserAuthentication::class
+                );
             }
-            $this->db->exec_UPDATEquery($this->user_table, $this->userid_column . '=' . (int)$this->user[$this->userid_column], array('uc' => serialize($variable)));
+            GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
+                $this->user_table,
+                ['uc' => serialize($variable)],
+                [$this->userid_column => (int)$this->user[$this->userid_column]]
+            );
         }
     }
 
@@ -1279,7 +1380,11 @@ abstract class AbstractUserAuthentication
         if ($this->writeDevLog) {
             GeneralUtility::devLog('setAndSaveSessionData: ses_id = ' . $this->user['ses_id'], AbstractUserAuthentication::class);
         }
-        $this->db->exec_UPDATEquery($this->session_table, 'ses_id=' . $this->db->fullQuoteStr($this->user['ses_id'], $this->session_table), array('ses_data' => $this->user['ses_data']));
+        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
+            $this->session_table,
+            ['ses_data' => $this->user['ses_data']],
+            ['ses_id' => $this->user['ses_id']]
+        );
     }
 
     /*************************
@@ -1364,6 +1469,8 @@ abstract class AbstractUserAuthentication
      */
     public function getAuthInfoArray()
     {
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->user_table);
+        $expressionBuilder = $queryBuilder->expr();
         $authInfo = array();
         $authInfo['loginType'] = $this->loginType;
         $authInfo['refInfo'] = parse_url(GeneralUtility::getIndpEnv('HTTP_REFERER'));
@@ -1377,11 +1484,13 @@ abstract class AbstractUserAuthentication
         $authInfo['db_user']['username_column'] = $this->username_column;
         $authInfo['db_user']['userident_column'] = $this->userident_column;
         $authInfo['db_user']['usergroup_column'] = $this->usergroup_column;
-        $authInfo['db_user']['enable_clause'] = $this->user_where_clause();
+        $authInfo['db_user']['enable_clause'] = $this->userConstraints($expressionBuilder);
         if ($this->checkPid && $this->checkPid_value !== null) {
             $authInfo['db_user']['checkPidList'] = $this->checkPid_value;
-            $authInfo['db_user']['check_pid_clause'] = ' AND pid IN (' .
-                $this->db->cleanIntList($this->checkPid_value) . ')';
+            $authInfo['db_user']['check_pid_clause'] = $expressionBuilder->in(
+                'pid',
+                GeneralUtility::intExplode(',', $this->checkPid_value)
+            );
         } else {
             $authInfo['db_user']['checkPidList'] = '';
             $authInfo['db_user']['check_pid_clause'] = '';
@@ -1411,7 +1520,21 @@ abstract class AbstractUserAuthentication
      */
     public function gc()
     {
-        $this->db->exec_DELETEquery($this->session_table, 'ses_tstamp < ' . (int)($GLOBALS['EXEC_TIME'] - $this->gc_time) . ' AND ses_name = ' . $this->db->fullQuoteStr($this->name, $this->session_table));
+        $query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->session_table);
+        $query->delete($this->session_table)
+            ->where(
+                $query->expr()->lt(
+                    'ses_tstamp',
+                    $query->createNamedParameter((int)($GLOBALS['EXEC_TIME'] - $this->gc_time))
+                )
+            )
+            ->andWhere(
+                $query->expr()->eq(
+                    'ses_name',
+                    $query->createNamedParameter($this->name)
+                )
+            )
+            ->execute();
     }
 
     /**
@@ -1485,13 +1608,13 @@ abstract class AbstractUserAuthentication
      */
     public function getRawUserByUid($uid)
     {
-        $user = false;
-        $dbres = $this->db->exec_SELECTquery('*', $this->user_table, 'uid=' . (int)$uid . ' ' . $this->user_where_clause());
-        if ($dbres) {
-            $user = $this->db->sql_fetch_assoc($dbres);
-            $this->db->sql_free_result($dbres);
-        }
-        return $user;
+        $query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->user_table);
+        $query->select('*')
+            ->from($this->user_table)
+            ->where($query->expr()->eq('uid', $query->createNamedParameter($uid)))
+            ->andWhere($this->userConstraints($query->expr()));
+
+        return $query->execute()->fetch();
     }
 
     /**
@@ -1504,13 +1627,13 @@ abstract class AbstractUserAuthentication
      */
     public function getRawUserByName($name)
     {
-        $user = false;
-        $dbres = $this->db->exec_SELECTquery('*', $this->user_table, 'username=' . $this->db->fullQuoteStr($name, $this->user_table) . ' ' . $this->user_where_clause());
-        if ($dbres) {
-            $user = $this->db->sql_fetch_assoc($dbres);
-            $this->db->sql_free_result($dbres);
-        }
-        return $user;
+        $query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->user_table);
+        $query->select('*')
+            ->from($this->user_table)
+            ->where($query->expr()->eq('username', $query->createNamedParameter($name)))
+            ->andWhere($this->userConstraints($query->expr()));
+
+        return $query->execute()->fetch();
     }
 
     /*************************
@@ -1530,15 +1653,29 @@ abstract class AbstractUserAuthentication
     public function fetchUserRecord($dbUser, $username, $extraWhere = '')
     {
         $user = false;
-        $usernameClause = $username ? $dbUser['username_column'] . '=' . $this->db->fullQuoteStr($username, $dbUser['table']) : '1=1';
         if ($username || $extraWhere) {
-            // Look up the user by the username and/or extraWhere:
-            $dbres = $this->db->exec_SELECTquery('*', $dbUser['table'], $usernameClause . $dbUser['check_pid_clause'] . $dbUser['enable_clause'] . $extraWhere);
-            if ($dbres) {
-                $user = $this->db->sql_fetch_assoc($dbres);
-                $this->db->sql_free_result($dbres);
+            $query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($dbUser['table']);
+
+            $constraints = array_filter([
+                QueryHelper::stripLogicalOperatorPrefix($dbUser['check_pid_clause']),
+                QueryHelper::stripLogicalOperatorPrefix($dbUser['enable_clause']),
+                QueryHelper::stripLogicalOperatorPrefix($extraWhere),
+            ]);
+
+            if (!empty($username)) {
+                array_unshift(
+                    $constraints,
+                    $query->expr()->eq($dbUser['username_column'], $query->createNamedParameter($username))
+                );
             }
+
+            $user = $query->select('*')
+                ->from($dbUser['table'])
+                ->where(...$constraints)
+                ->execute()
+                ->fetch();
         }
+
         return $user;
     }
 
index ec0bcbb..1f2c457 100644 (file)
@@ -15,6 +15,8 @@ namespace TYPO3\CMS\Core\Authentication;
  */
 
 use TYPO3\CMS\Backend\Utility\BackendUtility;
+use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Core\Resource\ResourceStorage;
 use TYPO3\CMS\Core\Type\Bitmask\JsConfirmation;
 use TYPO3\CMS\Core\Type\Bitmask\Permission;
@@ -1336,8 +1338,21 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
                 $webmounts = explode(',', $this->groupData['webmounts']);
                 // Explode mounts
                 // Selecting all webmounts with permission clause for reading
-                $where = 'deleted=0 AND uid IN (' . $this->groupData['webmounts'] . ') AND ' . $this->getPagePermsClause(1);
-                $MProws = $this->db->exec_SELECTgetRows('uid', 'pages', $where, '', '', '', 'uid');
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
+                $MProws = $queryBuilder->select('uid')
+                    ->from('pages')
+                    ->where($queryBuilder->expr()->eq('deleted', 0))
+                    ->andWhere(
+                        $queryBuilder->expr()->in(
+                            'uid',
+                            $queryBuilder->createNamedParameter($this->groupData['webmounts'])
+                        )
+                    )
+                    // @todo DOCTRINE: check how to make getPagePermsClause() portable
+                    ->andWhere($this->getPagePermsClause(1))
+                    ->execute()
+                    ->fetchAll();
+                $MProws = array_column(($MProws ?: []), 'uid', 'uid');
                 foreach ($webmounts as $idx => $mountPointUid) {
                     // If the mount ID is NOT found among selected pages, unset it:
                     if ($mountPointUid > 0 && !isset($MProws[$mountPointUid])) {
@@ -1364,24 +1379,39 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
     public function fetchGroups($grList, $idList = '')
     {
         // Fetching records of the groups in $grList (which are not blocked by lockedToDomain either):
-        $lockToDomain_SQL = ' AND (lockToDomain=\'\' OR lockToDomain IS NULL OR lockToDomain=' . $this->db->fullQuoteStr(GeneralUtility::getIndpEnv('HTTP_HOST'), $this->usergroup_table) . ')';
-        $grList = $this->db->cleanIntList($grList);
-        $whereSQL = 'deleted=0 AND hidden=0 AND pid=0 AND uid IN (' . $grList . ')' . $lockToDomain_SQL;
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->usergroup_table);
+        $expressionBuilder = $queryBuilder->expr();
+        $constraints = $expressionBuilder->andX(
+            $expressionBuilder->eq('deleted', 0),
+            $expressionBuilder->eq('hidden', 0),
+            $expressionBuilder->eq('pid', 0),
+            $expressionBuilder->in('uid', GeneralUtility::intExplode(',', $grList)),
+            $expressionBuilder->orX(
+                $expressionBuilder->eq('lockToDomain', $queryBuilder->quote('')),
+                $expressionBuilder->isNull('lockToDomain'),
+                $expressionBuilder->eq(
+                    'lockToDomain',
+                    $queryBuilder->createNamedParameter(GeneralUtility::getIndpEnv('HTTP_HOST'))
+                )
+            )
+        );
         // Hook for manipulation of the WHERE sql sentence which controls which BE-groups are included
         if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_userauthgroup.php']['fetchGroupQuery'])) {
             foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_userauthgroup.php']['fetchGroupQuery'] as $classRef) {
                 $hookObj = GeneralUtility::getUserObj($classRef);
                 if (method_exists($hookObj, 'fetchGroupQuery_processQuery')) {
-                    $whereSQL = $hookObj->fetchGroupQuery_processQuery($this, $grList, $idList, $whereSQL);
+                    $constraints = $hookObj->fetchGroupQuery_processQuery($this, $grList, $idList, (string)$constraints);
                 }
             }
         }
-        $res = $this->db->exec_SELECTquery('*', $this->usergroup_table, $whereSQL);
+        $res = $queryBuilder->select('*')
+            ->from($this->usergroup_table)
+            ->where($constraints)
+            ->execute();
         // The userGroups array is filled
-        while ($row = $this->db->sql_fetch_assoc($res)) {
+        while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
             $this->userGroups[$row['uid']] = $row;
         }
-        $this->db->sql_free_result($res);
         // Traversing records in the correct order
         foreach (explode(',', $grList) as $uid) {
             // Get row:
@@ -1448,7 +1478,11 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
     public function setCachedList($cList)
     {
         if ((string)$cList != (string)$this->user['usergroup_cached_list']) {
-            $this->db->exec_UPDATEquery('be_users', 'uid=' . (int)$this->user['uid'], array('usergroup_cached_list' => $cList));
+            GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('be_users')->update(
+                'be_users',
+                ['usergroup_cached_list' => $cList],
+                ['uid' => (int)$this->user['uid']]
+            );
         }
     }
 
@@ -1533,6 +1567,8 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
             return $fileMountRecordCache;
         }
 
+        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
+
         // Processing file mounts (both from the user and the groups)
         $fileMounts = array_unique(GeneralUtility::intExplode(',', $this->dataLists['filemount_list'], true));
 
@@ -1543,18 +1579,25 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
         }
 
         if (!empty($fileMounts)) {
-            $orderBy = isset($GLOBALS['TCA']['sys_filemounts']['ctrl']['default_sortby'])
-                ? $this->db->stripOrderBy($GLOBALS['TCA']['sys_filemounts']['ctrl']['default_sortby'])
-                : 'sorting';
-            $fileMountRecords = $this->db->exec_SELECTgetRows(
-                '*',
-                'sys_filemounts',
-                'deleted=0 AND hidden=0 AND pid=0 AND uid IN (' . implode(',', $fileMounts) . ')',
-                '',
-                $orderBy
-            );
-            foreach ($fileMountRecords as $fileMount) {
-                $fileMountRecordCache[$fileMount['base'] . $fileMount['path']] = $fileMount;
+            $orderBy = $GLOBALS['TCA']['sys_filemounts']['ctrl']['default_sortby'] ?? 'sorting';
+
+            $queryBuilder = $connectionPool->getQueryBuilderForTable('sys_filemounts');
+            $queryBuilder->select('*')
+                ->from('sys_filemounts')
+                ->where($queryBuilder->expr()->eq('deleted', 0))
+                ->andWhere($queryBuilder->expr()->eq('hidden', 0))
+                ->andWhere($queryBuilder->expr()->eq('pid', 0))
+                ->andWhere($queryBuilder->expr()->in('uid', $queryBuilder->createNamedParameter($fileMounts)));
+
+            foreach (QueryHelper::parseOrderBy($orderBy) as $fieldAndDirection) {
+                $queryBuilder->addOrderBy(...$fieldAndDirection);
+            }
+
+            $fileMountRecords = $queryBuilder->execute()->fetchAll(\PDO::FETCH_ASSOC);
+            if ($fileMountRecords !== false) {
+                foreach ($fileMountRecords as $fileMount) {
+                    $fileMountRecordCache[$fileMount['base'] . $fileMount['path']] = $fileMount;
+                }
             }
         }
 
@@ -1563,8 +1606,14 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
         if ($readOnlyMountPoints) {
             // We cannot use the API here but need to fetch the default storage record directly
             // to not instantiate it (which directly applies mount points) before all mount points are resolved!
-            $whereClause = 'is_default=1 ' . BackendUtility::BEenableFields('sys_file_storage') . BackendUtility::deleteClause('sys_file_storage');
-            $defaultStorageRow = $this->db->exec_SELECTgetSingleRow('uid', 'sys_file_storage', $whereClause);
+            $queryBuilder = $connectionPool->getQueryBuilderForTable('sys_file_storage');
+            $defaultStorageRow = $queryBuilder->select('uid')
+                ->from('sys_file_storage')
+                ->where($queryBuilder->expr()->eq('is_default', 1))
+                ->setMaxResults(1)
+                ->execute()
+                ->fetch(\PDO::FETCH_ASSOC);
+
             $readOnlyMountPointArray = GeneralUtility::trimExplode(',', $readOnlyMountPoints);
             foreach ($readOnlyMountPointArray as $readOnlyMountPoint) {
                 $readOnlyMountPointConfiguration = GeneralUtility::trimExplode(':', $readOnlyMountPoint);
@@ -1975,12 +2024,20 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
                     break;
                 default:
                     if (\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::isLoaded('workspaces')) {
-                        $wsRec = $this->db->exec_SELECTgetSingleRow($fields,
-                            'sys_workspace',
-                            'pid=0 AND uid=' . (int)$wsRec . BackendUtility::deleteClause('sys_workspace'),
-                            '',
-                            'title'
-                        );
+                        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_workspace');
+                        $wsRec = $queryBuilder->select(...GeneralUtility::trimExplode(',', $fields))
+                            ->from('sys_workspace')
+                            ->where($queryBuilder->expr()->eq('pid', 0))
+                            ->andWhere(
+                                $queryBuilder->expr()->eq(
+                                    'uid',
+                                    $queryBuilder->createNamedParameter((int)$wsRec)
+                                )
+                            )
+                            ->orderBy('title')
+                            ->setMaxResults(1)
+                            ->execute()
+                            ->fetch(\PDO::FETCH_ASSOC);
                     }
             }
         }
@@ -2064,7 +2121,11 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
         // If ID is different from the stored one, change it:
         if ((int)$this->workspace !== (int)$this->user['workspace_id']) {
             $this->user['workspace_id'] = $this->workspace;
-            $this->db->exec_UPDATEquery('be_users', 'uid=' . (int)$this->user['uid'], array('workspace_id' => $this->user['workspace_id']));
+            GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('be_users')->update(
+                'be_users',
+                ['workspace_id' => $this->user['workspace_id']],
+                ['uid' => (int)$this->user['uid']]
+            );
             $this->simplelog('User changed workspace to "' . $this->workspace . '"');
         }
     }
@@ -2109,7 +2170,11 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
     public function setWorkspacePreview($previewState)
     {
         $this->user['workspace_preview'] = $previewState;
-        $this->db->exec_UPDATEquery('be_users', 'uid=' . (int)$this->user['uid'], array('workspace_preview' => $this->user['workspace_preview']));
+        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('be_users')->update(
+            'be_users',
+            ['workspace_preview_id' => $this->user['workspace_preview']],
+            ['uid' => (int)$this->user['uid']]
+        );
     }
 
     /**
@@ -2130,11 +2195,20 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
             $defaultWorkspace = -1;
         } elseif (\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::isLoaded('workspaces')) {
             // Traverse custom workspaces:
-            $workspaces = $this->db->exec_SELECTgetRows('uid,title,adminusers,members,reviewers', 'sys_workspace', 'pid=0' . BackendUtility::deleteClause('sys_workspace'), '', 'title');
-            foreach ($workspaces as $rec) {
-                if ($this->checkWorkspace($rec)) {
-                    $defaultWorkspace = $rec['uid'];
-                    break;
+            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_workspace');
+            $workspaces = $queryBuilder->select('uid', 'title', 'adminusers', 'reviewers')
+                ->from('sys_workspace')
+                ->where($queryBuilder->expr()->eq('pid', 0))
+                ->orderBy('title')
+                ->execute()
+                ->fetchAll(\PDO::FETCH_ASSOC);
+
+            if ($workspaces !== false) {
+                foreach ($workspaces as $rec) {
+                    if ($this->checkWorkspace($rec)) {
+                        $defaultWorkspace = $rec['uid'];
+                        break;
+                    }
                 }
             }
         }
@@ -2172,7 +2246,7 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
             $data['originalUser'] = $this->user['ses_backuserid'];
         }
 
-        $fields_values = array(
+        $fields = array(
             'userid' => (int)$userId,
             'type' => (int)$type,
             'action' => (int)$action,
@@ -2188,8 +2262,30 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
             'NEWid' => $NEWid,
             'workspace' => $this->workspace
         );
-        $this->db->exec_INSERTquery('sys_log', $fields_values);
-        return $this->db->sql_insert_id();
+
+        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('be_users');
+        $connection->insert(
+            'sys_log',
+            $fields,
+            [
+                \PDO::PARAM_INT,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_STR,
+                \PDO::PARAM_STR,
+                \PDO::PARAM_STR,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_STR,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_INT,
+                \PDO::PARAM_STR,
+                \PDO::PARAM_STR,
+            ]
+        );
+
+        return (int)$connection->lastInsertId();
     }
 
     /**
@@ -2220,30 +2316,48 @@ class BackendUserAuthentication extends \TYPO3\CMS\Core\Authentication\AbstractU
     public function checkLogFailures($email, $secondsBack = 3600, $max = 3)
     {
         if ($email) {
+            $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
+
             // Get last flag set in the log for sending
             $theTimeBack = $GLOBALS['EXEC_TIME'] - $secondsBack;
-            $res = $this->db->exec_SELECTquery('tstamp', 'sys_log', 'type=255 AND action=4 AND tstamp>' . (int)$theTimeBack, '', 'tstamp DESC', '1');
-            if ($testRow = $this->db->sql_fetch_assoc($res)) {
+            $queryBuilder = $connectionPool->getQueryBuilderForTable('sys_log');
+            $queryBuilder->select('tstamp')
+                ->from('sys_log')
+                ->where($queryBuilder->expr()->eq('type', 255))
+                ->andWhere($queryBuilder->expr()->eq('action', 4))
+                ->andWhere($queryBuilder->expr()->gt('tstamp', $queryBuilder->createNamedParameter((int)$theTimeBack)))
+                ->orderBy('tstamp', 'DESC')
+                ->setMaxResults(1);
+            if ($testRow = $queryBuilder->execute()->fetch(\PDO::FETCH_ASSOC)) {
                 $theTimeBack = $testRow['tstamp'];
             }
-            $this->db->sql_free_result($res);
+
+            $queryBuilder = $connectionPool->getQueryBuilderForTable('sys_log');
+            $result = $queryBuilder->select('*')
+                ->from('sys_log')
+                ->where($queryBuilder->expr()->eq('type', 255))
+                ->andWhere($queryBuilder->expr()->eq('action', 3))
+                ->andWhere($queryBuilder->expr()->neq('error', 0))
+                ->andWhere($queryBuilder->expr()->gt('tstamp', $queryBuilder->createNamedParameter((int)$theTimeBack)))
+                ->orderBy('tstamp')
+                ->execute();
+
             // Check for more than $max number of error failures with the last period.
-            $res = $this->db->exec_SELECTquery('*', 'sys_log', 'type=255 AND action=3 AND error<>0 AND tstamp>' . (int)$theTimeBack, '', 'tstamp');
-            if ($this->db->sql_num_rows($res) > $max) {
+            if ($result->rowCount() > $max) {
                 // OK, so there were more than the max allowed number of login failures - so we will send an email then.
                 $subject = 'TYPO3 Login Failure Warning (at ' . $GLOBALS['TYPO3_CONF_VARS']['SYS']['sitename'] . ')';
-                $email_body = 'There have been some attempts (' . $this->db->sql_num_rows($res) . ') to login at the TYPO3
+                $email_body = 'There have been some attempts (' . $result->rowCount() . ') to login at the TYPO3
 site "' . $GLOBALS['TYPO3_CONF_VARS']['SYS']['sitename'] . '" (' . GeneralUtility::getIndpEnv('HTTP_HOST') . ').
 
 This is a dump of the failures:
 
 ';
-                while ($testRows = $this->db->sql_fetch_assoc($res)) {
-                    $theData = unserialize($testRows['log_data']);
+                while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
+                    $theData = unserialize($row['log_data']);
                     $email_body .= date(
                             $GLOBALS['TYPO3_CONF_VARS']['SYS']['ddmmyy'] . ' ' . $GLOBALS['TYPO3_CONF_VARS']['SYS']['hhmm'],
-                            $testRows['tstamp']
-                        ) . ':  ' . @sprintf($testRows['details'], (string)$theData[0], (string)$theData[1], (string)$theData[2]);
+                            $row['tstamp']
+                        ) . ':  ' . @sprintf($row['details'], (string)$theData[0], (string)$theData[1], (string)$theData[2]);
                     $email_body .= LF;
                 }
                 $from = \TYPO3\CMS\Core\Utility\MailUtility::getSystemFrom();
@@ -2252,8 +2366,7 @@ This is a dump of the failures:
                 $mail->setTo($email)->setFrom($from)->setSubject($subject)->setBody($email_body);
                 $mail->send();
                 // Logout written to log
-                $this->writelog(255, 4, 0, 3, 'Failure warning (%s failures within %s seconds) sent by email to %s', array($this->db->sql_num_rows($res), $secondsBack, $email));
-                $this->db->sql_free_result($res);
+                $this->writelog(255, 4, 0, 3, 'Failure warning (%s failures within %s seconds) sent by email to %s', array($result->rowCount(), $secondsBack, $email));
             }
         }
     }
@@ -2485,10 +2598,13 @@ This is a dump of the failures:
             $isUserAllowedToLogin = true;
         } elseif ($this->user['ses_backuserid']) {
             $backendUserId = (int)$this->user['ses_backuserid'];
-            $whereAdmin = 'uid=' . $backendUserId . ' AND admin=1' . BackendUtility::BEenableFields('be_users');
-            if ($this->db->exec_SELECTcountRows('uid', 'be_users', $whereAdmin) > 0) {
-                $isUserAllowedToLogin = true;
-            }
+            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('be_users');
+            $isUserAllowedToLogin = (bool)$queryBuilder->count('uid')
+                ->from('be_users')
+                ->where($queryBuilder->expr()->eq('uid', $queryBuilder->createNamedParameter($backendUserId)))
+                ->andWhere($queryBuilder->expr()->eq('admin', 1))
+                ->execute()
+                ->fetchColumn(0);
         }
         return $isUserAllowedToLogin;
     }
index d64c6cd..045a906 100644 (file)
@@ -14,6 +14,15 @@ namespace TYPO3\CMS\Core\Tests\Unit\Authentication;
  * The TYPO3 project - inspiring people to share!
  */
 
+use Prophecy\Argument;
+use Prophecy\Prophecy\ObjectProphecy;
+use TYPO3\CMS\Core\Database\Connection;
+use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
+use TYPO3\CMS\Core\Database\Query\QueryBuilder;
+use TYPO3\CMS\Core\Tests\Unit\Database\Mocks\MockPlatform;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
 /**
  * Testcase for class \TYPO3\CMS\Core\Authentication\AbstractUserAuthentication
  *
@@ -25,13 +34,29 @@ class AbstractUserAuthenticationTest extends \TYPO3\CMS\Core\Tests\UnitTestCase
      */
     public function getAuthInfoArrayReturnsEmptyPidListIfNoCheckPidValueIsGiven()
     {
-        $GLOBALS['TYPO3_DB'] = $this->getMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('cleanIntList'));
-        $GLOBALS['TYPO3_DB']->expects($this->never())->method('cleanIntList');
+        /** @var Connection|ObjectProphecy $connection */
+        $connection = $this->prophesize(Connection::class);
+        $connection->getDatabasePlatform()->willReturn(new MockPlatform());
+        $connection->getExpressionBuilder()->willReturn(new ExpressionBuilder($connection->reveal()));
+
+        $queryBuilder = GeneralUtility::makeInstance(
+            QueryBuilder::class,
+            $connection->reveal(),
+            null,
+            $this->prophesize(\Doctrine\DBAL\Query\QueryBuilder::class)->reveal()
+        );
+
+        /** @var ConnectionPool|ObjectProphecy $connection */
+        $connectionPool = $this->prophesize(ConnectionPool::class);
+        $connectionPool->getQueryBuilderForTable(Argument::cetera())->willReturn($queryBuilder);
+
+        GeneralUtility::addInstance(ConnectionPool::class, $connectionPool->reveal());
 
         /** @var $mock \TYPO3\CMS\Core\Authentication\AbstractUserAuthentication */
         $mock = $this->getMock(\TYPO3\CMS\Core\Authentication\AbstractUserAuthentication::class, array('dummy'));
         $mock->checkPid = true;
         $mock->checkPid_value = null;
+        $mock->user_table = 'be_users';
         $result = $mock->getAuthInfoArray();
         $this->assertEquals('', $result['db_user']['checkPidList']);
     }
index 6be323a..29fd572 100644 (file)
@@ -14,8 +14,13 @@ namespace TYPO3\CMS\Core\Tests\Unit\Authentication;
  * The TYPO3 project - inspiring people to share!
  */
 
+use Prophecy\Argument;
+use Prophecy\Prophecy\ObjectProphecy;
 use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
+use TYPO3\CMS\Core\Database\Connection;
+use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Type\Bitmask\JsConfirmation;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
 
 /**
  * Testcase for BackendUserAuthentication
@@ -65,18 +70,23 @@ class BackendUserAuthenticationTest extends \TYPO3\CMS\Core\Tests\UnitTestCase
      */
     public function logoffCleansFormProtectionIfBackendUserIsLoggedIn()
     {
-        $formProtection = $this->getMock(
-            \TYPO3\CMS\Core\FormProtection\BackendFormProtection::class,
-            array('clean'),
-            array(),
-            '',
-            false
-        );
-        $formProtection->expects($this->once())->method('clean');
+        /** @var ObjectProphecy|Connection $connection */
+        $connection = $this->prophesize(Connection::class);
+        $connection->delete('be_sessions', Argument::cetera())->willReturn(1);
+
+        /** @var ObjectProphecy|ConnectionPool $connectionPool */
+        $connectionPool = $this->prophesize(ConnectionPool::class);
+        $connectionPool->getConnectionForTable(Argument::cetera())->willReturn($connection->reveal());
+
+        GeneralUtility::addInstance(ConnectionPool::class, $connectionPool->reveal());
+
+        /** @var ObjectProphecy|\TYPO3\CMS\Core\FormProtection\AbstractFormProtection $formProtection */
+        $formProtection = $this->prophesize(\TYPO3\CMS\Core\FormProtection\BackendFormProtection::class);
+        $formProtection->clean()->shouldBeCalled();
 
         \TYPO3\CMS\Core\FormProtection\FormProtectionFactory::set(
             'default',
-            $formProtection
+            $formProtection->reveal()
         );
 
         // logoff() call the static factory that has a dependency to a valid BE_USER object. Mock this away
@@ -236,7 +246,7 @@ class BackendUserAuthenticationTest extends \TYPO3\CMS\Core\Tests\UnitTestCase
      */
     public function getTSConfigReturnsCorrectArrayForGivenObjectString(array $completeConfiguration, $objectString, array $expectedConfiguration)
     {
-        $subject = $this->getMock(BackendUserAuthentication::class, array('dummy'), array(), '', FALSE);
+        $subject = $this->getMock(BackendUserAuthentication::class, array('dummy'), array(), '', false);
         $subject->userTS = $completeConfiguration;
 
         $actualConfiguration = $subject->getTSConfig($objectString);