Php MySQL Query在托管时内存不足，资源有限

Question

Hello everyone before anything I want to set important facts that cannot be changed:

• The hosting can't be changed
• The php_ini max memory setted in php won't work
• The php.ini cannot be edited
• The php can't be updated

We have a webapi (similar to a restful service) where with an url, the users can be shown in a json way. But we have 85k+ records, when we try to brig them all, the php crashes (that doesn't happen when we look just for 1 record), we have 2 codes to work with, but none of them bring all the users

Version 1

<?php
//security variables
$variableName = 'name';
$variableValue = 'value';

//system variables

ob_end_flush();
ob_start();
if(isset($_GET[$variableName]) && $_GET[$variableName]== $variableValue){
    $dbVars = array();
    $query = 'SELECT * FROM users_data ';
    foreach ($_GET as $key => $value) {

        if( $key != $variableName && $key != 'sized' && $key != 'devmode'){
            array_push($dbVars, $key."='".$value."'");
        }
    }
    if( count($dbVars) > 0 ){

        $query .= 'WHERE '.implode(' AND ', $dbVars);
    }
    $result = mysql($w['database'], $query);
    $results = array();
    while($row = mysql_fetch_assoc($result)){
        array_push($results, $row);
    }
    echo json_encode($results);
}
?>

Version 2 (using flush)

<?php
//security variables
$variableName = 'name';
$variableValue = 'value';

//system variables

ob_end_flush();
ob_start();
if(isset($_GET[$variableName]) && $_GET[$variableName]== $variableValue){
    $dbVars = array();
    $query = 'SELECT * FROM users_data ';
    foreach ($_GET as $key => $value) {

        if( $key != $variableName && $key != 'sized' && $key != 'devmode'){
            array_push($dbVars, $key."='".$value."'");
        }
    }
    if( count($dbVars) > 0 ){

        $query .= 'WHERE '.implode(' AND ', $dbVars);
    }
    $result = mysql($w['database'], $query);
    echo "[";
    while($row = mysql_fetch_assoc($result)){
        $results = array(); 
        foreach($row as $key => $value){
            array_push($results, '"'.$key.'":"'.$value.'"');
        }
        echo '{'.implode(',',$results).'}';
        ob_flush();
    }
    echo "]";
}
?>

When we want to get all the users in database are 85k, the result is none because memory limit How can we made this work for all the users?

Answer 1

First of all, your code is filled to the brim with SQL injection vulnerabilities. Anyone can own your database. This is your problem.

Now, the reason PHP runs out of memory is that you are using buffered queries.

http://php.net/manual/en/mysqlinfo.concepts.buffering.php

Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process.

Read the link I provided (which uses mysqli, for chrissakes) and use unbuffered mode to stream your result set to the client.

Also, don't array_push() the entire result set into an array.

Also, obviously,

array_push($dbVars, $key."='".$value."'");

will generate invalid JSON if any value contains a " or other escape chars.