Translation update done using Pootle.
[phpmyadmin-themes.git] / libraries / sqlparser.lib.php
blobe5d2f6ee982f1bee04967f4702135e69d0129a87
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_charsets_count = 0;
52 $mysql_collations_flat = array();
53 $mysql_collations_count = 0;
56 if (!defined('DEBUG_TIMING')) {
57 /**
58 * currently we don't need the $pos (token position in query)
59 * for other purposes than LIMIT clause verification,
60 * so many calls to this function do not include the 4th parameter
62 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
64 $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos);
65 $arrsize++;
66 } // end of the "PMA_SQP_arrayAdd()" function
67 } else {
68 /**
69 * This is debug variant of above.
70 * @ignore
72 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
74 global $timer;
76 $t = $timer;
77 $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos, 'time' => $t);
78 $timer = microtime();
79 $arrsize++;
80 } // end of the "PMA_SQP_arrayAdd()" function
81 } // end if... else...
84 /**
85 * Reset the error variable for the SQL parser
87 * @access public
89 function PMA_SQP_resetError()
91 global $SQP_errorString;
92 $SQP_errorString = '';
93 unset($SQP_errorString);
96 /**
97 * Get the contents of the error variable for the SQL parser
99 * @return string Error string from SQL parser
101 * @access public
103 function PMA_SQP_getErrorString()
105 global $SQP_errorString;
106 return isset($SQP_errorString) ? $SQP_errorString : '';
110 * Check if the SQL parser hit an error
112 * @return boolean error state
114 * @access public
116 function PMA_SQP_isError()
118 global $SQP_errorString;
119 return isset($SQP_errorString) && !empty($SQP_errorString);
123 * Set an error message for the system
125 * @param string The error message
126 * @param string The failing SQL query
128 * @access private
129 * @scope SQL Parser internal
131 function PMA_SQP_throwError($message, $sql)
133 global $SQP_errorString;
134 $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"
135 . '<pre>' . "\n"
136 . 'ERROR: ' . $message . "\n"
137 . 'SQL: ' . htmlspecialchars($sql) . "\n"
138 . '</pre>' . "\n";
140 } // end of the "PMA_SQP_throwError()" function
144 * Do display the bug report
146 * @param string The error message
147 * @param string The failing SQL query
149 * @access public
151 function PMA_SQP_bug($message, $sql)
153 global $SQP_errorString;
154 $debugstr = 'ERROR: ' . $message . "\n";
155 $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";
156 $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ' . PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";
157 $debugstr .= 'PMA: ' . PMA_VERSION . "\n";
158 $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";
159 $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
160 $debugstr .= 'SQL: ' . htmlspecialchars($sql);
162 $encodedstr = $debugstr;
163 if (@function_exists('gzcompress')) {
164 $encodedstr = gzcompress($debugstr, 9);
166 $encodedstr = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));
169 $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:')
170 . '<br />' . "\n"
171 . '----' . __('BEGIN CUT') . '----' . '<br />' . "\n"
172 . $encodedstr . "\n"
173 . '----' . __('END CUT') . '----' . '<br />' . "\n";
175 $SQP_errorString .= '----' . __('BEGIN RAW') . '----<br />' . "\n"
176 . '<pre>' . "\n"
177 . $debugstr
178 . '</pre>' . "\n"
179 . '----' . __('END RAW') . '----<br />' . "\n";
181 } // end of the "PMA_SQP_bug()" function
185 * Parses the SQL queries
187 * @param string The SQL query list
189 * @return mixed Most of times, nothing...
191 * @global array The current PMA configuration
192 * @global array MySQL column attributes
193 * @global array MySQL reserved words
194 * @global array MySQL column types
195 * @global array MySQL function names
196 * @global integer MySQL column attributes count
197 * @global integer MySQL reserved words count
198 * @global integer MySQL column types count
199 * @global integer MySQL function names count
200 * @global array List of available character sets
201 * @global array List of available collations
202 * @global integer Character sets count
203 * @global integer Collations count
205 * @access public
207 function PMA_SQP_parse($sql)
209 global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,
210 $PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;
211 global $mysql_charsets, $mysql_collations_flat, $mysql_charsets_count, $mysql_collations_count;
212 global $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt;
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 $sql_array = array();
224 $sql_array['raw'] = $sql;
225 $count1 = 0;
226 $count2 = 0;
227 $punct_queryend = ';';
228 $punct_qualifier = '.';
229 $punct_listsep = ',';
230 $punct_level_plus = '(';
231 $punct_level_minus = ')';
232 $punct_user = '@';
233 $digit_floatdecimal = '.';
234 $digit_hexset = 'x';
235 $bracket_list = '()[]{}';
236 $allpunct_list = '-,;:!?/.^~\*&%+<=>|';
237 $allpunct_list_pair = array (
238 0 => '!=',
239 1 => '&&',
240 2 => ':=',
241 3 => '<<',
242 4 => '<=',
243 5 => '<=>',
244 6 => '<>',
245 7 => '>=',
246 8 => '>>',
247 9 => '||'
249 $allpunct_list_pair_size = 10; //count($allpunct_list_pair);
250 $quote_list = '\'"`';
251 $arraysize = 0;
253 $previous_was_space = false;
254 $this_was_space = false;
255 $previous_was_bracket = false;
256 $this_was_bracket = false;
257 $previous_was_punct = false;
258 $this_was_punct = false;
259 $previous_was_listsep = false;
260 $this_was_listsep = false;
261 $previous_was_quote = false;
262 $this_was_quote = false;
264 while ($count2 < $len) {
265 $c = $GLOBALS['PMA_substr']($sql, $count2, 1);
266 $count1 = $count2;
268 $previous_was_space = $this_was_space;
269 $this_was_space = false;
270 $previous_was_bracket = $this_was_bracket;
271 $this_was_bracket = false;
272 $previous_was_punct = $this_was_punct;
273 $this_was_punct = false;
274 $previous_was_listsep = $this_was_listsep;
275 $this_was_listsep = false;
276 $previous_was_quote = $this_was_quote;
277 $this_was_quote = false;
279 if (($c == "\n")) {
280 $this_was_space = true;
281 $count2++;
282 PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
283 continue;
286 // Checks for white space
287 if ($GLOBALS['PMA_STR_isSpace']($c)) {
288 $this_was_space = true;
289 $count2++;
290 continue;
293 // Checks for comment lines.
294 // MySQL style #
295 // C style /* */
296 // ANSI style --
297 if (($c == '#')
298 || (($count2 + 1 < $len) && ($c == '/') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '*'))
299 || (($count2 + 2 == $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-'))
300 || (($count2 + 2 < $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-') && (($GLOBALS['PMA_substr']($sql, $count2 + 2, 1) <= ' ')))) {
301 $count2++;
302 $pos = 0;
303 $type = 'bad';
304 switch ($c) {
305 case '#':
306 $type = 'mysql';
307 case '-':
308 $type = 'ansi';
309 $pos = $GLOBALS['PMA_strpos']($sql, "\n", $count2);
310 break;
311 case '/':
312 $type = 'c';
313 $pos = $GLOBALS['PMA_strpos']($sql, '*/', $count2);
314 $pos += 2;
315 break;
316 default:
317 break;
318 } // end switch
319 $count2 = ($pos < $count2) ? $len : $pos;
320 $str = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
321 PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
322 continue;
323 } // end if
325 // Checks for something inside quotation marks
326 if ($GLOBALS['PMA_strpos']($quote_list, $c) !== false) {
327 $startquotepos = $count2;
328 $quotetype = $c;
329 $count2++;
330 $escaped = FALSE;
331 $escaped_escaped = FALSE;
332 $pos = $count2;
333 $oldpos = 0;
334 do {
335 $oldpos = $pos;
336 $pos = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos + 1) - 1;
337 // ($pos === FALSE)
338 if ($pos < 0) {
339 if ($c == '`') {
341 * Behave same as MySQL and accept end of query as end of backtick.
342 * I know this is sick, but MySQL behaves like this:
344 * SELECT * FROM `table
346 * is treated like
348 * SELECT * FROM `table`
350 $pos_quote_separator = $GLOBALS['PMA_strpos'](' ' . $sql, $GLOBALS['sql_delimiter'], $oldpos + 1) - 1;
351 if ($pos_quote_separator < 0) {
352 $len += 1;
353 $sql .= '`';
354 $sql_array['raw'] .= '`';
355 $pos = $len;
356 } else {
357 $len += 1;
358 $sql = $GLOBALS['PMA_substr']($sql, 0, $pos_quote_separator) . '`' . $GLOBALS['PMA_substr']($sql, $pos_quote_separator);
359 $sql_array['raw'] = $sql;
360 $pos = $pos_quote_separator;
362 if (class_exists('PMA_Message')) {
363 PMA_Message::warning(__('Automatically appended backtick to the end of query!'))->display();
365 } else {
366 $debugstr = __('Unclosed quote') . ' @ ' . $startquotepos. "\n"
367 . 'STR: ' . htmlspecialchars($quotetype);
368 PMA_SQP_throwError($debugstr, $sql);
369 return $sql_array;
373 // If the quote is the first character, it can't be
374 // escaped, so don't do the rest of the code
375 if ($pos == 0) {
376 break;
379 // Checks for MySQL escaping using a \
380 // And checks for ANSI escaping using the $quotetype character
381 if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {
382 $pos ++;
383 continue;
384 } elseif (($pos + 1 < $len) && ($GLOBALS['PMA_substr']($sql, $pos, 1) == $quotetype) && ($GLOBALS['PMA_substr']($sql, $pos + 1, 1) == $quotetype)) {
385 $pos = $pos + 2;
386 continue;
387 } else {
388 break;
390 } while ($len > $pos); // end do
392 $count2 = $pos;
393 $count2++;
394 $type = 'quote_';
395 switch ($quotetype) {
396 case '\'':
397 $type .= 'single';
398 $this_was_quote = true;
399 break;
400 case '"':
401 $type .= 'double';
402 $this_was_quote = true;
403 break;
404 case '`':
405 $type .= 'backtick';
406 $this_was_quote = true;
407 break;
408 default:
409 break;
410 } // end switch
411 $data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
412 PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
413 continue;
416 // Checks for brackets
417 if ($GLOBALS['PMA_strpos']($bracket_list, $c) !== false) {
418 // All bracket tokens are only one item long
419 $this_was_bracket = true;
420 $count2++;
421 $type_type = '';
422 if ($GLOBALS['PMA_strpos']('([{', $c) !== false) {
423 $type_type = 'open';
424 } else {
425 $type_type = 'close';
428 $type_style = '';
429 if ($GLOBALS['PMA_strpos']('()', $c) !== false) {
430 $type_style = 'round';
431 } elseif ($GLOBALS['PMA_strpos']('[]', $c) !== false) {
432 $type_style = 'square';
433 } else {
434 $type_style = 'curly';
437 $type = 'punct_bracket_' . $type_type . '_' . $type_style;
438 PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
439 continue;
442 /* DEBUG
443 echo '<pre>1';
444 var_dump(PMA_STR_isSqlIdentifier($c, false));
445 var_dump($c == '@');
446 var_dump($c == '.');
447 var_dump(PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1)));
448 var_dump($previous_was_space);
449 var_dump($previous_was_bracket);
450 var_dump($previous_was_listsep);
451 echo '</pre>';
454 // Checks for identifier (alpha or numeric)
455 if (PMA_STR_isSqlIdentifier($c, false)
456 || $c == '@'
457 || ($c == '.'
458 && $GLOBALS['PMA_STR_isDigit']($GLOBALS['PMA_substr']($sql, $count2 + 1, 1))
459 && ($previous_was_space || $previous_was_bracket || $previous_was_listsep))) {
461 /* DEBUG
462 echo PMA_substr($sql, $count2);
463 echo '<hr />';
466 $count2++;
469 * @todo a @ can also be present in expressions like
470 * FROM 'user'@'%' or TO 'user'@'%'
471 * in this case, the @ is wrongly marked as alpha_variable
473 $is_identifier = $previous_was_punct;
474 $is_sql_variable = $c == '@' && ! $previous_was_quote;
475 $is_user = $c == '@' && $previous_was_quote;
476 $is_digit = !$is_identifier && !$is_sql_variable && $GLOBALS['PMA_STR_isDigit']($c);
477 $is_hex_digit = $is_digit && $c == '0' && $count2 < $len && $GLOBALS['PMA_substr']($sql, $count2, 1) == 'x';
478 $is_float_digit = $c == '.';
479 $is_float_digit_exponent = FALSE;
481 /* DEBUG
482 echo '<pre>2';
483 var_dump($is_identifier);
484 var_dump($is_sql_variable);
485 var_dump($is_digit);
486 var_dump($is_float_digit);
487 echo '</pre>';
490 // Nijel: Fast skip is especially needed for huge BLOB data, requires PHP at least 4.3.0:
491 if ($is_hex_digit) {
492 $count2++;
493 $pos = strspn($sql, '0123456789abcdefABCDEF', $count2);
494 if ($pos > $count2) {
495 $count2 = $pos;
497 unset($pos);
498 } elseif ($is_digit) {
499 $pos = strspn($sql, '0123456789', $count2);
500 if ($pos > $count2) {
501 $count2 = $pos;
503 unset($pos);
506 while (($count2 < $len) && PMA_STR_isSqlIdentifier($GLOBALS['PMA_substr']($sql, $count2, 1), ($is_sql_variable || $is_digit))) {
507 $c2 = $GLOBALS['PMA_substr']($sql, $count2, 1);
508 if ($is_sql_variable && ($c2 == '.')) {
509 $count2++;
510 continue;
512 if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
513 $count2++;
514 if (!$is_float_digit) {
515 $is_float_digit = TRUE;
516 continue;
517 } else {
518 $debugstr = __('Invalid Identifer') . ' @ ' . ($count1+1) . "\n"
519 . 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));
520 PMA_SQP_throwError($debugstr, $sql);
521 return $sql_array;
524 if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {
525 if (!$is_float_digit_exponent) {
526 $is_float_digit_exponent = TRUE;
527 $is_float_digit = TRUE;
528 $count2++;
529 continue;
530 } else {
531 $is_digit = FALSE;
532 $is_float_digit = FALSE;
535 if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && $GLOBALS['PMA_STR_isDigit']($c2))) {
536 $count2++;
537 continue;
538 } else {
539 $is_digit = FALSE;
540 $is_hex_digit = FALSE;
543 $count2++;
544 } // end while
546 $l = $count2 - $count1;
547 $str = $GLOBALS['PMA_substr']($sql, $count1, $l);
549 $type = '';
550 if ($is_digit || $is_float_digit || $is_hex_digit) {
551 $type = 'digit';
552 if ($is_float_digit) {
553 $type .= '_float';
554 } elseif ($is_hex_digit) {
555 $type .= '_hex';
556 } else {
557 $type .= '_integer';
559 } elseif ($is_user) {
560 $type = 'punct_user';
561 } elseif ($is_sql_variable != FALSE) {
562 $type = 'alpha_variable';
563 } else {
564 $type = 'alpha';
565 } // end if... else....
566 PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize, $count2);
568 continue;
571 // Checks for punct
572 if ($GLOBALS['PMA_strpos']($allpunct_list, $c) !== false) {
573 while (($count2 < $len) && $GLOBALS['PMA_strpos']($allpunct_list, $GLOBALS['PMA_substr']($sql, $count2, 1)) !== false) {
574 $count2++;
576 $l = $count2 - $count1;
577 if ($l == 1) {
578 $punct_data = $c;
579 } else {
580 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $l);
583 // Special case, sometimes, althought two characters are
584 // adjectent directly, they ACTUALLY need to be seperate
585 /* DEBUG
586 echo '<pre>';
587 var_dump($l);
588 var_dump($punct_data);
589 echo '</pre>';
592 if ($l == 1) {
593 $t_suffix = '';
594 switch ($punct_data) {
595 case $punct_queryend:
596 $t_suffix = '_queryend';
597 break;
598 case $punct_qualifier:
599 $t_suffix = '_qualifier';
600 $this_was_punct = true;
601 break;
602 case $punct_listsep:
603 $this_was_listsep = true;
604 $t_suffix = '_listsep';
605 break;
606 default:
607 break;
609 PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
610 } elseif ($punct_data == $GLOBALS['sql_delimiter'] || PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {
611 // Ok, we have one of the valid combined punct expressions
612 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
613 } else {
614 // Bad luck, lets split it up more
615 $first = $punct_data[0];
616 $first2 = $punct_data[0] . $punct_data[1];
617 $last2 = $punct_data[$l - 2] . $punct_data[$l - 1];
618 $last = $punct_data[$l - 1];
619 if (($first == ',') || ($first == ';') || ($first == '.') || ($first == '*')) {
620 $count2 = $count1 + 1;
621 $punct_data = $first;
622 } elseif (($last2 == '/*') || (($last2 == '--') && ($count2 == $len || $GLOBALS['PMA_substr']($sql, $count2, 1) <= ' '))) {
623 $count2 -= 2;
624 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
625 } elseif (($last == '-') || ($last == '+') || ($last == '!')) {
626 $count2--;
627 $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
629 * @todo for negation operator, split in 2 tokens ?
630 * "select x&~1 from t"
631 * becomes "select x & ~ 1 from t" ?
634 } elseif ($last != '~') {
635 $debugstr = __('Unknown Punctuation String') . ' @ ' . ($count1+1) . "\n"
636 . 'STR: ' . htmlspecialchars($punct_data);
637 PMA_SQP_throwError($debugstr, $sql);
638 return $sql_array;
640 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
641 continue;
642 } // end if... elseif... else
643 continue;
646 // DEBUG
647 $count2++;
649 $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len . "\n"
650 . 'STR: ' . $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1) . "\n";
651 PMA_SQP_bug($debugstr, $sql);
652 return $sql_array;
654 } // end while ($count2 < $len)
657 echo '<pre>';
658 print_r($sql_array);
659 echo '</pre>';
662 if ($arraysize > 0) {
663 $t_next = $sql_array[0]['type'];
664 $t_prev = '';
665 $t_bef_prev = '';
666 $t_cur = '';
667 $d_next = $sql_array[0]['data'];
668 $d_prev = '';
669 $d_bef_prev = '';
670 $d_cur = '';
671 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
672 $d_prev_upper = '';
673 $d_bef_prev_upper = '';
674 $d_cur_upper = '';
677 for ($i = 0; $i < $arraysize; $i++) {
678 $t_bef_prev = $t_prev;
679 $t_prev = $t_cur;
680 $t_cur = $t_next;
681 $d_bef_prev = $d_prev;
682 $d_prev = $d_cur;
683 $d_cur = $d_next;
684 $d_bef_prev_upper = $d_prev_upper;
685 $d_prev_upper = $d_cur_upper;
686 $d_cur_upper = $d_next_upper;
687 if (($i + 1) < $arraysize) {
688 $t_next = $sql_array[$i + 1]['type'];
689 $d_next = $sql_array[$i + 1]['data'];
690 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
691 } else {
692 $t_next = '';
693 $d_next = '';
694 $d_next_upper = '';
697 //DEBUG echo "[prev: <strong>".$d_prev."</strong> ".$t_prev."][cur: <strong>".$d_cur."</strong> ".$t_cur."][next: <strong>".$d_next."</strong> ".$t_next."]<br />";
699 if ($t_cur == 'alpha') {
700 $t_suffix = '_identifier';
701 // for example: `thebit` bit(8) NOT NULL DEFAULT b'0'
702 if ($t_prev == 'alpha' && $d_prev == 'DEFAULT' && $d_cur == 'b' && $t_next == 'quote_single') {
703 $t_suffix = '_bitfield_constant_introducer';
704 } elseif (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
705 $t_suffix = '_identifier';
706 } elseif (($t_next == 'punct_bracket_open_round')
707 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
709 * @todo 2005-10-16: in the case of a CREATE TABLE containing
710 * a TIMESTAMP, since TIMESTAMP() is also a function, it's
711 * found here and the token is wrongly marked as alpha_functionName.
712 * But we compensate for this when analysing for timestamp_not_null
713 * later in this script.
715 * Same applies to CHAR vs. CHAR() function.
717 $t_suffix = '_functionName';
718 /* There are functions which might be as well column types */
719 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
720 $t_suffix = '_columnType';
723 * Temporary fix for BUG #621357 and #2027720
725 * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
727 if (($d_cur_upper == 'SET' || $d_cur_upper == 'BINARY') && $t_next != 'punct_bracket_open_round') {
728 $t_suffix = '_reservedWord';
730 //END OF TEMPORARY FIX
732 // CHARACTER is a synonym for CHAR, but can also be meant as
733 // CHARACTER SET. In this case, we have a reserved word.
734 if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
735 $t_suffix = '_reservedWord';
738 // experimental
739 // current is a column type, so previous must not be
740 // a reserved word but an identifier
741 // CREATE TABLE SG_Persons (first varchar(64))
743 //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
744 // $sql_array[$i-1]['type'] = 'alpha_identifier';
747 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
748 $t_suffix = '_reservedWord';
749 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
750 $t_suffix = '_columnAttrib';
751 // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
752 // it should be regarded as a reserved word.
753 if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
754 $t_suffix = '_reservedWord';
757 if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
758 $t_suffix = '_reservedWord';
760 // Binary as character set
761 if ($d_cur_upper == 'BINARY' && (
762 ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
763 || ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
764 || ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
765 || $d_prev_upper == 'CHARSET'
766 ) && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {
767 $t_suffix = '_charset';
769 } elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)
770 || PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)
771 || ($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {
772 $t_suffix = '_charset';
773 } else {
774 // Do nothing
776 // check if present in the list of forbidden words
777 if ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {
778 $sql_array[$i]['forbidden'] = TRUE;
779 } else {
780 $sql_array[$i]['forbidden'] = FALSE;
782 $sql_array[$i]['type'] .= $t_suffix;
784 } // end for
786 // Stores the size of the array inside the array, as count() is a slow
787 // operation.
788 $sql_array['len'] = $arraysize;
790 // DEBUG echo 'After parsing<pre>'; print_r($sql_array); echo '</pre>';
791 // Sends the data back
792 return $sql_array;
793 } // end of the "PMA_SQP_parse()" function
796 * Checks for token types being what we want...
798 * @param string String of type that we have
799 * @param string String of type that we want
801 * @return boolean result of check
803 * @access private
805 function PMA_SQP_typeCheck($toCheck, $whatWeWant)
807 $typeSeperator = '_';
808 if (strcmp($whatWeWant, $toCheck) == 0) {
809 return TRUE;
810 } else {
811 if (strpos($whatWeWant, $typeSeperator) === FALSE) {
812 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
813 } else {
814 return FALSE;
821 * Analyzes SQL queries
823 * @param array The SQL queries
825 * @return array The analyzed SQL queries
827 * @access public
829 function PMA_SQP_analyze($arr)
831 if ($arr == array() || !isset($arr['len'])) {
832 return array();
834 $result = array();
835 $size = $arr['len'];
836 $subresult = array(
837 'querytype' => '',
838 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
839 'position_of_first_select' => '', // the array index
840 'from_clause'=> '',
841 'group_by_clause'=> '',
842 'order_by_clause'=> '',
843 'having_clause' => '',
844 'limit_clause' => '',
845 'where_clause' => '',
846 'where_clause_identifiers' => array(),
847 'unsorted_query' => '',
848 'queryflags' => array(),
849 'select_expr' => array(),
850 'table_ref' => array(),
851 'foreign_keys' => array(),
852 'create_table_fields' => array()
854 $subresult_empty = $subresult;
855 $seek_queryend = FALSE;
856 $seen_end_of_table_ref = FALSE;
857 $number_of_brackets_in_extract = 0;
858 $number_of_brackets_in_group_concat = 0;
860 $number_of_brackets = 0;
861 $in_subquery = false;
862 $seen_subquery = false;
863 $seen_from = false;
865 // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
866 // we must not use CURDATE as a table_ref
867 // so we track whether we are in the EXTRACT()
868 $in_extract = FALSE;
870 // for GROUP_CONCAT(...)
871 $in_group_concat = FALSE;
873 /* Description of analyzer results
875 * db, table, column, alias
876 * ------------------------
878 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
880 * The SELECT syntax (simplified) is
882 * SELECT
883 * select_expression,...
884 * [FROM [table_references]
887 * ['select_expr'] is filled with each expression, the key represents the
888 * expression position in the list (0-based) (so we don't lose track of
889 * multiple occurences of the same column).
891 * ['table_ref'] is filled with each table ref, same thing for the key.
893 * I create all sub-values empty, even if they are
894 * not present (for example no select_expression alias).
896 * There is a debug section at the end of loop #1, if you want to
897 * see the exact contents of select_expr and table_ref
899 * queryflags
900 * ----------
902 * In $subresult, array 'queryflags' is filled, according to what we
903 * find in the query.
905 * Currently, those are generated:
907 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
908 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
909 * ['queryflags']['distinct'] = 1; for a DISTINCT
910 * ['queryflags']['union'] = 1; for a UNION
911 * ['queryflags']['join'] = 1; for a JOIN
912 * ['queryflags']['offset'] = 1; for the presence of OFFSET
913 * ['queryflags']['procedure'] = 1; for the presence of PROCEDURE
915 * query clauses
916 * -------------
918 * The select is splitted in those clauses:
919 * ['select_expr_clause']
920 * ['from_clause']
921 * ['group_by_clause']
922 * ['order_by_clause']
923 * ['having_clause']
924 * ['limit_clause']
925 * ['where_clause']
927 * The identifiers of the WHERE clause are put into the array
928 * ['where_clause_identifier']
930 * For a SELECT, the whole query without the ORDER BY clause is put into
931 * ['unsorted_query']
933 * foreign keys
934 * ------------
935 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
936 * analyzed and ['foreign_keys'] is an array filled with
937 * the constraint name, the index list,
938 * the REFERENCES table name and REFERENCES index list,
939 * and ON UPDATE | ON DELETE clauses
941 * position_of_first_select
942 * ------------------------
944 * The array index of the first SELECT we find. Will be used to
945 * insert a SQL_CALC_FOUND_ROWS.
947 * create_table_fields
948 * -------------------
950 * Used to detect the DEFAULT CURRENT_TIMESTAMP and
951 * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
952 * Also used to store the default value of the field.
953 * An array, each element is the identifier name.
954 * Note that for now, the timestamp_not_null element is created
955 * even for non-TIMESTAMP fields.
957 * Sub-elements: ['type'] which contains the column type
958 * optional (currently they are never false but can be absent):
959 * ['default_current_timestamp'] boolean
960 * ['on_update_current_timestamp'] boolean
961 * ['timestamp_not_null'] boolean
963 * section_before_limit, section_after_limit
964 * -----------------------------------------
966 * Marks the point of the query where we can insert a LIMIT clause;
967 * so the section_before_limit will contain the left part before
968 * a possible LIMIT clause
971 * End of description of analyzer results
974 // must be sorted
975 // TODO: current logic checks for only one word, so I put only the
976 // first word of the reserved expressions that end a table ref;
977 // maybe this is not ok (the first word might mean something else)
978 // $words_ending_table_ref = array(
979 // 'FOR UPDATE',
980 // 'GROUP BY',
981 // 'HAVING',
982 // 'LIMIT',
983 // 'LOCK IN SHARE MODE',
984 // 'ORDER BY',
985 // 'PROCEDURE',
986 // 'UNION',
987 // 'WHERE'
988 // );
989 $words_ending_table_ref = array(
990 'FOR',
991 'GROUP',
992 'HAVING',
993 'LIMIT',
994 'LOCK',
995 'ORDER',
996 'PROCEDURE',
997 'UNION',
998 'WHERE'
1000 $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
1002 $words_ending_clauses = array(
1003 'FOR',
1004 'LIMIT',
1005 'LOCK',
1006 'PROCEDURE',
1007 'UNION'
1009 $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
1014 // must be sorted
1015 $supported_query_types = array(
1016 'SELECT'
1018 // Support for these additional query types will come later on.
1019 'DELETE',
1020 'INSERT',
1021 'REPLACE',
1022 'TRUNCATE',
1023 'UPDATE'
1024 'EXPLAIN',
1025 'DESCRIBE',
1026 'SHOW',
1027 'CREATE',
1028 'SET',
1029 'ALTER'
1032 $supported_query_types_cnt = count($supported_query_types);
1034 // loop #1 for each token: select_expr, table_ref for SELECT
1036 for ($i = 0; $i < $size; $i++) {
1037 //DEBUG echo "Loop1 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1039 // High speed seek for locating the end of the current query
1040 if ($seek_queryend == TRUE) {
1041 if ($arr[$i]['type'] == 'punct_queryend') {
1042 $seek_queryend = FALSE;
1043 } else {
1044 continue;
1045 } // end if (type == punct_queryend)
1046 } // end if ($seek_queryend)
1049 * Note: do not split if this is a punct_queryend for the first and only query
1050 * @todo when we find a UNION, should we split in another subresult?
1052 if ($arr[$i]['type'] == 'punct_queryend' && ($i + 1 != $size)) {
1053 $result[] = $subresult;
1054 $subresult = $subresult_empty;
1055 continue;
1056 } // end if (type == punct_queryend)
1058 // ==============================================================
1059 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1060 $number_of_brackets++;
1061 if ($in_extract) {
1062 $number_of_brackets_in_extract++;
1064 if ($in_group_concat) {
1065 $number_of_brackets_in_group_concat++;
1068 // ==============================================================
1069 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1070 $number_of_brackets--;
1071 if ($number_of_brackets == 0) {
1072 $in_subquery = false;
1074 if ($in_extract) {
1075 $number_of_brackets_in_extract--;
1076 if ($number_of_brackets_in_extract == 0) {
1077 $in_extract = FALSE;
1080 if ($in_group_concat) {
1081 $number_of_brackets_in_group_concat--;
1082 if ($number_of_brackets_in_group_concat == 0) {
1083 $in_group_concat = FALSE;
1088 if ($in_subquery) {
1090 * skip the subquery to avoid setting
1091 * select_expr or table_ref with the contents
1092 * of this subquery; this is to avoid a bug when
1093 * trying to edit the results of
1094 * select * from child where not exists (select id from
1095 * parent where child.parent_id = parent.id);
1097 continue;
1099 // ==============================================================
1100 if ($arr[$i]['type'] == 'alpha_functionName') {
1101 $upper_data = strtoupper($arr[$i]['data']);
1102 if ($upper_data =='EXTRACT') {
1103 $in_extract = TRUE;
1104 $number_of_brackets_in_extract = 0;
1106 if ($upper_data =='GROUP_CONCAT') {
1107 $in_group_concat = TRUE;
1108 $number_of_brackets_in_group_concat = 0;
1112 // ==============================================================
1113 if ($arr[$i]['type'] == 'alpha_reservedWord'
1114 // && $arr[$i]['forbidden'] == FALSE) {
1116 // We don't know what type of query yet, so run this
1117 if ($subresult['querytype'] == '') {
1118 $subresult['querytype'] = strtoupper($arr[$i]['data']);
1119 } // end if (querytype was empty)
1121 // Check if we support this type of query
1122 if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
1123 // Skip ahead to the next one if we don't
1124 $seek_queryend = TRUE;
1125 continue;
1126 } // end if (query not supported)
1128 // upper once
1129 $upper_data = strtoupper($arr[$i]['data']);
1131 * @todo reset for each query?
1134 if ($upper_data == 'SELECT') {
1135 if ($number_of_brackets > 0) {
1136 $in_subquery = true;
1137 $seen_subquery = true;
1138 // this is a subquery so do not analyze inside it
1139 continue;
1141 $seen_from = FALSE;
1142 $previous_was_identifier = FALSE;
1143 $current_select_expr = -1;
1144 $seen_end_of_table_ref = FALSE;
1145 } // end if (data == SELECT)
1147 if ($upper_data =='FROM' && !$in_extract) {
1148 $current_table_ref = -1;
1149 $seen_from = TRUE;
1150 $previous_was_identifier = FALSE;
1151 $save_table_ref = TRUE;
1152 } // end if (data == FROM)
1154 // here, do not 'continue' the loop, as we have more work for
1155 // reserved words below
1156 } // end if (type == alpha_reservedWord)
1158 // ==============================
1159 if ($arr[$i]['type'] == 'quote_backtick'
1160 || $arr[$i]['type'] == 'quote_double'
1161 || $arr[$i]['type'] == 'quote_single'
1162 || $arr[$i]['type'] == 'alpha_identifier'
1163 || ($arr[$i]['type'] == 'alpha_reservedWord'
1164 && $arr[$i]['forbidden'] == FALSE)) {
1166 switch ($arr[$i]['type']) {
1167 case 'alpha_identifier':
1168 case 'alpha_reservedWord':
1170 * this is not a real reservedWord, because it's not
1171 * present in the list of forbidden words, for example
1172 * "storage" which can be used as an identifier
1174 * @todo avoid the pretty printing in color in this case
1176 $identifier = $arr[$i]['data'];
1177 break;
1179 case 'quote_backtick':
1180 case 'quote_double':
1181 case 'quote_single':
1182 $identifier = PMA_unQuote($arr[$i]['data']);
1183 break;
1184 } // end switch
1186 if ($subresult['querytype'] == 'SELECT'
1187 && ! $in_group_concat
1188 && ! ($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
1189 if (!$seen_from) {
1190 if ($previous_was_identifier && isset($chain)) {
1191 // found alias for this select_expr, save it
1192 // but only if we got something in $chain
1193 // (for example, SELECT COUNT(*) AS cnt
1194 // puts nothing in $chain, so we avoid
1195 // setting the alias)
1196 $alias_for_select_expr = $identifier;
1197 } else {
1198 $chain[] = $identifier;
1199 $previous_was_identifier = TRUE;
1201 } // end if !$previous_was_identifier
1202 } else {
1203 // ($seen_from)
1204 if ($save_table_ref && !$seen_end_of_table_ref) {
1205 if ($previous_was_identifier) {
1206 // found alias for table ref
1207 // save it for later
1208 $alias_for_table_ref = $identifier;
1209 } else {
1210 $chain[] = $identifier;
1211 $previous_was_identifier = TRUE;
1213 } // end if ($previous_was_identifier)
1214 } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1215 } // end if (!$seen_from)
1216 } // end if (querytype SELECT)
1217 } // end if (quote_backtick or double quote or alpha_identifier)
1219 // ===================================
1220 if ($arr[$i]['type'] == 'punct_qualifier') {
1221 // to be able to detect an identifier following another
1222 $previous_was_identifier = FALSE;
1223 continue;
1224 } // end if (punct_qualifier)
1227 * @todo check if 3 identifiers following one another -> error
1230 // s a v e a s e l e c t e x p r
1231 // finding a list separator or FROM
1232 // means that we must save the current chain of identifiers
1233 // into a select expression
1235 // for now, we only save a select expression if it contains
1236 // at least one identifier, as we are interested in checking
1237 // the columns and table names, so in "select * from persons",
1238 // the "*" is not saved
1240 if (isset($chain) && !$seen_end_of_table_ref
1241 && ((!$seen_from && $arr[$i]['type'] == 'punct_listsep')
1242 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM'))) {
1243 $size_chain = count($chain);
1244 $current_select_expr++;
1245 $subresult['select_expr'][$current_select_expr] = array(
1246 'expr' => '',
1247 'alias' => '',
1248 'db' => '',
1249 'table_name' => '',
1250 'table_true_name' => '',
1251 'column' => ''
1254 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1255 // we had found an alias for this select expression
1256 $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1257 unset($alias_for_select_expr);
1259 // there is at least a column
1260 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1261 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1263 // maybe a table
1264 if ($size_chain > 1) {
1265 $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1266 // we assume for now that this is also the true name
1267 $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1268 $subresult['select_expr'][$current_select_expr]['expr']
1269 = $subresult['select_expr'][$current_select_expr]['table_name']
1270 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1271 } // end if ($size_chain > 1)
1273 // maybe a db
1274 if ($size_chain > 2) {
1275 $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1276 $subresult['select_expr'][$current_select_expr]['expr']
1277 = $subresult['select_expr'][$current_select_expr]['db']
1278 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1279 } // end if ($size_chain > 2)
1280 unset($chain);
1283 * @todo explain this:
1285 if (($arr[$i]['type'] == 'alpha_reservedWord')
1286 && ($upper_data != 'FROM')) {
1287 $previous_was_identifier = TRUE;
1290 } // end if (save a select expr)
1293 //======================================
1294 // s a v e a t a b l e r e f
1295 //======================================
1297 // maybe we just saw the end of table refs
1298 // but the last table ref has to be saved
1299 // or we are at the last token
1300 // or we just got a reserved word
1302 * @todo there could be another query after this one
1305 if (isset($chain) && $seen_from && $save_table_ref
1306 && ($arr[$i]['type'] == 'punct_listsep'
1307 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1308 || $seen_end_of_table_ref
1309 || $i==$size-1)) {
1311 $size_chain = count($chain);
1312 $current_table_ref++;
1313 $subresult['table_ref'][$current_table_ref] = array(
1314 'expr' => '',
1315 'db' => '',
1316 'table_name' => '',
1317 'table_alias' => '',
1318 'table_true_name' => ''
1320 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1321 $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1322 unset($alias_for_table_ref);
1324 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1325 // we assume for now that this is also the true name
1326 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1327 $subresult['table_ref'][$current_table_ref]['expr']
1328 = $subresult['table_ref'][$current_table_ref]['table_name'];
1329 // maybe a db
1330 if ($size_chain > 1) {
1331 $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1332 $subresult['table_ref'][$current_table_ref]['expr']
1333 = $subresult['table_ref'][$current_table_ref]['db']
1334 . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1335 } // end if ($size_chain > 1)
1337 // add the table alias into the whole expression
1338 $subresult['table_ref'][$current_table_ref]['expr']
1339 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1341 unset($chain);
1342 $previous_was_identifier = TRUE;
1343 //continue;
1345 } // end if (save a table ref)
1348 // when we have found all table refs,
1349 // for each table_ref alias, put the true name of the table
1350 // in the corresponding select expressions
1352 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
1353 for ($tr=0; $tr <= $current_table_ref; $tr++) {
1354 $alias = $subresult['table_ref'][$tr]['table_alias'];
1355 $truename = $subresult['table_ref'][$tr]['table_true_name'];
1356 for ($se=0; $se <= $current_select_expr; $se++) {
1357 if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1358 == $alias) {
1359 $subresult['select_expr'][$se]['table_true_name']
1360 = $truename;
1361 } // end if (found the alias)
1362 } // end for (select expressions)
1364 } // end for (table refs)
1365 } // end if (set the true names)
1368 // e n d i n g l o o p #1
1369 // set the $previous_was_identifier to FALSE if the current
1370 // token is not an identifier
1371 if (($arr[$i]['type'] != 'alpha_identifier')
1372 && ($arr[$i]['type'] != 'quote_double')
1373 && ($arr[$i]['type'] != 'quote_single')
1374 && ($arr[$i]['type'] != 'quote_backtick')) {
1375 $previous_was_identifier = FALSE;
1376 } // end if
1378 // however, if we are on AS, we must keep the $previous_was_identifier
1379 if (($arr[$i]['type'] == 'alpha_reservedWord')
1380 && ($upper_data == 'AS')) {
1381 $previous_was_identifier = TRUE;
1384 if (($arr[$i]['type'] == 'alpha_reservedWord')
1385 && ($upper_data =='ON' || $upper_data =='USING')) {
1386 $save_table_ref = FALSE;
1387 } // end if (data == ON)
1389 if (($arr[$i]['type'] == 'alpha_reservedWord')
1390 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1391 $save_table_ref = TRUE;
1392 } // end if (data == JOIN)
1395 * no need to check the end of table ref if we already did
1397 * @todo maybe add "&& $seen_from"
1399 if (!$seen_end_of_table_ref) {
1400 // if this is the last token, it implies that we have
1401 // seen the end of table references
1402 // Check for the end of table references
1404 // Note: if we are analyzing a GROUP_CONCAT clause,
1405 // we might find a word that seems to indicate that
1406 // we have found the end of table refs (like ORDER)
1407 // but it's a modifier of the GROUP_CONCAT so
1408 // it's not the real end of table refs
1409 if (($i == $size-1)
1410 || ($arr[$i]['type'] == 'alpha_reservedWord'
1411 && !$in_group_concat
1412 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1413 $seen_end_of_table_ref = TRUE;
1414 // to be able to save the last table ref, but do not
1415 // set it true if we found a word like "ON" that has
1416 // already set it to false
1417 if (isset($save_table_ref) && $save_table_ref != FALSE) {
1418 $save_table_ref = TRUE;
1419 } //end if
1421 } // end if (check for end of table ref)
1422 } //end if (!$seen_end_of_table_ref)
1424 if ($seen_end_of_table_ref) {
1425 $save_table_ref = FALSE;
1426 } // end if
1428 } // end for $i (loop #1)
1430 //DEBUG
1432 if (isset($current_select_expr)) {
1433 for ($trace=0; $trace<=$current_select_expr; $trace++) {
1434 echo "<br />";
1435 reset ($subresult['select_expr'][$trace]);
1436 while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1437 echo "sel expr $trace $key => $val<br />\n";
1441 if (isset($current_table_ref)) {
1442 echo "current_table_ref = " . $current_table_ref . "<br>";
1443 for ($trace=0; $trace<=$current_table_ref; $trace++) {
1445 echo "<br />";
1446 reset ($subresult['table_ref'][$trace]);
1447 while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1448 echo "table ref $trace $key => $val<br />\n";
1452 // -------------------------------------------------------
1455 // loop #2: - queryflags
1456 // - querytype (for queries != 'SELECT')
1457 // - section_before_limit, section_after_limit
1459 // we will also need this queryflag in loop 2
1460 // so set it here
1461 if (isset($current_table_ref) && $current_table_ref > -1) {
1462 $subresult['queryflags']['select_from'] = 1;
1465 $section_before_limit = '';
1466 $section_after_limit = ''; // truly the section after the limit clause
1467 $seen_reserved_word = FALSE;
1468 $seen_group = FALSE;
1469 $seen_order = FALSE;
1470 $seen_order_by = FALSE;
1471 $in_group_by = FALSE; // true when we are inside the GROUP BY clause
1472 $in_order_by = FALSE; // true when we are inside the ORDER BY clause
1473 $in_having = FALSE; // true when we are inside the HAVING clause
1474 $in_select_expr = FALSE; // true when we are inside the select expr clause
1475 $in_where = FALSE; // true when we are inside the WHERE clause
1476 $seen_limit = FALSE; // true if we have seen a LIMIT clause
1477 $in_limit = FALSE; // true when we are inside the LIMIT clause
1478 $after_limit = FALSE; // true when we are after the LIMIT clause
1479 $in_from = FALSE; // true when we are in the FROM clause
1480 $in_group_concat = FALSE;
1481 $first_reserved_word = '';
1482 $current_identifier = '';
1483 $unsorted_query = $arr['raw']; // in case there is no ORDER BY
1484 $number_of_brackets = 0;
1485 $in_subquery = false;
1487 for ($i = 0; $i < $size; $i++) {
1488 //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1490 // need_confirm
1492 // check for reserved words that will have to generate
1493 // a confirmation request later in sql.php
1494 // the cases are:
1495 // DROP TABLE
1496 // DROP DATABASE
1497 // ALTER TABLE... DROP
1498 // DELETE FROM...
1500 // this code is not used for confirmations coming from functions.js
1502 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1503 $number_of_brackets++;
1506 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1507 $number_of_brackets--;
1508 if ($number_of_brackets == 0) {
1509 $in_subquery = false;
1513 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1514 $upper_data = strtoupper($arr[$i]['data']);
1516 if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
1517 $in_subquery = true;
1520 if (!$seen_reserved_word) {
1521 $first_reserved_word = $upper_data;
1522 $subresult['querytype'] = $upper_data;
1523 $seen_reserved_word = TRUE;
1525 // if the first reserved word is DROP or DELETE,
1526 // we know this is a query that needs to be confirmed
1527 if ($first_reserved_word=='DROP'
1528 || $first_reserved_word == 'DELETE'
1529 || $first_reserved_word == 'TRUNCATE') {
1530 $subresult['queryflags']['need_confirm'] = 1;
1533 if ($first_reserved_word=='SELECT'){
1534 $position_of_first_select = $i;
1537 } else {
1538 if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1539 $subresult['queryflags']['need_confirm'] = 1;
1543 if ($upper_data == 'LIMIT' && ! $in_subquery) {
1544 $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1545 $in_limit = TRUE;
1546 $seen_limit = TRUE;
1547 $limit_clause = '';
1548 $in_order_by = FALSE; // @todo maybe others to set FALSE
1551 if ($upper_data == 'PROCEDURE') {
1552 $subresult['queryflags']['procedure'] = 1;
1553 $in_limit = FALSE;
1554 $after_limit = TRUE;
1557 * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1559 if ($upper_data == 'SELECT') {
1560 $in_select_expr = TRUE;
1561 $select_expr_clause = '';
1563 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1564 $subresult['queryflags']['distinct'] = 1;
1567 if ($upper_data == 'UNION') {
1568 $subresult['queryflags']['union'] = 1;
1571 if ($upper_data == 'JOIN') {
1572 $subresult['queryflags']['join'] = 1;
1575 if ($upper_data == 'OFFSET') {
1576 $subresult['queryflags']['offset'] = 1;
1579 // if this is a real SELECT...FROM
1580 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1581 $in_from = TRUE;
1582 $from_clause = '';
1583 $in_select_expr = FALSE;
1587 // (we could have less resetting of variables to FALSE
1588 // if we trust that the query respects the standard
1589 // MySQL order for clauses)
1591 // we use $seen_group and $seen_order because we are looking
1592 // for the BY
1593 if ($upper_data == 'GROUP') {
1594 $seen_group = TRUE;
1595 $seen_order = FALSE;
1596 $in_having = FALSE;
1597 $in_order_by = FALSE;
1598 $in_where = FALSE;
1599 $in_select_expr = FALSE;
1600 $in_from = FALSE;
1602 if ($upper_data == 'ORDER' && !$in_group_concat) {
1603 $seen_order = TRUE;
1604 $seen_group = FALSE;
1605 $in_having = FALSE;
1606 $in_group_by = FALSE;
1607 $in_where = FALSE;
1608 $in_select_expr = FALSE;
1609 $in_from = FALSE;
1611 if ($upper_data == 'HAVING') {
1612 $in_having = TRUE;
1613 $having_clause = '';
1614 $seen_group = FALSE;
1615 $seen_order = FALSE;
1616 $in_group_by = FALSE;
1617 $in_order_by = FALSE;
1618 $in_where = FALSE;
1619 $in_select_expr = FALSE;
1620 $in_from = FALSE;
1623 if ($upper_data == 'WHERE') {
1624 $in_where = TRUE;
1625 $where_clause = '';
1626 $where_clause_identifiers = array();
1627 $seen_group = FALSE;
1628 $seen_order = FALSE;
1629 $in_group_by = FALSE;
1630 $in_order_by = FALSE;
1631 $in_having = FALSE;
1632 $in_select_expr = FALSE;
1633 $in_from = FALSE;
1636 if ($upper_data == 'BY') {
1637 if ($seen_group) {
1638 $in_group_by = TRUE;
1639 $group_by_clause = '';
1641 if ($seen_order) {
1642 $seen_order_by = TRUE;
1643 // Here we assume that the ORDER BY keywords took
1644 // exactly 8 characters.
1645 // We use PMA_substr() to be charset-safe; otherwise
1646 // if the table name contains accents, the unsorted
1647 // query would be missing some characters.
1648 $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1649 $in_order_by = TRUE;
1650 $order_by_clause = '';
1654 // if we find one of the words that could end the clause
1655 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1657 $in_group_by = FALSE;
1658 $in_order_by = FALSE;
1659 $in_having = FALSE;
1660 $in_where = FALSE;
1661 $in_select_expr = FALSE;
1662 $in_from = FALSE;
1665 } // endif (reservedWord)
1668 // do not add a space after a function name
1670 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1673 $sep = ' ';
1674 if ($arr[$i]['type'] == 'alpha_functionName') {
1675 $sep='';
1676 $upper_data = strtoupper($arr[$i]['data']);
1677 if ($upper_data =='GROUP_CONCAT') {
1678 $in_group_concat = TRUE;
1679 $number_of_brackets_in_group_concat = 0;
1683 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1684 if ($in_group_concat) {
1685 $number_of_brackets_in_group_concat++;
1688 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1689 if ($in_group_concat) {
1690 $number_of_brackets_in_group_concat--;
1691 if ($number_of_brackets_in_group_concat == 0) {
1692 $in_group_concat = FALSE;
1697 // do not add a space after an identifier if followed by a dot
1698 if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1699 $sep = '';
1702 // do not add a space after a dot if followed by an identifier
1703 if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1704 $sep = '';
1707 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1708 $select_expr_clause .= $arr[$i]['data'] . $sep;
1710 if ($in_from && $upper_data != 'FROM') {
1711 $from_clause .= $arr[$i]['data'] . $sep;
1713 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1714 $group_by_clause .= $arr[$i]['data'] . $sep;
1716 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1717 // add a space only before ASC or DESC
1718 // not around the dot between dbname and tablename
1719 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1720 $order_by_clause .= $sep;
1722 $order_by_clause .= $arr[$i]['data'];
1724 if ($in_having && $upper_data != 'HAVING') {
1725 $having_clause .= $arr[$i]['data'] . $sep;
1727 if ($in_where && $upper_data != 'WHERE') {
1728 $where_clause .= $arr[$i]['data'] . $sep;
1730 if (($arr[$i]['type'] == 'quote_backtick')
1731 || ($arr[$i]['type'] == 'alpha_identifier')) {
1732 $where_clause_identifiers[] = $arr[$i]['data'];
1736 // to grab the rest of the query after the ORDER BY clause
1737 if (isset($subresult['queryflags']['select_from'])
1738 && $subresult['queryflags']['select_from'] == 1
1739 && ! $in_order_by
1740 && $seen_order_by
1741 && $upper_data != 'BY') {
1742 $unsorted_query .= $arr[$i]['data'];
1743 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1744 && $arr[$i]['type'] != 'punct_bracket_close_round'
1745 && $arr[$i]['type'] != 'punct') {
1746 $unsorted_query .= $sep;
1750 if ($in_limit) {
1751 if ($upper_data == 'OFFSET') {
1752 $limit_clause .= $sep;
1754 $limit_clause .= $arr[$i]['data'];
1755 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1756 $limit_clause .= $sep;
1759 if ($after_limit && $seen_limit) {
1760 $section_after_limit .= $arr[$i]['data'] . $sep;
1763 // clear $upper_data for next iteration
1764 $upper_data='';
1765 } // end for $i (loop #2)
1766 if (empty($section_before_limit)) {
1767 $section_before_limit = $arr['raw'];
1770 // -----------------------------------------------------
1771 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1772 // (for now, check only the first query)
1773 // (for now, identifiers are assumed to be backquoted)
1775 // If we find that we are dealing with a CREATE TABLE query,
1776 // we look for the next punct_bracket_open_round, which
1777 // introduces the fields list. Then, when we find a
1778 // quote_backtick, it must be a field, so we put it into
1779 // the create_table_fields array. Even if this field is
1780 // not a timestamp, it will be useful when logic has been
1781 // added for complete field attributes analysis.
1783 $seen_foreign = FALSE;
1784 $seen_references = FALSE;
1785 $seen_constraint = FALSE;
1786 $foreign_key_number = -1;
1787 $seen_create_table = FALSE;
1788 $seen_create = FALSE;
1789 $seen_alter = FALSE;
1790 $in_create_table_fields = FALSE;
1791 $brackets_level = 0;
1792 $in_timestamp_options = FALSE;
1793 $seen_default = FALSE;
1795 for ($i = 0; $i < $size; $i++) {
1796 // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1798 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1799 $upper_data = strtoupper($arr[$i]['data']);
1801 if ($upper_data == 'NOT' && $in_timestamp_options) {
1802 $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1806 if ($upper_data == 'CREATE') {
1807 $seen_create = TRUE;
1810 if ($upper_data == 'ALTER') {
1811 $seen_alter = TRUE;
1814 if ($upper_data == 'TABLE' && $seen_create) {
1815 $seen_create_table = TRUE;
1816 $create_table_fields = array();
1819 if ($upper_data == 'CURRENT_TIMESTAMP') {
1820 if ($in_timestamp_options) {
1821 if ($seen_default) {
1822 $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1827 if ($upper_data == 'CONSTRAINT') {
1828 $foreign_key_number++;
1829 $seen_foreign = FALSE;
1830 $seen_references = FALSE;
1831 $seen_constraint = TRUE;
1833 if ($upper_data == 'FOREIGN') {
1834 $seen_foreign = TRUE;
1835 $seen_references = FALSE;
1836 $seen_constraint = FALSE;
1838 if ($upper_data == 'REFERENCES') {
1839 $seen_foreign = FALSE;
1840 $seen_references = TRUE;
1841 $seen_constraint = FALSE;
1845 // Cases covered:
1847 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1848 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1850 // but we set ['on_delete'] or ['on_cascade'] to
1851 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1853 // ON UPDATE CURRENT_TIMESTAMP
1855 if ($upper_data == 'ON') {
1856 if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1857 $second_upper_data = strtoupper($arr[$i+1]['data']);
1858 if ($second_upper_data == 'DELETE') {
1859 $clause = 'on_delete';
1861 if ($second_upper_data == 'UPDATE') {
1862 $clause = 'on_update';
1864 if (isset($clause)
1865 && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1867 // ugly workaround because currently, NO is not
1868 // in the list of reserved words in sqlparser.data
1869 // (we got a bug report about not being able to use
1870 // 'no' as an identifier)
1871 || ($arr[$i+2]['type'] == 'alpha_identifier'
1872 && strtoupper($arr[$i+2]['data'])=='NO'))
1874 $third_upper_data = strtoupper($arr[$i+2]['data']);
1875 if ($third_upper_data == 'CASCADE'
1876 || $third_upper_data == 'RESTRICT') {
1877 $value = $third_upper_data;
1878 } elseif ($third_upper_data == 'SET'
1879 || $third_upper_data == 'NO') {
1880 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1881 $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1883 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1884 if ($clause == 'on_update'
1885 && $in_timestamp_options) {
1886 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1887 $seen_default = FALSE;
1890 } else {
1891 $value = '';
1893 if (!empty($value)) {
1894 $foreign[$foreign_key_number][$clause] = $value;
1896 unset($clause);
1897 } // endif (isset($clause))
1901 } // end of reserved words analysis
1904 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1905 $brackets_level++;
1906 if ($seen_create_table && $brackets_level == 1) {
1907 $in_create_table_fields = TRUE;
1912 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1913 $brackets_level--;
1914 if ($seen_references) {
1915 $seen_references = FALSE;
1917 if ($seen_create_table && $brackets_level == 0) {
1918 $in_create_table_fields = FALSE;
1922 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1923 $upper_data = strtoupper($arr[$i]['data']);
1924 if ($seen_create_table && $in_create_table_fields) {
1925 if ($upper_data == 'DEFAULT') {
1926 $seen_default = TRUE;
1927 $create_table_fields[$current_identifier]['default_value'] = $arr[$i + 1]['data'];
1933 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1935 if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1936 $upper_data = strtoupper($arr[$i]['data']);
1937 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1938 $create_table_fields[$current_identifier]['type'] = $upper_data;
1939 if ($upper_data == 'TIMESTAMP') {
1940 $arr[$i]['type'] = 'alpha_columnType';
1941 $in_timestamp_options = TRUE;
1942 } else {
1943 $in_timestamp_options = FALSE;
1944 if ($upper_data == 'CHAR') {
1945 $arr[$i]['type'] = 'alpha_columnType';
1952 if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1954 if ($arr[$i]['type'] == 'quote_backtick') {
1955 // remove backquotes
1956 $identifier = PMA_unQuote($arr[$i]['data']);
1957 } else {
1958 $identifier = $arr[$i]['data'];
1961 if ($seen_create_table && $in_create_table_fields) {
1962 $current_identifier = $identifier;
1963 // warning: we set this one even for non TIMESTAMP type
1964 $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1967 if ($seen_constraint) {
1968 $foreign[$foreign_key_number]['constraint'] = $identifier;
1971 if ($seen_foreign && $brackets_level > 0) {
1972 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1975 if ($seen_references) {
1976 if ($seen_alter && $brackets_level > 0) {
1977 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1978 // here, the first bracket level corresponds to the
1979 // bracket of CREATE TABLE
1980 // so if we are on level 2, it must be the index list
1981 // of the foreign key REFERENCES
1982 } elseif ($brackets_level > 1) {
1983 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1984 } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1985 // identifier is `db`.`table`
1986 // the first pass will pick the db name
1987 // the next pass will pick the table name
1988 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1989 } else {
1990 // identifier is `table`
1991 $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
1995 } // end for $i (loop #3)
1998 // Fill the $subresult array
2000 if (isset($create_table_fields)) {
2001 $subresult['create_table_fields'] = $create_table_fields;
2004 if (isset($foreign)) {
2005 $subresult['foreign_keys'] = $foreign;
2008 if (isset($select_expr_clause)) {
2009 $subresult['select_expr_clause'] = $select_expr_clause;
2011 if (isset($from_clause)) {
2012 $subresult['from_clause'] = $from_clause;
2014 if (isset($group_by_clause)) {
2015 $subresult['group_by_clause'] = $group_by_clause;
2017 if (isset($order_by_clause)) {
2018 $subresult['order_by_clause'] = $order_by_clause;
2020 if (isset($having_clause)) {
2021 $subresult['having_clause'] = $having_clause;
2023 if (isset($limit_clause)) {
2024 $subresult['limit_clause'] = $limit_clause;
2026 if (isset($where_clause)) {
2027 $subresult['where_clause'] = $where_clause;
2029 if (isset($unsorted_query) && !empty($unsorted_query)) {
2030 $subresult['unsorted_query'] = $unsorted_query;
2032 if (isset($where_clause_identifiers)) {
2033 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
2036 if (isset($position_of_first_select)) {
2037 $subresult['position_of_first_select'] = $position_of_first_select;
2038 $subresult['section_before_limit'] = $section_before_limit;
2039 $subresult['section_after_limit'] = $section_after_limit;
2042 // They are naughty and didn't have a trailing semi-colon,
2043 // then still handle it properly
2044 if ($subresult['querytype'] != '') {
2045 $result[] = $subresult;
2047 return $result;
2048 } // end of the "PMA_SQP_analyze()" function
2052 * Colorizes SQL queries html formatted
2054 * @todo check why adding a "\n" after the </span> would cause extra blanks
2055 * to be displayed: SELECT p . person_name
2056 * @param array The SQL queries html formatted
2058 * @return array The colorized SQL queries
2060 * @access public
2062 function PMA_SQP_formatHtml_colorize($arr)
2064 $i = $GLOBALS['PMA_strpos']($arr['type'], '_');
2065 $class = '';
2066 if ($i > 0) {
2067 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2070 $class .= 'syntax_' . $arr['type'];
2072 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2073 } // end of the "PMA_SQP_formatHtml_colorize()" function
2077 * Formats SQL queries to html
2079 * @param array The SQL queries
2080 * @param string mode
2081 * @param integer starting token
2082 * @param integer number of tokens to format, -1 = all
2084 * @return string The formatted SQL queries
2086 * @access public
2088 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2089 $number_of_tokens=-1)
2091 //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2092 // then check for an array
2093 if (!is_array($arr)) {
2094 return htmlspecialchars($arr);
2096 // first check for the SQL parser having hit an error
2097 if (PMA_SQP_isError()) {
2098 return htmlspecialchars($arr['raw']);
2100 // else do it properly
2101 switch ($mode) {
2102 case 'color':
2103 $str = '<span class="syntax">';
2104 $html_line_break = '<br />';
2105 $docu = TRUE;
2106 break;
2107 case 'query_only':
2108 $str = '';
2109 $html_line_break = "\n";
2110 $docu = FALSE;
2111 break;
2112 case 'text':
2113 $str = '';
2114 $html_line_break = '<br />';
2115 $docu = TRUE;
2116 break;
2117 } // end switch
2118 // inner_sql is a span that exists for all cases, except query_only
2119 // of $cfg['SQP']['fmtType'] to make possible a replacement
2120 // for inline editing
2121 if ($mode!='query_only') {
2122 $str .= '<span class="inner_sql">';
2124 $close_docu_link = false;
2125 $indent = 0;
2126 $bracketlevel = 0;
2127 $functionlevel = 0;
2128 $infunction = FALSE;
2129 $space_punct_listsep = ' ';
2130 $space_punct_listsep_function_name = ' ';
2131 // $space_alpha_reserved_word = '<br />'."\n";
2132 $space_alpha_reserved_word = ' ';
2134 $keywords_with_brackets_1before = array(
2135 'INDEX',
2136 'KEY',
2137 'ON',
2138 'USING'
2140 $keywords_with_brackets_1before_cnt = 4;
2142 $keywords_with_brackets_2before = array(
2143 'IGNORE',
2144 'INDEX',
2145 'INTO',
2146 'KEY',
2147 'PRIMARY',
2148 'PROCEDURE',
2149 'REFERENCES',
2150 'UNIQUE',
2151 'USE'
2153 // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
2154 $keywords_with_brackets_2before_cnt = 9;
2156 // These reserved words do NOT get a newline placed near them.
2157 $keywords_no_newline = array(
2158 'AS',
2159 'ASC',
2160 'DESC',
2161 'DISTINCT',
2162 'DUPLICATE',
2163 'HOUR',
2164 'INTERVAL',
2165 'IS',
2166 'LIKE',
2167 'NOT',
2168 'NULL',
2169 'ON',
2170 'REGEXP'
2172 $keywords_no_newline_cnt = 12;
2174 // These reserved words introduce a privilege list
2175 $keywords_priv_list = array(
2176 'GRANT',
2177 'REVOKE'
2179 $keywords_priv_list_cnt = 2;
2181 if ($number_of_tokens == -1) {
2182 $arraysize = $arr['len'];
2183 } else {
2184 $arraysize = $number_of_tokens;
2186 $typearr = array();
2187 if ($arraysize >= 0) {
2188 $typearr[0] = '';
2189 $typearr[1] = '';
2190 $typearr[2] = '';
2191 //$typearr[3] = $arr[0]['type'];
2192 $typearr[3] = $arr[$start_token]['type'];
2195 $in_priv_list = FALSE;
2196 for ($i = $start_token; $i < $arraysize; $i++) {
2197 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2198 $before = '';
2199 $after = '';
2200 // array_shift($typearr);
2202 0 prev2
2203 1 prev
2204 2 current
2205 3 next
2207 if (($i + 1) < $arraysize) {
2208 // array_push($typearr, $arr[$i + 1]['type']);
2209 $typearr[4] = $arr[$i + 1]['type'];
2210 } else {
2211 //array_push($typearr, null);
2212 $typearr[4] = '';
2215 for ($j=0; $j<4; $j++) {
2216 $typearr[$j] = $typearr[$j + 1];
2219 switch ($typearr[2]) {
2220 case 'alpha_bitfield_constant_introducer':
2221 $before = ' ';
2222 $after = '';
2223 break;
2224 case 'white_newline':
2225 $before = '';
2226 break;
2227 case 'punct_bracket_open_round':
2228 $bracketlevel++;
2229 $infunction = FALSE;
2230 // Make sure this array is sorted!
2231 if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2232 || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2233 || (($typearr[0] == 'alpha_reservedWord')
2234 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2235 || (($typearr[1] == 'alpha_reservedWord')
2236 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2238 $functionlevel++;
2239 $infunction = TRUE;
2240 $after .= ' ';
2241 } else {
2242 $indent++;
2243 $after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2245 break;
2246 case 'alpha_identifier':
2247 if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2248 $after = '';
2249 $before = '';
2251 // for example SELECT 1 somealias
2252 if ($typearr[1] == 'digit_integer') {
2253 $before = ' ';
2255 if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2256 $after .= ' ';
2258 break;
2259 case 'punct_user':
2260 case 'punct_qualifier':
2261 $before = '';
2262 $after = '';
2263 break;
2264 case 'punct_listsep':
2265 if ($infunction == TRUE) {
2266 $after .= $space_punct_listsep_function_name;
2267 } else {
2268 $after .= $space_punct_listsep;
2270 break;
2271 case 'punct_queryend':
2272 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2273 $after .= $html_line_break;
2274 $after .= $html_line_break;
2276 $space_punct_listsep = ' ';
2277 $space_punct_listsep_function_name = ' ';
2278 $space_alpha_reserved_word = ' ';
2279 $in_priv_list = FALSE;
2280 break;
2281 case 'comment_mysql':
2282 case 'comment_ansi':
2283 $after .= $html_line_break;
2284 break;
2285 case 'punct':
2286 $before .= ' ';
2287 // workaround for
2288 // select * from mytable limit 0,-1
2289 // (a side effect of this workaround is that
2290 // select 20 - 9
2291 // becomes
2292 // select 20 -9
2293 // )
2294 if ($typearr[3] != 'digit_integer') {
2295 $after .= ' ';
2297 break;
2298 case 'punct_bracket_close_round':
2299 // only close bracket level when it was opened before
2300 if ($bracketlevel > 0) {
2301 $bracketlevel--;
2302 if ($infunction == TRUE) {
2303 $functionlevel--;
2304 $after .= ' ';
2305 $before .= ' ';
2306 } else {
2307 $indent--;
2308 $before .= ($mode != 'query_only' ? '</div>' : ' ');
2310 $infunction = ($functionlevel > 0) ? TRUE : FALSE;
2312 break;
2313 case 'alpha_columnType':
2314 if ($docu) {
2315 switch ($arr[$i]['data']) {
2316 case 'tinyint':
2317 case 'smallint':
2318 case 'mediumint':
2319 case 'int':
2320 case 'bigint':
2321 case 'decimal':
2322 case 'float':
2323 case 'double':
2324 case 'real':
2325 case 'bit':
2326 case 'boolean':
2327 case 'serial':
2328 $before .= PMA_showMySQLDocu('data-types', 'numeric-types', false, '', true);
2329 $after = '</a>' . $after;
2330 break;
2331 case 'date':
2332 case 'datetime':
2333 case 'timestamp':
2334 case 'time':
2335 case 'year':
2336 $before .= PMA_showMySQLDocu('data-types', 'date-and-time-types', false, '', true);
2337 $after = '</a>' . $after;
2338 break;
2339 case 'char':
2340 case 'varchar':
2341 case 'tinytext':
2342 case 'text':
2343 case 'mediumtext':
2344 case 'longtext':
2345 case 'binary':
2346 case 'varbinary':
2347 case 'tinyblob':
2348 case 'mediumblob':
2349 case 'blob':
2350 case 'longblob':
2351 case 'enum':
2352 case 'set':
2353 $before .= PMA_showMySQLDocu('data-types', 'string-types', false, '', true);
2354 $after = '</a>' . $after;
2355 break;
2358 if ($typearr[3] == 'alpha_columnAttrib') {
2359 $after .= ' ';
2361 if ($typearr[1] == 'alpha_columnType') {
2362 $before .= ' ';
2364 break;
2365 case 'alpha_columnAttrib':
2367 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2368 // COLLATE LATIN1_GENERAL_CI DEFAULT
2369 if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2370 $before .= ' ';
2372 if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2373 $after .= ' ';
2375 // workaround for
2376 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2378 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2379 $before .= ' ';
2381 // workaround for
2382 // select * from mysql.user where binary user="root"
2383 // binary is marked as alpha_columnAttrib
2384 // but should be marked as a reserved word
2385 if (strtoupper($arr[$i]['data']) == 'BINARY'
2386 && $typearr[3] == 'alpha_identifier') {
2387 $after .= ' ';
2389 break;
2390 case 'alpha_reservedWord':
2391 // do not uppercase the reserved word if we are calling
2392 // this function in query_only mode, because we need
2393 // the original query (otherwise we get problems with
2394 // semi-reserved words like "storage" which is legal
2395 // as an identifier name)
2397 if ($mode != 'query_only') {
2398 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2401 if ((($typearr[1] != 'alpha_reservedWord')
2402 || (($typearr[1] == 'alpha_reservedWord')
2403 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2404 && ($typearr[1] != 'punct_level_plus')
2405 && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2406 // do not put a space before the first token, because
2407 // we use a lot of pattern matching checking for the
2408 // first reserved word at beginning of query
2409 // so do not put a newline before
2411 // also we must not be inside a privilege list
2412 if ($i > 0) {
2413 // the alpha_identifier exception is there to
2414 // catch cases like
2415 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2416 // (else, we get mydb.mytableTO)
2418 // the quote_single exception is there to
2419 // catch cases like
2420 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2422 * @todo fix all cases and find why this happens
2425 if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2426 $before .= $space_alpha_reserved_word;
2428 } else {
2429 // on first keyword, check if it introduces a
2430 // privilege list
2431 if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2432 $in_priv_list = TRUE;
2435 } else {
2436 $before .= ' ';
2439 switch ($arr[$i]['data']) {
2440 case 'CREATE':
2441 case 'ALTER':
2442 case 'DROP':
2443 case 'RENAME';
2444 case 'TRUNCATE':
2445 case 'ANALYZE':
2446 case 'ANALYSE':
2447 case 'OPTIMIZE':
2448 if ($docu) {
2449 switch ($arr[$i + 1]['data']) {
2450 case 'EVENT':
2451 case 'TABLE':
2452 case 'TABLESPACE':
2453 case 'FUNCTION':
2454 case 'INDEX':
2455 case 'PROCEDURE':
2456 case 'TRIGGER':
2457 case 'SERVER':
2458 case 'DATABASE':
2459 case 'VIEW':
2460 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_' . $arr[$i + 1]['data'], false, '', true);
2461 $close_docu_link = true;
2462 break;
2464 if ($arr[$i + 1]['data'] == 'LOGFILE' && $arr[$i + 2]['data'] == 'GROUP') {
2465 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_LOGFILE_GROUP', false, '', true);
2466 $close_docu_link = true;
2469 if (!$in_priv_list) {
2470 $space_punct_listsep = $html_line_break;
2471 $space_alpha_reserved_word = ' ';
2473 break;
2474 case 'EVENT':
2475 case 'TABLESPACE':
2476 case 'TABLE':
2477 case 'FUNCTION':
2478 case 'INDEX':
2479 case 'PROCEDURE':
2480 case 'SERVER':
2481 case 'TRIGGER':
2482 case 'DATABASE':
2483 case 'VIEW':
2484 case 'GROUP':
2485 if ($close_docu_link) {
2486 $after = '</a>' . $after;
2487 $close_docu_link = false;
2489 break;
2490 case 'SET':
2491 if ($docu && ($i == 0 || $arr[$i - 1]['data'] != 'CHARACTER')) {
2492 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2493 $after = '</a>' . $after;
2495 if (!$in_priv_list) {
2496 $space_punct_listsep = $html_line_break;
2497 $space_alpha_reserved_word = ' ';
2499 break;
2500 case 'EXPLAIN':
2501 case 'DESCRIBE':
2502 case 'DELETE':
2503 case 'SHOW':
2504 case 'UPDATE':
2505 if ($docu) {
2506 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2507 $after = '</a>' . $after;
2509 if (!$in_priv_list) {
2510 $space_punct_listsep = $html_line_break;
2511 $space_alpha_reserved_word = ' ';
2513 break;
2514 case 'INSERT':
2515 case 'REPLACE':
2516 if ($docu) {
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 = $html_line_break;
2524 break;
2525 case 'VALUES':
2526 $space_punct_listsep = ' ';
2527 $space_alpha_reserved_word = $html_line_break;
2528 break;
2529 case 'SELECT':
2530 if ($docu) {
2531 $before .= PMA_showMySQLDocu('SQL-Syntax', 'SELECT', false, '', true);
2532 $after = '</a>' . $after;
2534 $space_punct_listsep = ' ';
2535 $space_alpha_reserved_word = $html_line_break;
2536 break;
2537 case 'CALL':
2538 case 'DO':
2539 case 'HANDLER':
2540 if ($docu) {
2541 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2542 $after = '</a>' . $after;
2544 break;
2545 default:
2546 break;
2547 } // end switch ($arr[$i]['data'])
2549 $after .= ' ';
2550 break;
2551 case 'digit_integer':
2552 case 'digit_float':
2553 case 'digit_hex':
2555 * @todo could there be other types preceding a digit?
2557 if ($typearr[1] == 'alpha_reservedWord') {
2558 $after .= ' ';
2560 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2561 $after .= ' ';
2563 if ($typearr[1] == 'alpha_columnAttrib') {
2564 $before .= ' ';
2566 break;
2567 case 'alpha_variable':
2568 $after = ' ';
2569 break;
2570 case 'quote_double':
2571 case 'quote_single':
2572 // workaround: for the query
2573 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2574 // the @ is incorrectly marked as alpha_variable
2575 // in the parser, and here, the '%' gets a blank before,
2576 // which is a syntax error
2577 if ($typearr[1] != 'punct_user' && $typearr[1] != 'alpha_bitfield_constant_introducer') {
2578 $before .= ' ';
2580 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2581 $after .= ' ';
2583 break;
2584 case 'quote_backtick':
2585 // here we check for punct_user to handle correctly
2586 // DEFINER = `username`@`%`
2587 // where @ is the punct_user and `%` is the quote_backtick
2588 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2589 $after .= ' ';
2591 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2592 $before .= ' ';
2594 break;
2595 default:
2596 break;
2597 } // end switch ($typearr[2])
2600 if ($typearr[3] != 'punct_qualifier') {
2601 $after .= ' ';
2603 $after .= "\n";
2605 $str .= $before;
2606 if ($mode=='color') {
2607 $str .= PMA_SQP_formatHTML_colorize($arr[$i]);
2608 } elseif ($mode == 'text') {
2609 $str .= htmlspecialchars($arr[$i]['data']);
2610 } else {
2611 $str .= $arr[$i]['data'];
2613 $str .= $after;
2614 } // end for
2615 // close unclosed indent levels
2616 while ($indent > 0) {
2617 $indent--;
2618 $str .= ($mode != 'query_only' ? '</div>' : ' ');
2620 /* End possibly unclosed documentation link */
2621 if ($close_docu_link) {
2622 $str .= '</a>';
2623 $close_docu_link = false;
2625 if ($mode!='query_only') {
2626 // close inner_sql span
2627 $str .= '</span>';
2629 if ($mode=='color') {
2630 // close syntax span
2631 $str .= '</span>';
2634 return $str;
2635 } // end of the "PMA_SQP_formatHtml()" function
2639 * Builds a CSS rule used for html formatted SQL queries
2641 * @param string The class name
2642 * @param string The property name
2643 * @param string The property value
2645 * @return string The CSS rule
2647 * @access public
2649 * @see PMA_SQP_buildCssData()
2651 function PMA_SQP_buildCssRule($classname, $property, $value)
2653 $str = '.' . $classname . ' {';
2654 if ($value != '') {
2655 $str .= $property . ': ' . $value . ';';
2657 $str .= '}' . "\n";
2659 return $str;
2660 } // end of the "PMA_SQP_buildCssRule()" function
2664 * Builds CSS rules used for html formatted SQL queries
2666 * @return string The CSS rules set
2668 * @access public
2670 * @global array The current PMA configuration
2672 * @see PMA_SQP_buildCssRule()
2674 function PMA_SQP_buildCssData()
2676 global $cfg;
2678 $css_string = '';
2679 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2680 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2683 for ($i = 0; $i < 8; $i++) {
2684 $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2687 return $css_string;
2688 } // end of the "PMA_SQP_buildCssData()" function
2690 if (! defined('PMA_MINIMUM_COMMON')) {
2692 * Gets SQL queries with no format
2694 * @param array The SQL queries list
2696 * @return string The SQL queries with no format
2698 * @access public
2700 function PMA_SQP_formatNone($arr)
2702 $formatted_sql = htmlspecialchars($arr['raw']);
2703 $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2705 return $formatted_sql;
2706 } // end of the "PMA_SQP_formatNone()" function
2708 } // end if: minimal common.lib needed?