?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Chinese-simplified update
[phpmyadmin/crack.git] / libraries / sqlparser.lib.php
blob37783734a859c0e05071e9444180d11f24edf72f
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;
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;
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 (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;
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;
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 if (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
680 $t_suffix = '_identifier';
681 } elseif (($t_next == 'punct_bracket_open_round')
682 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
683 /**
684 * @todo 2005-10-16: in the case of a CREATE TABLE containing
685 * a TIMESTAMP, since TIMESTAMP() is also a function, it's
686 * found here and the token is wrongly marked as alpha_functionName.
687 * But we compensate for this when analysing for timestamp_not_null
688 * later in this script.
689 *
690 * Same applies to CHAR vs. CHAR() function.
691 */
692 $t_suffix = '_functionName';
693 /* There are functions which might be as well column types */
694 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
695 $t_suffix = '_columnType';
696
697 /**
698 * Temporary fix for BUG #621357 and #2027720
699 *
700 * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
701 */
702 if (($d_cur_upper == 'SET' || $d_cur_upper == 'BINARY') && $t_next != 'punct_bracket_open_round') {
703 $t_suffix = '_reservedWord';
704 }
705 //END OF TEMPORARY FIX
706
707 // CHARACTER is a synonym for CHAR, but can also be meant as
708 // CHARACTER SET. In this case, we have a reserved word.
709 if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
710 $t_suffix = '_reservedWord';
711 }
712
713 // experimental
714 // current is a column type, so previous must not be
715 // a reserved word but an identifier
716 // CREATE TABLE SG_Persons (first varchar(64))
717
718 //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
719 // $sql_array[$i-1]['type'] = 'alpha_identifier';
720 //}
721
722 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
723 $t_suffix = '_reservedWord';
724 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
725 $t_suffix = '_columnAttrib';
726 // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
727 // it should be regarded as a reserved word.
728 if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
729 $t_suffix = '_reservedWord';
730 }
731
732 if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
733 $t_suffix = '_reservedWord';
734 }
735 // Binary as character set
736 if ($d_cur_upper == 'BINARY' && (
737 ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
738 || ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
739 || ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
740 || $d_prev_upper == 'CHARSET'
741 ) && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {
742 $t_suffix = '_charset';
743 }
744 } elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)
745 || PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)
746 || ($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {
747 $t_suffix = '_charset';
748 } else {
749 // Do nothing
750 }
751 // check if present in the list of forbidden words
752 if ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {
753 $sql_array[$i]['forbidden'] = TRUE;
754 } else {
755 $sql_array[$i]['forbidden'] = FALSE;
756 }
757 $sql_array[$i]['type'] .= $t_suffix;
758 }
759 } // end for
760
761 // Stores the size of the array inside the array, as count() is a slow
762 // operation.
763 $sql_array['len'] = $arraysize;
764
765 // DEBUG echo 'After parsing<pre>'; print_r($sql_array); echo '</pre>';
766 // Sends the data back
767 return $sql_array;
768 } // end of the "PMA_SQP_parse()" function
769
770 /**
771 * Checks for token types being what we want...
772 *
773 * @param string String of type that we have
774 * @param string String of type that we want
775 *
776 * @return boolean result of check
777 *
778 * @access private
779 */
780 function PMA_SQP_typeCheck($toCheck, $whatWeWant)
781 {
782 $typeSeperator = '_';
783 if (strcmp($whatWeWant, $toCheck) == 0) {
784 return TRUE;
785 } else {
786 if (strpos($whatWeWant, $typeSeperator) === FALSE) {
787 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
788 } else {
789 return FALSE;
790 }
791 }
792 }
793
794
795 /**
796 * Analyzes SQL queries
797 *
798 * @param array The SQL queries
799 *
800 * @return array The analyzed SQL queries
801 *
802 * @access public
803 */
804 function PMA_SQP_analyze($arr)
805 {
806 if ($arr == array()) {
807 return array();
808 }
809 $result = array();
810 $size = $arr['len'];
811 $subresult = array(
812 'querytype' => '',
813 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
814 'position_of_first_select' => '', // the array index
815 'from_clause'=> '',
816 'group_by_clause'=> '',
817 'order_by_clause'=> '',
818 'having_clause' => '',
819 'limit_clause' => '',
820 'where_clause' => '',
821 'where_clause_identifiers' => array(),
822 'unsorted_query' => '',
823 'queryflags' => array(),
824 'select_expr' => array(),
825 'table_ref' => array(),
826 'foreign_keys' => array(),
827 'create_table_fields' => array()
828 );
829 $subresult_empty = $subresult;
830 $seek_queryend = FALSE;
831 $seen_end_of_table_ref = FALSE;
832 $number_of_brackets_in_extract = 0;
833 $number_of_brackets_in_group_concat = 0;
834
835 $number_of_brackets = 0;
836 $in_subquery = false;
837 $seen_subquery = false;
838 $seen_from = false;
839
840 // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
841 // we must not use CURDATE as a table_ref
842 // so we track whether we are in the EXTRACT()
843 $in_extract = FALSE;
844
845 // for GROUP_CONCAT(...)
846 $in_group_concat = FALSE;
847
848 /* Description of analyzer results by lem9
849 *
850 * db, table, column, alias
851 * ------------------------
852 *
853 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
854 *
855 * The SELECT syntax (simplified) is
856 *
857 * SELECT
858 * select_expression,...
859 * [FROM [table_references]
860 *
861 *
862 * ['select_expr'] is filled with each expression, the key represents the
863 * expression position in the list (0-based) (so we don't lose track of
864 * multiple occurences of the same column).
865 *
866 * ['table_ref'] is filled with each table ref, same thing for the key.
867 *
868 * I create all sub-values empty, even if they are
869 * not present (for example no select_expression alias).
870 *
871 * There is a debug section at the end of loop #1, if you want to
872 * see the exact contents of select_expr and table_ref
873 *
874 * queryflags
875 * ----------
876 *
877 * In $subresult, array 'queryflags' is filled, according to what we
878 * find in the query.
879 *
880 * Currently, those are generated:
881 *
882 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
883 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
884 * ['queryflags']['distinct'] = 1; for a DISTINCT
885 * ['queryflags']['union'] = 1; for a UNION
886 * ['queryflags']['join'] = 1; for a JOIN
887 * ['queryflags']['offset'] = 1; for the presence of OFFSET
888 * ['queryflags']['procedure'] = 1; for the presence of PROCEDURE
889 *
890 * query clauses
891 * -------------
892 *
893 * The select is splitted in those clauses:
894 * ['select_expr_clause']
895 * ['from_clause']
896 * ['group_by_clause']
897 * ['order_by_clause']
898 * ['having_clause']
899 * ['limit_clause']
900 * ['where_clause']
901 *
902 * The identifiers of the WHERE clause are put into the array
903 * ['where_clause_identifier']
904 *
905 * For a SELECT, the whole query without the ORDER BY clause is put into
906 * ['unsorted_query']
907 *
908 * foreign keys
909 * ------------
910 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
911 * analyzed and ['foreign_keys'] is an array filled with
912 * the constraint name, the index list,
913 * the REFERENCES table name and REFERENCES index list,
914 * and ON UPDATE | ON DELETE clauses
915 *
916 * position_of_first_select
917 * ------------------------
918 *
919 * The array index of the first SELECT we find. Will be used to
920 * insert a SQL_CALC_FOUND_ROWS.
921 *
922 * create_table_fields
923 * -------------------
924 *
925 * For now, mostly used to detect the DEFAULT CURRENT_TIMESTAMP and
926 * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
927 * An array, each element is the identifier name.
928 * Note that for now, the timestamp_not_null element is created
929 * even for non-TIMESTAMP fields.
930 *
931 * Sub-elements: ['type'] which contains the column type
932 * optional (currently they are never false but can be absent):
933 * ['default_current_timestamp'] boolean
934 * ['on_update_current_timestamp'] boolean
935 * ['timestamp_not_null'] boolean
936 *
937 * section_before_limit, section_after_limit
938 * -----------------------------------------
939 *
940 * Marks the point of the query where we can insert a LIMIT clause;
941 * so the section_before_limit will contain the left part before
942 * a possible LIMIT clause
943 *
944 *
945 * End of description of analyzer results
946 */
947
948 // must be sorted
949 // TODO: current logic checks for only one word, so I put only the
950 // first word of the reserved expressions that end a table ref;
951 // maybe this is not ok (the first word might mean something else)
952 // $words_ending_table_ref = array(
953 // 'FOR UPDATE',
954 // 'GROUP BY',
955 // 'HAVING',
956 // 'LIMIT',
957 // 'LOCK IN SHARE MODE',
958 // 'ORDER BY',
959 // 'PROCEDURE',
960 // 'UNION',
961 // 'WHERE'
962 // );
963 $words_ending_table_ref = array(
964 'FOR',
965 'GROUP',
966 'HAVING',
967 'LIMIT',
968 'LOCK',
969 'ORDER',
970 'PROCEDURE',
971 'UNION',
972 'WHERE'
973 );
974 $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
975
976 $words_ending_clauses = array(
977 'FOR',
978 'LIMIT',
979 'LOCK',
980 'PROCEDURE',
981 'UNION'
982 );
983 $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
984
985
986
987
988 // must be sorted
989 $supported_query_types = array(
990 'SELECT'
991 /*
992 // Support for these additional query types will come later on.
993 'DELETE',
994 'INSERT',
995 'REPLACE',
996 'TRUNCATE',
997 'UPDATE'
998 'EXPLAIN',
999 'DESCRIBE',
1000 'SHOW',
1001 'CREATE',
1002 'SET',
1003 'ALTER'
1004 */
1005 );
1006 $supported_query_types_cnt = count($supported_query_types);
1007
1008 // loop #1 for each token: select_expr, table_ref for SELECT
1009
1010 for ($i = 0; $i < $size; $i++) {
1011 //DEBUG echo "Loop1 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1012
1013 // High speed seek for locating the end of the current query
1014 if ($seek_queryend == TRUE) {
1015 if ($arr[$i]['type'] == 'punct_queryend') {
1016 $seek_queryend = FALSE;
1017 } else {
1018 continue;
1019 } // end if (type == punct_queryend)
1020 } // end if ($seek_queryend)
1021
1022 /**
1023 * Note: do not split if this is a punct_queryend for the first and only query
1024 * @todo when we find a UNION, should we split in another subresult?
1025 */
1026 if ($arr[$i]['type'] == 'punct_queryend' && ($i + 1 != $size)) {
1027 $result[] = $subresult;
1028 $subresult = $subresult_empty;
1029 continue;
1030 } // end if (type == punct_queryend)
1031
1032 // ==============================================================
1033 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1034 $number_of_brackets++;
1035 if ($in_extract) {
1036 $number_of_brackets_in_extract++;
1037 }
1038 if ($in_group_concat) {
1039 $number_of_brackets_in_group_concat++;
1040 }
1041 }
1042 // ==============================================================
1043 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1044 $number_of_brackets--;
1045 if ($number_of_brackets == 0) {
1046 $in_subquery = false;
1047 }
1048 if ($in_extract) {
1049 $number_of_brackets_in_extract--;
1050 if ($number_of_brackets_in_extract == 0) {
1051 $in_extract = FALSE;
1052 }
1053 }
1054 if ($in_group_concat) {
1055 $number_of_brackets_in_group_concat--;
1056 if ($number_of_brackets_in_group_concat == 0) {
1057 $in_group_concat = FALSE;
1058 }
1059 }
1060 }
1061
1062 if ($in_subquery) {
1063 /**
1064 * skip the subquery to avoid setting
1065 * select_expr or table_ref with the contents
1066 * of this subquery; this is to avoid a bug when
1067 * trying to edit the results of
1068 * select * from child where not exists (select id from
1069 * parent where child.parent_id = parent.id);
1070 */
1071 continue;
1072 }
1073 // ==============================================================
1074 if ($arr[$i]['type'] == 'alpha_functionName') {
1075 $upper_data = strtoupper($arr[$i]['data']);
1076 if ($upper_data =='EXTRACT') {
1077 $in_extract = TRUE;
1078 $number_of_brackets_in_extract = 0;
1079 }
1080 if ($upper_data =='GROUP_CONCAT') {
1081 $in_group_concat = TRUE;
1082 $number_of_brackets_in_group_concat = 0;
1083 }
1084 }
1085
1086 // ==============================================================
1087 if ($arr[$i]['type'] == 'alpha_reservedWord'
1088 // && $arr[$i]['forbidden'] == FALSE) {
1089 ) {
1090 // We don't know what type of query yet, so run this
1091 if ($subresult['querytype'] == '') {
1092 $subresult['querytype'] = strtoupper($arr[$i]['data']);
1093 } // end if (querytype was empty)
1094
1095 // Check if we support this type of query
1096 if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
1097 // Skip ahead to the next one if we don't
1098 $seek_queryend = TRUE;
1099 continue;
1100 } // end if (query not supported)
1101
1102 // upper once
1103 $upper_data = strtoupper($arr[$i]['data']);
1104 /**
1105 * @todo reset for each query?
1106 */
1107
1108 if ($upper_data == 'SELECT') {
1109 if ($number_of_brackets > 0) {
1110 $in_subquery = true;
1111 $seen_subquery = true;
1112 // this is a subquery so do not analyze inside it
1113 continue;
1114 }
1115 $seen_from = FALSE;
1116 $previous_was_identifier = FALSE;
1117 $current_select_expr = -1;
1118 $seen_end_of_table_ref = FALSE;
1119 } // end if (data == SELECT)
1120
1121 if ($upper_data =='FROM' && !$in_extract) {
1122 $current_table_ref = -1;
1123 $seen_from = TRUE;
1124 $previous_was_identifier = FALSE;
1125 $save_table_ref = TRUE;
1126 } // end if (data == FROM)
1127
1128 // here, do not 'continue' the loop, as we have more work for
1129 // reserved words below
1130 } // end if (type == alpha_reservedWord)
1131
1132 // ==============================
1133 if ($arr[$i]['type'] == 'quote_backtick'
1134 || $arr[$i]['type'] == 'quote_double'
1135 || $arr[$i]['type'] == 'quote_single'
1136 || $arr[$i]['type'] == 'alpha_identifier'
1137 || ($arr[$i]['type'] == 'alpha_reservedWord'
1138 && $arr[$i]['forbidden'] == FALSE)) {
1139
1140 switch ($arr[$i]['type']) {
1141 case 'alpha_identifier':
1142 case 'alpha_reservedWord':
1143 /**
1144 * this is not a real reservedWord, because it's not
1145 * present in the list of forbidden words, for example
1146 * "storage" which can be used as an identifier
1147 *
1148 * @todo avoid the pretty printing in color in this case
1149 */
1150 $identifier = $arr[$i]['data'];
1151 break;
1152
1153 case 'quote_backtick':
1154 case 'quote_double':
1155 case 'quote_single':
1156 $identifier = PMA_unQuote($arr[$i]['data']);
1157 break;
1158 } // end switch
1159
1160 if ($subresult['querytype'] == 'SELECT'
1161 && ! $in_group_concat
1162 && ! ($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
1163 if (!$seen_from) {
1164 if ($previous_was_identifier && isset($chain)) {
1165 // found alias for this select_expr, save it
1166 // but only if we got something in $chain
1167 // (for example, SELECT COUNT(*) AS cnt
1168 // puts nothing in $chain, so we avoid
1169 // setting the alias)
1170 $alias_for_select_expr = $identifier;
1171 } else {
1172 $chain[] = $identifier;
1173 $previous_was_identifier = TRUE;
1174
1175 } // end if !$previous_was_identifier
1176 } else {
1177 // ($seen_from)
1178 if ($save_table_ref && !$seen_end_of_table_ref) {
1179 if ($previous_was_identifier) {
1180 // found alias for table ref
1181 // save it for later
1182 $alias_for_table_ref = $identifier;
1183 } else {
1184 $chain[] = $identifier;
1185 $previous_was_identifier = TRUE;
1186
1187 } // end if ($previous_was_identifier)
1188 } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1189 } // end if (!$seen_from)
1190 } // end if (querytype SELECT)
1191 } // end if (quote_backtick or double quote or alpha_identifier)
1192
1193 // ===================================
1194 if ($arr[$i]['type'] == 'punct_qualifier') {
1195 // to be able to detect an identifier following another
1196 $previous_was_identifier = FALSE;
1197 continue;
1198 } // end if (punct_qualifier)
1199
1200 /**
1201 * @todo check if 3 identifiers following one another -> error
1202 */
1203
1204 // s a v e a s e l e c t e x p r
1205 // finding a list separator or FROM
1206 // means that we must save the current chain of identifiers
1207 // into a select expression
1208
1209 // for now, we only save a select expression if it contains
1210 // at least one identifier, as we are interested in checking
1211 // the columns and table names, so in "select * from persons",
1212 // the "*" is not saved
1213
1214 if (isset($chain) && !$seen_end_of_table_ref
1215 && ((!$seen_from && $arr[$i]['type'] == 'punct_listsep')
1216 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM'))) {
1217 $size_chain = count($chain);
1218 $current_select_expr++;
1219 $subresult['select_expr'][$current_select_expr] = array(
1220 'expr' => '',
1221 'alias' => '',
1222 'db' => '',
1223 'table_name' => '',
1224 'table_true_name' => '',
1225 'column' => ''
1226 );
1227
1228 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1229 // we had found an alias for this select expression
1230 $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1231 unset($alias_for_select_expr);
1232 }
1233 // there is at least a column
1234 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1235 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1236
1237 // maybe a table
1238 if ($size_chain > 1) {
1239 $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1240 // we assume for now that this is also the true name
1241 $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1242 $subresult['select_expr'][$current_select_expr]['expr']
1243 = $subresult['select_expr'][$current_select_expr]['table_name']
1244 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1245 } // end if ($size_chain > 1)
1246
1247 // maybe a db
1248 if ($size_chain > 2) {
1249 $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1250 $subresult['select_expr'][$current_select_expr]['expr']
1251 = $subresult['select_expr'][$current_select_expr]['db']
1252 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1253 } // end if ($size_chain > 2)
1254 unset($chain);
1255
1256 /**
1257 * @todo explain this:
1258 */
1259 if (($arr[$i]['type'] == 'alpha_reservedWord')
1260 && ($upper_data != 'FROM')) {
1261 $previous_was_identifier = TRUE;
1262 }
1263
1264 } // end if (save a select expr)
1265
1266
1267 //======================================
1268 // s a v e a t a b l e r e f
1269 //======================================
1270
1271 // maybe we just saw the end of table refs
1272 // but the last table ref has to be saved
1273 // or we are at the last token
1274 // or we just got a reserved word
1275 /**
1276 * @todo there could be another query after this one
1277 */
1278
1279 if (isset($chain) && $seen_from && $save_table_ref
1280 && ($arr[$i]['type'] == 'punct_listsep'
1281 || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1282 || $seen_end_of_table_ref
1283 || $i==$size-1)) {
1284
1285 $size_chain = count($chain);
1286 $current_table_ref++;
1287 $subresult['table_ref'][$current_table_ref] = array(
1288 'expr' => '',
1289 'db' => '',
1290 'table_name' => '',
1291 'table_alias' => '',
1292 'table_true_name' => ''
1293 );
1294 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1295 $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1296 unset($alias_for_table_ref);
1297 }
1298 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1299 // we assume for now that this is also the true name
1300 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1301 $subresult['table_ref'][$current_table_ref]['expr']
1302 = $subresult['table_ref'][$current_table_ref]['table_name'];
1303 // maybe a db
1304 if ($size_chain > 1) {
1305 $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1306 $subresult['table_ref'][$current_table_ref]['expr']
1307 = $subresult['table_ref'][$current_table_ref]['db']
1308 . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1309 } // end if ($size_chain > 1)
1310
1311 // add the table alias into the whole expression
1312 $subresult['table_ref'][$current_table_ref]['expr']
1313 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1314
1315 unset($chain);
1316 $previous_was_identifier = TRUE;
1317 //continue;
1318
1319 } // end if (save a table ref)
1320
1321
1322 // when we have found all table refs,
1323 // for each table_ref alias, put the true name of the table
1324 // in the corresponding select expressions
1325
1326 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
1327 for ($tr=0; $tr <= $current_table_ref; $tr++) {
1328 $alias = $subresult['table_ref'][$tr]['table_alias'];
1329 $truename = $subresult['table_ref'][$tr]['table_true_name'];
1330 for ($se=0; $se <= $current_select_expr; $se++) {
1331 if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1332 == $alias) {
1333 $subresult['select_expr'][$se]['table_true_name']
1334 = $truename;
1335 } // end if (found the alias)
1336 } // end for (select expressions)
1337
1338 } // end for (table refs)
1339 } // end if (set the true names)
1340
1341
1342 // e n d i n g l o o p #1
1343 // set the $previous_was_identifier to FALSE if the current
1344 // token is not an identifier
1345 if (($arr[$i]['type'] != 'alpha_identifier')
1346 && ($arr[$i]['type'] != 'quote_double')
1347 && ($arr[$i]['type'] != 'quote_single')
1348 && ($arr[$i]['type'] != 'quote_backtick')) {
1349 $previous_was_identifier = FALSE;
1350 } // end if
1351
1352 // however, if we are on AS, we must keep the $previous_was_identifier
1353 if (($arr[$i]['type'] == 'alpha_reservedWord')
1354 && ($upper_data == 'AS')) {
1355 $previous_was_identifier = TRUE;
1356 }
1357
1358 if (($arr[$i]['type'] == 'alpha_reservedWord')
1359 && ($upper_data =='ON' || $upper_data =='USING')) {
1360 $save_table_ref = FALSE;
1361 } // end if (data == ON)
1362
1363 if (($arr[$i]['type'] == 'alpha_reservedWord')
1364 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1365 $save_table_ref = TRUE;
1366 } // end if (data == JOIN)
1367
1368 /**
1369 * no need to check the end of table ref if we already did
1370 *
1371 * @todo maybe add "&& $seen_from"
1372 */
1373 if (!$seen_end_of_table_ref) {
1374 // if this is the last token, it implies that we have
1375 // seen the end of table references
1376 // Check for the end of table references
1377 //
1378 // Note: if we are analyzing a GROUP_CONCAT clause,
1379 // we might find a word that seems to indicate that
1380 // we have found the end of table refs (like ORDER)
1381 // but it's a modifier of the GROUP_CONCAT so
1382 // it's not the real end of table refs
1383 if (($i == $size-1)
1384 || ($arr[$i]['type'] == 'alpha_reservedWord'
1385 && !$in_group_concat
1386 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1387 $seen_end_of_table_ref = TRUE;
1388 // to be able to save the last table ref, but do not
1389 // set it true if we found a word like "ON" that has
1390 // already set it to false
1391 if (isset($save_table_ref) && $save_table_ref != FALSE) {
1392 $save_table_ref = TRUE;
1393 } //end if
1394
1395 } // end if (check for end of table ref)
1396 } //end if (!$seen_end_of_table_ref)
1397
1398 if ($seen_end_of_table_ref) {
1399 $save_table_ref = FALSE;
1400 } // end if
1401
1402 } // end for $i (loop #1)
1403
1404 //DEBUG
1405 /*
1406 if (isset($current_select_expr)) {
1407 for ($trace=0; $trace<=$current_select_expr; $trace++) {
1408 echo "<br />";
1409 reset ($subresult['select_expr'][$trace]);
1410 while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1411 echo "sel expr $trace $key => $val<br />\n";
1412 }
1413 }
1414
1415 if (isset($current_table_ref)) {
1416 echo "current_table_ref = " . $current_table_ref . "<br>";
1417 for ($trace=0; $trace<=$current_table_ref; $trace++) {
1418
1419 echo "<br />";
1420 reset ($subresult['table_ref'][$trace]);
1421 while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1422 echo "table ref $trace $key => $val<br />\n";
1423 }
1424 }
1425 */
1426 // -------------------------------------------------------
1427
1428
1429 // loop #2: - queryflags
1430 // - querytype (for queries != 'SELECT')
1431 // - section_before_limit, section_after_limit
1432 //
1433 // we will also need this queryflag in loop 2
1434 // so set it here
1435 if (isset($current_table_ref) && $current_table_ref > -1) {
1436 $subresult['queryflags']['select_from'] = 1;
1437 }
1438
1439 $section_before_limit = '';
1440 $section_after_limit = ''; // truly the section after the limit clause
1441 $seen_reserved_word = FALSE;
1442 $seen_group = FALSE;
1443 $seen_order = FALSE;
1444 $seen_order_by = FALSE;
1445 $in_group_by = FALSE; // true when we are inside the GROUP BY clause
1446 $in_order_by = FALSE; // true when we are inside the ORDER BY clause
1447 $in_having = FALSE; // true when we are inside the HAVING clause
1448 $in_select_expr = FALSE; // true when we are inside the select expr clause
1449 $in_where = FALSE; // true when we are inside the WHERE clause
1450 $seen_limit = FALSE; // true if we have seen a LIMIT clause
1451 $in_limit = FALSE; // true when we are inside the LIMIT clause
1452 $after_limit = FALSE; // true when we are after the LIMIT clause
1453 $in_from = FALSE; // true when we are in the FROM clause
1454 $in_group_concat = FALSE;
1455 $first_reserved_word = '';
1456 $current_identifier = '';
1457 $unsorted_query = $arr['raw']; // in case there is no ORDER BY
1458
1459 for ($i = 0; $i < $size; $i++) {
1460 //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1461
1462 // need_confirm
1463 //
1464 // check for reserved words that will have to generate
1465 // a confirmation request later in sql.php
1466 // the cases are:
1467 // DROP TABLE
1468 // DROP DATABASE
1469 // ALTER TABLE... DROP
1470 // DELETE FROM...
1471 //
1472 // this code is not used for confirmations coming from functions.js
1473
1474 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1475 $upper_data = strtoupper($arr[$i]['data']);
1476 if (!$seen_reserved_word) {
1477 $first_reserved_word = $upper_data;
1478 $subresult['querytype'] = $upper_data;
1479 $seen_reserved_word = TRUE;
1480
1481 // if the first reserved word is DROP or DELETE,
1482 // we know this is a query that needs to be confirmed
1483 if ($first_reserved_word=='DROP'
1484 || $first_reserved_word == 'DELETE'
1485 || $first_reserved_word == 'TRUNCATE') {
1486 $subresult['queryflags']['need_confirm'] = 1;
1487 }
1488
1489 if ($first_reserved_word=='SELECT'){
1490 $position_of_first_select = $i;
1491 }
1492
1493 } else {
1494 if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1495 $subresult['queryflags']['need_confirm'] = 1;
1496 }
1497 }
1498
1499 if ($upper_data == 'LIMIT') {
1500 $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1501 $in_limit = TRUE;
1502 $seen_limit = TRUE;
1503 $limit_clause = '';
1504 $in_order_by = FALSE; // @todo maybe others to set FALSE
1505 }
1506
1507 if ($upper_data == 'PROCEDURE') {
1508 $subresult['queryflags']['procedure'] = 1;
1509 $in_limit = FALSE;
1510 $after_limit = TRUE;
1511 }
1512 /**
1513 * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1514 */
1515 if ($upper_data == 'SELECT') {
1516 $in_select_expr = TRUE;
1517 $select_expr_clause = '';
1518 }
1519 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1520 $subresult['queryflags']['distinct'] = 1;
1521 }
1522
1523 if ($upper_data == 'UNION') {
1524 $subresult['queryflags']['union'] = 1;
1525 }
1526
1527 if ($upper_data == 'JOIN') {
1528 $subresult['queryflags']['join'] = 1;
1529 }
1530
1531 if ($upper_data == 'OFFSET') {
1532 $subresult['queryflags']['offset'] = 1;
1533 }
1534
1535 // if this is a real SELECT...FROM
1536 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1537 $in_from = TRUE;
1538 $from_clause = '';
1539 $in_select_expr = FALSE;
1540 }
1541
1542
1543 // (we could have less resetting of variables to FALSE
1544 // if we trust that the query respects the standard
1545 // MySQL order for clauses)
1546
1547 // we use $seen_group and $seen_order because we are looking
1548 // for the BY
1549 if ($upper_data == 'GROUP') {
1550 $seen_group = TRUE;
1551 $seen_order = FALSE;
1552 $in_having = FALSE;
1553 $in_order_by = FALSE;
1554 $in_where = FALSE;
1555 $in_select_expr = FALSE;
1556 $in_from = FALSE;
1557 }
1558 if ($upper_data == 'ORDER' && !$in_group_concat) {
1559 $seen_order = TRUE;
1560 $seen_group = FALSE;
1561 $in_having = FALSE;
1562 $in_group_by = FALSE;
1563 $in_where = FALSE;
1564 $in_select_expr = FALSE;
1565 $in_from = FALSE;
1566 }
1567 if ($upper_data == 'HAVING') {
1568 $in_having = TRUE;
1569 $having_clause = '';
1570 $seen_group = FALSE;
1571 $seen_order = FALSE;
1572 $in_group_by = FALSE;
1573 $in_order_by = FALSE;
1574 $in_where = FALSE;
1575 $in_select_expr = FALSE;
1576 $in_from = FALSE;
1577 }
1578
1579 if ($upper_data == 'WHERE') {
1580 $in_where = TRUE;
1581 $where_clause = '';
1582 $where_clause_identifiers = array();
1583 $seen_group = FALSE;
1584 $seen_order = FALSE;
1585 $in_group_by = FALSE;
1586 $in_order_by = FALSE;
1587 $in_having = FALSE;
1588 $in_select_expr = FALSE;
1589 $in_from = FALSE;
1590 }
1591
1592 if ($upper_data == 'BY') {
1593 if ($seen_group) {
1594 $in_group_by = TRUE;
1595 $group_by_clause = '';
1596 }
1597 if ($seen_order) {
1598 $seen_order_by = TRUE;
1599 // Here we assume that the ORDER BY keywords took
1600 // exactly 8 characters.
1601 // We use PMA_substr() to be charset-safe; otherwise
1602 // if the table name contains accents, the unsorted
1603 // query would be missing some characters.
1604 $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1605 $in_order_by = TRUE;
1606 $order_by_clause = '';
1607 }
1608 }
1609
1610 // if we find one of the words that could end the clause
1611 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1612
1613 $in_group_by = FALSE;
1614 $in_order_by = FALSE;
1615 $in_having = FALSE;
1616 $in_where = FALSE;
1617 $in_select_expr = FALSE;
1618 $in_from = FALSE;
1619 }
1620
1621 } // endif (reservedWord)
1622
1623
1624 // do not add a space after a function name
1625 /**
1626 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1627 */
1628
1629 $sep = ' ';
1630 if ($arr[$i]['type'] == 'alpha_functionName') {
1631 $sep='';
1632 $upper_data = strtoupper($arr[$i]['data']);
1633 if ($upper_data =='GROUP_CONCAT') {
1634 $in_group_concat = TRUE;
1635 $number_of_brackets_in_group_concat = 0;
1636 }
1637 }
1638
1639 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1640 if ($in_group_concat) {
1641 $number_of_brackets_in_group_concat++;
1642 }
1643 }
1644 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1645 if ($in_group_concat) {
1646 $number_of_brackets_in_group_concat--;
1647 if ($number_of_brackets_in_group_concat == 0) {
1648 $in_group_concat = FALSE;
1649 }
1650 }
1651 }
1652
1653 // do not add a space after an identifier if followed by a dot
1654 if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1655 $sep = '';
1656 }
1657
1658 // do not add a space after a dot if followed by an identifier
1659 if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1660 $sep = '';
1661 }
1662
1663 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1664 $select_expr_clause .= $arr[$i]['data'] . $sep;
1665 }
1666 if ($in_from && $upper_data != 'FROM') {
1667 $from_clause .= $arr[$i]['data'] . $sep;
1668 }
1669 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1670 $group_by_clause .= $arr[$i]['data'] . $sep;
1671 }
1672 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1673 // add a space only before ASC or DESC
1674 // not around the dot between dbname and tablename
1675 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1676 $order_by_clause .= $sep;
1677 }
1678 $order_by_clause .= $arr[$i]['data'];
1679 }
1680 if ($in_having && $upper_data != 'HAVING') {
1681 $having_clause .= $arr[$i]['data'] . $sep;
1682 }
1683 if ($in_where && $upper_data != 'WHERE') {
1684 $where_clause .= $arr[$i]['data'] . $sep;
1685
1686 if (($arr[$i]['type'] == 'quote_backtick')
1687 || ($arr[$i]['type'] == 'alpha_identifier')) {
1688 $where_clause_identifiers[] = $arr[$i]['data'];
1689 }
1690 }
1691
1692 // to grab the rest of the query after the ORDER BY clause
1693 if (isset($subresult['queryflags']['select_from'])
1694 && $subresult['queryflags']['select_from'] == 1
1695 && ! $in_order_by
1696 && $seen_order_by
1697 && $upper_data != 'BY') {
1698 $unsorted_query .= $arr[$i]['data'];
1699 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1700 && $arr[$i]['type'] != 'punct_bracket_close_round'
1701 && $arr[$i]['type'] != 'punct') {
1702 $unsorted_query .= $sep;
1703 }
1704 }
1705
1706 if ($in_limit) {
1707 if ($upper_data == 'OFFSET') {
1708 $limit_clause .= $sep;
1709 }
1710 $limit_clause .= $arr[$i]['data'];
1711 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1712 $limit_clause .= $sep;
1713 }
1714 }
1715 if ($after_limit && $seen_limit) {
1716 $section_after_limit .= $arr[$i]['data'] . $sep;
1717 }
1718
1719 // clear $upper_data for next iteration
1720 $upper_data='';
1721 } // end for $i (loop #2)
1722 if (empty($section_before_limit)) {
1723 $section_before_limit = $arr['raw'];
1724 }
1725
1726 // -----------------------------------------------------
1727 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1728 // (for now, check only the first query)
1729 // (for now, identifiers are assumed to be backquoted)
1730
1731 // If we find that we are dealing with a CREATE TABLE query,
1732 // we look for the next punct_bracket_open_round, which
1733 // introduces the fields list. Then, when we find a
1734 // quote_backtick, it must be a field, so we put it into
1735 // the create_table_fields array. Even if this field is
1736 // not a timestamp, it will be useful when logic has been
1737 // added for complete field attributes analysis.
1738
1739 $seen_foreign = FALSE;
1740 $seen_references = FALSE;
1741 $seen_constraint = FALSE;
1742 $foreign_key_number = -1;
1743 $seen_create_table = FALSE;
1744 $seen_create = FALSE;
1745 $seen_alter = FALSE;
1746 $in_create_table_fields = FALSE;
1747 $brackets_level = 0;
1748 $in_timestamp_options = FALSE;
1749 $seen_default = FALSE;
1750
1751 for ($i = 0; $i < $size; $i++) {
1752 // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1753
1754 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1755 $upper_data = strtoupper($arr[$i]['data']);
1756
1757 if ($upper_data == 'NOT' && $in_timestamp_options) {
1758 $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1759
1760 }
1761
1762 if ($upper_data == 'CREATE') {
1763 $seen_create = TRUE;
1764 }
1765
1766 if ($upper_data == 'ALTER') {
1767 $seen_alter = TRUE;
1768 }
1769
1770 if ($upper_data == 'TABLE' && $seen_create) {
1771 $seen_create_table = TRUE;
1772 $create_table_fields = array();
1773 }
1774
1775 if ($upper_data == 'CURRENT_TIMESTAMP') {
1776 if ($in_timestamp_options) {
1777 if ($seen_default) {
1778 $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1779 }
1780 }
1781 }
1782
1783 if ($upper_data == 'CONSTRAINT') {
1784 $foreign_key_number++;
1785 $seen_foreign = FALSE;
1786 $seen_references = FALSE;
1787 $seen_constraint = TRUE;
1788 }
1789 if ($upper_data == 'FOREIGN') {
1790 $seen_foreign = TRUE;
1791 $seen_references = FALSE;
1792 $seen_constraint = FALSE;
1793 }
1794 if ($upper_data == 'REFERENCES') {
1795 $seen_foreign = FALSE;
1796 $seen_references = TRUE;
1797 $seen_constraint = FALSE;
1798 }
1799
1800
1801 // Cases covered:
1802
1803 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1804 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1805
1806 // but we set ['on_delete'] or ['on_cascade'] to
1807 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1808
1809 // ON UPDATE CURRENT_TIMESTAMP
1810
1811 if ($upper_data == 'ON') {
1812 if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1813 $second_upper_data = strtoupper($arr[$i+1]['data']);
1814 if ($second_upper_data == 'DELETE') {
1815 $clause = 'on_delete';
1816 }
1817 if ($second_upper_data == 'UPDATE') {
1818 $clause = 'on_update';
1819 }
1820 if (isset($clause)
1821 && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1822
1823 // ugly workaround because currently, NO is not
1824 // in the list of reserved words in sqlparser.data
1825 // (we got a bug report about not being able to use
1826 // 'no' as an identifier)
1827 || ($arr[$i+2]['type'] == 'alpha_identifier'
1828 && strtoupper($arr[$i+2]['data'])=='NO'))
1829 ) {
1830 $third_upper_data = strtoupper($arr[$i+2]['data']);
1831 if ($third_upper_data == 'CASCADE'
1832 || $third_upper_data == 'RESTRICT') {
1833 $value = $third_upper_data;
1834 } elseif ($third_upper_data == 'SET'
1835 || $third_upper_data == 'NO') {
1836 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1837 $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1838 }
1839 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1840 if ($clause == 'on_update'
1841 && $in_timestamp_options) {
1842 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1843 $seen_default = FALSE;
1844 }
1845
1846 } else {
1847 $value = '';
1848 }
1849 if (!empty($value)) {
1850 $foreign[$foreign_key_number][$clause] = $value;
1851 }
1852 unset($clause);
1853 } // endif (isset($clause))
1854 }
1855 }
1856
1857 } // end of reserved words analysis
1858
1859
1860 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1861 $brackets_level++;
1862 if ($seen_create_table && $brackets_level == 1) {
1863 $in_create_table_fields = TRUE;
1864 }
1865 }
1866
1867
1868 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1869 $brackets_level--;
1870 if ($seen_references) {
1871 $seen_references = FALSE;
1872 }
1873 if ($seen_create_table && $brackets_level == 0) {
1874 $in_create_table_fields = FALSE;
1875 }
1876 }
1877
1878 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1879 $upper_data = strtoupper($arr[$i]['data']);
1880 if ($seen_create_table && $in_create_table_fields) {
1881 if ($upper_data == 'DEFAULT') {
1882 $seen_default = TRUE;
1883 }
1884 }
1885 }
1886
1887 /**
1888 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1889 */
1890 if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1891 $upper_data = strtoupper($arr[$i]['data']);
1892 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1893 $create_table_fields[$current_identifier]['type'] = $upper_data;
1894 if ($upper_data == 'TIMESTAMP') {
1895 $arr[$i]['type'] = 'alpha_columnType';
1896 $in_timestamp_options = TRUE;
1897 } else {
1898 $in_timestamp_options = FALSE;
1899 if ($upper_data == 'CHAR') {
1900 $arr[$i]['type'] = 'alpha_columnType';
1901 }
1902 }
1903 }
1904 }
1905
1906
1907 if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1908
1909 if ($arr[$i]['type'] == 'quote_backtick') {
1910 // remove backquotes
1911 $identifier = PMA_unQuote($arr[$i]['data']);
1912 } else {
1913 $identifier = $arr[$i]['data'];
1914 }
1915
1916 if ($seen_create_table && $in_create_table_fields) {
1917 $current_identifier = $identifier;
1918 // warning: we set this one even for non TIMESTAMP type
1919 $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1920 }
1921
1922 if ($seen_constraint) {
1923 $foreign[$foreign_key_number]['constraint'] = $identifier;
1924 }
1925
1926 if ($seen_foreign && $brackets_level > 0) {
1927 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1928 }
1929
1930 if ($seen_references) {
1931 if ($seen_alter && $brackets_level > 0) {
1932 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1933 // here, the first bracket level corresponds to the
1934 // bracket of CREATE TABLE
1935 // so if we are on level 2, it must be the index list
1936 // of the foreign key REFERENCES
1937 } elseif ($brackets_level > 1) {
1938 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1939 } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1940 // identifier is `db`.`table`
1941 // the first pass will pick the db name
1942 // the next pass will pick the table name
1943 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1944 } else {
1945 // identifier is `table`
1946 $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
1947 }
1948 }
1949 }
1950 } // end for $i (loop #3)
1951
1952
1953 // Fill the $subresult array
1954
1955 if (isset($create_table_fields)) {
1956 $subresult['create_table_fields'] = $create_table_fields;
1957 }
1958
1959 if (isset($foreign)) {
1960 $subresult['foreign_keys'] = $foreign;
1961 }
1962
1963 if (isset($select_expr_clause)) {
1964 $subresult['select_expr_clause'] = $select_expr_clause;
1965 }
1966 if (isset($from_clause)) {
1967 $subresult['from_clause'] = $from_clause;
1968 }
1969 if (isset($group_by_clause)) {
1970 $subresult['group_by_clause'] = $group_by_clause;
1971 }
1972 if (isset($order_by_clause)) {
1973 $subresult['order_by_clause'] = $order_by_clause;
1974 }
1975 if (isset($having_clause)) {
1976 $subresult['having_clause'] = $having_clause;
1977 }
1978 if (isset($limit_clause)) {
1979 $subresult['limit_clause'] = $limit_clause;
1980 }
1981 if (isset($where_clause)) {
1982 $subresult['where_clause'] = $where_clause;
1983 }
1984 if (isset($unsorted_query) && !empty($unsorted_query)) {
1985 $subresult['unsorted_query'] = $unsorted_query;
1986 }
1987 if (isset($where_clause_identifiers)) {
1988 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
1989 }
1990
1991 if (isset($position_of_first_select)) {
1992 $subresult['position_of_first_select'] = $position_of_first_select;
1993 $subresult['section_before_limit'] = $section_before_limit;
1994 $subresult['section_after_limit'] = $section_after_limit;
1995 }
1996
1997 // They are naughty and didn't have a trailing semi-colon,
1998 // then still handle it properly
1999 if ($subresult['querytype'] != '') {
2000 $result[] = $subresult;
2001 }
2002 return $result;
2003 } // end of the "PMA_SQP_analyze()" function
2004
2005
2006 /**
2007 * Colorizes SQL queries html formatted
2008 *
2009 * @todo check why adding a "\n" after the </span> would cause extra blanks
2010 * to be displayed: SELECT p . person_name
2011 * @param array The SQL queries html formatted
2012 *
2013 * @return array The colorized SQL queries
2014 *
2015 * @access public
2016 */
2017 function PMA_SQP_formatHtml_colorize($arr)
2018 {
2019 $i = $GLOBALS['PMA_strpos']($arr['type'], '_');
2020 $class = '';
2021 if ($i > 0) {
2022 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2023 }
2024
2025 $class .= 'syntax_' . $arr['type'];
2026
2027 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2028 } // end of the "PMA_SQP_formatHtml_colorize()" function
2029
2030
2031 /**
2032 * Formats SQL queries to html
2033 *
2034 * @param array The SQL queries
2035 * @param string mode
2036 * @param integer starting token
2037 * @param integer number of tokens to format, -1 = all
2038 *
2039 * @return string The formatted SQL queries
2040 *
2041 * @access public
2042 */
2043 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2044 $number_of_tokens=-1)
2045 {
2046 //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2047 // then check for an array
2048 if (!is_array($arr)) {
2049 return htmlspecialchars($arr);
2050 }
2051 // first check for the SQL parser having hit an error
2052 if (PMA_SQP_isError()) {
2053 return htmlspecialchars($arr['raw']);
2054 }
2055 // else do it properly
2056 switch ($mode) {
2057 case 'color':
2058 $str = '<span class="syntax">';
2059 $html_line_break = '<br />';
2060 break;
2061 case 'query_only':
2062 $str = '';
2063 $html_line_break = "\n";
2064 break;
2065 case 'text':
2066 $str = '';
2067 $html_line_break = '<br />';
2068 break;
2069 } // end switch
2070 $indent = 0;
2071 $bracketlevel = 0;
2072 $functionlevel = 0;
2073 $infunction = FALSE;
2074 $space_punct_listsep = ' ';
2075 $space_punct_listsep_function_name = ' ';
2076 // $space_alpha_reserved_word = '<br />'."\n";
2077 $space_alpha_reserved_word = ' ';
2078
2079 $keywords_with_brackets_1before = array(
2080 'INDEX',
2081 'KEY',
2082 'ON',
2083 'USING'
2084 );
2085 $keywords_with_brackets_1before_cnt = 4;
2086
2087 $keywords_with_brackets_2before = array(
2088 'IGNORE',
2089 'INDEX',
2090 'INTO',
2091 'KEY',
2092 'PRIMARY',
2093 'PROCEDURE',
2094 'REFERENCES',
2095 'UNIQUE',
2096 'USE'
2097 );
2098 // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
2099 $keywords_with_brackets_2before_cnt = 9;
2100
2101 // These reserved words do NOT get a newline placed near them.
2102 $keywords_no_newline = array(
2103 'AS',
2104 'ASC',
2105 'DESC',
2106 'DISTINCT',
2107 'DUPLICATE',
2108 'HOUR',
2109 'INTERVAL',
2110 'IS',
2111 'LIKE',
2112 'NOT',
2113 'NULL',
2114 'ON',
2115 'REGEXP'
2116 );
2117 $keywords_no_newline_cnt = 12;
2118
2119 // These reserved words introduce a privilege list
2120 $keywords_priv_list = array(
2121 'GRANT',
2122 'REVOKE'
2123 );
2124 $keywords_priv_list_cnt = 2;
2125
2126 if ($number_of_tokens == -1) {
2127 $arraysize = $arr['len'];
2128 } else {
2129 $arraysize = $number_of_tokens;
2130 }
2131 $typearr = array();
2132 if ($arraysize >= 0) {
2133 $typearr[0] = '';
2134 $typearr[1] = '';
2135 $typearr[2] = '';
2136 //$typearr[3] = $arr[0]['type'];
2137 $typearr[3] = $arr[$start_token]['type'];
2138 }
2139
2140 $in_priv_list = FALSE;
2141 for ($i = $start_token; $i < $arraysize; $i++) {
2142 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2143 $before = '';
2144 $after = '';
2145 $indent = 0;
2146 // array_shift($typearr);
2147 /*
2148 0 prev2
2149 1 prev
2150 2 current
2151 3 next
2152 */
2153 if (($i + 1) < $arraysize) {
2154 // array_push($typearr, $arr[$i + 1]['type']);
2155 $typearr[4] = $arr[$i + 1]['type'];
2156 } else {
2157 //array_push($typearr, null);
2158 $typearr[4] = '';
2159 }
2160
2161 for ($j=0; $j<4; $j++) {
2162 $typearr[$j] = $typearr[$j + 1];
2163 }
2164
2165 switch ($typearr[2]) {
2166 case 'white_newline':
2167 $before = '';
2168 break;
2169 case 'punct_bracket_open_round':
2170 $bracketlevel++;
2171 $infunction = FALSE;
2172 // Make sure this array is sorted!
2173 if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2174 || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2175 || (($typearr[0] == 'alpha_reservedWord')
2176 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2177 || (($typearr[1] == 'alpha_reservedWord')
2178 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2179 ) {
2180 $functionlevel++;
2181 $infunction = TRUE;
2182 $after .= ' ';
2183 } else {
2184 $indent++;
2185 $after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2186 }
2187 break;
2188 case 'alpha_identifier':
2189 if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2190 $after = '';
2191 $before = '';
2192 }
2193 if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2194 $after .= ' ';
2195 }
2196 break;
2197 case 'punct_user':
2198 case 'punct_qualifier':
2199 $before = '';
2200 $after = '';
2201 break;
2202 case 'punct_listsep':
2203 if ($infunction == TRUE) {
2204 $after .= $space_punct_listsep_function_name;
2205 } else {
2206 $after .= $space_punct_listsep;
2207 }
2208 break;
2209 case 'punct_queryend':
2210 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2211 $after .= $html_line_break;
2212 $after .= $html_line_break;
2213 }
2214 $space_punct_listsep = ' ';
2215 $space_punct_listsep_function_name = ' ';
2216 $space_alpha_reserved_word = ' ';
2217 $in_priv_list = FALSE;
2218 break;
2219 case 'comment_mysql':
2220 case 'comment_ansi':
2221 $after .= $html_line_break;
2222 break;
2223 case 'punct':
2224 $before .= ' ';
2225 // workaround for
2226 // select * from mytable limit 0,-1
2227 // (a side effect of this workaround is that
2228 // select 20 - 9
2229 // becomes
2230 // select 20 -9
2231 // )
2232 if ($typearr[3] != 'digit_integer') {
2233 $after .= ' ';
2234 }
2235 break;
2236 case 'punct_bracket_close_round':
2237 $bracketlevel--;
2238 if ($infunction == TRUE) {
2239 $functionlevel--;
2240 $after .= ' ';
2241 $before .= ' ';
2242 } else {
2243 $indent--;
2244 $before .= ($mode != 'query_only' ? '</div>' : ' ');
2245 }
2246 $infunction = ($functionlevel > 0) ? TRUE : FALSE;
2247 break;
2248 case 'alpha_columnType':
2249 if ($typearr[3] == 'alpha_columnAttrib') {
2250 $after .= ' ';
2251 }
2252 if ($typearr[1] == 'alpha_columnType') {
2253 $before .= ' ';
2254 }
2255 break;
2256 case 'alpha_columnAttrib':
2257
2258 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2259 // COLLATE LATIN1_GENERAL_CI DEFAULT
2260 if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2261 $before .= ' ';
2262 }
2263 if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2264 $after .= ' ';
2265 }
2266 // workaround for
2267 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2268
2269 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2270 $before .= ' ';
2271 }
2272 // workaround for
2273 // select * from mysql.user where binary user="root"
2274 // binary is marked as alpha_columnAttrib
2275 // but should be marked as a reserved word
2276 if (strtoupper($arr[$i]['data']) == 'BINARY'
2277 && $typearr[3] == 'alpha_identifier') {
2278 $after .= ' ';
2279 }
2280 break;
2281 case 'alpha_reservedWord':
2282 // do not uppercase the reserved word if we are calling
2283 // this function in query_only mode, because we need
2284 // the original query (otherwise we get problems with
2285 // semi-reserved words like "storage" which is legal
2286 // as an identifier name)
2287
2288 if ($mode != 'query_only') {
2289 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2290 }
2291
2292 if ((($typearr[1] != 'alpha_reservedWord')
2293 || (($typearr[1] == 'alpha_reservedWord')
2294 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2295 && ($typearr[1] != 'punct_level_plus')
2296 && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2297 // do not put a space before the first token, because
2298 // we use a lot of pattern matching checking for the
2299 // first reserved word at beginning of query
2300 // so do not put a newline before
2301 //
2302 // also we must not be inside a privilege list
2303 if ($i > 0) {
2304 // the alpha_identifier exception is there to
2305 // catch cases like
2306 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2307 // (else, we get mydb.mytableTO)
2308 //
2309 // the quote_single exception is there to
2310 // catch cases like
2311 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2312 /**
2313 * @todo fix all cases and find why this happens
2314 */
2315
2316 if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2317 $before .= $space_alpha_reserved_word;
2318 }
2319 } else {
2320 // on first keyword, check if it introduces a
2321 // privilege list
2322 if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2323 $in_priv_list = TRUE;
2324 }
2325 }
2326 } else {
2327 $before .= ' ';
2328 }
2329
2330 switch ($arr[$i]['data']) {
2331 case 'CREATE':
2332 if (!$in_priv_list) {
2333 $space_punct_listsep = $html_line_break;
2334 $space_alpha_reserved_word = ' ';
2335 }
2336 break;
2337 case 'EXPLAIN':
2338 case 'DESCRIBE':
2339 case 'SET':
2340 case 'ALTER':
2341 case 'DELETE':
2342 case 'SHOW':
2343 case 'DROP':
2344 case 'UPDATE':
2345 case 'TRUNCATE':
2346 case 'ANALYZE':
2347 case 'ANALYSE':
2348 if (!$in_priv_list) {
2349 $space_punct_listsep = $html_line_break;
2350 $space_alpha_reserved_word = ' ';
2351 }
2352 break;
2353 case 'INSERT':
2354 case 'REPLACE':
2355 if (!$in_priv_list) {
2356 $space_punct_listsep = $html_line_break;
2357 $space_alpha_reserved_word = $html_line_break;
2358 }
2359 break;
2360 case 'VALUES':
2361 $space_punct_listsep = ' ';
2362 $space_alpha_reserved_word = $html_line_break;
2363 break;
2364 case 'SELECT':
2365 $space_punct_listsep = ' ';
2366 $space_alpha_reserved_word = $html_line_break;
2367 break;
2368 default:
2369 break;
2370 } // end switch ($arr[$i]['data'])
2371
2372 $after .= ' ';
2373 break;
2374 case 'digit_integer':
2375 case 'digit_float':
2376 case 'digit_hex':
2377 /**
2378 * @todo could there be other types preceding a digit?
2379 */
2380 if ($typearr[1] == 'alpha_reservedWord') {
2381 $after .= ' ';
2382 }
2383 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2384 $after .= ' ';
2385 }
2386 if ($typearr[1] == 'alpha_columnAttrib') {
2387 $before .= ' ';
2388 }
2389 break;
2390 case 'alpha_variable':
2391 $after = ' ';
2392 break;
2393 case 'quote_double':
2394 case 'quote_single':
2395 // workaround: for the query
2396 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2397 // the @ is incorrectly marked as alpha_variable
2398 // in the parser, and here, the '%' gets a blank before,
2399 // which is a syntax error
2400 if ($typearr[1] != 'punct_user') {
2401 $before .= ' ';
2402 }
2403 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2404 $after .= ' ';
2405 }
2406 break;
2407 case 'quote_backtick':
2408 // here we check for punct_user to handle correctly
2409 // DEFINER = `username`@`%`
2410 // where @ is the punct_user and `%` is the quote_backtick
2411 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2412 $after .= ' ';
2413 }
2414 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2415 $before .= ' ';
2416 }
2417 break;
2418 default:
2419 break;
2420 } // end switch ($typearr[2])
2421
2422 /*
2423 if ($typearr[3] != 'punct_qualifier') {
2424 $after .= ' ';
2425 }
2426 $after .= "\n";
2427 */
2428 $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
2429 } // end for
2430 if ($mode=='color') {
2431 $str .= '</span>';
2432 }
2433
2434 return $str;
2435 } // end of the "PMA_SQP_formatHtml()" function
2436 }
2437
2438 /**
2439 * Builds a CSS rule used for html formatted SQL queries
2440 *
2441 * @param string The class name
2442 * @param string The property name
2443 * @param string The property value
2444 *
2445 * @return string The CSS rule
2446 *
2447 * @access public
2448 *
2449 * @see PMA_SQP_buildCssData()
2450 */
2451 function PMA_SQP_buildCssRule($classname, $property, $value)
2452 {
2453 $str = '.' . $classname . ' {';
2454 if ($value != '') {
2455 $str .= $property . ': ' . $value . ';';
2456 }
2457 $str .= '}' . "\n";
2458
2459 return $str;
2460 } // end of the "PMA_SQP_buildCssRule()" function
2461
2462
2463 /**
2464 * Builds CSS rules used for html formatted SQL queries
2465 *
2466 * @return string The CSS rules set
2467 *
2468 * @access public
2469 *
2470 * @global array The current PMA configuration
2471 *
2472 * @see PMA_SQP_buildCssRule()
2473 */
2474 function PMA_SQP_buildCssData()
2475 {
2476 global $cfg;
2477
2478 $css_string = '';
2479 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2480 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2481 }
2482
2483 for ($i = 0; $i < 8; $i++) {
2484 $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2485 }
2486
2487 return $css_string;
2488 } // end of the "PMA_SQP_buildCssData()" function
2489
2490 if (! defined('PMA_MINIMUM_COMMON')) {
2491 /**
2492 * Gets SQL queries with no format
2493 *
2494 * @param array The SQL queries list
2495 *
2496 * @return string The SQL queries with no format
2497 *
2498 * @access public
2499 */
2500 function PMA_SQP_formatNone($arr)
2501 {
2502 $formatted_sql = htmlspecialchars($arr['raw']);
2503 $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2504
2505 return $formatted_sql;
2506 } // end of the "PMA_SQP_formatNone()" function
2507
2508
2509 /**
2510 * Gets SQL queries in text format
2511 *
2512 * @todo WRITE THIS!
2513 * @param array The SQL queries list
2514 *
2515 * @return string The SQL queries in text format
2516 *
2517 * @access public
2518 */
2519 function PMA_SQP_formatText($arr)
2520 {
2521 return PMA_SQP_formatNone($arr);
2522 } // end of the "PMA_SQP_formatText()" function
2523 } // end if: minimal common.lib needed?
2524
2525 ?>
Drizzle / GSOC 2011