2e4e292853cbd1314bc95d4b8f44c2d4d43b31fd
[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 * Test case
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'] = $this->getAccessibleMock(
59 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
60 array('exec_PREPAREDquery', 'fullQuoteStr'),
61 array(),
62 '',
63 FALSE,
64 FALSE
65 );
66 $GLOBALS['TYPO3_DB']->expects($this->any())->method('fullQuoteStr')
67 ->will($this->returnCallback(
68 function($quoteStr, $table) {
69 return "'" . $quoteStr . "'";
70 }
71 ));
72
73 return $GLOBALS['TYPO3_DB'];
74 }
75
76 /**
77 * Create a object fo the subject to be tested.
78 *
79 * @param string $query
80 * @return \TYPO3\CMS\Core\Database\PreparedStatement
81 */
82 private function createPreparedStatement($query) {
83 return new \TYPO3\CMS\Core\Database\PreparedStatement($query, 'pages');
84 }
85
86 ////////////////////////////////////
87 // Tests for the utility functions
88 ////////////////////////////////////
89
90 /**
91 * @test
92 * @return void
93 */
94 public function setUpAndReturnDatabaseStubReturnsMockObjectOfDatabaseConnection() {
95 $this->assertTrue($this->setUpAndReturnDatabaseStub() instanceof \TYPO3\CMS\Core\Database\DatabaseConnection);
96 }
97
98 /**
99 * @test
100 * @return void
101 */
102 public function createPreparedStatementReturnsInstanceOfPreparedStatementClass() {
103 $this->assertTrue($this->createPreparedStatement('dummy') instanceof \TYPO3\CMS\Core\Database\PreparedStatement);
104 }
105
106 ///////////////////////////////////////
107 // Tests for \TYPO3\CMS\Core\Database\PreparedStatement
108 ///////////////////////////////////////
109 /**
110 * Data Provider for two tests, providing sample queries, parameters and expected result queries.
111 *
112 * @see parametersAreReplacedInQueryByCallingExecute
113 * @see parametersAreReplacedInQueryWhenBoundWithBindValues
114 * @return array
115 */
116 public function parametersAndQueriesDataProvider() {
117 return array(
118 'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1'),
119 'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=1'),
120 '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'),
121 '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'),
122 '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'),
123 'php bool TRUE parameter is replaced with 1' => array('SELECT * FROM pages WHERE deleted=?', array(TRUE), 'SELECT * FROM pages WHERE deleted=1'),
124 'php bool FALSE parameter is replaced with 0' => array('SELECT * FROM pages WHERE deleted=?', array(FALSE), 'SELECT * FROM pages WHERE deleted=0'),
125 'php null parameter is replaced with NULL' => array('SELECT * FROM pages WHERE deleted=?', array(NULL), 'SELECT * FROM pages WHERE deleted=NULL'),
126 'string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('Foo bar'), 'SELECT * FROM pages WHERE title=\'Foo bar\''),
127 'number as string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('12'), 'SELECT * FROM pages WHERE title=\'12\''),
128 '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\''),
129 '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\''),
130 '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\''),
131 '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\'')
132 );
133 }
134
135 /**
136 * Checking if calling execute() with parameters, they are
137 * properly replaced in the query.
138 *
139 * @test
140 * @dataProvider parametersAndQueriesDataProvider
141 * @param string $query Query with unreplaced markers
142 * @param array $parameters Array of parameters to be replaced in the query
143 * @param string $expectedResult Query with all markers replaced
144 * @return void
145 */
146 public function parametersAreReplacedInQueryByCallingExecute($query, $parameters, $expectedResult) {
147 $statement = $this->createPreparedStatement($query);
148 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
149 $statement->execute($parameters);
150 }
151
152 /**
153 * @test
154 */
155 public function executeCallsFullQuoteStrForStringParameter() {
156 $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
157 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
158 array('exec_PREPAREDquery', 'fullQuoteStr'),
159 array(),
160 '',
161 FALSE,
162 FALSE
163 );
164 $query = 'SELECT * FROM pages WHERE title=?';
165 $parameter = "'Foo'";
166 $uniqueQuoteResult = uniqid('quoteResult');
167 $GLOBALS['TYPO3_DB']->expects($this->once())->method('fullQuoteStr')->with($parameter)->will($this->returnValue($uniqueQuoteResult));
168 $GLOBALS['TYPO3_DB']->expects($this->any())->method('exec_PREPAREDquery')->with($this->stringContains($uniqueQuoteResult));
169 $statement = $this->createPreparedStatement($query);
170 $statement->execute(array($parameter));
171 }
172
173 /**
174 * Checking if parameters bound to the statement by bindValues()
175 * are properly replaced in the query.
176 *
177 * @test
178 * @dataProvider parametersAndQueriesDataProvider
179 * @param string $query Query with unreplaced markers
180 * @param array $parameters Array of parameters to be replaced in the query
181 * @param string $expectedResult Query with all markers replaced
182 * @return void
183 */
184 public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult) {
185 $statement = $this->createPreparedStatement($query);
186 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
187 $statement->bindValues($parameters);
188 $statement->execute();
189 }
190
191 /**
192 * Data Provider with invalid parameters.
193 *
194 * @see invalidParameterTypesPassedToBindValueThrowsException
195 * @return array
196 */
197 public function invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider() {
198 return array(
199 'integer passed with param type NULL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
200 'string passed with param type NULL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
201 'bool passed with param type NULL' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
202 'NULL passed with param type INT' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
203 'string passed with param type INT' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
204 'bool passed with param type INT' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
205 'NULL passed with param type BOOL' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
206 'string passed with param type BOOL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
207 'integer passed with param type BOOL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL)
208 );
209 }
210
211 /**
212 * Checking if an exception is thrown if invalid parameters are
213 * provided vor bindValue().
214 *
215 * @test
216 * @expectedException \InvalidArgumentException
217 * @dataProvider invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider
218 * @param mixed $parameter Parameter to be replaced in the query
219 * @param integer $type Type of the parameter value
220 * @return void
221 */
222 public function invalidParameterTypesPassedToBindValueThrowsException($parameter, $type) {
223 $statement = $this->createPreparedStatement('');
224 $statement->bindValue(1, $parameter, $type);
225 }
226
227 /**
228 * Checking if formerly bound values are replaced by the values passed to execute().
229 *
230 * @test
231 * @return void
232 */
233 public function parametersPassedToExecuteOverrulesFormerlyBoundValues() {
234 $query = 'SELECT * FROM pages WHERE pid=? OR uid=?';
235 $expectedResult = 'SELECT * FROM pages WHERE pid=30 OR uid=40';
236 $this->databaseStub->expects($this->any())->method('exec_PREPAREDquery')->with($this->equalTo($expectedResult));
237 $statement = $this->createPreparedStatement($query);
238 $statement->bindValues(array(10, 20));
239 $statement->execute(array(30, 40));
240 }
241
242 /**
243 * Data Provider for invalid marker names.
244 *
245 * @see passingInvalidMarkersThrowsExeption
246 * @return array
247 */
248 public function passingInvalidMarkersThrowsExeptionDataProvider() {
249 return array(
250 'using other prefix than colon' => array('SELECT * FROM pages WHERE pid=#pid', array('#pid' => 1)),
251 'using non alphanumerical character' => array('SELECT * FROM pages WHERE title=:stra≠e', array(':stra≠e' => 1)),
252 'no colon used' => array('SELECT * FROM pages WHERE pid=pid', array('pid' => 1)),
253 'colon at the end' => array('SELECT * FROM pages WHERE pid=pid:', array('pid:' => 1)),
254 'colon without alphanumerical character' => array('SELECT * FROM pages WHERE pid=:', array(':' => 1))
255 );
256 }
257
258 /**
259 * Checks if an exception is thrown, if parameter have invalid marker named.
260 *
261 * @test
262 * @expectedException \InvalidArgumentException
263 * @dataProvider passingInvalidMarkersThrowsExeptionDataProvider
264 * @param string $query Query with unreplaced markers
265 * @param array $parameters Array of parameters to be replaced in the query
266 * @return void
267 */
268 public function passingInvalidMarkersThrowsExeption($query, $parameters) {
269 $statement = $this->createPreparedStatement($query);
270 $statement->execute($parameters);
271 }
272
273 }