sqlparser.lib.php

一个用PHP编写的

                    // Skip ahead to the next one if we don't
                    $seek_queryend = TRUE;
                    continue;
                } // end if (query not supported)

                // upper once
                $upper_data = strtoupper($arr[$i]['data']);
                //TODO: reset for each query?

                if ($upper_data == 'SELECT') {
                    $seen_from = FALSE;
                    $previous_was_identifier = FALSE;
                    $current_select_expr = -1;
                    $seen_end_of_table_ref = FALSE;
                } // end if ( data == SELECT)

                if ($upper_data =='FROM' && !$in_extract) {
                    $current_table_ref = -1;
                    $seen_from = TRUE;
                    $previous_was_identifier = FALSE;
                    $save_table_ref = TRUE;
                } // end if (data == FROM)

                // here, do not 'continue' the loop, as we have more work for
                // reserved words below
            } // end if (type == alpha_reservedWord)

// ==============================
            if ($arr[$i]['type'] == 'quote_backtick'
             || $arr[$i]['type'] == 'quote_double'
             || $arr[$i]['type'] == 'quote_single'
             || $arr[$i]['type'] == 'alpha_identifier'
             || ($arr[$i]['type'] == 'alpha_reservedWord'
                && $arr[$i]['forbidden'] == FALSE)) {

                switch ($arr[$i]['type']) {
                    case 'alpha_identifier':
                    case 'alpha_reservedWord':
                        // this is not a real reservedWord, because
                        // it's not present in the list of forbidden words,
                        // for example "storage" which can be used as
                        // an identifier
                        //
                        // TODO: avoid the pretty printing in color
                        //       in this case

                        $identifier = $arr[$i]['data'];
                        break;

                //TODO: check embedded double quotes or backticks?
                // and/or remove just the first and last character?
                    case 'quote_backtick':
                        $identifier = str_replace('`', '', $arr[$i]['data']);
                        break;
                    case 'quote_double':
                        $identifier = str_replace('"', '', $arr[$i]['data']);
                        break;
                    case 'quote_single':
                        $identifier = str_replace("'", "", $arr[$i]['data']);
                        break;
                } // end switch

                if ($subresult['querytype'] == 'SELECT' && !$in_group_concat) {
                    if (!$seen_from) {
                        if ($previous_was_identifier && isset($chain)) {
                            // found alias for this select_expr, save it
                            // but only if we got something in $chain
                            // (for example, SELECT COUNT(*) AS cnt
                            // puts nothing in $chain, so we avoid
                            // setting the alias)
                            $alias_for_select_expr = $identifier;
                        } else {
                            $chain[] = $identifier;
                            $previous_was_identifier = TRUE;

                        } // end if !$previous_was_identifier
                    } else {
                        // ($seen_from)
                        if ($save_table_ref && !$seen_end_of_table_ref) {
                            if ($previous_was_identifier) {
                                // found alias for table ref
                                // save it for later
                                $alias_for_table_ref = $identifier;
                            } else {
                                $chain[] = $identifier;
                                $previous_was_identifier = TRUE;

                            } // end if ($previous_was_identifier)
                        } // end if ($save_table_ref &&!$seen_end_of_table_ref)
                    } // end if (!$seen_from)
                } // end if (querytype SELECT)
            } // end if ( quote_backtick or double quote or alpha_identifier)

