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