在将结果返回到应用程序的其余部分之前更改Mysqli_result

I have around 75 php existing scripts that access mysql database similar to this pseudo code:

$query="SELECT * from table";
$result=mysqli_query($conn,$query);
if (mysqli_num_rows($result)) {
    while($row=mysqli_fetch_assoc($result)) {
        //use the rows
    }
}

I was recently forced to encrypt all the database fields individually, so now when those 75 php scripts run as shown above, all the $row come back with all the fields encrypted, thus unusable.

So rather than change all the 75 php scripts to decode each field, i wanted to create a function that executes the mysqli_query, and then decrypts all the fields, and returns the result as if it was returned by the mysqli_query, but decrypted. Something like

function QueryAndDecrypt($conn,$query){
    $result=mysqli_query($conn,$query);
    if (mysqli_num_rows($result)) {
        while($row=mysqli_fetch_assoc($result)) {
            $row=decrypt($row);
        } 
    }
    return $result;  <<----- return result with fields decrypted
}

// now all 75 scripts would have to change just one line to call above

$query="SELECT * from table";
$result=QueryAndDecrypt($conn,$query);  <<--- change only 1 line
if (mysqli_num_rows($result)) {
    while($row=mysqli_fetch_assoc($result)) {
        //use the rows as normal decrypted
    }
}

As you can see I just want to change that one line in all the 75 scripts so that it will do the same thing as before, and the result will come back with all the fields already decrypted.

I tried writing this QueryAndDecrypt function, but when i change the result from mysqli_result $row as shown above it wont change because the result from mysql is some sort of set that is not changeable (I was told), or something like that.

So is there anyway to do this by writing a common function that can be called from all the scripts which does the sql query and also decrypts the result in such a way that it can be accessed by all the other scripts like a regular mysql query result?

Can anybody help, im "fresh off the boat", so i dont know sql that well or php, i'm so desperate right now because all the scripts are broken because of this!!

Thanks

Sorry, you can't modify the rows of the result and then somehow 'unfetch' them back into the result to be fetched again.

But you can fix your code by changing one line:

$query = "SELECT * from table";
$result = mysqli_query($conn,$query);
if (mysqli_num_rows($result)) {
    while ($row = MyFetchAssocAndDecrypt($result)) {   <<--- change only 1 line
        //use the rows as normal decrypted
    }
}

You'd have to write functions something like this:

function MyDecrypt(&$item, $key) {
    $item = openssl_decrypt($item, OPENSSL_CIPHER_AES_256_CBC, MY_SECRET_KEY);
}

function MyFetchAssocAndDecrypt($conn, $result){
    $row = mysqli_fetch_assoc($conn, $result);
    array_walk($row, 'MyDecrypt');
    return $row;  <<----- return row with fields decrypted
}

PS: You mentioned the requirement that you aren't supposed to send unencrypted data over the network to the database. That wouldn't be my concern, because you can use a VPN or else connect to the database via SSL.

The greater concern is that the query that contains your plaintext data and the plaintext encryption password would be written to database logs on the MySQL server, and these logs are not encrypted.

There are some optional extensions to MySQL that promise to do full-database encryption, but these extensions overlook the query logs.