// ===================================
            if ($arr[$i]['type'] == 'punct_qualifier') {
                // to be able to detect an identifier following another
                $previous_was_identifier = FALSE;
                continue;
            } // end if (punct_qualifier)

            // TODO: check if 3 identifiers following one another -> error

            //    s a v e    a    s e l e c t    e x p r
            // finding a list separator or FROM
            // means that we must save the current chain of identifiers
            // into a select expression

            // for now, we only save a select expression if it contains
            // at least one identifier, as we are interested in checking
            // the columns and table names, so in "select * from persons",
            // the "*" is not saved

            if (isset($chain) && !$seen_end_of_table_ref
               && (   (!$seen_from
                   && $arr[$i]['type'] == 'punct_listsep')
                  || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM')) ) {
                $size_chain = count($chain);
                $current_select_expr++;
                $subresult['select_expr'][$current_select_expr] = array(
                  'expr' => '',
                  'alias' => '',
                  'db'   => '',
                  'table_name' => '',
                  'table_true_name' => '',
                  'column' => ''
                 );

                if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
                    // we had found an alias for this select expression
                    $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
                    unset($alias_for_select_expr);
                }
                // there is at least a column
                $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
                $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];

                // maybe a table
                if ($size_chain > 1) {
                    $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
                    // we assume for now that this is also the true name
                    $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
                    $subresult['select_expr'][$current_select_expr]['expr']
                     = $subresult['select_expr'][$current_select_expr]['table_name']
                      . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
                } // end if ($size_chain > 1)

                // maybe a db
                if ($size_chain > 2) {
                    $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
                    $subresult['select_expr'][$current_select_expr]['expr']
                     = $subresult['select_expr'][$current_select_expr]['db']
                      . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
                } // end if ($size_chain > 2)
                unset($chain);

                // TODO: explain this:
                if (($arr[$i]['type'] == 'alpha_reservedWord')
                 && ($upper_data != 'FROM')) {
                    $previous_was_identifier = TRUE;
                }

            } // end if (save a select expr)


            //======================================
            //    s a v e    a    t a b l e    r e f
            //======================================

            // maybe we just saw the end of table refs
            // but the last table ref has to be saved
            // or we are at the last token (TODO: there could be another
            // query after this one)
            // or we just got a reserved word

            if (isset($chain) && $seen_from && $save_table_ref
             && ($arr[$i]['type'] == 'punct_listsep'
               || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
               || $seen_end_of_table_ref
               || $i==$size-1 )) {

                $size_chain = count($chain);
                $current_table_ref++;
                $subresult['table_ref'][$current_table_ref] = array(
                  'expr'            => '',
                  'db'              => '',
                  'table_name'      => '',
                  'table_alias'     => '',
                  'table_true_name' => ''
                 );
                if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
                    $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
                    unset($alias_for_table_ref);
                }
                $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
                // we assume for now that this is also the true name
                $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
                $subresult['table_ref'][$current_table_ref]['expr']
                     = $subresult['table_ref'][$current_table_ref]['table_name'];
                // maybe a db
                if ($size_chain > 1) {
                    $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
                    $subresult['table_ref'][$current_table_ref]['expr']
                     = $subresult['table_ref'][$current_table_ref]['db']
                      . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
                } // end if ($size_chain > 1)

                // add the table alias into the whole expression
                $subresult['table_ref'][$current_table_ref]['expr']
                 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];

                unset($chain);
                $previous_was_identifier = TRUE;
                //continue;

            } // end if (save a table ref)


            // when we have found all table refs,
            // for each table_ref alias, put the true name of the table
            // in the corresponding select expressions

            if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
                for ($tr=0; $tr <= $current_table_ref; $tr++) {
                    $alias = $subresult['table_ref'][$tr]['table_alias'];
                    $truename = $subresult['table_ref'][$tr]['table_true_name'];
                    for ($se=0; $se <= $current_select_expr; $se++) {
                        if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
                           == $alias) {
                            $subresult['select_expr'][$se]['table_true_name']
                             = $truename;
                        } // end if (found the alias)
                    } // end for (select expressions)

                } // end for (table refs)
            } // end if (set the true names)


           // e n d i n g    l o o p  #1
           // set the $previous_was_identifier to FALSE if the current
           // token is not an identifier
           if (($arr[$i]['type'] != 'alpha_identifier')
            && ($arr[$i]['type'] != 'quote_double')
            && ($arr[$i]['type'] != 'quote_single')
            && ($arr[$i]['type'] != 'quote_backtick')) {
               $previous_was_identifier = FALSE;
           } // end if

           // however, if we are on AS, we must keep the $previous_was_identifier
           if (($arr[$i]['type'] == 'alpha_reservedWord')
            && ($upper_data == 'AS'))  {
               $previous_was_identifier = TRUE;
           }

           if (($arr[$i]['type'] == 'alpha_reservedWord')
            && ($upper_data =='ON' || $upper_data =='USING')) {
               $save_table_ref = FALSE;
           } // end if (data == ON)

           if (($arr[$i]['type'] == 'alpha_reservedWord')
            && ($upper_data =='JOIN' || $upper_data =='FROM')) {
               $save_table_ref = TRUE;
           } // end if (data == JOIN)

           // no need to check the end of table ref if we already did
           // TODO: maybe add "&& $seen_from"
           if (!$seen_end_of_table_ref) {
               // if this is the last token, it implies that we have
               // seen the end of table references
               // Check for the end of table references
               //
               // Note: if we are analyzing a GROUP_CONCAT clause,
               // we might find a word that seems to indicate that
               // we have found the end of table refs (like ORDER)
               // but it's a modifier of the GROUP_CONCAT so
               // it's not the real end of table refs
               if (($i == $size-1)
                 || ($arr[$i]['type'] == 'alpha_reservedWord'
                 && !$in_group_concat
                 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
                   $seen_end_of_table_ref = TRUE;
                   // to be able to save the last table ref, but do not
                   // set it true if we found a word like "ON" that has
                   // already set it to false
                   if (isset($save_table_ref) && $save_table_ref != FALSE) {
                      $save_table_ref = TRUE;
                   } //end if

               } // end if (check for end of table ref)
           } //end if (!$seen_end_of_table_ref)

           if ($seen_end_of_table_ref) {
               $save_table_ref = FALSE;
           } // end if

        } // end for $i (loop #1)

        // -------------------------------------------------------
        // This is a big hunk of debugging code by Marc for this.
        // -------------------------------------------------------
        /*
          if (isset($current_select_expr)) {
           for ($trace=0; $trace<=$current_select_expr; $trace++) {
               echo "<br />";
               reset ($subresult['select_expr'][$trace]);
               while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
                   echo "sel expr $trace $key => $val<br />\n";
               }
          }

          if (isset($current_table_ref)) {
           echo "current_table_ref = " . $current_table_ref . "<br>";
           for ($trace=0; $trace<=$current_table_ref; $trace++) {

               echo "<br />";
               reset ($subresult['table_ref'][$trace]);
               while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
               echo "table ref $trace $key => $val<br />\n";
               }
          }
        */
        // -------------------------------------------------------


        // loop #2: - queryflags
        //          - querytype (for queries != 'SELECT')