Syntax error in SQL Query while iterating through databases

Question

I am writing a PHP file that iterates through all of my databases on a server, and replaces an entire column (or two) of entries with four **** to protect sensitive information. However, the code I have below is returning a parse error in the SQL syntax that I am using:

<?php
/**
 * This replaces an entire column within a table with a 4 asterisk long string
 */

$host = 'example.example.com';
$user = 'example';
$password = 'password';

$connection = new mysqli($host, $user, $password);

if (!$connection){
    die ('Could not connect to server: '.mysqli_error($connection));
}

// Get the databases as an array
$res = mysqli_query($connection, "SHOW DATABASES");
$d = mysqli_fetch_array($res);

// Loop through the array of databases
for ($i = 0; $i < count($d); $i++){

    $db = $d[$i];
    echo "$db\n";

    // To skip the first database information_schema
    if ($i > 0){
        $sql1 = /** @lang text */
                "USE $db";
        $query1 = mysqli_query($connection, $sql1);

        if (!$query1){
            die('Could not select database: '.mysqli_error($connection));
        }

        $sql2 = /** @lang text */
                "SELECT * FROM `info`";

        $query2 = mysqli_query($connection, $sql2);

        if (!$query2){
            die('Could not select from `info`: '.mysqli_error($connection));
        }

        while ($row = mysqli_fetch_array($query2)){

            $id = $row['id'];

            $sql3 = /** @lang text */
                "IF COL_LENGTH('info','borrower') IS NOT NULL
                 BEGIN
                    UPDATE `info`
                        SET `borrower` = '****'
                        WHERE `id` = '$id'
                 END";

        $query3 = mysqli_query($connection, $sql3);

        if (!$query3){
            die('Could not replace number with "****" '.mysqli_error($connection));
        }

        $sql4 = /** @lang text */
                "IF COL_LENGTH('info','coborrower') IS NOT NULL
                 BEGIN
                    UPDATE `info`
                        SET `coborrower` = '****'
                        WHERE `id` = '$id'
                 END";

        $query4 = mysqli_query($connection, $sql4);

        if (!$query4){
            die('Could not replace number with "****" '.mysqli_error($connection));
        }
    }
}

mysqli_close($connection);
?>

This is the error message I am getting back:

information_schema

Could not select database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1

I think this error is occurring because when I am looping through my Databases as an array, for some reason the entries afterwards are blank. Not entirely sure why this is. When I try the SHOW DATABASES; query in Sequel Pro, it returns the proper list of databases. This is an example of that list:

1. information_schema
2. mysql
3. performance_schema
4. db1
5. db2
6. db3
7. etc....

My PHP interpreter and MySQL Server version are both 5.6

Answer 1

MySQL treats the special query SHOW DATABASES basically the same as a regular SELECT query in terms of how it is sent to the querying client, and your application code can therefore treat it exactly the same way as you would handle a regular SELECT statement.

The SHOW DATABASES query returns one column called Database and a row for each database.

> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| db1                     |
| db2                     |
+-------------------------+
3 rows in set (0.00 sec)

So instead of the for loop utilizing count() and a single call to mysqli_fetch_array(), use the same while loop structure you would use in a SELECT query, and assign $db with it.

$res = mysqli_query($connection, "SHOW DATABASES");
if (!$res) {
  // handle error...
}
// On query success, fetch in a normal loop 
while ($d = mysqli_fetch_assoc($res)) {
  // Database name is in the column `Database`
  $db = $d['Database'];

  // Advisable to quote it with backticks...
  $sql1 = "USE `$db`";
  // etc...
}