Jodo1992 Jodo1992 - 1 year ago 31
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:

* This replaces an entire column within a table with a 4 asterisk long string

$host = '';
$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
UPDATE `info`
SET `borrower` = '****'
WHERE `id` = '$id'

$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
UPDATE `info`
SET `coborrower` = '****'
WHERE `id` = '$id'

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

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


This is the error message I am getting back:


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


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