Fixed bug #13104: SQL parser cannot parse escaped single quote in INSERT statement
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / sqlparser_general_testcase.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 *
17 * This script is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
21 *
22 * This copyright notice MUST APPEAR in all copies of the script!
23 ***************************************************************/
24
25
26 require_once('BaseTestCase.php');
27
28 /**
29 * Testcase for class ux_t3lib_sqlparser
30 *
31 * $Id$
32 *
33 * @author Xavier Perseguers <typo3@perseguers.ch>
34 *
35 * @package TYPO3
36 * @subpackage dbal
37 */
38 class sqlparser_general_testcase extends BaseTestCase {
39
40 /**
41 * @var ux_t3lib_sqlparser (extended to make protected methods public)
42 */
43 protected $fixture;
44
45 /**
46 * Prepares the environment before running a test.
47 */
48 public function setUp() {
49 $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
50 $this->fixture = new $className;
51 }
52
53 /**
54 * Cleans up the environment after running a test.
55 */
56 public function tearDown() {
57 unset($this->fixture);
58 }
59
60 /**
61 * Cleans a SQL query.
62 *
63 * @param mixed $sql
64 * @return mixed (string or array)
65 */
66 private function cleanSql($sql) {
67 if (!is_string($sql)) {
68 return $sql;
69 }
70
71 $sql = str_replace("\n", ' ', $sql);
72 $sql = preg_replace('/\s+/', ' ', $sql);
73 return trim($sql);
74 }
75
76 /**
77 * @test
78 */
79 public function canExtractPartsOfAQuery() {
80 $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
81 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
82 $trimAll = TRUE;
83 $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
84
85 $this->assertEquals(
86 '*',
87 $fields
88 );
89 $this->assertEquals(
90 'FROM pages WHERE pid IN (1,2,3,4)',
91 $parseString
92 );
93
94 $regex = '^FROM ([^)]+) WHERE';
95 $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
96
97 $this->assertEquals(
98 'pages',
99 $table
100 );
101 $this->assertEquals(
102 'pages WHERE pid IN (1,2,3,4)',
103 $parseString
104 );
105 }
106
107 /**
108 * @test
109 */
110 public function canGetIntegerValue() {
111 $parseString = '1024';
112 $value = $this->fixture->_callRef('getValue', $parseString);
113 $expected = array(1024);
114
115 $this->assertEquals($expected, $value);
116 }
117
118 /**
119 * @test
120 * @see http://bugs.typo3.org/view.php?id=13104
121 */
122 public function canGetStringValue() {
123 $parseString = '"some owner\\\'s string"';
124 $value = $this->fixture->_callRef('getValue', $parseString);
125 $expected = array('some owner\'s string', '"');
126
127 $this->assertEquals($expected, $value);
128 }
129
130 /**
131 * @test
132 * @see http://bugs.typo3.org/view.php?id=13104
133 */
134 public function canGetStringValueWithSingleQuote() {
135 $parseString = "'some owner\'s string'";
136 $value = $this->fixture->_callRef('getValue', $parseString);
137 $expected = array('some owner\'s string', "'");
138
139 $this->assertEquals($expected, $value);
140 }
141
142 /**
143 * @test
144 * @see http://bugs.typo3.org/view.php?id=13104
145 */
146 public function canGetStringValueWithDoubleQuote() {
147 $parseString = '"the \"owner\" is here"';
148 $value = $this->fixture->_callRef('getValue', $parseString);
149 $expected = array('the "owner" is here', '"');
150
151 $this->assertEquals($expected, $value);
152 }
153
154 /**
155 * @test
156 */
157 public function canGetListOfValues() {
158 $parseString = '( 1, 2, 3 ,4)';
159 $operator = 'IN';
160 $values = $this->fixture->_callRef('getValue', $parseString, $operator);
161 $expected = array(
162 array(1),
163 array(2),
164 array(3),
165 array(4)
166 );
167
168 $this->assertEquals($expected, $values);
169 }
170
171 /**
172 * @test
173 */
174 public function parseWhereClauseReturnsArray() {
175 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
176 $where = $this->fixture->parseWhereClause($parseString);
177
178 $this->assertTrue(is_array($where), $where);
179 $this->assertTrue(empty($parseString), 'parseString is not empty');
180 }
181
182 /**
183 * @test
184 */
185 public function canSelectAllFieldsFromPages() {
186 $sql = 'SELECT * FROM pages';
187 $expected = $sql;
188 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
189
190 $this->assertEquals($expected, $actual);
191 }
192
193 ///////////////////////////////////////
194 // Tests concerning JOINs
195 ///////////////////////////////////////
196
197 /**
198 * @test
199 */
200 public function parseFromTablesWithInnerJoinReturnsArray() {
201 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
202 $tables = $this->fixture->parseFromTables($parseString);
203
204 $this->assertTrue(is_array($tables), $tables);
205 $this->assertTrue(empty($parseString), 'parseString is not empty');
206 }
207
208 /**
209 * @test
210 */
211 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
212 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
213 $tables = $this->fixture->parseFromTables($parseString);
214
215 $this->assertTrue(is_array($tables), $tables);
216 $this->assertTrue(empty($parseString), 'parseString is not empty');
217 }
218
219 /**
220 * @test
221 * @see http://bugs.typo3.org/view.php?id=12596
222 */
223 public function parseFromTablesWithRightOuterJoinReturnsArray() {
224 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
225 $tables = $this->fixture->parseFromTables($parseString);
226
227 $this->assertTrue(is_array($tables), $tables);
228 $this->assertTrue(empty($parseString), 'parseString is not empty');
229 }
230
231 /**
232 * @test
233 */
234 public function parseFromTablesWithMultipleJoinsReturnsArray() {
235 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
236 $tables = $this->fixture->parseFromTables($parseString);
237
238 $this->assertTrue(is_array($tables), $tables);
239 $this->assertTrue(empty($parseString), 'parseString is not empty');
240 }
241
242 /**
243 * @test
244 * @see http://bugs.typo3.org/view.php?id=12596
245 */
246 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
247 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
248 $tables = $this->fixture->parseFromTables($parseString);
249
250 $this->assertTrue(is_array($tables), $tables);
251 $this->assertTrue(empty($parseString), 'parseString is not empty');
252 }
253
254 /**
255 * @test
256 */
257 public function canUseInnerJoinInSelect() {
258 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
259 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
260 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
261
262 $this->assertEquals($expected, $actual);
263 }
264
265 /**
266 * @test
267 */
268 public function canUseMultipleInnerJoinsInSelect() {
269 $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
270 $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
271 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
272
273 $this->assertEquals($expected, $actual);
274 }
275
276 ///////////////////////////////////////
277 // Tests concerning DB management
278 ///////////////////////////////////////
279
280 /**
281 * @test
282 * @see http://bugs.typo3.org/view.php?id=4466
283 */
284 public function indexMayContainALengthRestriction() {
285 $parseString = '
286 CREATE TABLE tx_realurl_uniqalias (
287 uid int(11) NOT NULL auto_increment,
288 tstamp int(11) DEFAULT \'0\' NOT NULL,
289 tablename varchar(60) DEFAULT \'\' NOT NULL,
290 field_alias varchar(255) DEFAULT \'\' NOT NULL,
291 field_id varchar(60) DEFAULT \'\' NOT NULL,
292 value_alias varchar(255) DEFAULT \'\' NOT NULL,
293 value_id int(11) DEFAULT \'0\' NOT NULL,
294 lang int(11) DEFAULT \'0\' NOT NULL,
295 expire int(11) DEFAULT \'0\' NOT NULL,
296
297 PRIMARY KEY (uid),
298 KEY tablename (tablename),
299 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
300 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
301 );
302 ';
303
304 $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
305 $this->assertTrue(is_array($createTables), $createTables);
306 }
307
308 ///////////////////////////////////////
309 // Tests concerning subqueries
310 ///////////////////////////////////////
311
312 /**
313 * @test
314 * @see http://bugs.typo3.org/view.php?id=12758
315 */
316 public function inWhereClauseSupportsSubquery() {
317 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
318 $whereParts = $this->fixture->parseWhereClause($parseString);
319
320 $this->assertTrue(is_array($whereParts), $whereParts);
321 $this->assertTrue(empty($parseString), 'parseString is not empty');
322 }
323
324 /**
325 * @test
326 * @see http://bugs.typo3.org/view.php?id=12758
327 */
328 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
329 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
330 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
331 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
332
333 $this->assertEquals($expected, $actual);
334 }
335
336 /**
337 * @test
338 * @see http://bugs.typo3.org/view.php?id=12758
339 */
340 public function whereClauseSupportsExistsKeyword() {
341 $parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
342 $whereParts = $this->fixture->parseWhereClause($parseString);
343
344 $this->assertTrue(is_array($whereParts), $whereParts);
345 $this->assertTrue(empty($parseString), 'parseString is not empty');
346 }
347
348 /**
349 * @test
350 * @see http://bugs.typo3.org/view.php?id=12758
351 */
352 public function existsClauseIsProperlyCompiled() {
353 $sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
354 $expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
355 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
356
357 $this->assertEquals($expected, $actual);
358 }
359 }
360 ?>