?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
group related directives
[phpmyadmin/crack.git] / libraries / sqlparser.lib.php
blobb9d9da8767f13c8469ea81a7bc9fac85bd6e671a
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 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 $number_of_brackets = 0;
1459 $in_subquery = false;
1460
1461 for ($i = 0; $i < $size; $i++) {
1462 //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1463
1464 // need_confirm
1465 //
1466 // check for reserved words that will have to generate
1467 // a confirmation request later in sql.php
1468 // the cases are:
1469 // DROP TABLE
1470 // DROP DATABASE
1471 // ALTER TABLE... DROP
1472 // DELETE FROM...
1473 //
1474 // this code is not used for confirmations coming from functions.js
1475
1476 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1477 $number_of_brackets++;
1478 }
1479
1480 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1481 $number_of_brackets--;
1482 if ($number_of_brackets == 0) {
1483 $in_subquery = false;
1484 }
1485 }
1486
1487 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1488 $upper_data = strtoupper($arr[$i]['data']);
1489
1490 if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
1491 $in_subquery = true;
1492 }
1493
1494 if (!$seen_reserved_word) {
1495 $first_reserved_word = $upper_data;
1496 $subresult['querytype'] = $upper_data;
1497 $seen_reserved_word = TRUE;
1498
1499 // if the first reserved word is DROP or DELETE,
1500 // we know this is a query that needs to be confirmed
1501 if ($first_reserved_word=='DROP'
1502 || $first_reserved_word == 'DELETE'
1503 || $first_reserved_word == 'TRUNCATE') {
1504 $subresult['queryflags']['need_confirm'] = 1;
1505 }
1506
1507 if ($first_reserved_word=='SELECT'){
1508 $position_of_first_select = $i;
1509 }
1510
1511 } else {
1512 if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1513 $subresult['queryflags']['need_confirm'] = 1;
1514 }
1515 }
1516
1517 if ($upper_data == 'LIMIT' && ! $in_subquery) {
1518 $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1519 $in_limit = TRUE;
1520 $seen_limit = TRUE;
1521 $limit_clause = '';
1522 $in_order_by = FALSE; // @todo maybe others to set FALSE
1523 }
1524
1525 if ($upper_data == 'PROCEDURE') {
1526 $subresult['queryflags']['procedure'] = 1;
1527 $in_limit = FALSE;
1528 $after_limit = TRUE;
1529 }
1530 /**
1531 * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1532 */
1533 if ($upper_data == 'SELECT') {
1534 $in_select_expr = TRUE;
1535 $select_expr_clause = '';
1536 }
1537 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1538 $subresult['queryflags']['distinct'] = 1;
1539 }
1540
1541 if ($upper_data == 'UNION') {
1542 $subresult['queryflags']['union'] = 1;
1543 }
1544
1545 if ($upper_data == 'JOIN') {
1546 $subresult['queryflags']['join'] = 1;
1547 }
1548
1549 if ($upper_data == 'OFFSET') {
1550 $subresult['queryflags']['offset'] = 1;
1551 }
1552
1553 // if this is a real SELECT...FROM
1554 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1555 $in_from = TRUE;
1556 $from_clause = '';
1557 $in_select_expr = FALSE;
1558 }
1559
1560
1561 // (we could have less resetting of variables to FALSE
1562 // if we trust that the query respects the standard
1563 // MySQL order for clauses)
1564
1565 // we use $seen_group and $seen_order because we are looking
1566 // for the BY
1567 if ($upper_data == 'GROUP') {
1568 $seen_group = TRUE;
1569 $seen_order = FALSE;
1570 $in_having = FALSE;
1571 $in_order_by = FALSE;
1572 $in_where = FALSE;
1573 $in_select_expr = FALSE;
1574 $in_from = FALSE;
1575 }
1576 if ($upper_data == 'ORDER' && !$in_group_concat) {
1577 $seen_order = TRUE;
1578 $seen_group = FALSE;
1579 $in_having = FALSE;
1580 $in_group_by = FALSE;
1581 $in_where = FALSE;
1582 $in_select_expr = FALSE;
1583 $in_from = FALSE;
1584 }
1585 if ($upper_data == 'HAVING') {
1586 $in_having = TRUE;
1587 $having_clause = '';
1588 $seen_group = FALSE;
1589 $seen_order = FALSE;
1590 $in_group_by = FALSE;
1591 $in_order_by = FALSE;
1592 $in_where = FALSE;
1593 $in_select_expr = FALSE;
1594 $in_from = FALSE;
1595 }
1596
1597 if ($upper_data == 'WHERE') {
1598 $in_where = TRUE;
1599 $where_clause = '';
1600 $where_clause_identifiers = array();
1601 $seen_group = FALSE;
1602 $seen_order = FALSE;
1603 $in_group_by = FALSE;
1604 $in_order_by = FALSE;
1605 $in_having = FALSE;
1606 $in_select_expr = FALSE;
1607 $in_from = FALSE;
1608 }
1609
1610 if ($upper_data == 'BY') {
1611 if ($seen_group) {
1612 $in_group_by = TRUE;
1613 $group_by_clause = '';
1614 }
1615 if ($seen_order) {
1616 $seen_order_by = TRUE;
1617 // Here we assume that the ORDER BY keywords took
1618 // exactly 8 characters.
1619 // We use PMA_substr() to be charset-safe; otherwise
1620 // if the table name contains accents, the unsorted
1621 // query would be missing some characters.
1622 $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1623 $in_order_by = TRUE;
1624 $order_by_clause = '';
1625 }
1626 }
1627
1628 // if we find one of the words that could end the clause
1629 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1630
1631 $in_group_by = FALSE;
1632 $in_order_by = FALSE;
1633 $in_having = FALSE;
1634 $in_where = FALSE;
1635 $in_select_expr = FALSE;
1636 $in_from = FALSE;
1637 }
1638
1639 } // endif (reservedWord)
1640
1641
1642 // do not add a space after a function name
1643 /**
1644 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1645 */
1646
1647 $sep = ' ';
1648 if ($arr[$i]['type'] == 'alpha_functionName') {
1649 $sep='';
1650 $upper_data = strtoupper($arr[$i]['data']);
1651 if ($upper_data =='GROUP_CONCAT') {
1652 $in_group_concat = TRUE;
1653 $number_of_brackets_in_group_concat = 0;
1654 }
1655 }
1656
1657 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1658 if ($in_group_concat) {
1659 $number_of_brackets_in_group_concat++;
1660 }
1661 }
1662 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1663 if ($in_group_concat) {
1664 $number_of_brackets_in_group_concat--;
1665 if ($number_of_brackets_in_group_concat == 0) {
1666 $in_group_concat = FALSE;
1667 }
1668 }
1669 }
1670
1671 // do not add a space after an identifier if followed by a dot
1672 if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1673 $sep = '';
1674 }
1675
1676 // do not add a space after a dot if followed by an identifier
1677 if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1678 $sep = '';
1679 }
1680
1681 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1682 $select_expr_clause .= $arr[$i]['data'] . $sep;
1683 }
1684 if ($in_from && $upper_data != 'FROM') {
1685 $from_clause .= $arr[$i]['data'] . $sep;
1686 }
1687 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1688 $group_by_clause .= $arr[$i]['data'] . $sep;
1689 }
1690 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1691 // add a space only before ASC or DESC
1692 // not around the dot between dbname and tablename
1693 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1694 $order_by_clause .= $sep;
1695 }
1696 $order_by_clause .= $arr[$i]['data'];
1697 }
1698 if ($in_having && $upper_data != 'HAVING') {
1699 $having_clause .= $arr[$i]['data'] . $sep;
1700 }
1701 if ($in_where && $upper_data != 'WHERE') {
1702 $where_clause .= $arr[$i]['data'] . $sep;
1703
1704 if (($arr[$i]['type'] == 'quote_backtick')
1705 || ($arr[$i]['type'] == 'alpha_identifier')) {
1706 $where_clause_identifiers[] = $arr[$i]['data'];
1707 }
1708 }
1709
1710 // to grab the rest of the query after the ORDER BY clause
1711 if (isset($subresult['queryflags']['select_from'])
1712 && $subresult['queryflags']['select_from'] == 1
1713 && ! $in_order_by
1714 && $seen_order_by
1715 && $upper_data != 'BY') {
1716 $unsorted_query .= $arr[$i]['data'];
1717 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1718 && $arr[$i]['type'] != 'punct_bracket_close_round'
1719 && $arr[$i]['type'] != 'punct') {
1720 $unsorted_query .= $sep;
1721 }
1722 }
1723
1724 if ($in_limit) {
1725 if ($upper_data == 'OFFSET') {
1726 $limit_clause .= $sep;
1727 }
1728 $limit_clause .= $arr[$i]['data'];
1729 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1730 $limit_clause .= $sep;
1731 }
1732 }
1733 if ($after_limit && $seen_limit) {
1734 $section_after_limit .= $arr[$i]['data'] . $sep;
1735 }
1736
1737 // clear $upper_data for next iteration
1738 $upper_data='';
1739 } // end for $i (loop #2)
1740 if (empty($section_before_limit)) {
1741 $section_before_limit = $arr['raw'];
1742 }
1743
1744 // -----------------------------------------------------
1745 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1746 // (for now, check only the first query)
1747 // (for now, identifiers are assumed to be backquoted)
1748
1749 // If we find that we are dealing with a CREATE TABLE query,
1750 // we look for the next punct_bracket_open_round, which
1751 // introduces the fields list. Then, when we find a
1752 // quote_backtick, it must be a field, so we put it into
1753 // the create_table_fields array. Even if this field is
1754 // not a timestamp, it will be useful when logic has been
1755 // added for complete field attributes analysis.
1756
1757 $seen_foreign = FALSE;
1758 $seen_references = FALSE;
1759 $seen_constraint = FALSE;
1760 $foreign_key_number = -1;
1761 $seen_create_table = FALSE;
1762 $seen_create = FALSE;
1763 $seen_alter = FALSE;
1764 $in_create_table_fields = FALSE;
1765 $brackets_level = 0;
1766 $in_timestamp_options = FALSE;
1767 $seen_default = FALSE;
1768
1769 for ($i = 0; $i < $size; $i++) {
1770 // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1771
1772 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1773 $upper_data = strtoupper($arr[$i]['data']);
1774
1775 if ($upper_data == 'NOT' && $in_timestamp_options) {
1776 $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1777
1778 }
1779
1780 if ($upper_data == 'CREATE') {
1781 $seen_create = TRUE;
1782 }
1783
1784 if ($upper_data == 'ALTER') {
1785 $seen_alter = TRUE;
1786 }
1787
1788 if ($upper_data == 'TABLE' && $seen_create) {
1789 $seen_create_table = TRUE;
1790 $create_table_fields = array();
1791 }
1792
1793 if ($upper_data == 'CURRENT_TIMESTAMP') {
1794 if ($in_timestamp_options) {
1795 if ($seen_default) {
1796 $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1797 }
1798 }
1799 }
1800
1801 if ($upper_data == 'CONSTRAINT') {
1802 $foreign_key_number++;
1803 $seen_foreign = FALSE;
1804 $seen_references = FALSE;
1805 $seen_constraint = TRUE;
1806 }
1807 if ($upper_data == 'FOREIGN') {
1808 $seen_foreign = TRUE;
1809 $seen_references = FALSE;
1810 $seen_constraint = FALSE;
1811 }
1812 if ($upper_data == 'REFERENCES') {
1813 $seen_foreign = FALSE;
1814 $seen_references = TRUE;
1815 $seen_constraint = FALSE;
1816 }
1817
1818
1819 // Cases covered:
1820
1821 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1822 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1823
1824 // but we set ['on_delete'] or ['on_cascade'] to
1825 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1826
1827 // ON UPDATE CURRENT_TIMESTAMP
1828
1829 if ($upper_data == 'ON') {
1830 if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1831 $second_upper_data = strtoupper($arr[$i+1]['data']);
1832 if ($second_upper_data == 'DELETE') {
1833 $clause = 'on_delete';
1834 }
1835 if ($second_upper_data == 'UPDATE') {
1836 $clause = 'on_update';
1837 }
1838 if (isset($clause)
1839 && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1840
1841 // ugly workaround because currently, NO is not
1842 // in the list of reserved words in sqlparser.data
1843 // (we got a bug report about not being able to use
1844 // 'no' as an identifier)
1845 || ($arr[$i+2]['type'] == 'alpha_identifier'
1846 && strtoupper($arr[$i+2]['data'])=='NO'))
1847 ) {
1848 $third_upper_data = strtoupper($arr[$i+2]['data']);
1849 if ($third_upper_data == 'CASCADE'
1850 || $third_upper_data == 'RESTRICT') {
1851 $value = $third_upper_data;
1852 } elseif ($third_upper_data == 'SET'
1853 || $third_upper_data == 'NO') {
1854 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1855 $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1856 }
1857 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1858 if ($clause == 'on_update'
1859 && $in_timestamp_options) {
1860 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1861 $seen_default = FALSE;
1862 }
1863
1864 } else {
1865 $value = '';
1866 }
1867 if (!empty($value)) {
1868 $foreign[$foreign_key_number][$clause] = $value;
1869 }
1870 unset($clause);
1871 } // endif (isset($clause))
1872 }
1873 }
1874
1875 } // end of reserved words analysis
1876
1877
1878 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1879 $brackets_level++;
1880 if ($seen_create_table && $brackets_level == 1) {
1881 $in_create_table_fields = TRUE;
1882 }
1883 }
1884
1885
1886 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1887 $brackets_level--;
1888 if ($seen_references) {
1889 $seen_references = FALSE;
1890 }
1891 if ($seen_create_table && $brackets_level == 0) {
1892 $in_create_table_fields = FALSE;
1893 }
1894 }
1895
1896 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1897 $upper_data = strtoupper($arr[$i]['data']);
1898 if ($seen_create_table && $in_create_table_fields) {
1899 if ($upper_data == 'DEFAULT') {
1900 $seen_default = TRUE;
1901 }
1902 }
1903 }
1904
1905 /**
1906 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1907 */
1908 if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1909 $upper_data = strtoupper($arr[$i]['data']);
1910 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1911 $create_table_fields[$current_identifier]['type'] = $upper_data;
1912 if ($upper_data == 'TIMESTAMP') {
1913 $arr[$i]['type'] = 'alpha_columnType';
1914 $in_timestamp_options = TRUE;
1915 } else {
1916 $in_timestamp_options = FALSE;
1917 if ($upper_data == 'CHAR') {
1918 $arr[$i]['type'] = 'alpha_columnType';
1919 }
1920 }
1921 }
1922 }
1923
1924
1925 if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1926
1927 if ($arr[$i]['type'] == 'quote_backtick') {
1928 // remove backquotes
1929 $identifier = PMA_unQuote($arr[$i]['data']);
1930 } else {
1931 $identifier = $arr[$i]['data'];
1932 }
1933
1934 if ($seen_create_table && $in_create_table_fields) {
1935 $current_identifier = $identifier;
1936 // warning: we set this one even for non TIMESTAMP type
1937 $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1938 }
1939
1940 if ($seen_constraint) {
1941 $foreign[$foreign_key_number]['constraint'] = $identifier;
1942 }
1943
1944 if ($seen_foreign && $brackets_level > 0) {
1945 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1946 }
1947
1948 if ($seen_references) {
1949 if ($seen_alter && $brackets_level > 0) {
1950 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1951 // here, the first bracket level corresponds to the
1952 // bracket of CREATE TABLE
1953 // so if we are on level 2, it must be the index list
1954 // of the foreign key REFERENCES
1955 } elseif ($brackets_level > 1) {
1956 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1957 } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1958 // identifier is `db`.`table`
1959 // the first pass will pick the db name
1960 // the next pass will pick the table name
1961 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1962 } else {
1963 // identifier is `table`
1964 $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
1965 }
1966 }
1967 }
1968 } // end for $i (loop #3)
1969
1970
1971 // Fill the $subresult array
1972
1973 if (isset($create_table_fields)) {
1974 $subresult['create_table_fields'] = $create_table_fields;
1975 }
1976
1977 if (isset($foreign)) {
1978 $subresult['foreign_keys'] = $foreign;
1979 }
1980
1981 if (isset($select_expr_clause)) {
1982 $subresult['select_expr_clause'] = $select_expr_clause;
1983 }
1984 if (isset($from_clause)) {
1985 $subresult['from_clause'] = $from_clause;
1986 }
1987 if (isset($group_by_clause)) {
1988 $subresult['group_by_clause'] = $group_by_clause;
1989 }
1990 if (isset($order_by_clause)) {
1991 $subresult['order_by_clause'] = $order_by_clause;
1992 }
1993 if (isset($having_clause)) {
1994 $subresult['having_clause'] = $having_clause;
1995 }
1996 if (isset($limit_clause)) {
1997 $subresult['limit_clause'] = $limit_clause;
1998 }
1999 if (isset($where_clause)) {
2000 $subresult['where_clause'] = $where_clause;
2001 }
2002 if (isset($unsorted_query) && !empty($unsorted_query)) {
2003 $subresult['unsorted_query'] = $unsorted_query;
2004 }
2005 if (isset($where_clause_identifiers)) {
2006 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
2007 }
2008
2009 if (isset($position_of_first_select)) {
2010 $subresult['position_of_first_select'] = $position_of_first_select;
2011 $subresult['section_before_limit'] = $section_before_limit;
2012 $subresult['section_after_limit'] = $section_after_limit;
2013 }
2014
2015 // They are naughty and didn't have a trailing semi-colon,
2016 // then still handle it properly
2017 if ($subresult['querytype'] != '') {
2018 $result[] = $subresult;
2019 }
2020 return $result;
2021 } // end of the "PMA_SQP_analyze()" function
2022
2023
2024 /**
2025 * Colorizes SQL queries html formatted
2026 *
2027 * @todo check why adding a "\n" after the </span> would cause extra blanks
2028 * to be displayed: SELECT p . person_name
2029 * @param array The SQL queries html formatted
2030 *
2031 * @return array The colorized SQL queries
2032 *
2033 * @access public
2034 */
2035 function PMA_SQP_formatHtml_colorize($arr)
2036 {
2037 $i = $GLOBALS['PMA_strpos']($arr['type'], '_');
2038 $class = '';
2039 if ($i > 0) {
2040 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2041 }
2042
2043 $class .= 'syntax_' . $arr['type'];
2044
2045 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2046 } // end of the "PMA_SQP_formatHtml_colorize()" function
2047
2048
2049 /**
2050 * Formats SQL queries to html
2051 *
2052 * @param array The SQL queries
2053 * @param string mode
2054 * @param integer starting token
2055 * @param integer number of tokens to format, -1 = all
2056 *
2057 * @return string The formatted SQL queries
2058 *
2059 * @access public
2060 */
2061 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2062 $number_of_tokens=-1)
2063 {
2064 //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2065 // then check for an array
2066 if (!is_array($arr)) {
2067 return htmlspecialchars($arr);
2068 }
2069 // first check for the SQL parser having hit an error
2070 if (PMA_SQP_isError()) {
2071 return htmlspecialchars($arr['raw']);
2072 }
2073 // else do it properly
2074 switch ($mode) {
2075 case 'color':
2076 $str = '<span class="syntax">';
2077 $html_line_break = '<br />';
2078 break;
2079 case 'query_only':
2080 $str = '';
2081 $html_line_break = "\n";
2082 break;
2083 case 'text':
2084 $str = '';
2085 $html_line_break = '<br />';
2086 break;
2087 } // end switch
2088 $indent = 0;
2089 $bracketlevel = 0;
2090 $functionlevel = 0;
2091 $infunction = FALSE;
2092 $space_punct_listsep = ' ';
2093 $space_punct_listsep_function_name = ' ';
2094 // $space_alpha_reserved_word = '<br />'."\n";
2095 $space_alpha_reserved_word = ' ';
2096
2097 $keywords_with_brackets_1before = array(
2098 'INDEX',
2099 'KEY',
2100 'ON',
2101 'USING'
2102 );
2103 $keywords_with_brackets_1before_cnt = 4;
2104
2105 $keywords_with_brackets_2before = array(
2106 'IGNORE',
2107 'INDEX',
2108 'INTO',
2109 'KEY',
2110 'PRIMARY',
2111 'PROCEDURE',
2112 'REFERENCES',
2113 'UNIQUE',
2114 'USE'
2115 );
2116 // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
2117 $keywords_with_brackets_2before_cnt = 9;
2118
2119 // These reserved words do NOT get a newline placed near them.
2120 $keywords_no_newline = array(
2121 'AS',
2122 'ASC',
2123 'DESC',
2124 'DISTINCT',
2125 'DUPLICATE',
2126 'HOUR',
2127 'INTERVAL',
2128 'IS',
2129 'LIKE',
2130 'NOT',
2131 'NULL',
2132 'ON',
2133 'REGEXP'
2134 );
2135 $keywords_no_newline_cnt = 12;
2136
2137 // These reserved words introduce a privilege list
2138 $keywords_priv_list = array(
2139 'GRANT',
2140 'REVOKE'
2141 );
2142 $keywords_priv_list_cnt = 2;
2143
2144 if ($number_of_tokens == -1) {
2145 $arraysize = $arr['len'];
2146 } else {
2147 $arraysize = $number_of_tokens;
2148 }
2149 $typearr = array();
2150 if ($arraysize >= 0) {
2151 $typearr[0] = '';
2152 $typearr[1] = '';
2153 $typearr[2] = '';
2154 //$typearr[3] = $arr[0]['type'];
2155 $typearr[3] = $arr[$start_token]['type'];
2156 }
2157
2158 $in_priv_list = FALSE;
2159 for ($i = $start_token; $i < $arraysize; $i++) {
2160 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2161 $before = '';
2162 $after = '';
2163 $indent = 0;
2164 // array_shift($typearr);
2165 /*
2166 0 prev2
2167 1 prev
2168 2 current
2169 3 next
2170 */
2171 if (($i + 1) < $arraysize) {
2172 // array_push($typearr, $arr[$i + 1]['type']);
2173 $typearr[4] = $arr[$i + 1]['type'];
2174 } else {
2175 //array_push($typearr, null);
2176 $typearr[4] = '';
2177 }
2178
2179 for ($j=0; $j<4; $j++) {
2180 $typearr[$j] = $typearr[$j + 1];
2181 }
2182
2183 switch ($typearr[2]) {
2184 case 'white_newline':
2185 $before = '';
2186 break;
2187 case 'punct_bracket_open_round':
2188 $bracketlevel++;
2189 $infunction = FALSE;
2190 // Make sure this array is sorted!
2191 if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2192 || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2193 || (($typearr[0] == 'alpha_reservedWord')
2194 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2195 || (($typearr[1] == 'alpha_reservedWord')
2196 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2197 ) {
2198 $functionlevel++;
2199 $infunction = TRUE;
2200 $after .= ' ';
2201 } else {
2202 $indent++;
2203 $after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2204 }
2205 break;
2206 case 'alpha_identifier':
2207 if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2208 $after = '';
2209 $before = '';
2210 }
2211 if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2212 $after .= ' ';
2213 }
2214 break;
2215 case 'punct_user':
2216 case 'punct_qualifier':
2217 $before = '';
2218 $after = '';
2219 break;
2220 case 'punct_listsep':
2221 if ($infunction == TRUE) {
2222 $after .= $space_punct_listsep_function_name;
2223 } else {
2224 $after .= $space_punct_listsep;
2225 }
2226 break;
2227 case 'punct_queryend':
2228 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2229 $after .= $html_line_break;
2230 $after .= $html_line_break;
2231 }
2232 $space_punct_listsep = ' ';
2233 $space_punct_listsep_function_name = ' ';
2234 $space_alpha_reserved_word = ' ';
2235 $in_priv_list = FALSE;
2236 break;
2237 case 'comment_mysql':
2238 case 'comment_ansi':
2239 $after .= $html_line_break;
2240 break;
2241 case 'punct':
2242 $before .= ' ';
2243 // workaround for
2244 // select * from mytable limit 0,-1
2245 // (a side effect of this workaround is that
2246 // select 20 - 9
2247 // becomes
2248 // select 20 -9
2249 // )
2250 if ($typearr[3] != 'digit_integer') {
2251 $after .= ' ';
2252 }
2253 break;
2254 case 'punct_bracket_close_round':
2255 $bracketlevel--;
2256 if ($infunction == TRUE) {
2257 $functionlevel--;
2258 $after .= ' ';
2259 $before .= ' ';
2260 } else {
2261 $indent--;
2262 $before .= ($mode != 'query_only' ? '</div>' : ' ');
2263 }
2264 $infunction = ($functionlevel > 0) ? TRUE : FALSE;
2265 break;
2266 case 'alpha_columnType':
2267 if ($typearr[3] == 'alpha_columnAttrib') {
2268 $after .= ' ';
2269 }
2270 if ($typearr[1] == 'alpha_columnType') {
2271 $before .= ' ';
2272 }
2273 break;
2274 case 'alpha_columnAttrib':
2275
2276 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2277 // COLLATE LATIN1_GENERAL_CI DEFAULT
2278 if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2279 $before .= ' ';
2280 }
2281 if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2282 $after .= ' ';
2283 }
2284 // workaround for
2285 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2286
2287 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2288 $before .= ' ';
2289 }
2290 // workaround for
2291 // select * from mysql.user where binary user="root"
2292 // binary is marked as alpha_columnAttrib
2293 // but should be marked as a reserved word
2294 if (strtoupper($arr[$i]['data']) == 'BINARY'
2295 && $typearr[3] == 'alpha_identifier') {
2296 $after .= ' ';
2297 }
2298 break;
2299 case 'alpha_reservedWord':
2300 // do not uppercase the reserved word if we are calling
2301 // this function in query_only mode, because we need
2302 // the original query (otherwise we get problems with
2303 // semi-reserved words like "storage" which is legal
2304 // as an identifier name)
2305
2306 if ($mode != 'query_only') {
2307 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2308 }
2309
2310 if ((($typearr[1] != 'alpha_reservedWord')
2311 || (($typearr[1] == 'alpha_reservedWord')
2312 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2313 && ($typearr[1] != 'punct_level_plus')
2314 && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2315 // do not put a space before the first token, because
2316 // we use a lot of pattern matching checking for the
2317 // first reserved word at beginning of query
2318 // so do not put a newline before
2319 //
2320 // also we must not be inside a privilege list
2321 if ($i > 0) {
2322 // the alpha_identifier exception is there to
2323 // catch cases like
2324 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2325 // (else, we get mydb.mytableTO)
2326 //
2327 // the quote_single exception is there to
2328 // catch cases like
2329 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2330 /**
2331 * @todo fix all cases and find why this happens
2332 */
2333
2334 if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2335 $before .= $space_alpha_reserved_word;
2336 }
2337 } else {
2338 // on first keyword, check if it introduces a
2339 // privilege list
2340 if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2341 $in_priv_list = TRUE;
2342 }
2343 }
2344 } else {
2345 $before .= ' ';
2346 }
2347
2348 switch ($arr[$i]['data']) {
2349 case 'CREATE':
2350 if (!$in_priv_list) {
2351 $space_punct_listsep = $html_line_break;
2352 $space_alpha_reserved_word = ' ';
2353 }
2354 break;
2355 case 'EXPLAIN':
2356 case 'DESCRIBE':
2357 case 'SET':
2358 case 'ALTER':
2359 case 'DELETE':
2360 case 'SHOW':
2361 case 'DROP':
2362 case 'UPDATE':
2363 case 'TRUNCATE':
2364 case 'ANALYZE':
2365 case 'ANALYSE':
2366 if (!$in_priv_list) {
2367 $space_punct_listsep = $html_line_break;
2368 $space_alpha_reserved_word = ' ';
2369 }
2370 break;
2371 case 'INSERT':
2372 case 'REPLACE':
2373 if (!$in_priv_list) {
2374 $space_punct_listsep = $html_line_break;
2375 $space_alpha_reserved_word = $html_line_break;
2376 }
2377 break;
2378 case 'VALUES':
2379 $space_punct_listsep = ' ';
2380 $space_alpha_reserved_word = $html_line_break;
2381 break;
2382 case 'SELECT':
2383 $space_punct_listsep = ' ';
2384 $space_alpha_reserved_word = $html_line_break;
2385 break;
2386 default:
2387 break;
2388 } // end switch ($arr[$i]['data'])
2389
2390 $after .= ' ';
2391 break;
2392 case 'digit_integer':
2393 case 'digit_float':
2394 case 'digit_hex':
2395 /**
2396 * @todo could there be other types preceding a digit?
2397 */
2398 if ($typearr[1] == 'alpha_reservedWord') {
2399 $after .= ' ';
2400 }
2401 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2402 $after .= ' ';
2403 }
2404 if ($typearr[1] == 'alpha_columnAttrib') {
2405 $before .= ' ';
2406 }
2407 break;
2408 case 'alpha_variable':
2409 $after = ' ';
2410 break;
2411 case 'quote_double':
2412 case 'quote_single':
2413 // workaround: for the query
2414 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2415 // the @ is incorrectly marked as alpha_variable
2416 // in the parser, and here, the '%' gets a blank before,
2417 // which is a syntax error
2418 if ($typearr[1] != 'punct_user') {
2419 $before .= ' ';
2420 }
2421 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2422 $after .= ' ';
2423 }
2424 break;
2425 case 'quote_backtick':
2426 // here we check for punct_user to handle correctly
2427 // DEFINER = `username`@`%`
2428 // where @ is the punct_user and `%` is the quote_backtick
2429 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2430 $after .= ' ';
2431 }
2432 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2433 $before .= ' ';
2434 }
2435 break;
2436 default:
2437 break;
2438 } // end switch ($typearr[2])
2439
2440 /*
2441 if ($typearr[3] != 'punct_qualifier') {
2442 $after .= ' ';
2443 }
2444 $after .= "\n";
2445 */
2446 $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
2447 } // end for
2448 if ($mode=='color') {
2449 $str .= '</span>';
2450 }
2451
2452 return $str;
2453 } // end of the "PMA_SQP_formatHtml()" function
2454 }
2455
2456 /**
2457 * Builds a CSS rule used for html formatted SQL queries
2458 *
2459 * @param string The class name
2460 * @param string The property name
2461 * @param string The property value
2462 *
2463 * @return string The CSS rule
2464 *
2465 * @access public
2466 *
2467 * @see PMA_SQP_buildCssData()
2468 */
2469 function PMA_SQP_buildCssRule($classname, $property, $value)
2470 {
2471 $str = '.' . $classname . ' {';
2472 if ($value != '') {
2473 $str .= $property . ': ' . $value . ';';
2474 }
2475 $str .= '}' . "\n";
2476
2477 return $str;
2478 } // end of the "PMA_SQP_buildCssRule()" function
2479
2480
2481 /**
2482 * Builds CSS rules used for html formatted SQL queries
2483 *
2484 * @return string The CSS rules set
2485 *
2486 * @access public
2487 *
2488 * @global array The current PMA configuration
2489 *
2490 * @see PMA_SQP_buildCssRule()
2491 */
2492 function PMA_SQP_buildCssData()
2493 {
2494 global $cfg;
2495
2496 $css_string = '';
2497 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2498 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2499 }
2500
2501 for ($i = 0; $i < 8; $i++) {
2502 $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2503 }
2504
2505 return $css_string;
2506 } // end of the "PMA_SQP_buildCssData()" function
2507
2508 if (! defined('PMA_MINIMUM_COMMON')) {
2509 /**
2510 * Gets SQL queries with no format
2511 *
2512 * @param array The SQL queries list
2513 *
2514 * @return string The SQL queries with no format
2515 *
2516 * @access public
2517 */
2518 function PMA_SQP_formatNone($arr)
2519 {
2520 $formatted_sql = htmlspecialchars($arr['raw']);
2521 $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2522
2523 return $formatted_sql;
2524 } // end of the "PMA_SQP_formatNone()" function
2525
2526
2527 /**
2528 * Gets SQL queries in text format
2529 *
2530 * @todo WRITE THIS!
2531 * @param array The SQL queries list
2532 *
2533 * @return string The SQL queries in text format
2534 *
2535 * @access public
2536 */
2537 function PMA_SQP_formatText($arr)
2538 {
2539 return PMA_SQP_formatNone($arr);
2540 } // end of the "PMA_SQP_formatText()" function
2541 } // end if: minimal common.lib needed?
2542
2543 ?>
Drizzle / GSOC 2011