Translation update done using Pootle.
[phpmyadmin/madhuracj.git] / libraries / sqlparser.lib.php
blob703d08a2ec5baa39c2e46ce88c37deaf0582e4c5
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /** SQL Parser Functions for phpMyAdmin
5 * These functions define an SQL parser system, capable of understanding and
6 * extracting data from a MySQL type SQL query.
8 * The basic procedure for using the new SQL parser:
9 * On any page that needs to extract data from a query or to pretty-print a
10 * query, you need code like this up at the top:
12 * ($sql contains the query)
13 * $parsed_sql = PMA_SQP_parse($sql);
15 * If you want to extract data from it then, you just need to run
16 * $sql_info = PMA_SQP_analyze($parsed_sql);
18 * See comments in PMA_SQP_analyze for the returned info
19 * from the analyzer.
21 * If you want a pretty-printed version of the query, do:
22 * $string = PMA_SQP_formatHtml($parsed_sql);
23 * (note that that you need to have syntax.css.php included somehow in your
24 * page for it to work, I recommend '<link rel="stylesheet" type="text/css"
25 * href="syntax.css.php" />' at the moment.)
27 * @package phpMyAdmin
29 if (! defined('PHPMYADMIN')) {
30 exit;
33 /**
34 * Minimum inclusion? (i.e. for the stylesheet builder)
36 if (! defined('PMA_MINIMUM_COMMON')) {
37 /**
38 * Include the string library as we use it heavily
40 require_once './libraries/string.lib.php';
42 /**
43 * Include data for the SQL Parser
45 require_once './libraries/sqlparser.data.php';
46 if (!defined('TESTSUITE')) {
47 require_once './libraries/mysql_charsets.lib.php';
49 if (! isset($mysql_charsets)) {
50 $mysql_charsets = array();
51 $mysql_collations_flat = array();
54 if (!defined('DEBUG_TIMING')) {
55 /**
56 * currently we don't need the $pos (token position in query)
57 * for other purposes than LIMIT clause verification,
58 * so many calls to this function do not include the 4th parameter
60 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
62 $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos);
63 $arrsize++;
64 } // end of the "PMA_SQP_arrayAdd()" function
65 } else {
66 /**
67 * This is debug variant of above.
68 * @ignore
70 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
72 global $timer;
74 $t = $timer;
75 $arr[] = array(
76 'type' => $type,
77 'data' => $data,
78 'pos' => $pos,
79 'time' => $t);
80 $timer = microtime();
81 $arrsize++;
82 } // end of the "PMA_SQP_arrayAdd()" function
83 } // end if... else...
86 /**
87 * Reset the error variable for the SQL parser
89 * @access public
91 function PMA_SQP_resetError()
93 global $SQP_errorString;
94 $SQP_errorString = '';
95 unset($SQP_errorString);
98 /**
99 * Get the contents of the error variable for the SQL parser
101 * @return string Error string from SQL parser
103 * @access public
105 function PMA_SQP_getErrorString()
107 global $SQP_errorString;
108 return isset($SQP_errorString) ? $SQP_errorString : '';
112 * Check if the SQL parser hit an error
114 * @return boolean error state
116 * @access public
118 function PMA_SQP_isError()
120 global $SQP_errorString;
121 return isset($SQP_errorString) && !empty($SQP_errorString);
125 * Set an error message for the system
127 * @param string The error message
128 * @param string The failing SQL query
130 * @access private
131 * @scope SQL Parser internal
133 function PMA_SQP_throwError($message, $sql)
135 global $SQP_errorString;
136 $SQP_errorString = '<p>'.__('There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem') . '</p>' . "\n"
137 . '<pre>' . "\n"
138 . 'ERROR: ' . $message . "\n"
139 . 'SQL: ' . htmlspecialchars($sql) . "\n"
140 . '</pre>' . "\n";
142 } // end of the "PMA_SQP_throwError()" function
146 * Do display the bug report
148 * @param string The error message
149 * @param string The failing SQL query
151 * @access public
153 function PMA_SQP_bug($message, $sql)
155 global $SQP_errorString;
156 $debugstr = 'ERROR: ' . $message . "\n";
157 $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";
158 $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ';
159 $debugstr .= PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";
160 $debugstr .= 'PMA: ' . PMA_VERSION . "\n";
161 $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";
162 $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
163 $debugstr .= 'SQL: ' . htmlspecialchars($sql);
165 $encodedstr = $debugstr;
166 if (@function_exists('gzcompress')) {
167 $encodedstr = gzcompress($debugstr, 9);
169 $encodedstr = preg_replace(
170 "/(\015\012)|(\015)|(\012)/",
171 '<br />' . "\n",
172 chunk_split(base64_encode($encodedstr)));
175 $SQP_errorString .= __('There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:')
176 . '<br />' . "\n"
177 . '----' . __('BEGIN CUT') . '----' . '<br />' . "\n"
178 . $encodedstr . "\n"
179 . '----' . __('END CUT') . '----' . '<br />' . "\n";
181 $SQP_errorString .= '----' . __('BEGIN RAW') . '----<br />' . "\n"
182 . '<pre>' . "\n"
183 . $debugstr
184 . '</pre>' . "\n"
185 . '----' . __('END RAW') . '----<br />' . "\n";
187 } // end of the "PMA_SQP_bug()" function
191 * Parses the SQL queries
193 * @param string The SQL query list
195 * @return mixed Most of times, nothing...
197 * @global array The current PMA configuration
198 * @global array MySQL column attributes
199 * @global array MySQL reserved words
200 * @global array MySQL column types
201 * @global array MySQL function names
202 * @global array List of available character sets
203 * @global array List of available collations
205 * @access public
207 function PMA_SQP_parse($sql)
209 static $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word;
210 static $PMA_SQPdata_column_type;
211 static $PMA_SQPdata_function_name, $PMA_SQPdata_forbidden_word;
212 global $mysql_charsets, $mysql_collations_flat;
214 // Convert all line feeds to Unix style
215 $sql = str_replace("\r\n", "\n", $sql);
216 $sql = str_replace("\r", "\n", $sql);
218 $len = PMA_strlen($sql);
219 if ($len == 0) {
220 return array();
223 // Create local hashtables
224 if (!isset($PMA_SQPdata_column_attrib)) {
225 $PMA_SQPdata_column_attrib = array_flip(
226 $GLOBALS['PMA_SQPdata_column_attrib']
228 $PMA_SQPdata_function_name = array_flip(
229 $GLOBALS['PMA_SQPdata_function_name']
231 $PMA_SQPdata_reserved_word = array_flip(
232 $GLOBALS['PMA_SQPdata_reserved_word']
234 $PMA_SQPdata_forbidden_word = array_flip(
235 $GLOBALS['PMA_SQPdata_forbidden_word']
237 $PMA_SQPdata_column_type = array_flip(
238 $GLOBALS['PMA_SQPdata_column_type']
242 $sql_array = array();
243 $sql_array['raw'] = $sql;
244 $count1 = 0;
245 $count2 = 0;
246 $punct_queryend = ';';
247 $punct_qualifier = '.';
248 $punct_listsep = ',';
249 $punct_level_plus = '(';
250 $punct_level_minus = ')';
251 $punct_user = '@';
252 $digit_floatdecimal = '.';
253 $digit_hexset = 'x';
254 $bracket_list = '()[]{}';
255 $allpunct_list = '-,;:!?/.^~\*&%+<=>|';
256 $allpunct_list_pair = array(
257 '!=' => 1,
258 '&&' => 1,
259 ':=' => 1,
260 '<<' => 1,
261 '<=' => 1,
262 '<=>' => 1,
263 '<>' => 1,
264 '>=' => 1,
265 '>>' => 1,
266 '||' => 1,
267 '==' => 1
269 $quote_list = '\'"`';
270 $arraysize = 0;
272 $previous_was_space = false;
273 $this_was_space = false;
274 $previous_was_bracket = false;
275 $this_was_bracket = false;
276 $previous_was_punct = false;
277 $this_was_punct = false;
278 $previous_was_listsep = false;
279 $this_was_listsep = false;
280 $previous_was_quote = false;
281 $this_was_quote = false;
283 while ($count2 < $len) {
284 $c = PMA_substr($sql, $count2, 1);
285 $count1 = $count2;
287 $previous_was_space = $this_was_space;
288 $this_was_space = false;
289 $previous_was_bracket = $this_was_bracket;
290 $this_was_bracket = false;
291 $previous_was_punct = $this_was_punct;
292 $this_was_punct = false;
293 $previous_was_listsep = $this_was_listsep;
294 $this_was_listsep = false;
295 $previous_was_quote = $this_was_quote;
296 $this_was_quote = false;
298 if (($c == "\n")) {
299 $this_was_space = true;
300 $count2++;
301 PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
302 continue;
305 // Checks for white space
306 if (PMA_STR_isSpace($c)) {
307 $this_was_space = true;
308 $count2++;
309 continue;
312 // Checks for comment lines.
313 // MySQL style #
314 // C style /* */
315 // ANSI style --
316 $next_c = PMA_substr($sql, $count2 + 1, 1);
317 if (($c == '#')
318 || (($count2 + 1 < $len) && ($c == '/') && ($next_c == '*'))
319 || (($count2 + 2 == $len) && ($c == '-') && ($next_c == '-'))
320 || (($count2 + 2 < $len) && ($c == '-') && ($next_c == '-') && ((PMA_substr($sql, $count2 + 2, 1) <= ' ')))) {
321 $count2++;
322 $pos = 0;
323 $type = 'bad';
324 switch ($c) {
325 case '#':
326 $type = 'mysql';
327 case '-':
328 $type = 'ansi';
329 $pos = PMA_strpos($sql, "\n", $count2);
330 break;
331 case '/':
332 $type = 'c';
333 $pos = PMA_strpos($sql, '*/', $count2);
334 $pos += 2;
335 break;
336 default:
337 break;
338 } // end switch
339 $count2 = ($pos < $count2) ? $len : $pos;
340 $str = PMA_substr($sql, $count1, $count2 - $count1);
341 PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
342 continue;
343 } // end if
345 // Checks for something inside quotation marks
346 if (PMA_strpos($quote_list, $c) !== false) {
347 $startquotepos = $count2;
348 $quotetype = $c;
349 $count2++;
350 $escaped = false;
351 $pos = $count2;
352 $oldpos = 0;
353 do {
354 $oldpos = $pos;
355 $pos = PMA_strpos(' ' . $sql, $quotetype, $oldpos + 1) - 1;
356 // ($pos === false)
357 if ($pos < 0) {
358 if ($c == '`') {
360 * Behave same as MySQL and accept end of query as end of backtick.
361 * I know this is sick, but MySQL behaves like this:
363 * SELECT * FROM `table
365 * is treated like
367 * SELECT * FROM `table`
369 $pos_quote_separator = PMA_strpos(' ' . $sql, $GLOBALS['sql_delimiter'], $oldpos + 1) - 1;
370 if ($pos_quote_separator < 0) {
371 $len += 1;
372 $sql .= '`';
373 $sql_array['raw'] .= '`';
374 $pos = $len;
375 } else {
376 $len += 1;
377 $sql = PMA_substr($sql, 0, $pos_quote_separator) . '`' . PMA_substr($sql, $pos_quote_separator);
378 $sql_array['raw'] = $sql;
379 $pos = $pos_quote_separator;
381 if (class_exists('PMA_Message') && $GLOBALS['is_ajax_request'] != true) {
382 PMA_Message::notice(__('Automatically appended backtick to the end of query!'))->display();
384 } else {
385 $debugstr = __('Unclosed quote') . ' @ ' . $startquotepos. "\n"
386 . 'STR: ' . htmlspecialchars($quotetype);
387 PMA_SQP_throwError($debugstr, $sql);
388 return $sql_array;
392 // If the quote is the first character, it can't be
393 // escaped, so don't do the rest of the code
394 if ($pos == 0) {
395 break;
398 // Checks for MySQL escaping using a \
399 // And checks for ANSI escaping using the $quotetype character
400 if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos) && $c != '`') {
401 $pos ++;
402 continue;
403 } elseif (($pos + 1 < $len) && (PMA_substr($sql, $pos, 1) == $quotetype) && (PMA_substr($sql, $pos + 1, 1) == $quotetype)) {
404 $pos = $pos + 2;
405 continue;
406 } else {
407 break;
409 } while ($len > $pos); // end do
411 $count2 = $pos;
412 $count2++;
413 $type = 'quote_';
414 switch ($quotetype) {
415 case '\'':
416 $type .= 'single';
417 $this_was_quote = true;
418 break;
419 case '"':
420 $type .= 'double';
421 $this_was_quote = true;
422 break;
423 case '`':
424 $type .= 'backtick';
425 $this_was_quote = true;
426 break;
427 default:
428 break;
429 } // end switch
430 $data = PMA_substr($sql, $count1, $count2 - $count1);
431 PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
432 continue;
435 // Checks for brackets
436 if (PMA_strpos($bracket_list, $c) !== false) {
437 // All bracket tokens are only one item long
438 $this_was_bracket = true;
439 $count2++;
440 $type_type = '';
441 if (PMA_strpos('([{', $c) !== false) {
442 $type_type = 'open';
443 } else {
444 $type_type = 'close';
447 $type_style = '';
448 if (PMA_strpos('()', $c) !== false) {
449 $type_style = 'round';
450 } elseif (PMA_strpos('[]', $c) !== false) {
451 $type_style = 'square';
452 } else {
453 $type_style = 'curly';
456 $type = 'punct_bracket_' . $type_type . '_' . $type_style;
457 PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
458 continue;
461 /* DEBUG
462 echo '<pre>1';
463 var_dump(PMA_STR_isSqlIdentifier($c, false));
464 var_dump($c == '@');
465 var_dump($c == '.');
466 var_dump(PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1)));
467 var_dump($previous_was_space);
468 var_dump($previous_was_bracket);
469 var_dump($previous_was_listsep);
470 echo '</pre>';
473 // Checks for identifier (alpha or numeric)
474 if (PMA_STR_isSqlIdentifier($c, false)
475 || $c == '@'
476 || ($c == '.'
477 && PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1))
478 && ($previous_was_space || $previous_was_bracket || $previous_was_listsep))) {
480 /* DEBUG
481 echo PMA_substr($sql, $count2);
482 echo '<hr />';
485 $count2++;
488 * @todo a @ can also be present in expressions like
489 * FROM 'user'@'%' or TO 'user'@'%'
490 * in this case, the @ is wrongly marked as alpha_variable
492 $is_identifier = $previous_was_punct;
493 $is_sql_variable = $c == '@' && ! $previous_was_quote;
494 $is_user = $c == '@' && $previous_was_quote;
495 $is_digit = !$is_identifier && !$is_sql_variable && PMA_STR_isDigit($c);
496 $is_hex_digit = $is_digit && $c == '0' && $count2 < $len && PMA_substr($sql, $count2, 1) == 'x';
497 $is_float_digit = $c == '.';
498 $is_float_digit_exponent = false;
500 /* DEBUG
501 echo '<pre>2';
502 var_dump($is_identifier);
503 var_dump($is_sql_variable);
504 var_dump($is_digit);
505 var_dump($is_float_digit);
506 echo '</pre>';
509 // Fast skip is especially needed for huge BLOB data
510 if ($is_hex_digit) {
511 $count2++;
512 $pos = strspn($sql, '0123456789abcdefABCDEF', $count2);
513 if ($pos > $count2) {
514 $count2 = $pos;
516 unset($pos);
517 } elseif ($is_digit) {
518 $pos = strspn($sql, '0123456789', $count2);
519 if ($pos > $count2) {
520 $count2 = $pos;
522 unset($pos);
525 while (($count2 < $len) && PMA_STR_isSqlIdentifier(PMA_substr($sql, $count2, 1), ($is_sql_variable || $is_digit))) {
526 $c2 = PMA_substr($sql, $count2, 1);
527 if ($is_sql_variable && ($c2 == '.')) {
528 $count2++;
529 continue;
531 if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
532 $count2++;
533 if (!$is_float_digit) {
534 $is_float_digit = true;
535 continue;
536 } else {
537 $debugstr = __('Invalid Identifer') . ' @ ' . ($count1+1) . "\n"
538 . 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));
539 PMA_SQP_throwError($debugstr, $sql);
540 return $sql_array;
543 if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {
544 if (!$is_float_digit_exponent) {
545 $is_float_digit_exponent = true;
546 $is_float_digit = true;
547 $count2++;
548 continue;
549 } else {
550 $is_digit = false;
551 $is_float_digit = false;
554 if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && PMA_STR_isDigit($c2))) {
555 $count2++;
556 continue;
557 } else {
558 $is_digit = false;
559 $is_hex_digit = false;
562 $count2++;
563 } // end while
565 $l = $count2 - $count1;
566 $str = PMA_substr($sql, $count1, $l);
568 $type = '';
569 if ($is_digit || $is_float_digit || $is_hex_digit) {
570 $type = 'digit';
571 if ($is_float_digit) {
572 $type .= '_float';
573 } elseif ($is_hex_digit) {
574 $type .= '_hex';
575 } else {
576 $type .= '_integer';
578 } elseif ($is_user) {
579 $type = 'punct_user';
580 } elseif ($is_sql_variable != false) {
581 $type = 'alpha_variable';
582 } else {
583 $type = 'alpha';
584 } // end if... else....
585 PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize, $count2);
587 continue;
590 // Checks for punct
591 if (PMA_strpos($allpunct_list, $c) !== false) {
592 while (($count2 < $len) && PMA_strpos($allpunct_list, PMA_substr($sql, $count2, 1)) !== false) {
593 $count2++;
595 $l = $count2 - $count1;
596 if ($l == 1) {
597 $punct_data = $c;
598 } else {
599 $punct_data = PMA_substr($sql, $count1, $l);
602 // Special case, sometimes, althought two characters are
603 // adjectent directly, they ACTUALLY need to be seperate
604 /* DEBUG
605 echo '<pre>';
606 var_dump($l);
607 var_dump($punct_data);
608 echo '</pre>';
611 if ($l == 1) {
612 $t_suffix = '';
613 switch ($punct_data) {
614 case $punct_queryend:
615 $t_suffix = '_queryend';
616 break;
617 case $punct_qualifier:
618 $t_suffix = '_qualifier';
619 $this_was_punct = true;
620 break;
621 case $punct_listsep:
622 $this_was_listsep = true;
623 $t_suffix = '_listsep';
624 break;
625 default:
626 break;
628 PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
629 } elseif ($punct_data == $GLOBALS['sql_delimiter'] || isset($allpunct_list_pair[$punct_data])) {
630 // Ok, we have one of the valid combined punct expressions
631 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
632 } else {
633 // Bad luck, lets split it up more
634 $first = $punct_data[0];
635 $first2 = $punct_data[0] . $punct_data[1];
636 $last2 = $punct_data[$l - 2] . $punct_data[$l - 1];
637 $last = $punct_data[$l - 1];
638 if (($first == ',') || ($first == ';') || ($first == '.') || ($first == '*')) {
639 $count2 = $count1 + 1;
640 $punct_data = $first;
641 } elseif (($last2 == '/*') || (($last2 == '--') && ($count2 == $len || PMA_substr($sql, $count2, 1) <= ' '))) {
642 $count2 -= 2;
643 $punct_data = PMA_substr($sql, $count1, $count2 - $count1);
644 } elseif (($last == '-') || ($last == '+') || ($last == '!')) {
645 $count2--;
646 $punct_data = PMA_substr($sql, $count1, $count2 - $count1);
647 } elseif ($last != '~') {
649 * @todo for negation operator, split in 2 tokens ?
650 * "select x&~1 from t"
651 * becomes "select x & ~ 1 from t" ?
653 $debugstr = __('Unknown Punctuation String') . ' @ ' . ($count1+1) . "\n"
654 . 'STR: ' . htmlspecialchars($punct_data);
655 PMA_SQP_throwError($debugstr, $sql);
656 return $sql_array;
658 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
659 continue;
660 } // end if... elseif... else
661 continue;
664 // DEBUG
665 $count2++;
667 $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len . "\n"
668 . 'STR: ' . PMA_substr($sql, $count1, $count2 - $count1) . "\n";
669 PMA_SQP_bug($debugstr, $sql);
670 return $sql_array;
672 } // end while ($count2 < $len)
675 echo '<pre>';
676 print_r($sql_array);
677 echo '</pre>';
680 if ($arraysize > 0) {
681 $t_next = $sql_array[0]['type'];
682 $t_prev = '';
683 $t_bef_prev = '';
684 $t_cur = '';
685 $d_next = $sql_array[0]['data'];
686 $d_prev = '';
687 $d_bef_prev = '';
688 $d_cur = '';
689 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
690 $d_prev_upper = '';
691 $d_bef_prev_upper = '';
692 $d_cur_upper = '';
695 for ($i = 0; $i < $arraysize; $i++) {
696 $t_bef_prev = $t_prev;
697 $t_prev = $t_cur;
698 $t_cur = $t_next;
699 $d_bef_prev = $d_prev;
700 $d_prev = $d_cur;
701 $d_cur = $d_next;
702 $d_bef_prev_upper = $d_prev_upper;
703 $d_prev_upper = $d_cur_upper;
704 $d_cur_upper = $d_next_upper;
705 if (($i + 1) < $arraysize) {
706 $t_next = $sql_array[$i + 1]['type'];
707 $d_next = $sql_array[$i + 1]['data'];
708 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
709 } else {
710 $t_next = '';
711 $d_next = '';
712 $d_next_upper = '';
715 //DEBUG echo "[prev: <strong>".$d_prev."</strong> ".$t_prev."][cur: <strong>".$d_cur."</strong> ".$t_cur."][next: <strong>".$d_next."</strong> ".$t_next."]<br />";
717 if ($t_cur == 'alpha') {
718 $t_suffix = '_identifier';
719 // for example: `thebit` bit(8) NOT NULL DEFAULT b'0'
720 if ($t_prev == 'alpha' && $d_prev == 'DEFAULT' && $d_cur == 'b' && $t_next == 'quote_single') {
721 $t_suffix = '_bitfield_constant_introducer';
722 } elseif (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
723 $t_suffix = '_identifier';
724 } elseif (($t_next == 'punct_bracket_open_round')
725 && isset($PMA_SQPdata_function_name[$d_cur_upper])) {
727 * @todo 2005-10-16: in the case of a CREATE TABLE containing
728 * a TIMESTAMP, since TIMESTAMP() is also a function, it's
729 * found here and the token is wrongly marked as alpha_functionName.
730 * But we compensate for this when analysing for timestamp_not_null
731 * later in this script.
733 * Same applies to CHAR vs. CHAR() function.
735 $t_suffix = '_functionName';
736 /* There are functions which might be as well column types */
737 } elseif (isset($PMA_SQPdata_column_type[$d_cur_upper])) {
738 $t_suffix = '_columnType';
741 * Temporary fix for BUG #621357 and #2027720
743 * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
745 if (($d_cur_upper == 'SET' || $d_cur_upper == 'BINARY') && $t_next != 'punct_bracket_open_round') {
746 $t_suffix = '_reservedWord';
748 //END OF TEMPORARY FIX
750 // CHARACTER is a synonym for CHAR, but can also be meant as
751 // CHARACTER SET. In this case, we have a reserved word.
752 if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
753 $t_suffix = '_reservedWord';
756 // experimental
757 // current is a column type, so previous must not be
758 // a reserved word but an identifier
759 // CREATE TABLE SG_Persons (first varchar(64))
761 //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
762 // $sql_array[$i-1]['type'] = 'alpha_identifier';
765 } elseif (isset($PMA_SQPdata_reserved_word[$d_cur_upper])) {
766 $t_suffix = '_reservedWord';
767 } elseif (isset($PMA_SQPdata_column_attrib[$d_cur_upper])) {
768 $t_suffix = '_columnAttrib';
769 // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
770 // it should be regarded as a reserved word.
771 if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
772 $t_suffix = '_reservedWord';
775 if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
776 $t_suffix = '_reservedWord';
778 // Binary as character set
779 if ($d_cur_upper == 'BINARY' && (
780 ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
781 || ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
782 || ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
783 || $d_prev_upper == 'CHARSET'
784 ) && in_array($d_cur, $mysql_charsets)) {
785 $t_suffix = '_charset';
787 } elseif (in_array($d_cur, $mysql_charsets)
788 || in_array($d_cur, $mysql_collations_flat)
789 || ($d_cur{0} == '_' && in_array(substr($d_cur, 1), $mysql_charsets))) {
790 $t_suffix = '_charset';
791 } else {
792 // Do nothing
794 // check if present in the list of forbidden words
795 if ($t_suffix == '_reservedWord' && isset($PMA_SQPdata_forbidden_word[$d_cur_upper])) {
796 $sql_array[$i]['forbidden'] = true;
797 } else {
798 $sql_array[$i]['forbidden'] = false;
800 $sql_array[$i]['type'] .= $t_suffix;
802 } // end for
804 // Stores the size of the array inside the array, as count() is a slow
805 // operation.
806 $sql_array['len'] = $arraysize;
808 // DEBUG echo 'After parsing<pre>'; print_r($sql_array); echo '</pre>';
809 // Sends the data back
810 return $sql_array;
811 } // end of the "PMA_SQP_parse()" function
814 * Checks for token types being what we want...
816 * @param string String of type that we have
817 * @param string String of type that we want
819 * @return boolean result of check
821 * @access private
823 function PMA_SQP_typeCheck($toCheck, $whatWeWant)
825 $typeSeperator = '_';
826 if (strcmp($whatWeWant, $toCheck) == 0) {
827 return true;
828 } else {
829 if (strpos($whatWeWant, $typeSeperator) === false) {
830 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
831 } else {
832 return false;
839 * Analyzes SQL queries
841 * @param array The SQL queries
843 * @return array The analyzed SQL queries
845 * @access public
847 function PMA_SQP_analyze($arr)
849 if ($arr == array() || ! isset($arr['len'])) {
850 return array();
852 $result = array();
853 $size = $arr['len'];
854 $subresult = array(
855 'querytype' => '',
856 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
857 'position_of_first_select' => '', // the array index
858 'from_clause'=> '',
859 'group_by_clause'=> '',
860 'order_by_clause'=> '',
861 'having_clause' => '',
862 'limit_clause' => '',
863 'where_clause' => '',
864 'where_clause_identifiers' => array(),
865 'unsorted_query' => '',
866 'queryflags' => array(),
867 'select_expr' => array(),
868 'table_ref' => array(),
869 'foreign_keys' => array(),
870 'create_table_fields' => array()
872 $subresult_empty = $subresult;
873 $seek_queryend = false;
874 $seen_end_of_table_ref = false;
875 $number_of_brackets_in_extract = 0;
876 $number_of_brackets_in_group_concat = 0;
878 $number_of_brackets = 0;
879 $in_subquery = false;
880 $seen_subquery = false;
881 $seen_from = false;
883 // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
884 // we must not use CURDATE as a table_ref
885 // so we track whether we are in the EXTRACT()
886 $in_extract = false;
888 // for GROUP_CONCAT(...)
889 $in_group_concat = false;
891 /* Description of analyzer results
893 * db, table, column, alias
894 * ------------------------
896 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
898 * The SELECT syntax (simplified) is
900 * SELECT
901 * select_expression,...
902 * [FROM [table_references]
905 * ['select_expr'] is filled with each expression, the key represents the
906 * expression position in the list (0-based) (so we don't lose track of
907 * multiple occurences of the same column).
909 * ['table_ref'] is filled with each table ref, same thing for the key.
911 * I create all sub-values empty, even if they are
912 * not present (for example no select_expression alias).
914 * There is a debug section at the end of loop #1, if you want to
915 * see the exact contents of select_expr and table_ref
917 * queryflags
918 * ----------
920 * In $subresult, array 'queryflags' is filled, according to what we
921 * find in the query.
923 * Currently, those are generated:
925 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
926 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
927 * ['queryflags']['distinct'] = 1; for a DISTINCT
928 * ['queryflags']['union'] = 1; for a UNION
929 * ['queryflags']['join'] = 1; for a JOIN
930 * ['queryflags']['offset'] = 1; for the presence of OFFSET
931 * ['queryflags']['procedure'] = 1; for the presence of PROCEDURE
933 * query clauses
934 * -------------
936 * The select is splitted in those clauses:
937 * ['select_expr_clause']
938 * ['from_clause']
939 * ['group_by_clause']
940 * ['order_by_clause']
941 * ['having_clause']
942 * ['limit_clause']
943 * ['where_clause']
945 * The identifiers of the WHERE clause are put into the array
946 * ['where_clause_identifier']
948 * For a SELECT, the whole query without the ORDER BY clause is put into
949 * ['unsorted_query']
951 * foreign keys
952 * ------------
953 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
954 * analyzed and ['foreign_keys'] is an array filled with
955 * the constraint name, the index list,
956 * the REFERENCES table name and REFERENCES index list,
957 * and ON UPDATE | ON DELETE clauses
959 * position_of_first_select
960 * ------------------------
962 * The array index of the first SELECT we find. Will be used to
963 * insert a SQL_CALC_FOUND_ROWS.
965 * create_table_fields
966 * -------------------
968 * Used to detect the DEFAULT CURRENT_TIMESTAMP and
969 * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
970 * Also used to store the default value of the field.
971 * An array, each element is the identifier name.
972 * Note that for now, the timestamp_not_null element is created
973 * even for non-TIMESTAMP fields.
975 * Sub-elements: ['type'] which contains the column type
976 * optional (currently they are never false but can be absent):
977 * ['default_current_timestamp'] boolean
978 * ['on_update_current_timestamp'] boolean
979 * ['timestamp_not_null'] boolean
981 * section_before_limit, section_after_limit
982 * -----------------------------------------
984 * Marks the point of the query where we can insert a LIMIT clause;
985 * so the section_before_limit will contain the left part before
986 * a possible LIMIT clause
989 * End of description of analyzer results
992 // must be sorted
993 // TODO: current logic checks for only one word, so I put only the
994 // first word of the reserved expressions that end a table ref;
995 // maybe this is not ok (the first word might mean something else)
996 // $words_ending_table_ref = array(
997 // 'FOR UPDATE',
998 // 'GROUP BY',
999 // 'HAVING',
1000 // 'LIMIT',
1001 // 'LOCK IN SHARE MODE',
1002 // 'ORDER BY',
1003 // 'PROCEDURE',
1004 // 'UNION',
1005 // 'WHERE'
1006 // );
1007 $words_ending_table_ref = array(
1008 'FOR' => 1,
1009 'GROUP' => 1,
1010 'HAVING' => 1,
1011 'LIMIT' => 1,
1012 'LOCK' => 1,
1013 'ORDER' => 1,
1014 'PROCEDURE' => 1,
1015 'UNION' => 1,
1016 'WHERE' => 1
1019 $words_ending_clauses = array(
1020 'FOR' => 1,
1021 'LIMIT' => 1,
1022 'LOCK' => 1,
1023 'PROCEDURE' => 1,
1024 'UNION' => 1
1027 $supported_query_types = array(
1028 'SELECT' => 1,
1030 // Support for these additional query types will come later on.
1031 'DELETE' => 1,
1032 'INSERT' => 1,
1033 'REPLACE' => 1,
1034 'TRUNCATE' => 1,
1035 'UPDATE' => 1,
1036 'EXPLAIN' => 1,
1037 'DESCRIBE' => 1,
1038 'SHOW' => 1,
1039 'CREATE' => 1,
1040 'SET' => 1,
1041 'ALTER' => 1
1045 // loop #1 for each token: select_expr, table_ref for SELECT
1047 for ($i = 0; $i < $size; $i++) {
1048 //DEBUG echo "Loop1 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1050 // High speed seek for locating the end of the current query
1051 if ($seek_queryend == true) {
1052 if ($arr[$i]['type'] == 'punct_queryend') {
1053 $seek_queryend = false;
1054 } else {
1055 continue;
1056 } // end if (type == punct_queryend)
1057 } // end if ($seek_queryend)
1060 * Note: do not split if this is a punct_queryend for the first and only query
1061 * @todo when we find a UNION, should we split in another subresult?
1063 if ($arr[$i]['type'] == 'punct_queryend' && ($i + 1 != $size)) {
1064 $result[] = $subresult;
1065 $subresult = $subresult_empty;
1066 continue;
1067 } // end if (type == punct_queryend)
1069 // ==============================================================
1070 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1071 $number_of_brackets++;
1072 if ($in_extract) {
1073 $number_of_brackets_in_extract++;
1075 if ($in_group_concat) {
1076 $number_of_brackets_in_group_concat++;
1079 // ==============================================================
1080 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1081 $number_of_brackets--;
1082 if ($number_of_brackets == 0) {
1083 $in_subquery = false;
1085 if ($in_extract) {
1086 $number_of_brackets_in_extract--;
1087 if ($number_of_brackets_in_extract == 0) {
1088 $in_extract = false;
1091 if ($in_group_concat) {
1092 $number_of_brackets_in_group_concat--;
1093 if ($number_of_brackets_in_group_concat == 0) {
1094 $in_group_concat = false;
1099 if ($in_subquery) {
1101 * skip the subquery to avoid setting
1102 * select_expr or table_ref with the contents
1103 * of this subquery; this is to avoid a bug when
1104 * trying to edit the results of
1105 * select * from child where not exists (select id from
1106 * parent where child.parent_id = parent.id);
1108 continue;
1110 // ==============================================================
1111 if ($arr[$i]['type'] == 'alpha_functionName') {
1112 $upper_data = strtoupper($arr[$i]['data']);
1113 if ($upper_data =='EXTRACT') {
1114 $in_extract = true;
1115 $number_of_brackets_in_extract = 0;
1117 if ($upper_data =='GROUP_CONCAT') {
1118 $in_group_concat = true;
1119 $number_of_brackets_in_group_concat = 0;
1123 // ==============================================================
1124 if ($arr[$i]['type'] == 'alpha_reservedWord'
1125 //&& $arr[$i]['forbidden'] == false) {
1127 // We don't know what type of query yet, so run this
1128 if ($subresult['querytype'] == '') {
1129 $subresult['querytype'] = strtoupper($arr[$i]['data']);
1130 } // end if (querytype was empty)
1132 // Check if we support this type of query
1133 if (!isset($supported_query_types[$subresult['querytype']])) {
1134 // Skip ahead to the next one if we don't
1135 $seek_queryend = true;
1136 continue;
1137 } // end if (query not supported)
1139 // upper once
1140 $upper_data = strtoupper($arr[$i]['data']);
1142 * @todo reset for each query?
1145 if ($upper_data == 'SELECT') {
1146 if ($number_of_brackets > 0) {
1147 $in_subquery = true;
1148 $seen_subquery = true;
1149 // this is a subquery so do not analyze inside it
1150 continue;
1152 $seen_from = false;
1153 $previous_was_identifier = false;
1154 $current_select_expr = -1;
1155 $seen_end_of_table_ref = false;
1156 } // end if (data == SELECT)
1158 if ($upper_data =='FROM' && !$in_extract) {
1159 $current_table_ref = -1;
1160 $seen_from = true;
1161 $previous_was_identifier = false;
1162 $save_table_ref = true;
1163 } // end if (data == FROM)
1165 // here, do not 'continue' the loop, as we have more work for
1166 // reserved words below
1167 } // end if (type == alpha_reservedWord)
1169 // ==============================
1170 if ($arr[$i]['type'] == 'quote_backtick'
1171 || $arr[$i]['type'] == 'quote_double'
1172 || $arr[$i]['type'] == 'quote_single'
1173 || $arr[$i]['type'] == 'alpha_identifier'
1174 || ($arr[$i]['type'] == 'alpha_reservedWord'
1175 && $arr[$i]['forbidden'] == false)) {
1177 switch ($arr[$i]['type']) {
1178 case 'alpha_identifier':
1179 case 'alpha_reservedWord':
1181 * this is not a real reservedWord, because it's not
1182 * present in the list of forbidden words, for example
1183 * "storage" which can be used as an identifier
1185 * @todo avoid the pretty printing in color in this case
1187 $identifier = $arr[$i]['data'];
1188 break;
1190 case 'quote_backtick':
1191 case 'quote_double':
1192 case 'quote_single':
1193 $identifier = PMA_unQuote($arr[$i]['data']);
1194 break;
1195 } // end switch
1197 if ($subresult['querytype'] == 'SELECT'
1198 && ! $in_group_concat
1199 && ! ($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
1200 if (!$seen_from) {
1201 if ($previous_was_identifier && isset($chain)) {
1202 // found alias for this select_expr, save it
1203 // but only if we got something in $chain
1204 // (for example, SELECT COUNT(*) AS cnt
1205 // puts nothing in $chain, so we avoid
1206 // setting the alias)
1207 $alias_for_select_expr = $identifier;
1208 } else {
1209 $chain[] = $identifier;
1210 $previous_was_identifier = true;
1212 } // end if !$previous_was_identifier
1213 } else {
1214 // ($seen_from)
1215 if ($save_table_ref && !$seen_end_of_table_ref) {
1216 if ($previous_was_identifier) {
1217 // found alias for table ref
1218 // save it for later
1219 $alias_for_table_ref = $identifier;
1220 } else {
1221 $chain[] = $identifier;
1222 $previous_was_identifier = true;
1224 } // end if ($previous_was_identifier)
1225 } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1226 } // end if (!$seen_from)
1227 } // end if (querytype SELECT)
1228 } // end if (quote_backtick or double quote or alpha_identifier)
1230 // ===================================
1231 if ($arr[$i]['type'] == 'punct_qualifier') {
1232 // to be able to detect an identifier following another
1233 $previous_was_identifier = false;
1234 continue;
1235 } // end if (punct_qualifier)
1238 * @todo check if 3 identifiers following one another -> error
1241 // s a v e a s e l e c t e x p r
1242 // finding a list separator or FROM
1243 // means that we must save the current chain of identifiers
1244 // into a select expression
1246 // for now, we only save a select expression if it contains
1247 // at least one identifier, as we are interested in checking
1248 // the columns and table names, so in "select * from persons",
1249 // the "*" is not saved
1251 if (isset($chain) && !$seen_end_of_table_ref
1252 && ((!$seen_from && $arr[$i]['type'] == 'punct_listsep')
1253 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM'))) {
1254 $size_chain = count($chain);
1255 $current_select_expr++;
1256 $subresult['select_expr'][$current_select_expr] = array(
1257 'expr' => '',
1258 'alias' => '',
1259 'db' => '',
1260 'table_name' => '',
1261 'table_true_name' => '',
1262 'column' => ''
1265 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1266 // we had found an alias for this select expression
1267 $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1268 unset($alias_for_select_expr);
1270 // there is at least a column
1271 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1272 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1274 // maybe a table
1275 if ($size_chain > 1) {
1276 $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1277 // we assume for now that this is also the true name
1278 $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1279 $subresult['select_expr'][$current_select_expr]['expr']
1280 = $subresult['select_expr'][$current_select_expr]['table_name']
1281 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1282 } // end if ($size_chain > 1)
1284 // maybe a db
1285 if ($size_chain > 2) {
1286 $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1287 $subresult['select_expr'][$current_select_expr]['expr']
1288 = $subresult['select_expr'][$current_select_expr]['db']
1289 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1290 } // end if ($size_chain > 2)
1291 unset($chain);
1294 * @todo explain this:
1296 if (($arr[$i]['type'] == 'alpha_reservedWord')
1297 && ($upper_data != 'FROM')) {
1298 $previous_was_identifier = true;
1301 } // end if (save a select expr)
1304 //======================================
1305 // s a v e a t a b l e r e f
1306 //======================================
1308 // maybe we just saw the end of table refs
1309 // but the last table ref has to be saved
1310 // or we are at the last token
1311 // or we just got a reserved word
1313 * @todo there could be another query after this one
1316 if (isset($chain) && $seen_from && $save_table_ref
1317 && ($arr[$i]['type'] == 'punct_listsep'
1318 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1319 || $seen_end_of_table_ref
1320 || $i==$size-1)) {
1322 $size_chain = count($chain);
1323 $current_table_ref++;
1324 $subresult['table_ref'][$current_table_ref] = array(
1325 'expr' => '',
1326 'db' => '',
1327 'table_name' => '',
1328 'table_alias' => '',
1329 'table_true_name' => ''
1331 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1332 $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1333 unset($alias_for_table_ref);
1335 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1336 // we assume for now that this is also the true name
1337 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1338 $subresult['table_ref'][$current_table_ref]['expr']
1339 = $subresult['table_ref'][$current_table_ref]['table_name'];
1340 // maybe a db
1341 if ($size_chain > 1) {
1342 $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1343 $subresult['table_ref'][$current_table_ref]['expr']
1344 = $subresult['table_ref'][$current_table_ref]['db']
1345 . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1346 } // end if ($size_chain > 1)
1348 // add the table alias into the whole expression
1349 $subresult['table_ref'][$current_table_ref]['expr']
1350 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1352 unset($chain);
1353 $previous_was_identifier = true;
1354 //continue;
1356 } // end if (save a table ref)
1359 // when we have found all table refs,
1360 // for each table_ref alias, put the true name of the table
1361 // in the corresponding select expressions
1363 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
1364 for ($tr=0; $tr <= $current_table_ref; $tr++) {
1365 $alias = $subresult['table_ref'][$tr]['table_alias'];
1366 $truename = $subresult['table_ref'][$tr]['table_true_name'];
1367 for ($se=0; $se <= $current_select_expr; $se++) {
1368 if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1369 == $alias) {
1370 $subresult['select_expr'][$se]['table_true_name']
1371 = $truename;
1372 } // end if (found the alias)
1373 } // end for (select expressions)
1375 } // end for (table refs)
1376 } // end if (set the true names)
1379 // e n d i n g l o o p #1
1380 // set the $previous_was_identifier to false if the current
1381 // token is not an identifier
1382 if (($arr[$i]['type'] != 'alpha_identifier')
1383 && ($arr[$i]['type'] != 'quote_double')
1384 && ($arr[$i]['type'] != 'quote_single')
1385 && ($arr[$i]['type'] != 'quote_backtick')) {
1386 $previous_was_identifier = false;
1387 } // end if
1389 // however, if we are on AS, we must keep the $previous_was_identifier
1390 if (($arr[$i]['type'] == 'alpha_reservedWord')
1391 && ($upper_data == 'AS')) {
1392 $previous_was_identifier = true;
1395 if (($arr[$i]['type'] == 'alpha_reservedWord')
1396 && ($upper_data =='ON' || $upper_data =='USING')) {
1397 $save_table_ref = false;
1398 } // end if (data == ON)
1400 if (($arr[$i]['type'] == 'alpha_reservedWord')
1401 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1402 $save_table_ref = true;
1403 } // end if (data == JOIN)
1406 * no need to check the end of table ref if we already did
1408 * @todo maybe add "&& $seen_from"
1410 if (!$seen_end_of_table_ref) {
1411 // if this is the last token, it implies that we have
1412 // seen the end of table references
1413 // Check for the end of table references
1415 // Note: if we are analyzing a GROUP_CONCAT clause,
1416 // we might find a word that seems to indicate that
1417 // we have found the end of table refs (like ORDER)
1418 // but it's a modifier of the GROUP_CONCAT so
1419 // it's not the real end of table refs
1420 if (($i == $size-1)
1421 || ($arr[$i]['type'] == 'alpha_reservedWord'
1422 && !$in_group_concat
1423 && isset($words_ending_table_ref[$upper_data]))) {
1424 $seen_end_of_table_ref = true;
1425 // to be able to save the last table ref, but do not
1426 // set it true if we found a word like "ON" that has
1427 // already set it to false
1428 if (isset($save_table_ref) && $save_table_ref != false) {
1429 $save_table_ref = true;
1430 } //end if
1432 } // end if (check for end of table ref)
1433 } //end if (!$seen_end_of_table_ref)
1435 if ($seen_end_of_table_ref) {
1436 $save_table_ref = false;
1437 } // end if
1439 } // end for $i (loop #1)
1441 //DEBUG
1443 if (isset($current_select_expr)) {
1444 for ($trace=0; $trace<=$current_select_expr; $trace++) {
1445 echo "<br />";
1446 reset ($subresult['select_expr'][$trace]);
1447 while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1448 echo "sel expr $trace $key => $val<br />\n";
1452 if (isset($current_table_ref)) {
1453 echo "current_table_ref = " . $current_table_ref . "<br>";
1454 for ($trace=0; $trace<=$current_table_ref; $trace++) {
1456 echo "<br />";
1457 reset ($subresult['table_ref'][$trace]);
1458 while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1459 echo "table ref $trace $key => $val<br />\n";
1463 // -------------------------------------------------------
1466 // loop #2: - queryflags
1467 // - querytype (for queries != 'SELECT')
1468 // - section_before_limit, section_after_limit
1470 // we will also need this queryflag in loop 2
1471 // so set it here
1472 if (isset($current_table_ref) && $current_table_ref > -1) {
1473 $subresult['queryflags']['select_from'] = 1;
1476 $section_before_limit = '';
1477 $section_after_limit = ''; // truly the section after the limit clause
1478 $seen_reserved_word = false;
1479 $seen_group = false;
1480 $seen_order = false;
1481 $seen_order_by = false;
1482 $in_group_by = false; // true when we are inside the GROUP BY clause
1483 $in_order_by = false; // true when we are inside the ORDER BY clause
1484 $in_having = false; // true when we are inside the HAVING clause
1485 $in_select_expr = false; // true when we are inside the select expr clause
1486 $in_where = false; // true when we are inside the WHERE clause
1487 $seen_limit = false; // true if we have seen a LIMIT clause
1488 $in_limit = false; // true when we are inside the LIMIT clause
1489 $after_limit = false; // true when we are after the LIMIT clause
1490 $in_from = false; // true when we are in the FROM clause
1491 $in_group_concat = false;
1492 $first_reserved_word = '';
1493 $current_identifier = '';
1494 $unsorted_query = $arr['raw']; // in case there is no ORDER BY
1495 $number_of_brackets = 0;
1496 $in_subquery = false;
1498 for ($i = 0; $i < $size; $i++) {
1499 //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1501 // need_confirm
1503 // check for reserved words that will have to generate
1504 // a confirmation request later in sql.php
1505 // the cases are:
1506 // DROP TABLE
1507 // DROP DATABASE
1508 // ALTER TABLE... DROP
1509 // DELETE FROM...
1511 // this code is not used for confirmations coming from functions.js
1513 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1514 $number_of_brackets++;
1517 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1518 $number_of_brackets--;
1519 if ($number_of_brackets == 0) {
1520 $in_subquery = false;
1524 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1525 $upper_data = strtoupper($arr[$i]['data']);
1527 if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
1528 $in_subquery = true;
1531 if (!$seen_reserved_word) {
1532 $first_reserved_word = $upper_data;
1533 $subresult['querytype'] = $upper_data;
1534 $seen_reserved_word = true;
1536 // if the first reserved word is DROP or DELETE,
1537 // we know this is a query that needs to be confirmed
1538 if ($first_reserved_word=='DROP'
1539 || $first_reserved_word == 'DELETE'
1540 || $first_reserved_word == 'TRUNCATE') {
1541 $subresult['queryflags']['need_confirm'] = 1;
1544 if ($first_reserved_word=='SELECT') {
1545 $position_of_first_select = $i;
1548 } else {
1549 if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1550 $subresult['queryflags']['need_confirm'] = 1;
1554 if ($upper_data == 'LIMIT' && ! $in_subquery) {
1555 $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1556 $in_limit = true;
1557 $seen_limit = true;
1558 $limit_clause = '';
1559 $in_order_by = false; // @todo maybe others to set false
1562 if ($upper_data == 'PROCEDURE') {
1563 $subresult['queryflags']['procedure'] = 1;
1564 $in_limit = false;
1565 $after_limit = true;
1568 * @todo set also to false if we find FOR UPDATE or LOCK IN SHARE MODE
1570 if ($upper_data == 'SELECT') {
1571 $in_select_expr = true;
1572 $select_expr_clause = '';
1574 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1575 $subresult['queryflags']['distinct'] = 1;
1578 if ($upper_data == 'UNION') {
1579 $subresult['queryflags']['union'] = 1;
1582 if ($upper_data == 'JOIN') {
1583 $subresult['queryflags']['join'] = 1;
1586 if ($upper_data == 'OFFSET') {
1587 $subresult['queryflags']['offset'] = 1;
1590 // if this is a real SELECT...FROM
1591 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1592 $in_from = true;
1593 $from_clause = '';
1594 $in_select_expr = false;
1598 // (we could have less resetting of variables to false
1599 // if we trust that the query respects the standard
1600 // MySQL order for clauses)
1602 // we use $seen_group and $seen_order because we are looking
1603 // for the BY
1604 if ($upper_data == 'GROUP') {
1605 $seen_group = true;
1606 $seen_order = false;
1607 $in_having = false;
1608 $in_order_by = false;
1609 $in_where = false;
1610 $in_select_expr = false;
1611 $in_from = false;
1613 if ($upper_data == 'ORDER' && !$in_group_concat) {
1614 $seen_order = true;
1615 $seen_group = false;
1616 $in_having = false;
1617 $in_group_by = false;
1618 $in_where = false;
1619 $in_select_expr = false;
1620 $in_from = false;
1622 if ($upper_data == 'HAVING') {
1623 $in_having = true;
1624 $having_clause = '';
1625 $seen_group = false;
1626 $seen_order = false;
1627 $in_group_by = false;
1628 $in_order_by = false;
1629 $in_where = false;
1630 $in_select_expr = false;
1631 $in_from = false;
1634 if ($upper_data == 'WHERE') {
1635 $in_where = true;
1636 $where_clause = '';
1637 $where_clause_identifiers = array();
1638 $seen_group = false;
1639 $seen_order = false;
1640 $in_group_by = false;
1641 $in_order_by = false;
1642 $in_having = false;
1643 $in_select_expr = false;
1644 $in_from = false;
1647 if ($upper_data == 'BY') {
1648 if ($seen_group) {
1649 $in_group_by = true;
1650 $group_by_clause = '';
1652 if ($seen_order) {
1653 $seen_order_by = true;
1654 // Here we assume that the ORDER BY keywords took
1655 // exactly 8 characters.
1656 // We use PMA_substr() to be charset-safe; otherwise
1657 // if the table name contains accents, the unsorted
1658 // query would be missing some characters.
1659 $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1660 $in_order_by = true;
1661 $order_by_clause = '';
1665 // if we find one of the words that could end the clause
1666 if (isset($words_ending_clauses[$upper_data])) {
1668 $in_group_by = false;
1669 $in_order_by = false;
1670 $in_having = false;
1671 $in_where = false;
1672 $in_select_expr = false;
1673 $in_from = false;
1676 } // endif (reservedWord)
1679 // do not add a space after a function name
1681 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1684 $sep = ' ';
1685 if ($arr[$i]['type'] == 'alpha_functionName') {
1686 $sep='';
1687 $upper_data = strtoupper($arr[$i]['data']);
1688 if ($upper_data =='GROUP_CONCAT') {
1689 $in_group_concat = true;
1690 $number_of_brackets_in_group_concat = 0;
1694 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1695 if ($in_group_concat) {
1696 $number_of_brackets_in_group_concat++;
1699 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1700 if ($in_group_concat) {
1701 $number_of_brackets_in_group_concat--;
1702 if ($number_of_brackets_in_group_concat == 0) {
1703 $in_group_concat = false;
1708 // do not add a space after an identifier if followed by a dot
1709 if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1710 $sep = '';
1713 // do not add a space after a dot if followed by an identifier
1714 if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1715 $sep = '';
1718 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1719 $select_expr_clause .= $arr[$i]['data'] . $sep;
1721 if ($in_from && $upper_data != 'FROM') {
1722 $from_clause .= $arr[$i]['data'] . $sep;
1724 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1725 $group_by_clause .= $arr[$i]['data'] . $sep;
1727 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1728 // add a space only before ASC or DESC
1729 // not around the dot between dbname and tablename
1730 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1731 $order_by_clause .= $sep;
1733 $order_by_clause .= $arr[$i]['data'];
1735 if ($in_having && $upper_data != 'HAVING') {
1736 $having_clause .= $arr[$i]['data'] . $sep;
1738 if ($in_where && $upper_data != 'WHERE') {
1739 $where_clause .= $arr[$i]['data'] . $sep;
1741 if (($arr[$i]['type'] == 'quote_backtick')
1742 || ($arr[$i]['type'] == 'alpha_identifier')) {
1743 $where_clause_identifiers[] = $arr[$i]['data'];
1747 // to grab the rest of the query after the ORDER BY clause
1748 if (isset($subresult['queryflags']['select_from'])
1749 && $subresult['queryflags']['select_from'] == 1
1750 && ! $in_order_by
1751 && $seen_order_by
1752 && $upper_data != 'BY') {
1753 $unsorted_query .= $arr[$i]['data'];
1754 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1755 && $arr[$i]['type'] != 'punct_bracket_close_round'
1756 && $arr[$i]['type'] != 'punct') {
1757 $unsorted_query .= $sep;
1761 if ($in_limit) {
1762 if ($upper_data == 'OFFSET') {
1763 $limit_clause .= $sep;
1765 $limit_clause .= $arr[$i]['data'];
1766 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1767 $limit_clause .= $sep;
1770 if ($after_limit && $seen_limit) {
1771 $section_after_limit .= $arr[$i]['data'] . $sep;
1774 // clear $upper_data for next iteration
1775 $upper_data='';
1776 } // end for $i (loop #2)
1777 if (empty($section_before_limit)) {
1778 $section_before_limit = $arr['raw'];
1781 // -----------------------------------------------------
1782 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1783 // (for now, check only the first query)
1784 // (for now, identifiers are assumed to be backquoted)
1786 // If we find that we are dealing with a CREATE TABLE query,
1787 // we look for the next punct_bracket_open_round, which
1788 // introduces the fields list. Then, when we find a
1789 // quote_backtick, it must be a field, so we put it into
1790 // the create_table_fields array. Even if this field is
1791 // not a timestamp, it will be useful when logic has been
1792 // added for complete field attributes analysis.
1794 $seen_foreign = false;
1795 $seen_references = false;
1796 $seen_constraint = false;
1797 $foreign_key_number = -1;
1798 $seen_create_table = false;
1799 $seen_create = false;
1800 $seen_alter = false;
1801 $in_create_table_fields = false;
1802 $brackets_level = 0;
1803 $in_timestamp_options = false;
1804 $seen_default = false;
1806 for ($i = 0; $i < $size; $i++) {
1807 // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1809 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1810 $upper_data = strtoupper($arr[$i]['data']);
1812 if ($upper_data == 'NOT' && $in_timestamp_options) {
1813 $create_table_fields[$current_identifier]['timestamp_not_null'] = true;
1817 if ($upper_data == 'CREATE') {
1818 $seen_create = true;
1821 if ($upper_data == 'ALTER') {
1822 $seen_alter = true;
1825 if ($upper_data == 'TABLE' && $seen_create) {
1826 $seen_create_table = true;
1827 $create_table_fields = array();
1830 if ($upper_data == 'CURRENT_TIMESTAMP') {
1831 if ($in_timestamp_options) {
1832 if ($seen_default) {
1833 $create_table_fields[$current_identifier]['default_current_timestamp'] = true;
1838 if ($upper_data == 'CONSTRAINT') {
1839 $foreign_key_number++;
1840 $seen_foreign = false;
1841 $seen_references = false;
1842 $seen_constraint = true;
1844 if ($upper_data == 'FOREIGN') {
1845 $seen_foreign = true;
1846 $seen_references = false;
1847 $seen_constraint = false;
1849 if ($upper_data == 'REFERENCES') {
1850 $seen_foreign = false;
1851 $seen_references = true;
1852 $seen_constraint = false;
1856 // Cases covered:
1858 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1859 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1861 // but we set ['on_delete'] or ['on_cascade'] to
1862 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1864 // ON UPDATE CURRENT_TIMESTAMP
1866 if ($upper_data == 'ON') {
1867 if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1868 $second_upper_data = strtoupper($arr[$i+1]['data']);
1869 if ($second_upper_data == 'DELETE') {
1870 $clause = 'on_delete';
1872 if ($second_upper_data == 'UPDATE') {
1873 $clause = 'on_update';
1875 if (isset($clause)
1876 && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1878 // ugly workaround because currently, NO is not
1879 // in the list of reserved words in sqlparser.data
1880 // (we got a bug report about not being able to use
1881 // 'no' as an identifier)
1882 || ($arr[$i+2]['type'] == 'alpha_identifier'
1883 && strtoupper($arr[$i+2]['data'])=='NO'))
1885 $third_upper_data = strtoupper($arr[$i+2]['data']);
1886 if ($third_upper_data == 'CASCADE'
1887 || $third_upper_data == 'RESTRICT') {
1888 $value = $third_upper_data;
1889 } elseif ($third_upper_data == 'SET'
1890 || $third_upper_data == 'NO') {
1891 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1892 $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1894 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1895 if ($clause == 'on_update'
1896 && $in_timestamp_options) {
1897 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = true;
1898 $seen_default = false;
1901 } else {
1902 $value = '';
1904 if (!empty($value)) {
1905 $foreign[$foreign_key_number][$clause] = $value;
1907 unset($clause);
1908 } // endif (isset($clause))
1912 } // end of reserved words analysis
1915 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1916 $brackets_level++;
1917 if ($seen_create_table && $brackets_level == 1) {
1918 $in_create_table_fields = true;
1923 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1924 $brackets_level--;
1925 if ($seen_references) {
1926 $seen_references = false;
1928 if ($seen_create_table && $brackets_level == 0) {
1929 $in_create_table_fields = false;
1933 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1934 $upper_data = strtoupper($arr[$i]['data']);
1935 if ($seen_create_table && $in_create_table_fields) {
1936 if ($upper_data == 'DEFAULT') {
1937 $seen_default = true;
1938 $create_table_fields[$current_identifier]['default_value'] = $arr[$i + 1]['data'];
1944 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1946 if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1947 $upper_data = strtoupper($arr[$i]['data']);
1948 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1949 $create_table_fields[$current_identifier]['type'] = $upper_data;
1950 if ($upper_data == 'TIMESTAMP') {
1951 $arr[$i]['type'] = 'alpha_columnType';
1952 $in_timestamp_options = true;
1953 } else {
1954 $in_timestamp_options = false;
1955 if ($upper_data == 'CHAR') {
1956 $arr[$i]['type'] = 'alpha_columnType';
1963 if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1965 if ($arr[$i]['type'] == 'quote_backtick') {
1966 // remove backquotes
1967 $identifier = PMA_unQuote($arr[$i]['data']);
1968 } else {
1969 $identifier = $arr[$i]['data'];
1972 if ($seen_create_table && $in_create_table_fields) {
1973 $current_identifier = $identifier;
1974 // we set this one even for non TIMESTAMP type
1975 $create_table_fields[$current_identifier]['timestamp_not_null'] = false;
1978 if ($seen_constraint) {
1979 $foreign[$foreign_key_number]['constraint'] = $identifier;
1982 if ($seen_foreign && $brackets_level > 0) {
1983 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1986 if ($seen_references) {
1987 if ($seen_alter && $brackets_level > 0) {
1988 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1989 // here, the first bracket level corresponds to the
1990 // bracket of CREATE TABLE
1991 // so if we are on level 2, it must be the index list
1992 // of the foreign key REFERENCES
1993 } elseif ($brackets_level > 1) {
1994 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1995 } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1996 // identifier is `db`.`table`
1997 // the first pass will pick the db name
1998 // the next pass will pick the table name
1999 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
2000 } else {
2001 // identifier is `table`
2002 $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
2006 } // end for $i (loop #3)
2009 // Fill the $subresult array
2011 if (isset($create_table_fields)) {
2012 $subresult['create_table_fields'] = $create_table_fields;
2015 if (isset($foreign)) {
2016 $subresult['foreign_keys'] = $foreign;
2019 if (isset($select_expr_clause)) {
2020 $subresult['select_expr_clause'] = $select_expr_clause;
2022 if (isset($from_clause)) {
2023 $subresult['from_clause'] = $from_clause;
2025 if (isset($group_by_clause)) {
2026 $subresult['group_by_clause'] = $group_by_clause;
2028 if (isset($order_by_clause)) {
2029 $subresult['order_by_clause'] = $order_by_clause;
2031 if (isset($having_clause)) {
2032 $subresult['having_clause'] = $having_clause;
2034 if (isset($limit_clause)) {
2035 $subresult['limit_clause'] = $limit_clause;
2037 if (isset($where_clause)) {
2038 $subresult['where_clause'] = $where_clause;
2040 if (isset($unsorted_query) && !empty($unsorted_query)) {
2041 $subresult['unsorted_query'] = $unsorted_query;
2043 if (isset($where_clause_identifiers)) {
2044 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
2047 if (isset($position_of_first_select)) {
2048 $subresult['position_of_first_select'] = $position_of_first_select;
2049 $subresult['section_before_limit'] = $section_before_limit;
2050 $subresult['section_after_limit'] = $section_after_limit;
2053 // They are naughty and didn't have a trailing semi-colon,
2054 // then still handle it properly
2055 if ($subresult['querytype'] != '') {
2056 $result[] = $subresult;
2058 return $result;
2059 } // end of the "PMA_SQP_analyze()" function
2063 * Colorizes SQL queries html formatted
2065 * @todo check why adding a "\n" after the </span> would cause extra blanks
2066 * to be displayed: SELECT p . person_name
2067 * @param array The SQL queries html formatted
2069 * @return array The colorized SQL queries
2071 * @access public
2073 function PMA_SQP_formatHtml_colorize($arr)
2075 $i = PMA_strpos($arr['type'], '_');
2076 $class = '';
2077 if ($i > 0) {
2078 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2081 $class .= 'syntax_' . $arr['type'];
2083 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2084 } // end of the "PMA_SQP_formatHtml_colorize()" function
2088 * Formats SQL queries to html
2090 * @param array The SQL queries
2091 * @param string mode
2092 * @param integer starting token
2093 * @param integer number of tokens to format, -1 = all
2095 * @return string The formatted SQL queries
2097 * @access public
2099 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2100 $number_of_tokens=-1)
2102 global $PMA_SQPdata_operators_docs, $PMA_SQPdata_functions_docs;
2103 //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2104 // then check for an array
2105 if (! is_array($arr)) {
2106 return htmlspecialchars($arr);
2108 // first check for the SQL parser having hit an error
2109 if (PMA_SQP_isError()) {
2110 return htmlspecialchars($arr['raw']);
2112 // else do it properly
2113 switch ($mode) {
2114 case 'color':
2115 $str = '<span class="syntax">';
2116 $html_line_break = '<br />';
2117 $docu = true;
2118 break;
2119 case 'query_only':
2120 $str = '';
2121 $html_line_break = "\n";
2122 $docu = false;
2123 break;
2124 case 'text':
2125 $str = '';
2126 $html_line_break = '<br />';
2127 $docu = true;
2128 break;
2129 } // end switch
2130 // inner_sql is a span that exists for all cases, except query_only
2131 // of $cfg['SQP']['fmtType'] to make possible a replacement
2132 // for inline editing
2133 if ($mode!='query_only') {
2134 $str .= '<span class="inner_sql">';
2136 $close_docu_link = false;
2137 $indent = 0;
2138 $bracketlevel = 0;
2139 $functionlevel = 0;
2140 $infunction = false;
2141 $space_punct_listsep = ' ';
2142 $space_punct_listsep_function_name = ' ';
2143 // $space_alpha_reserved_word = '<br />'."\n";
2144 $space_alpha_reserved_word = ' ';
2146 $keywords_with_brackets_1before = array(
2147 'INDEX' => 1,
2148 'KEY' => 1,
2149 'ON' => 1,
2150 'USING' => 1
2153 $keywords_with_brackets_2before = array(
2154 'IGNORE' => 1,
2155 'INDEX' => 1,
2156 'INTO' => 1,
2157 'KEY' => 1,
2158 'PRIMARY' => 1,
2159 'PROCEDURE' => 1,
2160 'REFERENCES' => 1,
2161 'UNIQUE' => 1,
2162 'USE' => 1
2165 // These reserved words do NOT get a newline placed near them.
2166 $keywords_no_newline = array(
2167 'AS' => 1,
2168 'ASC' => 1,
2169 'DESC' => 1,
2170 'DISTINCT' => 1,
2171 'DUPLICATE' => 1,
2172 'HOUR' => 1,
2173 'INTERVAL' => 1,
2174 'IS' => 1,
2175 'LIKE' => 1,
2176 'NOT' => 1,
2177 'NULL' => 1,
2178 'ON' => 1,
2179 'REGEXP' => 1
2182 // These reserved words introduce a privilege list
2183 $keywords_priv_list = array(
2184 'GRANT' => 1,
2185 'REVOKE' => 1
2188 if ($number_of_tokens == -1) {
2189 $number_of_tokens = $arr['len'];
2191 $typearr = array();
2192 if ($number_of_tokens >= 0) {
2193 $typearr[0] = '';
2194 $typearr[1] = '';
2195 $typearr[2] = '';
2196 $typearr[3] = $arr[$start_token]['type'];
2199 $in_priv_list = false;
2200 for ($i = $start_token; $i < $number_of_tokens; $i++) {
2201 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2202 $before = '';
2203 $after = '';
2204 // array_shift($typearr);
2206 0 prev2
2207 1 prev
2208 2 current
2209 3 next
2211 if (($i + 1) < $number_of_tokens) {
2212 $typearr[4] = $arr[$i + 1]['type'];
2213 } else {
2214 $typearr[4] = '';
2217 for ($j=0; $j<4; $j++) {
2218 $typearr[$j] = $typearr[$j + 1];
2221 switch ($typearr[2]) {
2222 case 'alpha_bitfield_constant_introducer':
2223 $before = ' ';
2224 $after = '';
2225 break;
2226 case 'white_newline':
2227 $before = '';
2228 break;
2229 case 'punct_bracket_open_round':
2230 $bracketlevel++;
2231 $infunction = false;
2232 // Make sure this array is sorted!
2233 if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2234 || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2235 || (($typearr[0] == 'alpha_reservedWord')
2236 && isset($keywords_with_brackets_2before[strtoupper($arr[$i - 2]['data'])]))
2237 || (($typearr[1] == 'alpha_reservedWord')
2238 && isset($keywords_with_brackets_1before[strtoupper($arr[$i - 1]['data'])]))
2240 $functionlevel++;
2241 $infunction = true;
2242 $after .= ' ';
2243 } else {
2244 $indent++;
2245 $after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2247 break;
2248 case 'alpha_identifier':
2249 if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2250 $after = '';
2251 $before = '';
2253 // for example SELECT 1 somealias
2254 if ($typearr[1] == 'digit_integer') {
2255 $before = ' ';
2257 if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2258 $after .= ' ';
2260 break;
2261 case 'punct_user':
2262 case 'punct_qualifier':
2263 $before = '';
2264 $after = '';
2265 break;
2266 case 'punct_listsep':
2267 if ($infunction == true) {
2268 $after .= $space_punct_listsep_function_name;
2269 } else {
2270 $after .= $space_punct_listsep;
2272 break;
2273 case 'punct_queryend':
2274 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2275 $after .= $html_line_break;
2276 $after .= $html_line_break;
2278 $space_punct_listsep = ' ';
2279 $space_punct_listsep_function_name = ' ';
2280 $space_alpha_reserved_word = ' ';
2281 $in_priv_list = false;
2282 break;
2283 case 'comment_mysql':
2284 case 'comment_ansi':
2285 $after .= $html_line_break;
2286 break;
2287 case 'punct':
2288 $before .= ' ';
2289 if ($docu && isset($PMA_SQPdata_operators_docs[$arr[$i]['data']]) &&
2290 ($arr[$i]['data'] != '*' || in_array($arr[$i]['type'], array('digit_integer','digit_float','digit_hex')))) {
2291 $before .= PMA_showMySQLDocu(
2292 'functions',
2293 $PMA_SQPdata_operators_docs[$arr[$i]['data']]['link'],
2294 false,
2295 $PMA_SQPdata_operators_docs[$arr[$i]['data']]['anchor'],
2296 true);
2297 $after .= '</a>';
2300 // workaround for
2301 // select * from mytable limit 0,-1
2302 // (a side effect of this workaround is that
2303 // select 20 - 9
2304 // becomes
2305 // select 20 -9
2306 // )
2307 if ($typearr[3] != 'digit_integer') {
2308 $after .= ' ';
2310 break;
2311 case 'punct_bracket_close_round':
2312 // only close bracket level when it was opened before
2313 if ($bracketlevel > 0) {
2314 $bracketlevel--;
2315 if ($infunction == true) {
2316 $functionlevel--;
2317 $after .= ' ';
2318 $before .= ' ';
2319 } else {
2320 $indent--;
2321 $before .= ($mode != 'query_only' ? '</div>' : ' ');
2323 $infunction = ($functionlevel > 0) ? true : false;
2325 break;
2326 case 'alpha_columnType':
2327 if ($docu) {
2328 switch ($arr[$i]['data']) {
2329 case 'tinyint':
2330 case 'smallint':
2331 case 'mediumint':
2332 case 'int':
2333 case 'bigint':
2334 case 'decimal':
2335 case 'float':
2336 case 'double':
2337 case 'real':
2338 case 'bit':
2339 case 'boolean':
2340 case 'serial':
2341 $before .= PMA_showMySQLDocu('data-types', 'numeric-types', false, '', true);
2342 $after = '</a>' . $after;
2343 break;
2344 case 'date':
2345 case 'datetime':
2346 case 'timestamp':
2347 case 'time':
2348 case 'year':
2349 $before .= PMA_showMySQLDocu('data-types', 'date-and-time-types', false, '', true);
2350 $after = '</a>' . $after;
2351 break;
2352 case 'char':
2353 case 'varchar':
2354 case 'tinytext':
2355 case 'text':
2356 case 'mediumtext':
2357 case 'longtext':
2358 case 'binary':
2359 case 'varbinary':
2360 case 'tinyblob':
2361 case 'mediumblob':
2362 case 'blob':
2363 case 'longblob':
2364 case 'enum':
2365 case 'set':
2366 $before .= PMA_showMySQLDocu('data-types', 'string-types', false, '', true);
2367 $after = '</a>' . $after;
2368 break;
2371 if ($typearr[3] == 'alpha_columnAttrib') {
2372 $after .= ' ';
2374 if ($typearr[1] == 'alpha_columnType') {
2375 $before .= ' ';
2377 break;
2378 case 'alpha_columnAttrib':
2380 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2381 // COLLATE LATIN1_GENERAL_CI DEFAULT
2382 if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2383 $before .= ' ';
2385 if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2386 $after .= ' ';
2388 // workaround for
2389 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2391 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2392 $before .= ' ';
2394 // workaround for
2395 // select * from mysql.user where binary user="root"
2396 // binary is marked as alpha_columnAttrib
2397 // but should be marked as a reserved word
2398 if (strtoupper($arr[$i]['data']) == 'BINARY'
2399 && $typearr[3] == 'alpha_identifier') {
2400 $after .= ' ';
2402 break;
2403 case 'alpha_functionName':
2404 $funcname = strtoupper($arr[$i]['data']);
2405 if ($docu && isset($PMA_SQPdata_functions_docs[$funcname])) {
2406 $before .= PMA_showMySQLDocu(
2407 'functions',
2408 $PMA_SQPdata_functions_docs[$funcname]['link'],
2409 false,
2410 $PMA_SQPdata_functions_docs[$funcname]['anchor'],
2411 true);
2412 $after .= '</a>';
2414 break;
2415 case 'alpha_reservedWord':
2416 // do not uppercase the reserved word if we are calling
2417 // this function in query_only mode, because we need
2418 // the original query (otherwise we get problems with
2419 // semi-reserved words like "storage" which is legal
2420 // as an identifier name)
2422 if ($mode != 'query_only') {
2423 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2426 if ((($typearr[1] != 'alpha_reservedWord')
2427 || (($typearr[1] == 'alpha_reservedWord')
2428 && isset($keywords_no_newline[strtoupper($arr[$i - 1]['data'])])))
2429 && ($typearr[1] != 'punct_level_plus')
2430 && (!isset($keywords_no_newline[$arr[$i]['data']]))) {
2431 // do not put a space before the first token, because
2432 // we use a lot of pattern matching checking for the
2433 // first reserved word at beginning of query
2434 // so do not put a newline before
2436 // also we must not be inside a privilege list
2437 if ($i > 0) {
2438 // the alpha_identifier exception is there to
2439 // catch cases like
2440 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2441 // (else, we get mydb.mytableTO)
2443 // the quote_single exception is there to
2444 // catch cases like
2445 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2447 * @todo fix all cases and find why this happens
2450 if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2451 $before .= $space_alpha_reserved_word;
2453 } else {
2454 // on first keyword, check if it introduces a
2455 // privilege list
2456 if (isset($keywords_priv_list[$arr[$i]['data']])) {
2457 $in_priv_list = true;
2460 } else {
2461 $before .= ' ';
2464 switch ($arr[$i]['data']) {
2465 case 'CREATE':
2466 case 'ALTER':
2467 case 'DROP':
2468 case 'RENAME';
2469 case 'TRUNCATE':
2470 case 'ANALYZE':
2471 case 'ANALYSE':
2472 case 'OPTIMIZE':
2473 if ($docu) {
2474 switch ($arr[$i + 1]['data']) {
2475 case 'EVENT':
2476 case 'TABLE':
2477 case 'TABLESPACE':
2478 case 'FUNCTION':
2479 case 'INDEX':
2480 case 'PROCEDURE':
2481 case 'TRIGGER':
2482 case 'SERVER':
2483 case 'DATABASE':
2484 case 'VIEW':
2485 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_' . $arr[$i + 1]['data'], false, '', true);
2486 $close_docu_link = true;
2487 break;
2489 if ($arr[$i + 1]['data'] == 'LOGFILE' && $arr[$i + 2]['data'] == 'GROUP') {
2490 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_LOGFILE_GROUP', false, '', true);
2491 $close_docu_link = true;
2494 if (!$in_priv_list) {
2495 $space_punct_listsep = $html_line_break;
2496 $space_alpha_reserved_word = ' ';
2498 break;
2499 case 'EVENT':
2500 case 'TABLESPACE':
2501 case 'TABLE':
2502 case 'FUNCTION':
2503 case 'INDEX':
2504 case 'PROCEDURE':
2505 case 'SERVER':
2506 case 'TRIGGER':
2507 case 'DATABASE':
2508 case 'VIEW':
2509 case 'GROUP':
2510 if ($close_docu_link) {
2511 $after = '</a>' . $after;
2512 $close_docu_link = false;
2514 break;
2515 case 'SET':
2516 if ($docu && ($i == 0 || $arr[$i - 1]['data'] != 'CHARACTER')) {
2517 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2518 $after = '</a>' . $after;
2520 if (!$in_priv_list) {
2521 $space_punct_listsep = $html_line_break;
2522 $space_alpha_reserved_word = ' ';
2524 break;
2525 case 'EXPLAIN':
2526 case 'DESCRIBE':
2527 case 'DELETE':
2528 case 'SHOW':
2529 case 'UPDATE':
2530 if ($docu) {
2531 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2532 $after = '</a>' . $after;
2534 if (!$in_priv_list) {
2535 $space_punct_listsep = $html_line_break;
2536 $space_alpha_reserved_word = ' ';
2538 break;
2539 case 'INSERT':
2540 case 'REPLACE':
2541 if ($docu) {
2542 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2543 $after = '</a>' . $after;
2545 if (!$in_priv_list) {
2546 $space_punct_listsep = $html_line_break;
2547 $space_alpha_reserved_word = $html_line_break;
2549 break;
2550 case 'VALUES':
2551 $space_punct_listsep = ' ';
2552 $space_alpha_reserved_word = $html_line_break;
2553 break;
2554 case 'SELECT':
2555 if ($docu) {
2556 $before .= PMA_showMySQLDocu('SQL-Syntax', 'SELECT', false, '', true);
2557 $after = '</a>' . $after;
2559 $space_punct_listsep = ' ';
2560 $space_alpha_reserved_word = $html_line_break;
2561 break;
2562 case 'CALL':
2563 case 'DO':
2564 case 'HANDLER':
2565 if ($docu) {
2566 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2567 $after = '</a>' . $after;
2569 break;
2570 default:
2571 if ($close_docu_link && in_array($arr[$i]['data'], array('LIKE', 'NOT', 'IN', 'REGEXP', 'NULL'))) {
2572 $after .= '</a>';
2573 $close_docu_link = false;
2574 } else if ($docu && isset($PMA_SQPdata_functions_docs[$arr[$i]['data']])) {
2575 /* Handle multi word statements first */
2576 if (isset($typearr[4]) && $typearr[4] == 'alpha_reservedWord' && $typearr[3] == 'alpha_reservedWord' && isset($PMA_SQPdata_functions_docs[strtoupper($arr[$i]['data'] . '_' . $arr[$i + 1]['data'] . '_' . $arr[$i + 2]['data'])])) {
2577 $tempname = strtoupper($arr[$i]['data'] . '_' . $arr[$i + 1]['data'] . '_' . $arr[$i + 2]['data']);
2578 $before .= PMA_showMySQLDocu('functions', $PMA_SQPdata_functions_docs[$tempname]['link'], false, $PMA_SQPdata_functions_docs[$tempname]['anchor'], true);
2579 $close_docu_link = true;
2580 } else if (isset($typearr[3]) && $typearr[3] == 'alpha_reservedWord' && isset($PMA_SQPdata_functions_docs[strtoupper($arr[$i]['data'] . '_' . $arr[$i + 1]['data'])])) {
2581 $tempname = strtoupper($arr[$i]['data'] . '_' . $arr[$i + 1]['data']);
2582 $before .= PMA_showMySQLDocu('functions', $PMA_SQPdata_functions_docs[$tempname]['link'], false, $PMA_SQPdata_functions_docs[$tempname]['anchor'], true);
2583 $close_docu_link = true;
2584 } else {
2585 $before .= PMA_showMySQLDocu('functions', $PMA_SQPdata_functions_docs[$arr[$i]['data']]['link'], false, $PMA_SQPdata_functions_docs[$arr[$i]['data']]['anchor'], true);
2586 $after .= '</a>';
2589 break;
2590 } // end switch ($arr[$i]['data'])
2592 $after .= ' ';
2593 break;
2594 case 'digit_integer':
2595 case 'digit_float':
2596 case 'digit_hex':
2598 * @todo could there be other types preceding a digit?
2600 if ($typearr[1] == 'alpha_reservedWord') {
2601 $after .= ' ';
2603 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2604 $after .= ' ';
2606 if ($typearr[1] == 'alpha_columnAttrib') {
2607 $before .= ' ';
2609 break;
2610 case 'alpha_variable':
2611 $after = ' ';
2612 break;
2613 case 'quote_double':
2614 case 'quote_single':
2615 // workaround: for the query
2616 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2617 // the @ is incorrectly marked as alpha_variable
2618 // in the parser, and here, the '%' gets a blank before,
2619 // which is a syntax error
2620 if ($typearr[1] != 'punct_user' && $typearr[1] != 'alpha_bitfield_constant_introducer') {
2621 $before .= ' ';
2623 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2624 $after .= ' ';
2626 break;
2627 case 'quote_backtick':
2628 // here we check for punct_user to handle correctly
2629 // DEFINER = `username`@`%`
2630 // where @ is the punct_user and `%` is the quote_backtick
2631 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2632 $after .= ' ';
2634 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2635 $before .= ' ';
2637 break;
2638 default:
2639 break;
2640 } // end switch ($typearr[2])
2643 if ($typearr[3] != 'punct_qualifier') {
2644 $after .= ' ';
2646 $after .= "\n";
2648 $str .= $before;
2649 if ($mode=='color') {
2650 $str .= PMA_SQP_formatHTML_colorize($arr[$i]);
2651 } elseif ($mode == 'text') {
2652 $str .= htmlspecialchars($arr[$i]['data']);
2653 } else {
2654 $str .= $arr[$i]['data'];
2656 $str .= $after;
2657 } // end for
2658 // close unclosed indent levels
2659 while ($indent > 0) {
2660 $indent--;
2661 $str .= ($mode != 'query_only' ? '</div>' : ' ');
2663 /* End possibly unclosed documentation link */
2664 if ($close_docu_link) {
2665 $str .= '</a>';
2666 $close_docu_link = false;
2668 if ($mode!='query_only') {
2669 // close inner_sql span
2670 $str .= '</span>';
2672 if ($mode=='color') {
2673 // close syntax span
2674 $str .= '</span>';
2677 return $str;
2678 } // end of the "PMA_SQP_formatHtml()" function
2682 * Builds a CSS rule used for html formatted SQL queries
2684 * @param string The class name
2685 * @param string The property name
2686 * @param string The property value
2688 * @return string The CSS rule
2690 * @access public
2692 * @see PMA_SQP_buildCssData()
2694 function PMA_SQP_buildCssRule($classname, $property, $value)
2696 $str = '.' . $classname . ' {';
2697 if ($value != '') {
2698 $str .= $property . ': ' . $value . ';';
2700 $str .= '}' . "\n";
2702 return $str;
2703 } // end of the "PMA_SQP_buildCssRule()" function
2707 * Builds CSS rules used for html formatted SQL queries
2709 * @return string The CSS rules set
2711 * @access public
2713 * @global array The current PMA configuration
2715 * @see PMA_SQP_buildCssRule()
2717 function PMA_SQP_buildCssData()
2719 global $cfg;
2721 $css_string = '';
2722 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2723 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2726 for ($i = 0; $i < 8; $i++) {
2727 $css_string .= PMA_SQP_buildCssRule(
2728 'syntax_indent' . $i, 'margin-left',
2729 ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2732 return $css_string;
2733 } // end of the "PMA_SQP_buildCssData()" function
2735 if (! defined('PMA_MINIMUM_COMMON')) {
2737 * Gets SQL queries with no format
2739 * @param array The SQL queries list
2741 * @return string The SQL queries with no format
2743 * @access public
2745 function PMA_SQP_formatNone($arr)
2747 $formatted_sql = htmlspecialchars($arr['raw']);
2748 $formatted_sql = preg_replace(
2749 "@((\015\012)|(\015)|(\012)){3,}@",
2750 "\n\n",
2751 $formatted_sql);
2753 return $formatted_sql;
2754 } // end of the "PMA_SQP_formatNone()" function
2756 } // end if: minimal common.lib needed?