?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Converting number of Excel column names no longer limited
[phpmyadmin/arisferyanto.git] / libraries / sqlparser.lib.php
blob53f239a97829d40cb3cd05280abcd6ed5ecded2e
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /** SQL Parser Functions for phpMyAdmin
4 *
5 * Copyright 2002 Robin Johnson <robbat2@users.sourceforge.net>
6 * http://www.orbis-terrarum.net/?l=people.robbat2
7 *
8 * These functions define an SQL parser system, capable of understanding and
9 * extracting data from a MySQL type SQL query.
10 *
11 * The basic procedure for using the new SQL parser:
12 * On any page that needs to extract data from a query or to pretty-print a
13 * query, you need code like this up at the top:
14 *
15 * ($sql contains the query)
16 * $parsed_sql = PMA_SQP_parse($sql);
17 *
18 * If you want to extract data from it then, you just need to run
19 * $sql_info = PMA_SQP_analyze($parsed_sql);
20 *
21 * lem9: See comments in PMA_SQP_analyze for the returned info
22 * from the analyzer.
23 *
24 * If you want a pretty-printed version of the query, do:
25 * $string = PMA_SQP_formatHtml($parsed_sql);
26 * (note that that you need to have syntax.css.php included somehow in your
27 * page for it to work, I recommend '<link rel="stylesheet" type="text/css"
28 * href="syntax.css.php" />' at the moment.)
29 *
30 * @version $Id$
31 * @package phpMyAdmin
32 */
33 if (! defined('PHPMYADMIN')) {
34 exit;
35 }
36
37 /**
38 * Minimum inclusion? (i.e. for the stylesheet builder)
39 */
40 if (! defined('PMA_MINIMUM_COMMON')) {
41 /**
42 * Include the string library as we use it heavily
43 */
44 require_once './libraries/string.lib.php';
45
46 /**
47 * Include data for the SQL Parser
48 */
49 require_once './libraries/sqlparser.data.php';
50 require_once './libraries/mysql_charsets.lib.php';
51 if (!isset($mysql_charsets)) {
52 $mysql_charsets = array();
53 $mysql_charsets_count = 0;
54 $mysql_collations_flat = array();
55 $mysql_collations_count = 0;
56 }
57
58 if (!defined('DEBUG_TIMING')) {
59 /**
60 * currently we don't need the $pos (token position in query)
61 * for other purposes than LIMIT clause verification,
62 * so many calls to this function do not include the 4th parameter
63 */
64 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
65 {
66 $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos);
67 $arrsize++;
68 } // end of the "PMA_SQP_arrayAdd()" function
69 } else {
70 /**
71 * This is debug variant of above.
72 * @ignore
73 */
74 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
75 {
76 global $timer;
77
78 $t = $timer;
79 $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos, 'time' => $t);
80 $timer = microtime();
81 $arrsize++;
82 } // end of the "PMA_SQP_arrayAdd()" function
83 } // end if... else...
84
85
86 /**
87 * Reset the error variable for the SQL parser
88 *
89 * @access public
90 */
91 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
92 function PMA_SQP_resetError()
93 {
94 global $SQP_errorString;
95 $SQP_errorString = '';
96 unset($SQP_errorString);
97 }
98
99 /**
100 * Get the contents of the error variable for the SQL parser
101 *
102 * @return string Error string from SQL parser
103 *
104 * @access public
105 */
106 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
107 function PMA_SQP_getErrorString()
108 {
109 global $SQP_errorString;
110 return isset($SQP_errorString) ? $SQP_errorString : '';
111 }
112
113 /**
114 * Check if the SQL parser hit an error
115 *
116 * @return boolean error state
117 *
118 * @access public
119 */
120 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
121 function PMA_SQP_isError()
122 {
123 global $SQP_errorString;
124 return isset($SQP_errorString) && !empty($SQP_errorString);
125 }
126
127 /**
128 * Set an error message for the system
129 *
130 * @param string The error message
131 * @param string The failing SQL query
132 *
133 * @access private
134 * @scope SQL Parser internal
135 */
136 // Revised, Robbat2 - 13 Janurary 2003, 2:59PM
137 function PMA_SQP_throwError($message, $sql)
138 {
139 global $SQP_errorString;
140 $SQP_errorString = '<p>'.$GLOBALS['strSQLParserUserError'] . '</p>' . "\n"
141 . '<pre>' . "\n"
142 . 'ERROR: ' . $message . "\n"
143 . 'SQL: ' . htmlspecialchars($sql) . "\n"
144 . '</pre>' . "\n";
145
146 } // end of the "PMA_SQP_throwError()" function
147
148
149 /**
150 * Do display the bug report
151 *
152 * @param string The error message
153 * @param string The failing SQL query
154 *
155 * @access public
156 */
157 function PMA_SQP_bug($message, $sql)
158 {
159 global $SQP_errorString;
160 $debugstr = 'ERROR: ' . $message . "\n";
161 $debugstr .= 'SVN: $Id$' . "\n";
162 $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";
163 $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ' . PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";
164 $debugstr .= 'PMA: ' . PMA_VERSION . "\n";
165 $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";
166 $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
167 $debugstr .= 'SQL: ' . htmlspecialchars($sql);
168
169 $encodedstr = $debugstr;
170 if (@function_exists('gzcompress')) {
171 $encodedstr = gzcompress($debugstr, 9);
172 }
173 $encodedstr = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));
174
175 $SQP_errorString .= $GLOBALS['strSQLParserBugMessage'] . '<br />' . "\n"
176 . '----' . $GLOBALS['strBeginCut'] . '----' . '<br />' . "\n"
177 . $encodedstr . "\n"
178 . '----' . $GLOBALS['strEndCut'] . '----' . '<br />' . "\n";
179
180 $SQP_errorString .= '----' . $GLOBALS['strBeginRaw'] . '----<br />' . "\n"
181 . '<pre>' . "\n"
182 . $debugstr
183 . '</pre>' . "\n"
184 . '----' . $GLOBALS['strEndRaw'] . '----<br />' . "\n";
185
186 } // end of the "PMA_SQP_bug()" function
187
188
189 /**
190 * Parses the SQL queries
191 *
192 * @param string The SQL query list
193 *
194 * @return mixed Most of times, nothing...
195 *
196 * @global array The current PMA configuration
197 * @global array MySQL column attributes
198 * @global array MySQL reserved words
199 * @global array MySQL column types
200 * @global array MySQL function names
201 * @global integer MySQL column attributes count
202 * @global integer MySQL reserved words count
203 * @global integer MySQL column types count
204 * @global integer MySQL function names count
205 * @global array List of available character sets
206 * @global array List of available collations
207 * @global integer Character sets count
208 * @global integer Collations count
209 *
210 * @access public
211 */
212 function PMA_SQP_parse($sql)
213 {
214 global $cfg;
215 global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,
216 $PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;
217 global $mysql_charsets, $mysql_collations_flat, $mysql_charsets_count, $mysql_collations_count;
218 global $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt;
219
220 // rabus: Convert all line feeds to Unix style
221 $sql = str_replace("\r\n", "\n", $sql);
222 $sql = str_replace("\r", "\n", $sql);
223
224 $len = PMA_strlen($sql);
225 if ($len == 0) {
226 return array();
227 }
228
229 $sql_array = array();
230 $sql_array['raw'] = $sql;
231 $count1 = 0;
232 $count2 = 0;
233 $punct_queryend = ';';
234 $punct_qualifier = '.';
235 $punct_listsep = ',';
236 $punct_level_plus = '(';
237 $punct_level_minus = ')';
238 $punct_user = '@';
239 $digit_floatdecimal = '.';
240 $digit_hexset = 'x';
241 $bracket_list = '()[]{}';
242 $allpunct_list = '-,;:!?/.^~\*&%+<=>|';
243 $allpunct_list_pair = array (
244 0 => '!=',
245 1 => '&&',
246 2 => ':=',
247 3 => '<<',
248 4 => '<=',
249 5 => '<=>',
250 6 => '<>',
251 7 => '>=',
252 8 => '>>',
253 9 => '||'
254 );
255 $allpunct_list_pair_size = 10; //count($allpunct_list_pair);
256 $quote_list = '\'"`';
257 $arraysize = 0;
258
259 $previous_was_space = false;
260 $this_was_space = false;
261 $previous_was_bracket = false;
262 $this_was_bracket = false;
263 $previous_was_punct = false;
264 $this_was_punct = false;
265 $previous_was_listsep = false;
266 $this_was_listsep = false;
267 $previous_was_quote = false;
268 $this_was_quote = false;
269
270 while ($count2 < $len) {
271 $c = $GLOBALS['PMA_substr']($sql, $count2, 1);
272 $count1 = $count2;
273
274 $previous_was_space = $this_was_space;
275 $this_was_space = false;
276 $previous_was_bracket = $this_was_bracket;
277 $this_was_bracket = false;
278 $previous_was_punct = $this_was_punct;
279 $this_was_punct = false;
280 $previous_was_listsep = $this_was_listsep;
281 $this_was_listsep = false;
282 $previous_was_quote = $this_was_quote;
283 $this_was_quote = false;
284
285 if (($c == "\n")) {
286 $this_was_space = true;
287 $count2++;
288 PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
289 continue;
290 }
291
292 // Checks for white space
293 if ($GLOBALS['PMA_STR_isSpace']($c)) {
294 $this_was_space = true;
295 $count2++;
296 continue;
297 }
298
299 // Checks for comment lines.
300 // MySQL style #
301 // C style /* */
302 // ANSI style --
303 if (($c == '#')
304 || (($count2 + 1 < $len) && ($c == '/') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '*'))
305 || (($count2 + 2 == $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-'))
306 || (($count2 + 2 < $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-') && (($GLOBALS['PMA_substr']($sql, $count2 + 2, 1) <= ' ')))) {
307 $count2++;
308 $pos = 0;
309 $type = 'bad';
310 switch ($c) {
311 case '#':
312 $type = 'mysql';
313 case '-':
314 $type = 'ansi';
315 $pos = $GLOBALS['PMA_strpos']($sql, "\n", $count2);
316 break;
317 case '/':
318 $type = 'c';
319 $pos = $GLOBALS['PMA_strpos']($sql, '*/', $count2);
320 $pos += 2;
321 break;
322 default:
323 break;
324 } // end switch
325 $count2 = ($pos < $count2) ? $len : $pos;
326 $str = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
327 PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
328 continue;
329 } // end if
330
331 // Checks for something inside quotation marks
332 if ($GLOBALS['PMA_strpos']($quote_list, $c) !== false) {
333 $startquotepos = $count2;
334 $quotetype = $c;
335 $count2++;
336 $escaped = FALSE;
337 $escaped_escaped = FALSE;
338 $pos = $count2;
339 $oldpos = 0;
340 do {
341 $oldpos = $pos;
342 $pos = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos + 1) - 1;
343 // ($pos === FALSE)
344 if ($pos < 0) {
345 $debugstr = $GLOBALS['strSQPBugUnclosedQuote'] . ' @ ' . $startquotepos. "\n"
346 . 'STR: ' . htmlspecialchars($quotetype);
347 PMA_SQP_throwError($debugstr, $sql);
348 return $sql_array;
349 }
350
351 // If the quote is the first character, it can't be
352 // escaped, so don't do the rest of the code
353 if ($pos == 0) {
354 break;
355 }
356
357 // Checks for MySQL escaping using a \
358 // And checks for ANSI escaping using the $quotetype character
359 if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {
360 $pos ++;
361 continue;
362 } elseif (($pos + 1 < $len) && ($GLOBALS['PMA_substr']($sql, $pos, 1) == $quotetype) && ($GLOBALS['PMA_substr']($sql, $pos + 1, 1) == $quotetype)) {
363 $pos = $pos + 2;
364 continue;
365 } else {
366 break;
367 }
368 } while ($len > $pos); // end do
369
370 $count2 = $pos;
371 $count2++;
372 $type = 'quote_';
373 switch ($quotetype) {
374 case '\'':
375 $type .= 'single';
376 $this_was_quote = true;
377 break;
378 case '"':
379 $type .= 'double';
380 $this_was_quote = true;
381 break;
382 case '`':
383 $type .= 'backtick';
384 $this_was_quote = true;
385 break;
386 default:
387 break;
388 } // end switch
389 $data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
390 PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
391 continue;
392 }
393
394 // Checks for brackets
395 if ($GLOBALS['PMA_strpos']($bracket_list, $c) !== false) {
396 // All bracket tokens are only one item long
397 $this_was_bracket = true;
398 $count2++;
399 $type_type = '';
400 if ($GLOBALS['PMA_strpos']('([{', $c) !== false) {
401 $type_type = 'open';
402 } else {
403 $type_type = 'close';
404 }
405
406 $type_style = '';
407 if ($GLOBALS['PMA_strpos']('()', $c) !== false) {
408 $type_style = 'round';
409 } elseif ($GLOBALS['PMA_strpos']('[]', $c) !== false) {
410 $type_style = 'square';
411 } else {
412 $type_style = 'curly';
413 }
414
415 $type = 'punct_bracket_' . $type_type . '_' . $type_style;
416 PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
417 continue;
418 }
419
420 /* DEBUG
421 echo '<pre>1';
422 var_dump(PMA_STR_isSqlIdentifier($c, false));
423 var_dump($c == '@');
424 var_dump($c == '.');
425 var_dump(PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1)));
426 var_dump($previous_was_space);
427 var_dump($previous_was_bracket);
428 var_dump($previous_was_listsep);
429 echo '</pre>';
430 */
431
432 // Checks for identifier (alpha or numeric)
433 if (PMA_STR_isSqlIdentifier($c, false)
434 || $c == '@'
435 || ($c == '.'
436 && $GLOBALS['PMA_STR_isDigit']($GLOBALS['PMA_substr']($sql, $count2 + 1, 1))
437 && ($previous_was_space || $previous_was_bracket || $previous_was_listsep))) {
438
439 /* DEBUG
440 echo PMA_substr($sql, $count2);
441 echo '<hr />';
442 */
443
444 $count2++;
445
446 /**
447 * @todo a @ can also be present in expressions like
448 * FROM 'user'@'%' or TO 'user'@'%'
449 * in this case, the @ is wrongly marked as alpha_variable
450 */
451 $is_identifier = $previous_was_punct;
452 $is_sql_variable = $c == '@' && ! $previous_was_quote;
453 $is_user = $c == '@' && $previous_was_quote;
454 $is_digit = !$is_identifier && !$is_sql_variable && $GLOBALS['PMA_STR_isDigit']($c);
455 $is_hex_digit = $is_digit && $c == '0' && $count2 < $len && $GLOBALS['PMA_substr']($sql, $count2, 1) == 'x';
456 $is_float_digit = $c == '.';
457 $is_float_digit_exponent = FALSE;
458
459 /* DEBUG
460 echo '<pre>2';
461 var_dump($is_identifier);
462 var_dump($is_sql_variable);
463 var_dump($is_digit);
464 var_dump($is_float_digit);
465 echo '</pre>';
466 */
467
468 // Nijel: Fast skip is especially needed for huge BLOB data, requires PHP at least 4.3.0:
469 if ($is_hex_digit) {
470 $count2++;
471 $pos = strspn($sql, '0123456789abcdefABCDEF', $count2);
472 if ($pos > $count2) {
473 $count2 = $pos;
474 }
475 unset($pos);
476 } elseif ($is_digit) {
477 $pos = strspn($sql, '0123456789', $count2);
478 if ($pos > $count2) {
479 $count2 = $pos;
480 }
481 unset($pos);
482 }
483
484 while (($count2 < $len) && PMA_STR_isSqlIdentifier($GLOBALS['PMA_substr']($sql, $count2, 1), ($is_sql_variable || $is_digit))) {
485 $c2 = $GLOBALS['PMA_substr']($sql, $count2, 1);
486 if ($is_sql_variable && ($c2 == '.')) {
487 $count2++;
488 continue;
489 }
490 if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
491 $count2++;
492 if (!$is_float_digit) {
493 $is_float_digit = TRUE;
494 continue;
495 } else {
496 $debugstr = $GLOBALS['strSQPBugInvalidIdentifer'] . ' @ ' . ($count1+1) . "\n"
497 . 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));
498 PMA_SQP_throwError($debugstr, $sql);
499 return $sql_array;
500 }
501 }
502 if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {
503 if (!$is_float_digit_exponent) {
504 $is_float_digit_exponent = TRUE;
505 $is_float_digit = TRUE;
506 $count2++;
507 continue;
508 } else {
509 $is_digit = FALSE;
510 $is_float_digit = FALSE;
511 }
512 }
513 if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && $GLOBALS['PMA_STR_isDigit']($c2))) {
514 $count2++;
515 continue;
516 } else {
517 $is_digit = FALSE;
518 $is_hex_digit = FALSE;
519 }
520
521 $count2++;
522 } // end while
523
524 $l = $count2 - $count1;
525 $str = $GLOBALS['PMA_substr']($sql, $count1, $l);
526
527 $type = '';
528 if ($is_digit || $is_float_digit || $is_hex_digit) {
529 $type = 'digit';
530 if ($is_float_digit) {
531 $type .= '_float';
532 } elseif ($is_hex_digit) {
533 $type .= '_hex';
534 } else {
535 $type .= '_integer';
536 }
537 } elseif ($is_user) {
538 $type = 'punct_user';
539 } elseif ($is_sql_variable != FALSE) {
540 $type = 'alpha_variable';
541 } else {
542 $type = 'alpha';
543 } // end if... else....
544 PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize, $count2);
545
546 continue;
547 }
548
549 // Checks for punct
550 if ($GLOBALS['PMA_strpos']($allpunct_list, $c) !== false) {
551 while (($count2 < $len) && $GLOBALS['PMA_strpos']($allpunct_list, $GLOBALS['PMA_substr']($sql, $count2, 1)) !== false) {
552 $count2++;
553 }
554 $l = $count2 - $count1;
555 if ($l == 1) {
556 $punct_data = $c;
557 } else {
558 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $l);
559 }
560
561 // Special case, sometimes, althought two characters are
562 // adjectent directly, they ACTUALLY need to be seperate
563 /* DEBUG
564 echo '<pre>';
565 var_dump($l);
566 var_dump($punct_data);
567 echo '</pre>';
568 */
569
570 if ($l == 1) {
571 $t_suffix = '';
572 switch ($punct_data) {
573 case $punct_queryend:
574 $t_suffix = '_queryend';
575 break;
576 case $punct_qualifier:
577 $t_suffix = '_qualifier';
578 $this_was_punct = true;
579 break;
580 case $punct_listsep:
581 $this_was_listsep = true;
582 $t_suffix = '_listsep';
583 break;
584 default:
585 break;
586 }
587 PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
588 } elseif ($punct_data == $GLOBALS['sql_delimiter'] || PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {
589 // Ok, we have one of the valid combined punct expressions
590 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
591 } else {
592 // Bad luck, lets split it up more
593 $first = $punct_data[0];
594 $first2 = $punct_data[0] . $punct_data[1];
595 $last2 = $punct_data[$l - 2] . $punct_data[$l - 1];
596 $last = $punct_data[$l - 1];
597 if (($first == ',') || ($first == ';') || ($first == '.') || ($first == '*')) {
598 $count2 = $count1 + 1;
599 $punct_data = $first;
600 } elseif (($last2 == '/*') || (($last2 == '--') && ($count2 == $len || $GLOBALS['PMA_substr']($sql, $count2, 1) <= ' '))) {
601 $count2 -= 2;
602 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
603 } elseif (($last == '-') || ($last == '+') || ($last == '!')) {
604 $count2--;
605 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
606 /**
607 * @todo for negation operator, split in 2 tokens ?
608 * "select x&~1 from t"
609 * becomes "select x & ~ 1 from t" ?
610 */
611
612 } elseif ($last != '~') {
613 $debugstr = $GLOBALS['strSQPBugUnknownPunctuation'] . ' @ ' . ($count1+1) . "\n"
614 . 'STR: ' . htmlspecialchars($punct_data);
615 PMA_SQP_throwError($debugstr, $sql);
616 return $sql_array;
617 }
618 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
619 continue;
620 } // end if... elseif... else
621 continue;
622 }
623
624 // DEBUG
625 $count2++;
626
627 $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len . "\n"
628 . 'STR: ' . $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1) . "\n";
629 PMA_SQP_bug($debugstr, $sql);
630 return $sql_array;
631
632 } // end while ($count2 < $len)
633
634 /*
635 echo '<pre>';
636 print_r($sql_array);
637 echo '</pre>';
638 */
639
640 if ($arraysize > 0) {
641 $t_next = $sql_array[0]['type'];
642 $t_prev = '';
643 $t_bef_prev = '';
644 $t_cur = '';
645 $d_next = $sql_array[0]['data'];
646 $d_prev = '';
647 $d_bef_prev = '';
648 $d_cur = '';
649 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
650 $d_prev_upper = '';
651 $d_bef_prev_upper = '';
652 $d_cur_upper = '';
653 }
654
655 for ($i = 0; $i < $arraysize; $i++) {
656 $t_bef_prev = $t_prev;
657 $t_prev = $t_cur;
658 $t_cur = $t_next;
659 $d_bef_prev = $d_prev;
660 $d_prev = $d_cur;
661 $d_cur = $d_next;
662 $d_bef_prev_upper = $d_prev_upper;
663 $d_prev_upper = $d_cur_upper;
664 $d_cur_upper = $d_next_upper;
665 if (($i + 1) < $arraysize) {
666 $t_next = $sql_array[$i + 1]['type'];
667 $d_next = $sql_array[$i + 1]['data'];
668 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
669 } else {
670 $t_next = '';
671 $d_next = '';
672 $d_next_upper = '';
673 }
674
675 //DEBUG echo "[prev: <strong>".$d_prev."</strong> ".$t_prev."][cur: <strong>".$d_cur."</strong> ".$t_cur."][next: <strong>".$d_next."</strong> ".$t_next."]<br />";
676
677 if ($t_cur == 'alpha') {
678 $t_suffix = '_identifier';
679 // for example: `thebit` bit(8) NOT NULL DEFAULT b'0'
680 if ($t_prev == 'alpha' && $d_prev == 'DEFAULT' && $d_cur == 'b' && $t_next == 'quote_single') {
681 $t_suffix = '_bitfield_constant_introducer';
682 } elseif (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
683 $t_suffix = '_identifier';
684 } elseif (($t_next == 'punct_bracket_open_round')
685 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
686 /**
687 * @todo 2005-10-16: in the case of a CREATE TABLE containing
688 * a TIMESTAMP, since TIMESTAMP() is also a function, it's
689 * found here and the token is wrongly marked as alpha_functionName.
690 * But we compensate for this when analysing for timestamp_not_null
691 * later in this script.
692 *
693 * Same applies to CHAR vs. CHAR() function.
694 */
695 $t_suffix = '_functionName';
696 /* There are functions which might be as well column types */
697 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
698 $t_suffix = '_columnType';
699
700 /**
701 * Temporary fix for BUG #621357 and #2027720
702 *
703 * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
704 */
705 if (($d_cur_upper == 'SET' || $d_cur_upper == 'BINARY') && $t_next != 'punct_bracket_open_round') {
706 $t_suffix = '_reservedWord';
707 }
708 //END OF TEMPORARY FIX
709
710 // CHARACTER is a synonym for CHAR, but can also be meant as
711 // CHARACTER SET. In this case, we have a reserved word.
712 if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
713 $t_suffix = '_reservedWord';
714 }
715
716 // experimental
717 // current is a column type, so previous must not be
718 // a reserved word but an identifier
719 // CREATE TABLE SG_Persons (first varchar(64))
720
721 //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
722 // $sql_array[$i-1]['type'] = 'alpha_identifier';
723 //}
724
725 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
726 $t_suffix = '_reservedWord';
727 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
728 $t_suffix = '_columnAttrib';
729 // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
730 // it should be regarded as a reserved word.
731 if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
732 $t_suffix = '_reservedWord';
733 }
734
735 if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
736 $t_suffix = '_reservedWord';
737 }
738 // Binary as character set
739 if ($d_cur_upper == 'BINARY' && (
740 ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
741 || ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
742 || ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
743 || $d_prev_upper == 'CHARSET'
744 ) && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {
745 $t_suffix = '_charset';
746 }
747 } elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)
748 || PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)
749 || ($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {
750 $t_suffix = '_charset';
751 } else {
752 // Do nothing
753 }
754 // check if present in the list of forbidden words
755 if ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {
756 $sql_array[$i]['forbidden'] = TRUE;
757 } else {
758 $sql_array[$i]['forbidden'] = FALSE;
759 }
760 $sql_array[$i]['type'] .= $t_suffix;
761 }
762 } // end for
763
764 // Stores the size of the array inside the array, as count() is a slow
765 // operation.
766 $sql_array['len'] = $arraysize;
767
768 // DEBUG echo 'After parsing<pre>'; print_r($sql_array); echo '</pre>';
769 // Sends the data back
770 return $sql_array;
771 } // end of the "PMA_SQP_parse()" function
772
773 /**
774 * Checks for token types being what we want...
775 *
776 * @param string String of type that we have
777 * @param string String of type that we want
778 *
779 * @return boolean result of check
780 *
781 * @access private
782 */
783 function PMA_SQP_typeCheck($toCheck, $whatWeWant)
784 {
785 $typeSeperator = '_';
786 if (strcmp($whatWeWant, $toCheck) == 0) {
787 return TRUE;
788 } else {
789 if (strpos($whatWeWant, $typeSeperator) === FALSE) {
790 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
791 } else {
792 return FALSE;
793 }
794 }
795 }
796
797
798 /**
799 * Analyzes SQL queries
800 *
801 * @param array The SQL queries
802 *
803 * @return array The analyzed SQL queries
804 *
805 * @access public
806 */
807 function PMA_SQP_analyze($arr)
808 {
809 if ($arr == array()) {
810 return array();
811 }
812 $result = array();
813 $size = $arr['len'];
814 $subresult = array(
815 'querytype' => '',
816 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
817 'position_of_first_select' => '', // the array index
818 'from_clause'=> '',
819 'group_by_clause'=> '',
820 'order_by_clause'=> '',
821 'having_clause' => '',
822 'limit_clause' => '',
823 'where_clause' => '',
824 'where_clause_identifiers' => array(),
825 'unsorted_query' => '',
826 'queryflags' => array(),
827 'select_expr' => array(),
828 'table_ref' => array(),
829 'foreign_keys' => array(),
830 'create_table_fields' => array()
831 );
832 $subresult_empty = $subresult;
833 $seek_queryend = FALSE;
834 $seen_end_of_table_ref = FALSE;
835 $number_of_brackets_in_extract = 0;
836 $number_of_brackets_in_group_concat = 0;
837
838 $number_of_brackets = 0;
839 $in_subquery = false;
840 $seen_subquery = false;
841 $seen_from = false;
842
843 // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
844 // we must not use CURDATE as a table_ref
845 // so we track whether we are in the EXTRACT()
846 $in_extract = FALSE;
847
848 // for GROUP_CONCAT(...)
849 $in_group_concat = FALSE;
850
851 /* Description of analyzer results by lem9
852 *
853 * db, table, column, alias
854 * ------------------------
855 *
856 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
857 *
858 * The SELECT syntax (simplified) is
859 *
860 * SELECT
861 * select_expression,...
862 * [FROM [table_references]
863 *
864 *
865 * ['select_expr'] is filled with each expression, the key represents the
866 * expression position in the list (0-based) (so we don't lose track of
867 * multiple occurences of the same column).
868 *
869 * ['table_ref'] is filled with each table ref, same thing for the key.
870 *
871 * I create all sub-values empty, even if they are
872 * not present (for example no select_expression alias).
873 *
874 * There is a debug section at the end of loop #1, if you want to
875 * see the exact contents of select_expr and table_ref
876 *
877 * queryflags
878 * ----------
879 *
880 * In $subresult, array 'queryflags' is filled, according to what we
881 * find in the query.
882 *
883 * Currently, those are generated:
884 *
885 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
886 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
887 * ['queryflags']['distinct'] = 1; for a DISTINCT
888 * ['queryflags']['union'] = 1; for a UNION
889 * ['queryflags']['join'] = 1; for a JOIN
890 * ['queryflags']['offset'] = 1; for the presence of OFFSET
891 * ['queryflags']['procedure'] = 1; for the presence of PROCEDURE
892 *
893 * query clauses
894 * -------------
895 *
896 * The select is splitted in those clauses:
897 * ['select_expr_clause']
898 * ['from_clause']
899 * ['group_by_clause']
900 * ['order_by_clause']
901 * ['having_clause']
902 * ['limit_clause']
903 * ['where_clause']
904 *
905 * The identifiers of the WHERE clause are put into the array
906 * ['where_clause_identifier']
907 *
908 * For a SELECT, the whole query without the ORDER BY clause is put into
909 * ['unsorted_query']
910 *
911 * foreign keys
912 * ------------
913 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
914 * analyzed and ['foreign_keys'] is an array filled with
915 * the constraint name, the index list,
916 * the REFERENCES table name and REFERENCES index list,
917 * and ON UPDATE | ON DELETE clauses
918 *
919 * position_of_first_select
920 * ------------------------
921 *
922 * The array index of the first SELECT we find. Will be used to
923 * insert a SQL_CALC_FOUND_ROWS.
924 *
925 * create_table_fields
926 * -------------------
927 *
928 * Used to detect the DEFAULT CURRENT_TIMESTAMP and
929 * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
930 * Also used to store the default value of the field.
931 * An array, each element is the identifier name.
932 * Note that for now, the timestamp_not_null element is created
933 * even for non-TIMESTAMP fields.
934 *
935 * Sub-elements: ['type'] which contains the column type
936 * optional (currently they are never false but can be absent):
937 * ['default_current_timestamp'] boolean
938 * ['on_update_current_timestamp'] boolean
939 * ['timestamp_not_null'] boolean
940 *
941 * section_before_limit, section_after_limit
942 * -----------------------------------------
943 *
944 * Marks the point of the query where we can insert a LIMIT clause;
945 * so the section_before_limit will contain the left part before
946 * a possible LIMIT clause
947 *
948 *
949 * End of description of analyzer results
950 */
951
952 // must be sorted
953 // TODO: current logic checks for only one word, so I put only the
954 // first word of the reserved expressions that end a table ref;
955 // maybe this is not ok (the first word might mean something else)
956 // $words_ending_table_ref = array(
957 // 'FOR UPDATE',
958 // 'GROUP BY',
959 // 'HAVING',
960 // 'LIMIT',
961 // 'LOCK IN SHARE MODE',
962 // 'ORDER BY',
963 // 'PROCEDURE',
964 // 'UNION',
965 // 'WHERE'
966 // );
967 $words_ending_table_ref = array(
968 'FOR',
969 'GROUP',
970 'HAVING',
971 'LIMIT',
972 'LOCK',
973 'ORDER',
974 'PROCEDURE',
975 'UNION',
976 'WHERE'
977 );
978 $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
979
980 $words_ending_clauses = array(
981 'FOR',
982 'LIMIT',
983 'LOCK',
984 'PROCEDURE',
985 'UNION'
986 );
987 $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
988
989
990
991
992 // must be sorted
993 $supported_query_types = array(
994 'SELECT'
995 /*
996 // Support for these additional query types will come later on.
997 'DELETE',
998 'INSERT',
999 'REPLACE',
1000 'TRUNCATE',
1001 'UPDATE'
1002 'EXPLAIN',
1003 'DESCRIBE',
1004 'SHOW',
1005 'CREATE',
1006 'SET',
1007 'ALTER'
1008 */
1009 );
1010 $supported_query_types_cnt = count($supported_query_types);
1011
1012 // loop #1 for each token: select_expr, table_ref for SELECT
1013
1014 for ($i = 0; $i < $size; $i++) {
1015 //DEBUG echo "Loop1 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1016
1017 // High speed seek for locating the end of the current query
1018 if ($seek_queryend == TRUE) {
1019 if ($arr[$i]['type'] == 'punct_queryend') {
1020 $seek_queryend = FALSE;
1021 } else {
1022 continue;
1023 } // end if (type == punct_queryend)
1024 } // end if ($seek_queryend)
1025
1026 /**
1027 * Note: do not split if this is a punct_queryend for the first and only query
1028 * @todo when we find a UNION, should we split in another subresult?
1029 */
1030 if ($arr[$i]['type'] == 'punct_queryend' && ($i + 1 != $size)) {
1031 $result[] = $subresult;
1032 $subresult = $subresult_empty;
1033 continue;
1034 } // end if (type == punct_queryend)
1035
1036 // ==============================================================
1037 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1038 $number_of_brackets++;
1039 if ($in_extract) {
1040 $number_of_brackets_in_extract++;
1041 }
1042 if ($in_group_concat) {
1043 $number_of_brackets_in_group_concat++;
1044 }
1045 }
1046 // ==============================================================
1047 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1048 $number_of_brackets--;
1049 if ($number_of_brackets == 0) {
1050 $in_subquery = false;
1051 }
1052 if ($in_extract) {
1053 $number_of_brackets_in_extract--;
1054 if ($number_of_brackets_in_extract == 0) {
1055 $in_extract = FALSE;
1056 }
1057 }
1058 if ($in_group_concat) {
1059 $number_of_brackets_in_group_concat--;
1060 if ($number_of_brackets_in_group_concat == 0) {
1061 $in_group_concat = FALSE;
1062 }
1063 }
1064 }
1065
1066 if ($in_subquery) {
1067 /**
1068 * skip the subquery to avoid setting
1069 * select_expr or table_ref with the contents
1070 * of this subquery; this is to avoid a bug when
1071 * trying to edit the results of
1072 * select * from child where not exists (select id from
1073 * parent where child.parent_id = parent.id);
1074 */
1075 continue;
1076 }
1077 // ==============================================================
1078 if ($arr[$i]['type'] == 'alpha_functionName') {
1079 $upper_data = strtoupper($arr[$i]['data']);
1080 if ($upper_data =='EXTRACT') {
1081 $in_extract = TRUE;
1082 $number_of_brackets_in_extract = 0;
1083 }
1084 if ($upper_data =='GROUP_CONCAT') {
1085 $in_group_concat = TRUE;
1086 $number_of_brackets_in_group_concat = 0;
1087 }
1088 }
1089
1090 // ==============================================================
1091 if ($arr[$i]['type'] == 'alpha_reservedWord'
1092 // && $arr[$i]['forbidden'] == FALSE) {
1093 ) {
1094 // We don't know what type of query yet, so run this
1095 if ($subresult['querytype'] == '') {
1096 $subresult['querytype'] = strtoupper($arr[$i]['data']);
1097 } // end if (querytype was empty)
1098
1099 // Check if we support this type of query
1100 if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
1101 // Skip ahead to the next one if we don't
1102 $seek_queryend = TRUE;
1103 continue;
1104 } // end if (query not supported)
1105
1106 // upper once
1107 $upper_data = strtoupper($arr[$i]['data']);
1108 /**
1109 * @todo reset for each query?
1110 */
1111
1112 if ($upper_data == 'SELECT') {
1113 if ($number_of_brackets > 0) {
1114 $in_subquery = true;
1115 $seen_subquery = true;
1116 // this is a subquery so do not analyze inside it
1117 continue;
1118 }
1119 $seen_from = FALSE;
1120 $previous_was_identifier = FALSE;
1121 $current_select_expr = -1;
1122 $seen_end_of_table_ref = FALSE;
1123 } // end if (data == SELECT)
1124
1125 if ($upper_data =='FROM' && !$in_extract) {
1126 $current_table_ref = -1;
1127 $seen_from = TRUE;
1128 $previous_was_identifier = FALSE;
1129 $save_table_ref = TRUE;
1130 } // end if (data == FROM)
1131
1132 // here, do not 'continue' the loop, as we have more work for
1133 // reserved words below
1134 } // end if (type == alpha_reservedWord)
1135
1136 // ==============================
1137 if ($arr[$i]['type'] == 'quote_backtick'
1138 || $arr[$i]['type'] == 'quote_double'
1139 || $arr[$i]['type'] == 'quote_single'
1140 || $arr[$i]['type'] == 'alpha_identifier'
1141 || ($arr[$i]['type'] == 'alpha_reservedWord'
1142 && $arr[$i]['forbidden'] == FALSE)) {
1143
1144 switch ($arr[$i]['type']) {
1145 case 'alpha_identifier':
1146 case 'alpha_reservedWord':
1147 /**
1148 * this is not a real reservedWord, because it's not
1149 * present in the list of forbidden words, for example
1150 * "storage" which can be used as an identifier
1151 *
1152 * @todo avoid the pretty printing in color in this case
1153 */
1154 $identifier = $arr[$i]['data'];
1155 break;
1156
1157 case 'quote_backtick':
1158 case 'quote_double':
1159 case 'quote_single':
1160 $identifier = PMA_unQuote($arr[$i]['data']);
1161 break;
1162 } // end switch
1163
1164 if ($subresult['querytype'] == 'SELECT'
1165 && ! $in_group_concat
1166 && ! ($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
1167 if (!$seen_from) {
1168 if ($previous_was_identifier && isset($chain)) {
1169 // found alias for this select_expr, save it
1170 // but only if we got something in $chain
1171 // (for example, SELECT COUNT(*) AS cnt
1172 // puts nothing in $chain, so we avoid
1173 // setting the alias)
1174 $alias_for_select_expr = $identifier;
1175 } else {
1176 $chain[] = $identifier;
1177 $previous_was_identifier = TRUE;
1178
1179 } // end if !$previous_was_identifier
1180 } else {
1181 // ($seen_from)
1182 if ($save_table_ref && !$seen_end_of_table_ref) {
1183 if ($previous_was_identifier) {
1184 // found alias for table ref
1185 // save it for later
1186 $alias_for_table_ref = $identifier;
1187 } else {
1188 $chain[] = $identifier;
1189 $previous_was_identifier = TRUE;
1190
1191 } // end if ($previous_was_identifier)
1192 } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1193 } // end if (!$seen_from)
1194 } // end if (querytype SELECT)
1195 } // end if (quote_backtick or double quote or alpha_identifier)
1196
1197 // ===================================
1198 if ($arr[$i]['type'] == 'punct_qualifier') {
1199 // to be able to detect an identifier following another
1200 $previous_was_identifier = FALSE;
1201 continue;
1202 } // end if (punct_qualifier)
1203
1204 /**
1205 * @todo check if 3 identifiers following one another -> error
1206 */
1207
1208 // s a v e a s e l e c t e x p r
1209 // finding a list separator or FROM
1210 // means that we must save the current chain of identifiers
1211 // into a select expression
1212
1213 // for now, we only save a select expression if it contains
1214 // at least one identifier, as we are interested in checking
1215 // the columns and table names, so in "select * from persons",
1216 // the "*" is not saved
1217
1218 if (isset($chain) && !$seen_end_of_table_ref
1219 && ((!$seen_from && $arr[$i]['type'] == 'punct_listsep')
1220 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM'))) {
1221 $size_chain = count($chain);
1222 $current_select_expr++;
1223 $subresult['select_expr'][$current_select_expr] = array(
1224 'expr' => '',
1225 'alias' => '',
1226 'db' => '',
1227 'table_name' => '',
1228 'table_true_name' => '',
1229 'column' => ''
1230 );
1231
1232 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1233 // we had found an alias for this select expression
1234 $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1235 unset($alias_for_select_expr);
1236 }
1237 // there is at least a column
1238 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1239 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1240
1241 // maybe a table
1242 if ($size_chain > 1) {
1243 $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1244 // we assume for now that this is also the true name
1245 $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1246 $subresult['select_expr'][$current_select_expr]['expr']
1247 = $subresult['select_expr'][$current_select_expr]['table_name']
1248 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1249 } // end if ($size_chain > 1)
1250
1251 // maybe a db
1252 if ($size_chain > 2) {
1253 $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1254 $subresult['select_expr'][$current_select_expr]['expr']
1255 = $subresult['select_expr'][$current_select_expr]['db']
1256 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1257 } // end if ($size_chain > 2)
1258 unset($chain);
1259
1260 /**
1261 * @todo explain this:
1262 */
1263 if (($arr[$i]['type'] == 'alpha_reservedWord')
1264 && ($upper_data != 'FROM')) {
1265 $previous_was_identifier = TRUE;
1266 }
1267
1268 } // end if (save a select expr)
1269
1270
1271 //======================================
1272 // s a v e a t a b l e r e f
1273 //======================================
1274
1275 // maybe we just saw the end of table refs
1276 // but the last table ref has to be saved
1277 // or we are at the last token
1278 // or we just got a reserved word
1279 /**
1280 * @todo there could be another query after this one
1281 */
1282
1283 if (isset($chain) && $seen_from && $save_table_ref
1284 && ($arr[$i]['type'] == 'punct_listsep'
1285 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1286 || $seen_end_of_table_ref
1287 || $i==$size-1)) {
1288
1289 $size_chain = count($chain);
1290 $current_table_ref++;
1291 $subresult['table_ref'][$current_table_ref] = array(
1292 'expr' => '',
1293 'db' => '',
1294 'table_name' => '',
1295 'table_alias' => '',
1296 'table_true_name' => ''
1297 );
1298 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1299 $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1300 unset($alias_for_table_ref);
1301 }
1302 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1303 // we assume for now that this is also the true name
1304 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1305 $subresult['table_ref'][$current_table_ref]['expr']
1306 = $subresult['table_ref'][$current_table_ref]['table_name'];
1307 // maybe a db
1308 if ($size_chain > 1) {
1309 $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1310 $subresult['table_ref'][$current_table_ref]['expr']
1311 = $subresult['table_ref'][$current_table_ref]['db']
1312 . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1313 } // end if ($size_chain > 1)
1314
1315 // add the table alias into the whole expression
1316 $subresult['table_ref'][$current_table_ref]['expr']
1317 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1318
1319 unset($chain);
1320 $previous_was_identifier = TRUE;
1321 //continue;
1322
1323 } // end if (save a table ref)
1324
1325
1326 // when we have found all table refs,
1327 // for each table_ref alias, put the true name of the table
1328 // in the corresponding select expressions
1329
1330 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
1331 for ($tr=0; $tr <= $current_table_ref; $tr++) {
1332 $alias = $subresult['table_ref'][$tr]['table_alias'];
1333 $truename = $subresult['table_ref'][$tr]['table_true_name'];
1334 for ($se=0; $se <= $current_select_expr; $se++) {
1335 if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1336 == $alias) {
1337 $subresult['select_expr'][$se]['table_true_name']
1338 = $truename;
1339 } // end if (found the alias)
1340 } // end for (select expressions)
1341
1342 } // end for (table refs)
1343 } // end if (set the true names)
1344
1345
1346 // e n d i n g l o o p #1
1347 // set the $previous_was_identifier to FALSE if the current
1348 // token is not an identifier
1349 if (($arr[$i]['type'] != 'alpha_identifier')
1350 && ($arr[$i]['type'] != 'quote_double')
1351 && ($arr[$i]['type'] != 'quote_single')
1352 && ($arr[$i]['type'] != 'quote_backtick')) {
1353 $previous_was_identifier = FALSE;
1354 } // end if
1355
1356 // however, if we are on AS, we must keep the $previous_was_identifier
1357 if (($arr[$i]['type'] == 'alpha_reservedWord')
1358 && ($upper_data == 'AS')) {
1359 $previous_was_identifier = TRUE;
1360 }
1361
1362 if (($arr[$i]['type'] == 'alpha_reservedWord')
1363 && ($upper_data =='ON' || $upper_data =='USING')) {
1364 $save_table_ref = FALSE;
1365 } // end if (data == ON)
1366
1367 if (($arr[$i]['type'] == 'alpha_reservedWord')
1368 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1369 $save_table_ref = TRUE;
1370 } // end if (data == JOIN)
1371
1372 /**
1373 * no need to check the end of table ref if we already did
1374 *
1375 * @todo maybe add "&& $seen_from"
1376 */
1377 if (!$seen_end_of_table_ref) {
1378 // if this is the last token, it implies that we have
1379 // seen the end of table references
1380 // Check for the end of table references
1381 //
1382 // Note: if we are analyzing a GROUP_CONCAT clause,
1383 // we might find a word that seems to indicate that
1384 // we have found the end of table refs (like ORDER)
1385 // but it's a modifier of the GROUP_CONCAT so
1386 // it's not the real end of table refs
1387 if (($i == $size-1)
1388 || ($arr[$i]['type'] == 'alpha_reservedWord'
1389 && !$in_group_concat
1390 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1391 $seen_end_of_table_ref = TRUE;
1392 // to be able to save the last table ref, but do not
1393 // set it true if we found a word like "ON" that has
1394 // already set it to false
1395 if (isset($save_table_ref) && $save_table_ref != FALSE) {
1396 $save_table_ref = TRUE;
1397 } //end if
1398
1399 } // end if (check for end of table ref)
1400 } //end if (!$seen_end_of_table_ref)
1401
1402 if ($seen_end_of_table_ref) {
1403 $save_table_ref = FALSE;
1404 } // end if
1405
1406 } // end for $i (loop #1)
1407
1408 //DEBUG
1409 /*
1410 if (isset($current_select_expr)) {
1411 for ($trace=0; $trace<=$current_select_expr; $trace++) {
1412 echo "<br />";
1413 reset ($subresult['select_expr'][$trace]);
1414 while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1415 echo "sel expr $trace $key => $val<br />\n";
1416 }
1417 }
1418
1419 if (isset($current_table_ref)) {
1420 echo "current_table_ref = " . $current_table_ref . "<br>";
1421 for ($trace=0; $trace<=$current_table_ref; $trace++) {
1422
1423 echo "<br />";
1424 reset ($subresult['table_ref'][$trace]);
1425 while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1426 echo "table ref $trace $key => $val<br />\n";
1427 }
1428 }
1429 */
1430 // -------------------------------------------------------
1431
1432
1433 // loop #2: - queryflags
1434 // - querytype (for queries != 'SELECT')
1435 // - section_before_limit, section_after_limit
1436 //
1437 // we will also need this queryflag in loop 2
1438 // so set it here
1439 if (isset($current_table_ref) && $current_table_ref > -1) {
1440 $subresult['queryflags']['select_from'] = 1;
1441 }
1442
1443 $section_before_limit = '';
1444 $section_after_limit = ''; // truly the section after the limit clause
1445 $seen_reserved_word = FALSE;
1446 $seen_group = FALSE;
1447 $seen_order = FALSE;
1448 $seen_order_by = FALSE;
1449 $in_group_by = FALSE; // true when we are inside the GROUP BY clause
1450 $in_order_by = FALSE; // true when we are inside the ORDER BY clause
1451 $in_having = FALSE; // true when we are inside the HAVING clause
1452 $in_select_expr = FALSE; // true when we are inside the select expr clause
1453 $in_where = FALSE; // true when we are inside the WHERE clause
1454 $seen_limit = FALSE; // true if we have seen a LIMIT clause
1455 $in_limit = FALSE; // true when we are inside the LIMIT clause
1456 $after_limit = FALSE; // true when we are after the LIMIT clause
1457 $in_from = FALSE; // true when we are in the FROM clause
1458 $in_group_concat = FALSE;
1459 $first_reserved_word = '';
1460 $current_identifier = '';
1461 $unsorted_query = $arr['raw']; // in case there is no ORDER BY
1462 $number_of_brackets = 0;
1463 $in_subquery = false;
1464
1465 for ($i = 0; $i < $size; $i++) {
1466 //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1467
1468 // need_confirm
1469 //
1470 // check for reserved words that will have to generate
1471 // a confirmation request later in sql.php
1472 // the cases are:
1473 // DROP TABLE
1474 // DROP DATABASE
1475 // ALTER TABLE... DROP
1476 // DELETE FROM...
1477 //
1478 // this code is not used for confirmations coming from functions.js
1479
1480 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1481 $number_of_brackets++;
1482 }
1483
1484 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1485 $number_of_brackets--;
1486 if ($number_of_brackets == 0) {
1487 $in_subquery = false;
1488 }
1489 }
1490
1491 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1492 $upper_data = strtoupper($arr[$i]['data']);
1493
1494 if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
1495 $in_subquery = true;
1496 }
1497
1498 if (!$seen_reserved_word) {
1499 $first_reserved_word = $upper_data;
1500 $subresult['querytype'] = $upper_data;
1501 $seen_reserved_word = TRUE;
1502
1503 // if the first reserved word is DROP or DELETE,
1504 // we know this is a query that needs to be confirmed
1505 if ($first_reserved_word=='DROP'
1506 || $first_reserved_word == 'DELETE'
1507 || $first_reserved_word == 'TRUNCATE') {
1508 $subresult['queryflags']['need_confirm'] = 1;
1509 }
1510
1511 if ($first_reserved_word=='SELECT'){
1512 $position_of_first_select = $i;
1513 }
1514
1515 } else {
1516 if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1517 $subresult['queryflags']['need_confirm'] = 1;
1518 }
1519 }
1520
1521 if ($upper_data == 'LIMIT' && ! $in_subquery) {
1522 $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1523 $in_limit = TRUE;
1524 $seen_limit = TRUE;
1525 $limit_clause = '';
1526 $in_order_by = FALSE; // @todo maybe others to set FALSE
1527 }
1528
1529 if ($upper_data == 'PROCEDURE') {
1530 $subresult['queryflags']['procedure'] = 1;
1531 $in_limit = FALSE;
1532 $after_limit = TRUE;
1533 }
1534 /**
1535 * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1536 */
1537 if ($upper_data == 'SELECT') {
1538 $in_select_expr = TRUE;
1539 $select_expr_clause = '';
1540 }
1541 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1542 $subresult['queryflags']['distinct'] = 1;
1543 }
1544
1545 if ($upper_data == 'UNION') {
1546 $subresult['queryflags']['union'] = 1;
1547 }
1548
1549 if ($upper_data == 'JOIN') {
1550 $subresult['queryflags']['join'] = 1;
1551 }
1552
1553 if ($upper_data == 'OFFSET') {
1554 $subresult['queryflags']['offset'] = 1;
1555 }
1556
1557 // if this is a real SELECT...FROM
1558 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1559 $in_from = TRUE;
1560 $from_clause = '';
1561 $in_select_expr = FALSE;
1562 }
1563
1564
1565 // (we could have less resetting of variables to FALSE
1566 // if we trust that the query respects the standard
1567 // MySQL order for clauses)
1568
1569 // we use $seen_group and $seen_order because we are looking
1570 // for the BY
1571 if ($upper_data == 'GROUP') {
1572 $seen_group = TRUE;
1573 $seen_order = FALSE;
1574 $in_having = FALSE;
1575 $in_order_by = FALSE;
1576 $in_where = FALSE;
1577 $in_select_expr = FALSE;
1578 $in_from = FALSE;
1579 }
1580 if ($upper_data == 'ORDER' && !$in_group_concat) {
1581 $seen_order = TRUE;
1582 $seen_group = FALSE;
1583 $in_having = FALSE;
1584 $in_group_by = FALSE;
1585 $in_where = FALSE;
1586 $in_select_expr = FALSE;
1587 $in_from = FALSE;
1588 }
1589 if ($upper_data == 'HAVING') {
1590 $in_having = TRUE;
1591 $having_clause = '';
1592 $seen_group = FALSE;
1593 $seen_order = FALSE;
1594 $in_group_by = FALSE;
1595 $in_order_by = FALSE;
1596 $in_where = FALSE;
1597 $in_select_expr = FALSE;
1598 $in_from = FALSE;
1599 }
1600
1601 if ($upper_data == 'WHERE') {
1602 $in_where = TRUE;
1603 $where_clause = '';
1604 $where_clause_identifiers = array();
1605 $seen_group = FALSE;
1606 $seen_order = FALSE;
1607 $in_group_by = FALSE;
1608 $in_order_by = FALSE;
1609 $in_having = FALSE;
1610 $in_select_expr = FALSE;
1611 $in_from = FALSE;
1612 }
1613
1614 if ($upper_data == 'BY') {
1615 if ($seen_group) {
1616 $in_group_by = TRUE;
1617 $group_by_clause = '';
1618 }
1619 if ($seen_order) {
1620 $seen_order_by = TRUE;
1621 // Here we assume that the ORDER BY keywords took
1622 // exactly 8 characters.
1623 // We use PMA_substr() to be charset-safe; otherwise
1624 // if the table name contains accents, the unsorted
1625 // query would be missing some characters.
1626 $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1627 $in_order_by = TRUE;
1628 $order_by_clause = '';
1629 }
1630 }
1631
1632 // if we find one of the words that could end the clause
1633 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1634
1635 $in_group_by = FALSE;
1636 $in_order_by = FALSE;
1637 $in_having = FALSE;
1638 $in_where = FALSE;
1639 $in_select_expr = FALSE;
1640 $in_from = FALSE;
1641 }
1642
1643 } // endif (reservedWord)
1644
1645
1646 // do not add a space after a function name
1647 /**
1648 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1649 */
1650
1651 $sep = ' ';
1652 if ($arr[$i]['type'] == 'alpha_functionName') {
1653 $sep='';
1654 $upper_data = strtoupper($arr[$i]['data']);
1655 if ($upper_data =='GROUP_CONCAT') {
1656 $in_group_concat = TRUE;
1657 $number_of_brackets_in_group_concat = 0;
1658 }
1659 }
1660
1661 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1662 if ($in_group_concat) {
1663 $number_of_brackets_in_group_concat++;
1664 }
1665 }
1666 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1667 if ($in_group_concat) {
1668 $number_of_brackets_in_group_concat--;
1669 if ($number_of_brackets_in_group_concat == 0) {
1670 $in_group_concat = FALSE;
1671 }
1672 }
1673 }
1674
1675 // do not add a space after an identifier if followed by a dot
1676 if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1677 $sep = '';
1678 }
1679
1680 // do not add a space after a dot if followed by an identifier
1681 if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1682 $sep = '';
1683 }
1684
1685 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1686 $select_expr_clause .= $arr[$i]['data'] . $sep;
1687 }
1688 if ($in_from && $upper_data != 'FROM') {
1689 $from_clause .= $arr[$i]['data'] . $sep;
1690 }
1691 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1692 $group_by_clause .= $arr[$i]['data'] . $sep;
1693 }
1694 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1695 // add a space only before ASC or DESC
1696 // not around the dot between dbname and tablename
1697 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1698 $order_by_clause .= $sep;
1699 }
1700 $order_by_clause .= $arr[$i]['data'];
1701 }
1702 if ($in_having && $upper_data != 'HAVING') {
1703 $having_clause .= $arr[$i]['data'] . $sep;
1704 }
1705 if ($in_where && $upper_data != 'WHERE') {
1706 $where_clause .= $arr[$i]['data'] . $sep;
1707
1708 if (($arr[$i]['type'] == 'quote_backtick')
1709 || ($arr[$i]['type'] == 'alpha_identifier')) {
1710 $where_clause_identifiers[] = $arr[$i]['data'];
1711 }
1712 }
1713
1714 // to grab the rest of the query after the ORDER BY clause
1715 if (isset($subresult['queryflags']['select_from'])
1716 && $subresult['queryflags']['select_from'] == 1
1717 && ! $in_order_by
1718 && $seen_order_by
1719 && $upper_data != 'BY') {
1720 $unsorted_query .= $arr[$i]['data'];
1721 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1722 && $arr[$i]['type'] != 'punct_bracket_close_round'
1723 && $arr[$i]['type'] != 'punct') {
1724 $unsorted_query .= $sep;
1725 }
1726 }
1727
1728 if ($in_limit) {
1729 if ($upper_data == 'OFFSET') {
1730 $limit_clause .= $sep;
1731 }
1732 $limit_clause .= $arr[$i]['data'];
1733 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1734 $limit_clause .= $sep;
1735 }
1736 }
1737 if ($after_limit && $seen_limit) {
1738 $section_after_limit .= $arr[$i]['data'] . $sep;
1739 }
1740
1741 // clear $upper_data for next iteration
1742 $upper_data='';
1743 } // end for $i (loop #2)
1744 if (empty($section_before_limit)) {
1745 $section_before_limit = $arr['raw'];
1746 }
1747
1748 // -----------------------------------------------------
1749 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1750 // (for now, check only the first query)
1751 // (for now, identifiers are assumed to be backquoted)
1752
1753 // If we find that we are dealing with a CREATE TABLE query,
1754 // we look for the next punct_bracket_open_round, which
1755 // introduces the fields list. Then, when we find a
1756 // quote_backtick, it must be a field, so we put it into
1757 // the create_table_fields array. Even if this field is
1758 // not a timestamp, it will be useful when logic has been
1759 // added for complete field attributes analysis.
1760
1761 $seen_foreign = FALSE;
1762 $seen_references = FALSE;
1763 $seen_constraint = FALSE;
1764 $foreign_key_number = -1;
1765 $seen_create_table = FALSE;
1766 $seen_create = FALSE;
1767 $seen_alter = FALSE;
1768 $in_create_table_fields = FALSE;
1769 $brackets_level = 0;
1770 $in_timestamp_options = FALSE;
1771 $seen_default = FALSE;
1772
1773 for ($i = 0; $i < $size; $i++) {
1774 // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1775
1776 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1777 $upper_data = strtoupper($arr[$i]['data']);
1778
1779 if ($upper_data == 'NOT' && $in_timestamp_options) {
1780 $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1781
1782 }
1783
1784 if ($upper_data == 'CREATE') {
1785 $seen_create = TRUE;
1786 }
1787
1788 if ($upper_data == 'ALTER') {
1789 $seen_alter = TRUE;
1790 }
1791
1792 if ($upper_data == 'TABLE' && $seen_create) {
1793 $seen_create_table = TRUE;
1794 $create_table_fields = array();
1795 }
1796
1797 if ($upper_data == 'CURRENT_TIMESTAMP') {
1798 if ($in_timestamp_options) {
1799 if ($seen_default) {
1800 $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1801 }
1802 }
1803 }
1804
1805 if ($upper_data == 'CONSTRAINT') {
1806 $foreign_key_number++;
1807 $seen_foreign = FALSE;
1808 $seen_references = FALSE;
1809 $seen_constraint = TRUE;
1810 }
1811 if ($upper_data == 'FOREIGN') {
1812 $seen_foreign = TRUE;
1813 $seen_references = FALSE;
1814 $seen_constraint = FALSE;
1815 }
1816 if ($upper_data == 'REFERENCES') {
1817 $seen_foreign = FALSE;
1818 $seen_references = TRUE;
1819 $seen_constraint = FALSE;
1820 }
1821
1822
1823 // Cases covered:
1824
1825 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1826 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1827
1828 // but we set ['on_delete'] or ['on_cascade'] to
1829 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1830
1831 // ON UPDATE CURRENT_TIMESTAMP
1832
1833 if ($upper_data == 'ON') {
1834 if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1835 $second_upper_data = strtoupper($arr[$i+1]['data']);
1836 if ($second_upper_data == 'DELETE') {
1837 $clause = 'on_delete';
1838 }
1839 if ($second_upper_data == 'UPDATE') {
1840 $clause = 'on_update';
1841 }
1842 if (isset($clause)
1843 && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1844
1845 // ugly workaround because currently, NO is not
1846 // in the list of reserved words in sqlparser.data
1847 // (we got a bug report about not being able to use
1848 // 'no' as an identifier)
1849 || ($arr[$i+2]['type'] == 'alpha_identifier'
1850 && strtoupper($arr[$i+2]['data'])=='NO'))
1851 ) {
1852 $third_upper_data = strtoupper($arr[$i+2]['data']);
1853 if ($third_upper_data == 'CASCADE'
1854 || $third_upper_data == 'RESTRICT') {
1855 $value = $third_upper_data;
1856 } elseif ($third_upper_data == 'SET'
1857 || $third_upper_data == 'NO') {
1858 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1859 $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1860 }
1861 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1862 if ($clause == 'on_update'
1863 && $in_timestamp_options) {
1864 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1865 $seen_default = FALSE;
1866 }
1867
1868 } else {
1869 $value = '';
1870 }
1871 if (!empty($value)) {
1872 $foreign[$foreign_key_number][$clause] = $value;
1873 }
1874 unset($clause);
1875 } // endif (isset($clause))
1876 }
1877 }
1878
1879 } // end of reserved words analysis
1880
1881
1882 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1883 $brackets_level++;
1884 if ($seen_create_table && $brackets_level == 1) {
1885 $in_create_table_fields = TRUE;
1886 }
1887 }
1888
1889
1890 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1891 $brackets_level--;
1892 if ($seen_references) {
1893 $seen_references = FALSE;
1894 }
1895 if ($seen_create_table && $brackets_level == 0) {
1896 $in_create_table_fields = FALSE;
1897 }
1898 }
1899
1900 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1901 $upper_data = strtoupper($arr[$i]['data']);
1902 if ($seen_create_table && $in_create_table_fields) {
1903 if ($upper_data == 'DEFAULT') {
1904 $seen_default = TRUE;
1905 $create_table_fields[$current_identifier]['default_value'] = $arr[$i + 1]['data'];
1906 }
1907 }
1908 }
1909
1910 /**
1911 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1912 */
1913 if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1914 $upper_data = strtoupper($arr[$i]['data']);
1915 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1916 $create_table_fields[$current_identifier]['type'] = $upper_data;
1917 if ($upper_data == 'TIMESTAMP') {
1918 $arr[$i]['type'] = 'alpha_columnType';
1919 $in_timestamp_options = TRUE;
1920 } else {
1921 $in_timestamp_options = FALSE;
1922 if ($upper_data == 'CHAR') {
1923 $arr[$i]['type'] = 'alpha_columnType';
1924 }
1925 }
1926 }
1927 }
1928
1929
1930 if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1931
1932 if ($arr[$i]['type'] == 'quote_backtick') {
1933 // remove backquotes
1934 $identifier = PMA_unQuote($arr[$i]['data']);
1935 } else {
1936 $identifier = $arr[$i]['data'];
1937 }
1938
1939 if ($seen_create_table && $in_create_table_fields) {
1940 $current_identifier = $identifier;
1941 // warning: we set this one even for non TIMESTAMP type
1942 $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1943 }
1944
1945 if ($seen_constraint) {
1946 $foreign[$foreign_key_number]['constraint'] = $identifier;
1947 }
1948
1949 if ($seen_foreign && $brackets_level > 0) {
1950 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1951 }
1952
1953 if ($seen_references) {
1954 if ($seen_alter && $brackets_level > 0) {
1955 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1956 // here, the first bracket level corresponds to the
1957 // bracket of CREATE TABLE
1958 // so if we are on level 2, it must be the index list
1959 // of the foreign key REFERENCES
1960 } elseif ($brackets_level > 1) {
1961 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1962 } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1963 // identifier is `db`.`table`
1964 // the first pass will pick the db name
1965 // the next pass will pick the table name
1966 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1967 } else {
1968 // identifier is `table`
1969 $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
1970 }
1971 }
1972 }
1973 } // end for $i (loop #3)
1974
1975
1976 // Fill the $subresult array
1977
1978 if (isset($create_table_fields)) {
1979 $subresult['create_table_fields'] = $create_table_fields;
1980 }
1981
1982 if (isset($foreign)) {
1983 $subresult['foreign_keys'] = $foreign;
1984 }
1985
1986 if (isset($select_expr_clause)) {
1987 $subresult['select_expr_clause'] = $select_expr_clause;
1988 }
1989 if (isset($from_clause)) {
1990 $subresult['from_clause'] = $from_clause;
1991 }
1992 if (isset($group_by_clause)) {
1993 $subresult['group_by_clause'] = $group_by_clause;
1994 }
1995 if (isset($order_by_clause)) {
1996 $subresult['order_by_clause'] = $order_by_clause;
1997 }
1998 if (isset($having_clause)) {
1999 $subresult['having_clause'] = $having_clause;
2000 }
2001 if (isset($limit_clause)) {
2002 $subresult['limit_clause'] = $limit_clause;
2003 }
2004 if (isset($where_clause)) {
2005 $subresult['where_clause'] = $where_clause;
2006 }
2007 if (isset($unsorted_query) && !empty($unsorted_query)) {
2008 $subresult['unsorted_query'] = $unsorted_query;
2009 }
2010 if (isset($where_clause_identifiers)) {
2011 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
2012 }
2013
2014 if (isset($position_of_first_select)) {
2015 $subresult['position_of_first_select'] = $position_of_first_select;
2016 $subresult['section_before_limit'] = $section_before_limit;
2017 $subresult['section_after_limit'] = $section_after_limit;
2018 }
2019
2020 // They are naughty and didn't have a trailing semi-colon,
2021 // then still handle it properly
2022 if ($subresult['querytype'] != '') {
2023 $result[] = $subresult;
2024 }
2025 return $result;
2026 } // end of the "PMA_SQP_analyze()" function
2027
2028
2029 /**
2030 * Colorizes SQL queries html formatted
2031 *
2032 * @todo check why adding a "\n" after the </span> would cause extra blanks
2033 * to be displayed: SELECT p . person_name
2034 * @param array The SQL queries html formatted
2035 *
2036 * @return array The colorized SQL queries
2037 *
2038 * @access public
2039 */
2040 function PMA_SQP_formatHtml_colorize($arr)
2041 {
2042 $i = $GLOBALS['PMA_strpos']($arr['type'], '_');
2043 $class = '';
2044 if ($i > 0) {
2045 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2046 }
2047
2048 $class .= 'syntax_' . $arr['type'];
2049
2050 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2051 } // end of the "PMA_SQP_formatHtml_colorize()" function
2052
2053
2054 /**
2055 * Formats SQL queries to html
2056 *
2057 * @param array The SQL queries
2058 * @param string mode
2059 * @param integer starting token
2060 * @param integer number of tokens to format, -1 = all
2061 *
2062 * @return string The formatted SQL queries
2063 *
2064 * @access public
2065 */
2066 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2067 $number_of_tokens=-1)
2068 {
2069 //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2070 // then check for an array
2071 if (!is_array($arr)) {
2072 return htmlspecialchars($arr);
2073 }
2074 // first check for the SQL parser having hit an error
2075 if (PMA_SQP_isError()) {
2076 return htmlspecialchars($arr['raw']);
2077 }
2078 // else do it properly
2079 switch ($mode) {
2080 case 'color':
2081 $str = '<span class="syntax">';
2082 $html_line_break = '<br />';
2083 break;
2084 case 'query_only':
2085 $str = '';
2086 $html_line_break = "\n";
2087 break;
2088 case 'text':
2089 $str = '';
2090 $html_line_break = '<br />';
2091 break;
2092 } // end switch
2093 $indent = 0;
2094 $bracketlevel = 0;
2095 $functionlevel = 0;
2096 $infunction = FALSE;
2097 $space_punct_listsep = ' ';
2098 $space_punct_listsep_function_name = ' ';
2099 // $space_alpha_reserved_word = '<br />'."\n";
2100 $space_alpha_reserved_word = ' ';
2101
2102 $keywords_with_brackets_1before = array(
2103 'INDEX',
2104 'KEY',
2105 'ON',
2106 'USING'
2107 );
2108 $keywords_with_brackets_1before_cnt = 4;
2109
2110 $keywords_with_brackets_2before = array(
2111 'IGNORE',
2112 'INDEX',
2113 'INTO',
2114 'KEY',
2115 'PRIMARY',
2116 'PROCEDURE',
2117 'REFERENCES',
2118 'UNIQUE',
2119 'USE'
2120 );
2121 // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
2122 $keywords_with_brackets_2before_cnt = 9;
2123
2124 // These reserved words do NOT get a newline placed near them.
2125 $keywords_no_newline = array(
2126 'AS',
2127 'ASC',
2128 'DESC',
2129 'DISTINCT',
2130 'DUPLICATE',
2131 'HOUR',
2132 'INTERVAL',
2133 'IS',
2134 'LIKE',
2135 'NOT',
2136 'NULL',
2137 'ON',
2138 'REGEXP'
2139 );
2140 $keywords_no_newline_cnt = 12;
2141
2142 // These reserved words introduce a privilege list
2143 $keywords_priv_list = array(
2144 'GRANT',
2145 'REVOKE'
2146 );
2147 $keywords_priv_list_cnt = 2;
2148
2149 if ($number_of_tokens == -1) {
2150 $arraysize = $arr['len'];
2151 } else {
2152 $arraysize = $number_of_tokens;
2153 }
2154 $typearr = array();
2155 if ($arraysize >= 0) {
2156 $typearr[0] = '';
2157 $typearr[1] = '';
2158 $typearr[2] = '';
2159 //$typearr[3] = $arr[0]['type'];
2160 $typearr[3] = $arr[$start_token]['type'];
2161 }
2162
2163 $in_priv_list = FALSE;
2164 for ($i = $start_token; $i < $arraysize; $i++) {
2165 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2166 $before = '';
2167 $after = '';
2168 $indent = 0;
2169 // array_shift($typearr);
2170 /*
2171 0 prev2
2172 1 prev
2173 2 current
2174 3 next
2175 */
2176 if (($i + 1) < $arraysize) {
2177 // array_push($typearr, $arr[$i + 1]['type']);
2178 $typearr[4] = $arr[$i + 1]['type'];
2179 } else {
2180 //array_push($typearr, null);
2181 $typearr[4] = '';
2182 }
2183
2184 for ($j=0; $j<4; $j++) {
2185 $typearr[$j] = $typearr[$j + 1];
2186 }
2187
2188 switch ($typearr[2]) {
2189 case 'alpha_bitfield_constant_introducer':
2190 $before = ' ';
2191 $after = '';
2192 break;
2193 case 'white_newline':
2194 $before = '';
2195 break;
2196 case 'punct_bracket_open_round':
2197 $bracketlevel++;
2198 $infunction = FALSE;
2199 // Make sure this array is sorted!
2200 if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2201 || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2202 || (($typearr[0] == 'alpha_reservedWord')
2203 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2204 || (($typearr[1] == 'alpha_reservedWord')
2205 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2206 ) {
2207 $functionlevel++;
2208 $infunction = TRUE;
2209 $after .= ' ';
2210 } else {
2211 $indent++;
2212 $after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2213 }
2214 break;
2215 case 'alpha_identifier':
2216 if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2217 $after = '';
2218 $before = '';
2219 }
2220 // for example SELECT 1 somealias
2221 if ($typearr[1] == 'digit_integer') {
2222 $before = ' ';
2223 }
2224 if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2225 $after .= ' ';
2226 }
2227 break;
2228 case 'punct_user':
2229 case 'punct_qualifier':
2230 $before = '';
2231 $after = '';
2232 break;
2233 case 'punct_listsep':
2234 if ($infunction == TRUE) {
2235 $after .= $space_punct_listsep_function_name;
2236 } else {
2237 $after .= $space_punct_listsep;
2238 }
2239 break;
2240 case 'punct_queryend':
2241 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2242 $after .= $html_line_break;
2243 $after .= $html_line_break;
2244 }
2245 $space_punct_listsep = ' ';
2246 $space_punct_listsep_function_name = ' ';
2247 $space_alpha_reserved_word = ' ';
2248 $in_priv_list = FALSE;
2249 break;
2250 case 'comment_mysql':
2251 case 'comment_ansi':
2252 $after .= $html_line_break;
2253 break;
2254 case 'punct':
2255 $before .= ' ';
2256 // workaround for
2257 // select * from mytable limit 0,-1
2258 // (a side effect of this workaround is that
2259 // select 20 - 9
2260 // becomes
2261 // select 20 -9
2262 // )
2263 if ($typearr[3] != 'digit_integer') {
2264 $after .= ' ';
2265 }
2266 break;
2267 case 'punct_bracket_close_round':
2268 $bracketlevel--;
2269 if ($infunction == TRUE) {
2270 $functionlevel--;
2271 $after .= ' ';
2272 $before .= ' ';
2273 } else {
2274 $indent--;
2275 $before .= ($mode != 'query_only' ? '</div>' : ' ');
2276 }
2277 $infunction = ($functionlevel > 0) ? TRUE : FALSE;
2278 break;
2279 case 'alpha_columnType':
2280 if ($typearr[3] == 'alpha_columnAttrib') {
2281 $after .= ' ';
2282 }
2283 if ($typearr[1] == 'alpha_columnType') {
2284 $before .= ' ';
2285 }
2286 break;
2287 case 'alpha_columnAttrib':
2288
2289 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2290 // COLLATE LATIN1_GENERAL_CI DEFAULT
2291 if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2292 $before .= ' ';
2293 }
2294 if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2295 $after .= ' ';
2296 }
2297 // workaround for
2298 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2299
2300 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2301 $before .= ' ';
2302 }
2303 // workaround for
2304 // select * from mysql.user where binary user="root"
2305 // binary is marked as alpha_columnAttrib
2306 // but should be marked as a reserved word
2307 if (strtoupper($arr[$i]['data']) == 'BINARY'
2308 && $typearr[3] == 'alpha_identifier') {
2309 $after .= ' ';
2310 }
2311 break;
2312 case 'alpha_reservedWord':
2313 // do not uppercase the reserved word if we are calling
2314 // this function in query_only mode, because we need
2315 // the original query (otherwise we get problems with
2316 // semi-reserved words like "storage" which is legal
2317 // as an identifier name)
2318
2319 if ($mode != 'query_only') {
2320 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2321 }
2322
2323 if ((($typearr[1] != 'alpha_reservedWord')
2324 || (($typearr[1] == 'alpha_reservedWord')
2325 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2326 && ($typearr[1] != 'punct_level_plus')
2327 && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2328 // do not put a space before the first token, because
2329 // we use a lot of pattern matching checking for the
2330 // first reserved word at beginning of query
2331 // so do not put a newline before
2332 //
2333 // also we must not be inside a privilege list
2334 if ($i > 0) {
2335 // the alpha_identifier exception is there to
2336 // catch cases like
2337 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2338 // (else, we get mydb.mytableTO)
2339 //
2340 // the quote_single exception is there to
2341 // catch cases like
2342 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2343 /**
2344 * @todo fix all cases and find why this happens
2345 */
2346
2347 if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2348 $before .= $space_alpha_reserved_word;
2349 }
2350 } else {
2351 // on first keyword, check if it introduces a
2352 // privilege list
2353 if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2354 $in_priv_list = TRUE;
2355 }
2356 }
2357 } else {
2358 $before .= ' ';
2359 }
2360
2361 switch ($arr[$i]['data']) {
2362 case 'CREATE':
2363 if (!$in_priv_list) {
2364 $space_punct_listsep = $html_line_break;
2365 $space_alpha_reserved_word = ' ';
2366 }
2367 break;
2368 case 'EXPLAIN':
2369 case 'DESCRIBE':
2370 case 'SET':
2371 case 'ALTER':
2372 case 'DELETE':
2373 case 'SHOW':
2374 case 'DROP':
2375 case 'UPDATE':
2376 case 'TRUNCATE':
2377 case 'ANALYZE':
2378 case 'ANALYSE':
2379 if (!$in_priv_list) {
2380 $space_punct_listsep = $html_line_break;
2381 $space_alpha_reserved_word = ' ';
2382 }
2383 break;
2384 case 'INSERT':
2385 case 'REPLACE':
2386 if (!$in_priv_list) {
2387 $space_punct_listsep = $html_line_break;
2388 $space_alpha_reserved_word = $html_line_break;
2389 }
2390 break;
2391 case 'VALUES':
2392 $space_punct_listsep = ' ';
2393 $space_alpha_reserved_word = $html_line_break;
2394 break;
2395 case 'SELECT':
2396 $space_punct_listsep = ' ';
2397 $space_alpha_reserved_word = $html_line_break;
2398 break;
2399 default:
2400 break;
2401 } // end switch ($arr[$i]['data'])
2402
2403 $after .= ' ';
2404 break;
2405 case 'digit_integer':
2406 case 'digit_float':
2407 case 'digit_hex':
2408 /**
2409 * @todo could there be other types preceding a digit?
2410 */
2411 if ($typearr[1] == 'alpha_reservedWord') {
2412 $after .= ' ';
2413 }
2414 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2415 $after .= ' ';
2416 }
2417 if ($typearr[1] == 'alpha_columnAttrib') {
2418 $before .= ' ';
2419 }
2420 break;
2421 case 'alpha_variable':
2422 $after = ' ';
2423 break;
2424 case 'quote_double':
2425 case 'quote_single':
2426 // workaround: for the query
2427 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2428 // the @ is incorrectly marked as alpha_variable
2429 // in the parser, and here, the '%' gets a blank before,
2430 // which is a syntax error
2431 if ($typearr[1] != 'punct_user' && $typearr[1] != 'alpha_bitfield_constant_introducer') {
2432 $before .= ' ';
2433 }
2434 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2435 $after .= ' ';
2436 }
2437 break;
2438 case 'quote_backtick':
2439 // here we check for punct_user to handle correctly
2440 // DEFINER = `username`@`%`
2441 // where @ is the punct_user and `%` is the quote_backtick
2442 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2443 $after .= ' ';
2444 }
2445 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2446 $before .= ' ';
2447 }
2448 break;
2449 default:
2450 break;
2451 } // end switch ($typearr[2])
2452
2453 /*
2454 if ($typearr[3] != 'punct_qualifier') {
2455 $after .= ' ';
2456 }
2457 $after .= "\n";
2458 */
2459 $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
2460 } // end for
2461 if ($mode=='color') {
2462 $str .= '</span>';
2463 }
2464
2465 return $str;
2466 } // end of the "PMA_SQP_formatHtml()" function
2467 }
2468
2469 /**
2470 * Builds a CSS rule used for html formatted SQL queries
2471 *
2472 * @param string The class name
2473 * @param string The property name
2474 * @param string The property value
2475 *
2476 * @return string The CSS rule
2477 *
2478 * @access public
2479 *
2480 * @see PMA_SQP_buildCssData()
2481 */
2482 function PMA_SQP_buildCssRule($classname, $property, $value)
2483 {
2484 $str = '.' . $classname . ' {';
2485 if ($value != '') {
2486 $str .= $property . ': ' . $value . ';';
2487 }
2488 $str .= '}' . "\n";
2489
2490 return $str;
2491 } // end of the "PMA_SQP_buildCssRule()" function
2492
2493
2494 /**
2495 * Builds CSS rules used for html formatted SQL queries
2496 *
2497 * @return string The CSS rules set
2498 *
2499 * @access public
2500 *
2501 * @global array The current PMA configuration
2502 *
2503 * @see PMA_SQP_buildCssRule()
2504 */
2505 function PMA_SQP_buildCssData()
2506 {
2507 global $cfg;
2508
2509 $css_string = '';
2510 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2511 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2512 }
2513
2514 for ($i = 0; $i < 8; $i++) {
2515 $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2516 }
2517
2518 return $css_string;
2519 } // end of the "PMA_SQP_buildCssData()" function
2520
2521 if (! defined('PMA_MINIMUM_COMMON')) {
2522 /**
2523 * Gets SQL queries with no format
2524 *
2525 * @param array The SQL queries list
2526 *
2527 * @return string The SQL queries with no format
2528 *
2529 * @access public
2530 */
2531 function PMA_SQP_formatNone($arr)
2532 {
2533 $formatted_sql = htmlspecialchars($arr['raw']);
2534 $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2535
2536 return $formatted_sql;
2537 } // end of the "PMA_SQP_formatNone()" function
2538
2539
2540 /**
2541 * Gets SQL queries in text format
2542 *
2543 * @todo WRITE THIS!
2544 * @param array The SQL queries list
2545 *
2546 * @return string The SQL queries in text format
2547 *
2548 * @access public
2549 */
2550 function PMA_SQP_formatText($arr)
2551 {
2552 return PMA_SQP_formatNone($arr);
2553 } // end of the "PMA_SQP_formatText()" function
2554 } // end if: minimal common.lib needed?
2555
2556 ?>
Browse-mode Improvements ++