sqlparser.lib.php

来自「php绿色服务器,让大家试用greenamp」· PHP 代码 · 共 1,652 行 · 第 1/5 页

    function PMA_SQP_typeCheck($toCheck, $whatWeWant)
    {
        $typeSeperator = '_';
        if (strcmp($whatWeWant, $toCheck) == 0) {
            return TRUE;
        } else {
            if (strpos($whatWeWant, $typeSeperator) === FALSE) {
                return strncmp($whatWeWant, $toCheck , strpos($toCheck, $typeSeperator)) == 0;
            } else {
                return FALSE;
            }
        }
    }


    /**
     * Analyzes SQL queries
     *
     * @param  array   The SQL queries
     *
     * @return array   The analyzed SQL queries
     *
     * @access public
     */
    function PMA_SQP_analyze($arr)
    {
        $result          = array();
        $size            = $arr['len'];
        $subresult       = array(
            'querytype'      => '',
            'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
            'position_of_first_select' => '', // the array index
            'from_clause'=> '',
            'group_by_clause'=> '',
            'order_by_clause'=> '',
            'having_clause'  => '',
            'where_clause'   => '',
            'where_clause_identifiers'   => array(),
            'queryflags'     => array(),
            'select_expr'    => array(),
            'table_ref'      => array(),
            'foreign_keys'   => array()
        );
        $subresult_empty = $subresult;
        $seek_queryend         = FALSE;
        $seen_end_of_table_ref = FALSE;

        // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
        // we must not use CURDATE as a table_ref
        // so we track wether we are in the EXTRACT()
        $in_extract          = FALSE;

        // for GROUP_CONCAT( ... )
        $in_group_concat     = FALSE;

/* Description of analyzer results
 *
 * lem9: db, table, column, alias
 *      ------------------------
 *
 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
 *
 * The SELECT syntax (simplified) is
 *
 * SELECT
 *    select_expression,...
 *    [FROM [table_references]
 *
 *
 * ['select_expr'] is filled with each expression, the key represents the
 * expression position in the list (0-based) (so we don't lose track of
 * multiple occurences of the same column).
 *
 * ['table_ref'] is filled with each table ref, same thing for the key.
 *
 * I create all sub-values empty, even if they are
 * not present (for example no select_expression alias).
 *
 * There is a debug section at the end of loop #1, if you want to
 * see the exact contents of select_expr and table_ref
 *
 * lem9: queryflags
 *       ----------
 *
 * In $subresult, array 'queryflags' is filled, according to what we
 * find in the query.
 *
 * Currently, those are generated:
 *
 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
 * ['queryflags']['distinct'] = 1;    for a DISTINCT
 * ['queryflags']['union'] = 1;       for a UNION
 *
 * lem9:  query clauses
 *        -------------
 *
 * The select is splitted in those clauses:
 * ['select_expr_clause']
 * ['from_clause']
 * ['group_by_clause']
 * ['order_by_clause']
 * ['having_clause']
 * ['where_clause']
 *
 * and the identifiers of the where clause are put into the array
 * ['where_clause_identifier']
 *
 * lem9:   foreign keys
 *         ------------
 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
 * analyzed and ['foreign_keys'] is an array filled with
 * the constraint name, the index list,
 * the REFERENCES table name and REFERENCES index list,
 * and ON UPDATE | ON DELETE clauses
 *
 * lem9: position_of_first_select
 *       ------------------------
 *
 * The array index of the first SELECT we find. Will be used to
 * insert a SQL_CALC_FOUND_ROWS.
 */

        // must be sorted
        // TODO: current logic checks for only one word, so I put only the
        // first word of the reserved expressions that end a table ref;
        // maybe this is not ok (the first word might mean something else)
//        $words_ending_table_ref = array(
//            'FOR UPDATE',
//            'GROUP BY',
//            'HAVING',
//            'LIMIT',
//            'LOCK IN SHARE MODE',
//            'ORDER BY',
//            'PROCEDURE',
//            'UNION',
//            'WHERE'
//        );
        $words_ending_table_ref = array(
            'FOR',
            'GROUP',
            'HAVING',
            'LIMIT',
            'LOCK',
            'ORDER',
            'PROCEDURE',
            'UNION',
            'WHERE'
        );
        $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);

        $words_ending_clauses = array(
            'FOR',
            'LIMIT',
            'LOCK',
            'PROCEDURE',
            'UNION'
        );
        $words_ending_clauses_cnt = 5; //count($words_ending_clauses);




        // must be sorted
        $supported_query_types = array(
            'SELECT'
            /*
            // Support for these additional query types will come later on.
            'DELETE',
            'INSERT',
            'REPLACE',
            'TRUNCATE',
            'UPDATE'
            'EXPLAIN',
            'DESCRIBE',
            'SHOW',
            'CREATE',
            'SET',
            'ALTER'
            */
        );
        $supported_query_types_cnt = count($supported_query_types);

        // loop #1 for each token: select_expr, table_ref for SELECT

        for ($i = 0; $i < $size; $i++) {
//DEBUG echo "trace loop1 <b>"  . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br />";

            // High speed seek for locating the end of the current query
            if ($seek_queryend == TRUE) {
                if ($arr[$i]['type'] == 'punct_queryend') {
                    $seek_queryend = FALSE;
                } else {
                    continue;
                } // end if (type == punct_queryend)
            } // end if ($seek_queryend)

            // TODO: when we find a UNION, should we split
            // in another subresult?
            if ($arr[$i]['type'] == 'punct_queryend') {
                $result[]  = $subresult;
                $subresult = $subresult_empty;
                continue;
            } // end if (type == punct_queryend)

// ==============================================================
            if ($arr[$i]['type'] == 'punct_bracket_open_round') {
                if ($in_extract) {
                    $number_of_brackets_in_extract++;
                }
                if ($in_group_concat) {
                    $number_of_brackets_in_group_concat++;
                }
            }
// ==============================================================
            if ($arr[$i]['type'] == 'punct_bracket_close_round') {
                if ($in_extract) {
                    $number_of_brackets_in_extract--;
                    if ($number_of_brackets_in_extract == 0) {
                       $in_extract = FALSE;
                    }
                }
                if ($in_group_concat) {
                    $number_of_brackets_in_group_concat--;
                    if ($number_of_brackets_in_group_concat == 0) {
                       $in_group_concat = FALSE;
                    }
                }
            }
// ==============================================================
            if ($arr[$i]['type'] == 'alpha_functionName') {
                $upper_data = strtoupper($arr[$i]['data']);
                if ($upper_data =='EXTRACT') {
                    $in_extract = TRUE;
                    $number_of_brackets_in_extract = 0;
                }
                if ($upper_data =='GROUP_CONCAT') {
                    $in_group_concat = TRUE;
                    $number_of_brackets_in_group_concat = 0;
                }
            }

// ==============================================================
            if ($arr[$i]['type'] == 'alpha_reservedWord') {
                // We don't know what type of query yet, so run this
                if ($subresult['querytype'] == '') {
                    $subresult['querytype'] = strtoupper($arr[$i]['data']);
                } // end if (querytype was empty)

                // Check if we support this type of query
                if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
                    // 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')) {

                switch ($arr[$i]['type']) {
                    case 'alpha_identifier':
                        $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)