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

Answer Source

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...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download