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