mysql - PHP Database Dump Script - are there any issues?

Question

I found a PHP function to dump a mySQL database that someone else had written, and then cleaned it up and formatted it a bit. I wanted to know if I could get a critique of it. I have ran it, tested it on a Wordpress blog and the DB completely restores, but wanted to get some other eyes on the code.

Specifically I am looking for feedback on:

• Anything that could corrupt the data - not properly escaping etc
• Violating any best practice / principles
• Security issues
• Anything else you may see as an issue

NOTE: I am NOT looking to use mysqldump, I want to completely generate this sql backup from code. I also realize that the filename could be generated randomly, but the SQL file will be uploaded to dropbox as well, and I want to version it under the same name.

Thanks!

Code:

  // Variables
  $dbHost = 'DBHOST';
  $dbUser = 'DBUSER';
  $dbPassword = 'DBPASSWORD';
  $dbName = 'DBNAME';
  $tables = '*';
  $fileName = 'mydatabase.sql';


  // Logic
    $link = @mysql_connect($dbHost, $dbUser, $dbPassword);
    $db = @mysql_select_db($dbName, $link);

if(!$link || !$db)
 die("Database Error");

//get all of the tables
if($tables == '*') {

$tables = array();
$result = mysql_query('SHOW TABLES');

while($row = mysql_fetch_row($result)) {
  $tables[] = $row[0];
    }
}

else $tables = is_array($tables) ? $tables : explode(',',$tables);


// Loop through tables    
foreach($tables as $table) {

    $result = mysql_query('SELECT * FROM '. $table);
    $num_fields = mysql_num_fields($result);
    $return.= 'DROP TABLE IF EXISTS ' . $table . ';';
  $createTable = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
    $return .= "

" . $createTable[1] . ";

";

    for ($i = 0; $i < $num_fields; $i++) 
    {
        while($row = mysql_fetch_row($result))
        {

            $return.= 'INSERT INTO ' . $table . ' VALUES(';

            for($j = 0; $j < $num_fields; $j++) {

        $row[$j] = addslashes($row[$j]);
        $row[$j] = ereg_replace("
","\n", $row[$j]);

        if (isset($row[$j])) { 
          $return .= '"' . $row[$j] . '"' ; 
        } 

        else { 
          $return .= '""'; 
        }

        if ($j < ($num_fields-1)) { 
          $return .= ','; 
        }
            }
            $return .= ");
";
        }
    }

    $return .="


";

}

// Save the file
$handle = fopen($fileName, 'w+');
fwrite($handle, $return);
fclose($handle);

Answer 1

That script has serious, deal-breaking problems. It will not work for any but the most trivial database.

• NULLs are not supported.
• Character sets are not taken into account.
• Table names are not delimited.
• Only tables are supported -- not views, stored procedures, triggers, functions, etc.
• addslashes() is not character-set safe.
• mysql_query() pre-fetches all the rows from the table, so if you query a table with millions of rows, you will exceed your PHP memory limit. Use mysql_unbuffered_query() instead. On second thought, I see you collect all the output in $return so this is moot.
• Your suppression of errors with the @ operator is bad practice. Check for errors and fail gracefully with an informative message.

Your requirement not to use mysqldump is absurd.

Why make so much work for yourself reinventing the wheel, and still get it so wrong? Just run mysqldump via shellexec().

---

See also:

• Why is my database backup script not working in php?