예제 #1
0
function sql_query($sql, $cache = false, $fetchrows = -1, $dbstruct = true, $logthis = 2)
{
    # sql_query(sql) - execute a query and return the results as an array.
    # Database functions are wrapped in this way so supporting a database server other than MySQL is
    # easier.
    # $cache is not used at this time - it was intended for disk based results caching which may be added in the future.
    # If $fetchrows is set we don't have to loop through all the returned rows. We
    # just fetch $fetchrows row but pad the array to the full result set size with empty values.
    # This has been added retroactively to support large result sets, yet a pager can work as if a full
    # result set has been returned as an array (as it was working previously).
    # $logthis parameter is only relevant if $mysql_log_transactions is set.  0=don't log, 1=always log, 2=detect logging - i.e. SELECT statements will not be logged
    global $db, $config_show_performance_footer, $debug_log, $debug_log_override, $mysql_verbatim_queries, $use_mysqli, $mysql_log_transactions;
    if (!isset($debug_log_override)) {
        check_debug_log_override();
    }
    if ($config_show_performance_footer) {
        # Stats
        # Start measuring query time
        $time_start = microtime(true);
        global $querycount;
        $querycount++;
    }
    if ($debug_log || $debug_log_override) {
        debug("SQL: " . $sql);
    }
    if ($mysql_log_transactions && !($logthis == 0)) {
        global $mysql_log_location, $lang;
        $requirelog = true;
        if ($logthis == 2) {
            // Ignore any SELECTs if the decision to log has not been indicated by function call,
            if (strtoupper(substr(trim($sql), 0, 6)) == "SELECT") {
                $requirelog = false;
            }
        }
        if ($logthis == 1 || $requirelog) {
            # Log this to a transaction log file so it can be replayed after restoring database backup
            $mysql_log_dir = dirname($mysql_log_location);
            if (!is_dir($mysql_log_dir)) {
                @mkdir($mysql_log_dir, 0333, true);
                if (!is_dir($mysql_log_dir)) {
                    exit("ERROR: Unable to create  folder for \$mysql_log_location specified in config file: " . $mysql_log_location);
                }
            }
            if (!file_exists($mysql_log_location)) {
                global $mysql_db;
                $mlf = @fopen($mysql_log_location, "wb");
                @fwrite($mlf, "USE " . $mysql_db . ";\r\n");
                if (!file_exists($mysql_log_location)) {
                    exit("ERROR: Invalid \$mysql_log_location specified in config file: " . $mysql_log_location);
                }
                // Set the permissions if we can to prevent browser access(will not work on Windows)
                chmod($mysql_log_location, 0333);
            }
            $mlf = @fopen($mysql_log_location, "ab");
            fwrite($mlf, "/* " . date("Y-m-d H:i:s") . " */ " . $sql . ";\n");
            // Append the ';' so the file can be used to replay the changes
            fclose($mlf);
        }
    }
    # Execute query
    $result = $use_mysqli ? mysqli_query($db, $sql) : mysql_query($sql);
    if ($config_show_performance_footer) {
        # Stats
        # Log performance data
        global $querytime, $querylog;
        $time_total = microtime(true) - $time_start;
        if (isset($querylog[$sql])) {
            $querylog[$sql]['dupe'] = $querylog[$sql]['dupe'] + 1;
            $querylog[$sql]['time'] = $querylog[$sql]['time'] + $time_total;
        } else {
            $querylog[$sql]['dupe'] = 1;
            $querylog[$sql]['time'] = $time_total;
        }
        $querytime += $time_total;
    }
    $error = $use_mysqli ? mysqli_error($db) : mysql_error();
    $return_rows = array();
    if ($error != "") {
        if ($error == "Server shutdown in progress") {
            echo "<span class=error>Sorry, but this query would return too many results. Please try refining your query by adding addition keywords or search parameters.<!--{$sql}--></span>";
        } elseif (substr($error, 0, 15) == "Too many tables") {
            echo "<span class=error>Sorry, but this query contained too many keywords. Please try refining your query by removing any surplus keywords or search parameters.<!--{$sql}--></span>";
        } else {
            # Check that all database tables and columns exist using the files in the 'dbstruct' folder.
            if ($dbstruct) {
                check_db_structs();
                # Try again (no dbstruct this time to prevent an endless loop)
                return sql_query($sql, $cache, $fetchrows, false);
                exit;
            }
            errorhandler("N/A", $error . "<br/><br/>" . $sql, "(database)", "N/A");
        }
        exit;
    } elseif ($result === true) {
        return $return_rows;
        // no result set, (query was insert, update etc.) - simply return empty array.
    }
    $return_row_count = 0;
    while (($fetchrows == -1 || $return_row_count < $fetchrows) && ($use_mysqli && ($result_row = mysqli_fetch_assoc($result)) || !$use_mysqli && ($result_row = mysql_fetch_assoc($result)))) {
        if ($mysql_verbatim_queries) {
            $return_rows[$return_row_count] = $result_row;
            // simply dump the entire row into the return results set
        } else {
            while (list($name, $value) = each($result_row)) {
                $return_rows[$return_row_count][$name] = str_replace("\\", "", stripslashes($value));
                // iterate through each cell cleaning up
            }
        }
        $return_row_count++;
    }
    if ($fetchrows == -1) {
        return $return_rows;
    }
    # If we haven't returned all the rows ($fetchrows isn't -1) then we need to fill the array so the count
    # is still correct (even though these rows won't be shown).
    $query_returned_row_count = $use_mysqli ? mysqli_num_rows($result) : mysql_num_rows($result);
    // get the number of rows returned from the query
    if ($return_row_count < $query_returned_row_count) {
        $return_rows = array_pad($return_rows, $query_returned_row_count, 0);
        // if short then pad out
    }
    return $return_rows;
}
예제 #2
0
function sql_query($sql, $cache = false, $fetchrows = -1, $dbstruct = true)
{
    # sql_query(sql) - execute a query and return the results as an array.
    # Database functions are wrapped in this way so supporting a database server other than MySQL is
    # easier.
    # $cache is not used at this time - it was intended for disk based results caching which may be added in the future.
    # If $fetchrows is set we don't have to loop through all the returned rows. We
    # just fetch $fetchrows row but pad the array to the full result set size with empty values.
    # This has been added retroactively to support large result sets, yet a pager can work as if a full
    # result set has been returned as an array (as it was working previously).
    global $db, $querycount, $querytime, $config_show_performance_footer, $querylog, $debug_log, $mysql_verbatim_queries;
    $counter = 0;
    if ($config_show_performance_footer) {
        # Stats
        # Start measuring query time
        $time_start = microtime(true);
        $querycount++;
    }
    if ($debug_log) {
        debug("SQL: " . $sql);
    }
    # Execute query
    global $use_mysqli;
    if ($use_mysqli) {
        $result = mysqli_query($db, $sql);
    } else {
        $result = mysql_query($sql);
    }
    if ($config_show_performance_footer) {
        # Stats
        # Log performance data
        $time_end = microtime(true);
        $time_total = $time_end - $time_start;
        if (isset($querylog[$sql])) {
            $querylog[$sql]['dupe'] = $querylog[$sql]['dupe'] + 1;
            $querylog[$sql]['time'] = $querylog[$sql]['time'] + $time_total;
        } else {
            $querylog[$sql]['dupe'] = 1;
            $querylog[$sql]['time'] = $time_total;
        }
        $querytime += $time_total;
    }
    if ($use_mysqli) {
        $error = mysqli_error($db);
    } else {
        $error = mysql_error();
    }
    if ($error != "") {
        if ($error == "Server shutdown in progress") {
            echo "<span class=error>Sorry, but this query would return too many results. Please try refining your query by adding addition keywords or search parameters.<!--{$sql}--></span>";
        } elseif (substr($error, 0, 15) == "Too many tables") {
            echo "<span class=error>Sorry, but this query contained too many keywords. Please try refining your query by removing any surplus keywords or search parameters.<!--{$sql}--></span>";
        } else {
            # Check that all database tables and columns exist using the files in the 'dbstruct' folder.
            if ($dbstruct) {
                check_db_structs();
                # Try again (no dbstruct this time to prevent an endless loop)
                return sql_query($sql, $cache, $fetchrows, false);
                exit;
            }
            errorhandler("N/A", $error . "<br/><br/>" . $sql, "(database)", "N/A");
        }
        exit;
    } elseif ($result === true) {
        # no result set, (query was insert, update etc.)
    } else {
        $row = array();
        if ($use_mysqli) {
            while (($rs = mysqli_fetch_assoc($result)) && ($counter < $fetchrows || $fetchrows == -1)) {
                while (list($name, $value) = each($rs)) {
                    //if (!is_integer($name)) # do not run for integer values (MSSQL returns two keys for each returned column, a numeric and a text)
                    //    {
                    $row[$counter][$name] = $mysql_verbatim_queries ? $value : str_replace("\\", "", stripslashes($value));
                    //    }
                }
                $counter++;
            }
        } else {
            while (($rs = mysql_fetch_assoc($result)) && ($counter < $fetchrows || $fetchrows == -1)) {
                while (list($name, $value) = each($rs)) {
                    //if (!is_integer($name)) # do not run for integer values (MSSQL returns two keys for each returned column, a numeric and a text)
                    //    {
                    $row[$counter][$name] = $mysql_verbatim_queries ? $value : str_replace("\\", "", stripslashes($value));
                    //    }
                }
                $counter++;
            }
        }
        # If we haven't returned all the rows ($fetchrows isn't -1) then we need to fill the array so the count
        # is still correct (even though these rows won't be shown).
        $rows = count($row);
        if ($use_mysqli) {
            $totalrows = mysqli_num_rows($result);
        } else {
            $totalrows = mysql_num_rows($result);
        }
        #echo "-- $rows out of $totalrows --";
        if ($fetchrows != -1 && $rows < $totalrows) {
            $row = array_pad($row, $totalrows, 0);
        }
        return $row;
    }
}