/** * @test * * @return void */ public function prepareSelectQueryCreateValidQuery() { $this->assertTrue($this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('aTestValue')")); $preparedQuery = $this->subject->prepare_SELECTquery("{$this->testField},{$this->anotherTestField}", $this->testTable, 'id=:id', '', '', '', [':id' => 1]); $preparedQuery->execute(); $result = $preparedQuery->fetch(); $expectedResult = [$this->testField => 'aTestValue', $this->anotherTestField => null]; $this->assertSame($expectedResult, $result); }
/** * Fetches the rows from the database, using prepared statement * * @param array $statementParts * @param array $parameters * @return array the result */ protected function getRowsFromPreparedDatabase(array $statementParts, array $parameters) { $queryCommandParameters = $this->createQueryCommandParametersFromStatementParts($statementParts); $preparedStatement = $this->databaseHandle->prepare_SELECTquery($queryCommandParameters['selectFields'], $queryCommandParameters['fromTable'], $queryCommandParameters['whereClause'], '', $queryCommandParameters['orderBy'], $queryCommandParameters['limit']); $preparedStatement->execute($parameters); $rows = $preparedStatement->fetchAll(); $preparedStatement->free(); return $rows; }
/** * The session_id is used to find user in the database. * Two tables are joined: The session-table with user_id of the session and the usertable with its primary key * if the client is flash (e.g. from a flash application inside TYPO3 that does a server request) * 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 * @access private */ protected function fetchUserSessionFromDB() { $statement = NULL; $ipLockClause = $this->ipLockClause(); if ($GLOBALS['CLIENT']['BROWSER'] == 'flash') { // If on the flash client, the veri code is valid, then the user session is fetched // from the DB without the hashLock clause if (GeneralUtility::_GP('vC') == $this->veriCode()) { $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->user_where_clause()); $statement->bindValues(array(':ses_id' => $this->id, ':ses_name' => $this->name)); $statement->bindValues($ipLockClause['parameters']); } } else { $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; }
/** * The session_id is used to find user in the database. * Two tables are joined: The session-table with user_id of the session and the usertable with its primary key * if the client is flash (e.g. from a flash application inside TYPO3 that does a server request) * 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 * @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; }
/** * Method checks whether the given session collides with an existing one. * Checks if an associated speaker is associated to another session which: * - starts during the given session * - ends during the given session * - start at the same time as the given session * - ends at the same time as the given session * - surrounds the given session completely * * @param AbstractSession $session * @param array $exclude array of uids which should be exluded in the check (the given session is excluded by default) * @return array|false colliding sessions or false if no session collides * @throws \InvalidArgumentException */ public function getCollidingSessions(AbstractSession $session, $exclude = []) { if (!is_array($exclude)) { throw new \InvalidArgumentException('$exclude is not of type array. should be an array of uids'); } $speakers = $session->getSpeakers(); // array holding named placeholder values $params = []; // helper in order to prepare a dynamic IN statement $inStmt = []; $i = 0; foreach ($speakers as $speaker) { /** @var \TYPO3\CMS\Extbase\Domain\Model\FrontendUser $speaker */ // build unique placeholder name $placeholder = ':idref' . $i++; // assign the correct uid to the placeholder $params[$placeholder] = $speaker->getUid(); // "store" the dynamic placeholder $inStmt[] = $placeholder . ' '; } // flatten the dynamic placeholders for later IN statement use $inStmt = implode(',', $inStmt); // helper for dynamic NOT IN statement if (count($exclude) > 0) { $i = 0; $excludeUids = array_merge([$session->getUid()], $exclude); $excludeInStmt = []; foreach ($excludeUids as $eUid) { // build unique placeholder name $placeholder = ':excludessessions' . $i++; // assign the correct uid to the placeholder $params[$placeholder] = $eUid; // store the dynamic placeholder for later $excludeInStmt[] = $placeholder; } // flatten dynamic placeholders for usage in IN statement $excludeInStmt = implode(', ', $excludeInStmt); } // set the rest of param values (respect DMBS datetime format) $params[':start'] = $session->getBegin()->format($this->dbDateTimeFormat); $params[':end'] = $session->getEnd()->format($this->dbDateTimeFormat); $params[':excludedsession'] = $session->getUid(); $params[':scheduledtype'] = \TYPO3\Sessions\Domain\Model\ScheduledSession::class; $stmt = $this->db->prepare_SELECTquery(' DISTINCT tx_sessions_domain_model_session.uid AS uid ', ' tx_sessions_domain_model_session LEFT JOIN tx_sessions_session_record_mm AS srmm ON tx_sessions_domain_model_session.uid = srmm.uid_local AND srmm.tablenames = \'fe_users\' LEFT JOIN fe_users AS user ON srmm.uid_foreign = user.uid ', ' user.uid IN (' . $inStmt . ') AND ( /* this session starts while another session is running (start overlaps with other session) */ ( tx_sessions_domain_model_session.begin > :start AND tx_sessions_domain_model_session.begin < :end ) OR /* this session ends while another session is running (end overlaps with other session) */ ( tx_sessions_domain_model_session.end > :start AND tx_sessions_domain_model_session.end < :end ) OR /* this session starts at the same time */ tx_sessions_domain_model_session.begin = :start OR /* this session ends at the same time */ tx_sessions_domain_model_session.end = :end OR /* this session starts before and ends after */ (tx_sessions_domain_model_session.begin < :start AND tx_sessions_domain_model_session.end > :end) ) AND tx_sessions_domain_model_session.uid ' . (isset($excludeInStmt) ? 'NOT IN(' . $excludeInStmt . ')' : '<> :excludedsession') . ' AND tx_sessions_domain_model_session.type = :scheduledtype ' . \TYPO3\CMS\Backend\Utility\BackendUtility::BEenableFields('tx_sessions_domain_model_session') . ' ', '', ' tx_sessions_domain_model_session.uid DESC ', '', $params); if ($stmt->execute() && $stmt->rowCount() > 0) { if ($stmt->rowCount() === 1) { $row = $stmt->fetch(); $stmt->free(); return [$this->sessionRepository->findByUid($row['uid'])]; } $uids = []; while ($row = $stmt->fetch()) { $uids[] = $row['uid']; } $stmt->free(); return $this->sessionRepository->findByUids($uids)->toArray(); } return false; }