Fixed bug #13104: SQL parser cannot parse escaped single quote in INSERT statement
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / class.ux_t3lib_sqlparser.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
7 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
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 * A copy is found in the textfile GPL.txt and important notices to the license
19 * from the author is found in LICENSE.txt distributed with these scripts.
20 *
21 *
22 * This script is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 * GNU General Public License for more details.
26 *
27 * This copyright notice MUST APPEAR in all copies of the script!
28 ***************************************************************/
29 /**
30 * PHP SQL engine
31 *
32 * $Id$
33 *
34 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
35 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
36 * @author Xavier Perseguers <typo3@perseguers.ch>
37 */
38
39
40 /**
41 * PHP SQL engine / server
42 *
43 * @author Kasper Skaarhoj <kasper@typo3.com>
44 * @package TYPO3
45 * @subpackage t3lib
46 */
47 class ux_t3lib_sqlparser extends t3lib_sqlparser {
48
49 /**
50 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
51 * Can also compile field lists for ORDER BY and GROUP BY.
52 *
53 * @param array Array of select fields, (made with ->parseFieldList())
54 * @param boolean Whether comments should be compiled
55 * @return string Select field string
56 * @see parseFieldList()
57 */
58 public function compileFieldList($selectFields, $compileComments = TRUE) {
59 // TODO: Handle SQL hints in comments according to current DBMS
60 return parent::compileFieldList($selectFields, FALSE);
61 }
62
63 /**
64 * Add slashes function used for compiling queries
65 * This method overrides the method from t3lib_sqlparser because
66 * the input string is already properly escaped.
67 *
68 * @param string Input string
69 * @return string Output string
70 */
71 protected function compileAddslashes($str) {
72 return $str;
73 }
74
75 /*************************
76 *
77 * Compiling queries
78 *
79 *************************/
80
81 /**
82 * Compiles an INSERT statement from components array
83 *
84 * @param array Array of SQL query components
85 * @return string SQL INSERT query
86 * @see parseINSERT()
87 */
88 function compileINSERT($components) {
89 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
90 case 'native':
91 $query = parent::compileINSERT($components);
92 break;
93 case 'adodb':
94 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
95 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$components['TABLE']];
96 $fc = 0;
97 foreach ($fields as $fn => $fd) {
98 $query[$fn] = $components['VALUES_ONLY'][$fc++][0];
99 }
100 } else {
101 // Initialize:
102 foreach ($components['FIELDS'] as $fN => $fV) {
103 $query[$fN]=$fV[0];
104 }
105 }
106 break;
107 }
108
109 return $query;
110 }
111
112 /**
113 * Compiles a DROP TABLE statement from components array
114 *
115 * @param array Array of SQL query components
116 * @return string SQL DROP TABLE query
117 * @see compileSQL()
118 */
119 private function compileDROPTABLE($components) {
120 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
121 case 'native':
122 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE'];
123 break;
124 case 'adodb':
125 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
126 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], $handlerKey, TRUE);
127 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$handlerKey]->DataDictionary->DropTableSQL($tableName);
128 break;
129 }
130
131 return $query;
132 }
133
134 /**
135 * Compiles a CREATE TABLE statement from components array
136 *
137 * @param array Array of SQL query components
138 * @return array array with SQL CREATE TABLE/INDEX command(s)
139 * @see parseCREATETABLE()
140 */
141 public function compileCREATETABLE($components) {
142 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
143 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]['type']) {
144 case 'native':
145 $query[] = parent::compileCREATETABLE($components);
146 break;
147 case 'adodb':
148 // Create fields and keys:
149 $fieldsKeys = array();
150 $indexKeys = array();
151
152 foreach ($components['FIELDS'] as $fN => $fCfg) {
153 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
154 $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
155 }
156
157 if (isset($components['KEYS']) && is_array($components['KEYS'])) {
158 foreach($components['KEYS'] as $kN => $kCfg) {
159 if ($kN === 'PRIMARYKEY') {
160 foreach ($kCfg as $n => $field) {
161 $fieldsKeys[$field] .= ' PRIMARY';
162 }
163 } elseif ($kN === 'UNIQUE') {
164 foreach ($kCfg as $n => $field) {
165 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));
166 }
167 } else {
168 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));
169 }
170 }
171 }
172
173 // Generally create without OID on PostgreSQL
174 $tableOptions = array('postgres' => 'WITHOUT OIDS');
175
176 // Fetch table/index generation query:
177 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
178 $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
179 break;
180 }
181
182 return $query;
183 }
184
185 /**
186 * Compiles an ALTER TABLE statement from components array
187 *
188 * @param array Array of SQL query components
189 * @return string SQL ALTER TABLE query
190 * @see parseALTERTABLE()
191 */
192 public function compileALTERTABLE($components) {
193 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
194 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
195 case 'native':
196 $query[] = parent::compileALTERTABLE($components);
197 break;
198 case 'adodb':
199 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
200 $fieldName = $GLOBALS['TYPO3_DB']->quoteName($components['FIELD'], NULL, TRUE);
201 switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {
202 case 'ADD':
203 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
204 break;
205 case 'CHANGE':
206 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
207 break;
208 case 'DROP':
209 case 'DROPKEY':
210 break;
211 case 'ADDKEY':
212 case 'ADDPRIMARYKEY':
213 $query .= ' (' . implode(',', $components['fields']) . ')';
214 break;
215 }
216 break;
217 }
218
219 return $query;
220 }
221
222 /**
223 * Compile field definition
224 *
225 * @param array Field definition parts
226 * @return string Field definition string
227 */
228 public function compileFieldCfg($fieldCfg) {
229 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
230 case 'native':
231 $cfg = parent::compileFieldCfg($fieldCfg);
232 break;
233 case 'adodb':
234 // Set type:
235 $type = $GLOBALS['TYPO3_DB']->MySQLMetaType($fieldCfg['fieldType']);
236 $cfg = $type;
237
238 // Add value, if any:
239 if (strlen($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
240 $cfg .= ' '.$fieldCfg['value'];
241 } elseif (!isset($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
242 $cfg .= ' 255'; // add 255 as length for varchar without specified length (e.g. coming from tinytext, tinyblob)
243 }
244
245 // Add additional features:
246 $noQuote = TRUE;
247 if (is_array($fieldCfg['featureIndex'])) {
248
249 // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here
250 // numeric fields get 0 as default, other fields an empty string
251 if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {
252 switch ($type) {
253 case 'I8':
254 case 'F':
255 case 'N':
256 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));
257 break;
258 default:
259 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));
260 }
261 }
262
263 foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {
264 switch (TRUE) {
265 // unsigned only for mysql, as it is mysql specific
266 case ($feature === 'UNSIGNED' && !$GLOBALS['TYPO3_DB']->runningADOdbDriver('mysql')):
267 // auto_increment is removed, it is handled by (emulated) sequences
268 case ($feature === 'AUTO_INCREMENT'):
269 // never add NOT NULL if running on Oracle and we have an empty string as default
270 case ($feature === 'NOTNULL' && $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8')):
271 continue;
272 case ($feature === 'NOTNULL'):
273 $cfg .= ' NOTNULL';
274 break;
275 default:
276 $cfg .= ' ' . $featureDef['keyword'];
277 }
278
279 // Add value if found:
280 if (is_array($featureDef['value'])) {
281 if ($featureDef['value'][0] === '') {
282 $cfg .= ' "\'\'"';
283 } else {
284 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
285 if (!is_numeric($featureDef['value'][0])) {
286 $noQuote = FALSE;
287 }
288 }
289 }
290 }
291 }
292 if ($noQuote) {
293 $cfg .= ' NOQUOTE';
294 }
295 break;
296 }
297
298 // Return field definition string:
299 return $cfg;
300 }
301
302 /**
303 * Checks if the submitted feature index contains a default value definition and the default value
304 *
305 * @param array $featureIndex A feature index as produced by parseFieldDef()
306 * @return boolean
307 * @see t3lib_sqlparser::parseFieldDef()
308 */
309 public function checkEmptyDefaultValue($featureIndex) {
310 if (is_array($featureIndex['DEFAULT']['value'])) {
311 if (!is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0])) {
312 return TRUE;
313 } else {
314 return FALSE;
315 }
316 }
317 return TRUE;
318 }
319
320 /**
321 * Implodes an array of WHERE clause configuration into a WHERE clause.
322 *
323 * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are:
324 * - the bitwise logical and (&)
325 * - the addition (+)
326 * - the substraction (-)
327 * - the multiplication (*)
328 * - the division (/)
329 * - the modulo (%)
330 *
331 * @param array WHERE clause configuration
332 * @return string WHERE clause as string.
333 * @see t3lib_sqlparser::parseWhereClause()
334 */
335 public function compileWhereClause($clauseArray, $functionMapping = TRUE) {
336 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
337 case 'native':
338 $output = parent::compileWhereClause($clauseArray);
339 break;
340 case 'adodb':
341 // Prepare buffer variable:
342 $output = '';
343
344 // Traverse clause array:
345 if (is_array($clauseArray)) {
346 foreach($clauseArray as $k => $v) {
347
348 // Set operator:
349 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
350
351 // Look for sublevel:
352 if (is_array($v['sub'])) {
353 $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
354 } elseif (isset($v['func'])) {
355 $output .= ' ' . trim($v['modifier']) . ' ' . $v['func']['type'] . ' (' . $this->compileSELECT($v['func']['subquery']) . ')';
356 } else {
357
358 // Set field/table with modifying prefix if any:
359 $output .= ' ' . trim($v['modifier']) . ' ';
360
361 // DBAL-specific: Set calculation, if any:
362 if ($v['calc'] === '&' && $functionMapping) {
363 switch(TRUE) {
364 case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):
365 // Oracle only knows BITAND(x,y) - sigh
366 $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
367 break;
368 default:
369 // MySQL, MS SQL Server, PostgreSQL support the &-syntax
370 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
371 break;
372 }
373 } elseif ($v['calc']) {
374 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
375 if (isset($v['calc_table'])) {
376 $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
377 } else {
378 $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
379 }
380 } elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {
381 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
382 }
383
384 // Set comparator:
385 if ($v['comparator']) {
386 switch (TRUE) {
387 case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping):
388 // Oracle cannot handle LIKE on CLOB fields - sigh
389 if (isset($v['value']['operator'])) {
390 $values = array();
391 foreach ($v['value']['args'] as $fieldDef) {
392 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
393 }
394 $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
395 } else {
396 $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
397 }
398 $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
399 break;
400 default:
401 $output .= ' ' . $v['comparator'];
402
403 // Detecting value type; list or plain:
404 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $v['comparator'])))) {
405 if (isset($v['subquery'])) {
406 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
407 } else {
408 $valueBuffer = array();
409 foreach ($v['value'] as $realValue) {
410 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
411 }
412 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
413 }
414 } else if (isset($v['value']['operator'])) {
415 $values = array();
416 foreach ($v['value']['args'] as $fieldDef) {
417 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
418 }
419 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
420 } else {
421 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
422 }
423 break;
424 }
425 }
426 }
427 }
428 }
429 break;
430 }
431
432 return $output;
433 }
434 }
435
436
437 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']) {
438 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']);
439 }
440
441 ?>