Jodo1992 Jodo1992 - 5 months ago 10
PHP Question

Syntax error in SQL Query while iterating through databases

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

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...
}