d3bba07db7d004e8885b10fbc322f3513a77bb62
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / PreparedStatementTest.php
1 <?php
2 namespace TYPO3\CMS\Core\Tests\Unit\Database;
3
4 /***************************************************************
5 * Copyright notice
6 *
7 * (c) 2010-2013 Helmut Hummel <helmut@typo3.org>
8 * All rights reserved
9 *
10 * This script is part of the TYPO3 project. The TYPO3 project is
11 * free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * The GNU General Public License can be found at
17 * http://www.gnu.org/copyleft/gpl.html.
18 *
19 * This script is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 * GNU General Public License for more details.
23 *
24 * This copyright notice MUST APPEAR in all copies of the script!
25 ***************************************************************/
26
27 /**
28 * Testcase
29 *
30 * @author Helmut Hummel <helmut@typo3.org>
31 */
32 class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
33
34 /**
35 * @var \PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Database\DatabaseConnection
36 */
37 protected $databaseStub;
38
39 /**
40 * Create a new database mock object for every test
41 * and backup the original global database object.
42 *
43 * @return void
44 */
45 protected function setUp() {
46 $this->databaseStub = $this->setUpAndReturnDatabaseStub();
47 }
48
49 //////////////////////
50 // Utility functions
51 //////////////////////
52 /**
53 * Set up the stub to be able to get the result of the prepared statement.
54 *
55 * @return \PHPUnit_Framework_MockObject_MockObject
56 */
57 private function setUpAndReturnDatabaseStub() {
58 $GLOBALS['TYPO3_DB']->connectDB();
59 $databaseLink = $GLOBALS['TYPO3_DB']->getDatabaseHandle();
60 $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
61 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
62 array('exec_PREPAREDquery'),
63 array(),
64 '',
65 FALSE,
66 FALSE
67 );
68 $GLOBALS['TYPO3_DB']->_set('isConnected', TRUE);
69 $GLOBALS['TYPO3_DB']->setDatabaseHandle($databaseLink);
70 return $GLOBALS['TYPO3_DB'];
71 }
72
73 /**
74 * Create a object fo the subject to be tested.
75 *
76 * @param string $query
77 * @return \TYPO3\CMS\Core\Database\PreparedStatement
78 */
79 private function createPreparedStatement($query) {
80 return new \TYPO3\CMS\Core\Database\PreparedStatement($query, 'pages');
81 }
82
83 ////////////////////////////////////
84 // Tests for the utility functions
85 ////////////////////////////////////
86
87 /**
88 * @test
89 * @return void
90 */
91 public function setUpAndReturnDatabaseStubReturnsMockObjectOfDatabaseConnection() {
92 $this->assertTrue($this->setUpAndReturnDatabaseStub() instanceof \TYPO3\CMS\Core\Database\DatabaseConnection);
93 }
94
95 /**
96 * @test
97 * @return void
98 */
99 public function createPreparedStatementReturnsInstanceOfPreparedStatementClass() {
100 $this->assertTrue($this->createPreparedStatement('dummy') instanceof \TYPO3\CMS\Core\Database\PreparedStatement);
101 }
102
103 ///////////////////////////////////////
104 // Tests for \TYPO3\CMS\Core\Database\PreparedStatement
105 ///////////////////////////////////////
106 /**
107 * Data Provider for two tests, providing sample queries, parameters and expected result queries.
108 *
109 * @see parametersAreReplacedInQueryByCallingExecute
110 * @see parametersAreReplacedInQueryWhenBoundWithBindValues
111 * @return array
112 */
113 public function parametersAndQueriesDataProvider() {
114 return array(
115 'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1'),
116 'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=1'),
117 'one named integer parameter is replaced multiple times' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
118 'two named integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:uid', array(':pid' => 1, ':uid' => 10), 'SELECT * FROM pages WHERE pid=1 OR uid=10'),
119 'two unnamed integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=? OR uid=?', array(1, 1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
120 'php bool TRUE parameter is replaced with 1' => array('SELECT * FROM pages WHERE deleted=?', array(TRUE), 'SELECT * FROM pages WHERE deleted=1'),
121 'php bool FALSE parameter is replaced with 0' => array('SELECT * FROM pages WHERE deleted=?', array(FALSE), 'SELECT * FROM pages WHERE deleted=0'),
122 'php null parameter is replaced with NULL' => array('SELECT * FROM pages WHERE deleted=?', array(NULL), 'SELECT * FROM pages WHERE deleted=NULL'),
123 'string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('Foo bar'), 'SELECT * FROM pages WHERE title=\'Foo bar\''),
124 'number as string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('12'), 'SELECT * FROM pages WHERE title=\'12\''),
125 'string single quotes in parameter are properly escaped' => array('SELECT * FROM pages WHERE title=?', array('\'Foo\''), 'SELECT * FROM pages WHERE title=\'\\\'Foo\\\'\''),
126 'question mark as values with unnamed parameters are properly escaped' => array('SELECT * FROM foo WHERE title=? AND name=?', array('?', 'fancy title'), 'SELECT * FROM foo WHERE title=\'?\' AND name=\'fancy title\''),
127 'parameter name as value is properly escaped' => array('SELECT * FROM foo WHERE title=:name AND name=:title', array(':name' => ':title', ':title' => 'fancy title'), 'SELECT * FROM foo WHERE title=\':title\' AND name=\'fancy title\''),
128 'question mark as value of a parameter with a name is properly escaped' => array('SELECT * FROM foo WHERE title=:name AND name=?', array(':name' => '?', 'cool name'), 'SELECT * FROM foo WHERE title=\'?\' AND name=\'cool name\''),
129 'regular expression back references as values are left untouched' => array('SELECT * FROM foo WHERE title=:name AND name=?', array(':name' => '\\1', '${1}'), 'SELECT * FROM foo WHERE title=\'\\\\1\' AND name=\'${1}\''),
130 'unsubstituted question marks do not contain the token wrap' => array('SELECT * FROM foo WHERE title=:name AND question LIKE "%what?" AND name=:title', array(':name' => 'Title', ':title' => 'Name'), 'SELECT * FROM foo WHERE title=\'Title\' AND question LIKE "%what?" AND name=\'Name\'')
131 );
132 }
133
134 /**
135 * Checking if calling execute() with parameters, they are
136 * properly relpaced in the query.
137 *
138 * @test
139 * @dataProvider parametersAndQueriesDataProvider
140 * @param string $query Query with unreplaced markers
141 * @param array $parameters Array of parameters to be replaced in the query
142 * @param string $expectedResult Query with all markers replaced
143 * @return void
144 */
145 public function parametersAreReplacedInQueryByCallingExecute($query, $parameters, $expectedResult) {
146 $statement = $this->createPreparedStatement($query);
147 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
148 $statement->execute($parameters);
149 }
150
151 /**
152 * Checking if parameters bound to the statement by bindValues()
153 * are properly replaced in the query.
154 *
155 * @test
156 * @dataProvider parametersAndQueriesDataProvider
157 * @param string $query Query with unreplaced markers
158 * @param array $parameters Array of parameters to be replaced in the query
159 * @param string $expectedResult Query with all markers replaced
160 * @return void
161 */
162 public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult) {
163 $statement = $this->createPreparedStatement($query);
164 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
165 $statement->bindValues($parameters);
166 $statement->execute();
167 }
168
169 /**
170 * Data Provider with invalid parameters.
171 *
172 * @see invalidParameterTypesPassedToBindValueThrowsException
173 * @return array
174 */
175 public function invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider() {
176 return array(
177 'integer passed with param type NULL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
178 'string passed with param type NULL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
179 'bool passed with param type NULL' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
180 'NULL passed with param type INT' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
181 'string passed with param type INT' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
182 'bool passed with param type INT' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
183 'NULL passed with param type BOOL' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
184 'string passed with param type BOOL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
185 'integer passed with param type BOOL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL)
186 );
187 }
188
189 /**
190 * Checking if an exception is thrown if invalid parameters are
191 * provided vor bindValue().
192 *
193 * @test
194 * @expectedException \InvalidArgumentException
195 * @dataProvider invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider
196 * @param mixed $parameter Parameter to be replaced in the query
197 * @param integer $type Type of the parameter value
198 * @return void
199 */
200 public function invalidParameterTypesPassedToBindValueThrowsException($parameter, $type) {
201 $statement = $this->createPreparedStatement('');
202 $statement->bindValue(1, $parameter, $type);
203 }
204
205 /**
206 * Checking if formerly bound values are replaced by the values passed to execute().
207 *
208 * @test
209 * @return void
210 */
211 public function parametersPassedToExecuteOverrulesFormerlyBoundValues() {
212 $query = 'SELECT * FROM pages WHERE pid=? OR uid=?';
213 $expectedResult = 'SELECT * FROM pages WHERE pid=30 OR uid=40';
214 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
215 $statement = $this->createPreparedStatement($query);
216 $statement->bindValues(array(10, 20));
217 $statement->execute(array(30, 40));
218 }
219
220 /**
221 * Data Provieder for invalid marker names.
222 *
223 * @see passingInvalidMarkersThrowsExeption
224 * @return array
225 */
226 public function passingInvalidMarkersThrowsExeptionDataProvider() {
227 return array(
228 'using other prefix than colon' => array('SELECT * FROM pages WHERE pid=#pid', array('#pid' => 1)),
229 'using non alphanumerical character' => array('SELECT * FROM pages WHERE title=:stra≠e', array(':stra≠e' => 1)),
230 'no colon used' => array('SELECT * FROM pages WHERE pid=pid', array('pid' => 1)),
231 'colon at the end' => array('SELECT * FROM pages WHERE pid=pid:', array('pid:' => 1)),
232 'colon without alphanumerical character' => array('SELECT * FROM pages WHERE pid=:', array(':' => 1))
233 );
234 }
235
236 /**
237 * Checks if an exception is thrown, if parameter have invalid marker named.
238 *
239 * @test
240 * @expectedException \InvalidArgumentException
241 * @dataProvider passingInvalidMarkersThrowsExeptionDataProvider
242 * @param string $query Query with unreplaced markers
243 * @param array $parameters Array of parameters to be replaced in the query
244 * @return void
245 */
246 public function passingInvalidMarkersThrowsExeption($query, $parameters) {
247 $statement = $this->createPreparedStatement($query);
248 $statement->execute($parameters);
249 }
250
251 }