I am changing my SQL request to PDO in order to integrate more security against SQL injecting. I was using before procedural msqli and everything was working ok. Now I updated server to PHP 8.2 and I am using PDO and prepared statements.

The BD I am accessing is old MariaDB and has a table with 247 fields most of which are NULL on a single row. (It is sections for a description of mushrooms, but only some of each are really described in a single row (mushrooms)).

Most of the 247 fields are set to mediumtext because they can content up to a couple paragraphs.

My connection script is :

try {

    db = new PDO("mysql:dbname=$dbname;host=$servername;charset=utf8mb4", $username, $password );

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
    echo 'Erreur de connection: ',  $e->getMessage(), "n";
return;
} 

When I select one row of that table in order to echo the not NULL descriptions column, as soon as I execute the statement I run into a full memory. The error is at the stmt->execute() line, not even at the fetchAll line.

$description = 'someTag';

$sql = "SELECT * FROM description WHERE tag = :tag LIMIT 1";

$stmt = $db->prepare($sql);
$stmt->bindParam(':tag', $description, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$result = $stmt->fetchAll();
$stmt->closeCursor();

The error :

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 262144 bytes) in /home/mycoqueb/public_html/nou.mycoquebec.org/scripts/bd-query.php on line (**the $stmt->execute() line)

I tried to use :

while ($row = $stmt->fetch()) {

Instead of fetchAll() but it changed noting, I used it on every other request from the same page and it changed noting. I flushed variables that were no more useful to NULL on the rest of the page and it changed nothing. I even isolated the script on an empty page and it exceeds the memory limit.

I tried to add this to my connection script and it changed nothing :

db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

I tried to select only one field instead of * and this worked, but I don’t have an easy way to find NOT NULL columns in the table for a specific tag (row).

I tried to find a SQL statement to restrict my SELECT to NOT NULL columns but I was not successful.

I know I could get add memory_limit = 256M to the phpini file, but that is not what I want since the website has a high usage and for now its on a multihosted server.

The best solution would be to reengineer the MariaDB database using a table with description sections labels and linking only used sections to the main description table for each row, but I have limited access to the db since it is online with the old website and the old website needs to stay online and up to date for 1-2 years after new deployment. I though of creating a daily cron job to do that but I would like to avoid for the moment.

Any Idea how I can get this to work? It was working fine using msqli on php 8.1.

To answer the worries of contributors that think it’s related to another part of the page code, here is the complete code of the isolated test page.

function convert($size)
 {
    $unit=array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
 }


$servername = "localhost";
$username = "myUser";
$password = "myPass";
$dbname = "myDB";


try {

    $db = new PDO("mysql:dbname=$dbname;host=$servername;charset=utf8mb4", $username, $password );

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

 } catch (Exception $e) {
    echo 'Erreur de connection: ',  $e->getMessage(), "n";
    return;
}   

$query = (array_key_exists("query",$_REQUEST) ? $_REQUEST["query"] : "");


if($query != ""){

    $tag_desc = 'Aegerita candida';
    // Code works if I limit to "SELECT Titre FROM description WHERE tag = :tag LIMIT 1";
    $sql_description = "SELECT * FROM description WHERE tag = :tag LIMIT 1";

    $stmt = $db->prepare($sql_description);
    $stmt->bindParam(':tag', $tag_desc, PDO::PARAM_STR);
    
    echo convert(memory_get_usage(true));  // returns 2mb
    $stmt->execute();
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    $i = 0;
    
    while ($row = $stmt->fetch()) {

        if($i == 0) {

            ?><table style="border-collapse:collapse;border: 0;table-layout: fixed;color: #063850;background-color: #DDDDDD;text-align: left"><thead><tr style="border: 2px solid #063850;"> <?php
            foreach (array_keys($row) as $key) {
                //print_r($key);
                echo ("<th style='padding:5px;border: 2px solid #063850;position:sticky;top: -2;z-index:10;background-color:#e5a045'>$key</th>");
            }
            echo ("</tr></thead>");
        }

        $i++;

        echo ("<tr style='border: 2px solid #063850'>");
        foreach ($row as $value) {
            echo ("<td style='padding:2px;border: 2px solid #063850'>$value</td>");
            // <img src="$value" height="400" >
        }
        echo ("</tr>");
    }
    
    if ($i > 0) {
        echo ("</table>");
        echo ('<div style="margin-top:400px">&nbsp;</div>');
    } else {
        echo "Aucun résultat";
    }

}

$db = null;

Has mentioned it automatically work if I limit my sql to a couple fields.

The error is still on the stmt->execute() line.