Dump database php script - are there any problems?

Question

I found a PHP function to flush the mySQL database someone else had written, and then flushed it and formatted it a bit. I wanted to know if I could criticize him. I launched it, tested it on the Wordpress blog, and the DB fully recovered, but wanted to get other eyes on the code.

In particular, I am looking for reviews:

• Everything that can lead to data corruption - could not be avoided, etc.
• Violation of any best practice / principles
• Security concerns
• Everything you can see as a problem

NOTE. I am NOT looking at using mysqldump, I want to fully generate this sql backup from code. I also understand that the file name may be randomly generated, but the SQL file will also be uploaded to dropbox, and I want to fine-tune it under the same name.

Thanks!

The 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 .= "\n\n" . $createTable[1] . ";\n\n"; 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","\\n", $row[$j]); if (isset($row[$j])) { $return .= '"' . $row[$j] . '"' ; } else { $return .= '""'; } if ($j < ($num_fields-1)) { $return .= ','; } } $return .= ");\n"; } } $return .="\n\n\n"; } // Save the file $handle = fopen($fileName, 'w+'); fwrite($handle, $return); fclose($handle); 

Answer 1

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

• NULLs are not supported.
• Character sets are not counted.
• Table names are not limited.
• Only tables are supported — not views, stored procedures, triggers, functions, etc.
• addslashes() not safe for the character .
• mysql_query() prefetch all the rows from the table, so if you query a table with millions of rows, you will exceed the PHP memory limit. Use mysql_unbuffered_query() . On second thought, I see that you are collecting all weekend in $ refunds, so this is a moot point.
• Your error suppression using the @ operator is bad practice. Check for errors and gracefully end with an informative message.

Your requirement not to use mysqldump is absurd.

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

---

See also:

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

Answer 2

Try the mysql command or mysqldump command

Answer 3

Character Sets? Perhaps SET NAMES utf8 would be a good addition.

Also, what happens if the database contains views?

Answer 4

It does not reset stored procedures, functions, views, triggers, etc.

Edit: You can also reset procedures, etc. Just use ie SHOW PROCEDURE STATUS; for a list of procedures, and then SHOW CREATE PROCEDURE for each procedure. The same goes for functions, views, triggers ...

Do not forget SHOW CREATE DATABASE; .

Answer 5

In case this is a very large database that needs to be flushed, make sure that your server (and php max execution memory for the script) has enough memory to save all $ return in memory, otherwise you better flush the file once per second or every line.

Answer 6

create a backup database:

 <?php $dbHost = 'DBHOST'; $dbUser = 'DBUSER'; $dbPassword = 'DBPASSWORD'; $dbName = 'DBNAME'; $tables = '*'; backup_tables($dbHost,$dbUser,$dbPassword,$tables); /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $db = new PDO("mysql:host=$host;dbname=$name;", $user, $pass); //get all of the tables if($tables == '*') { $tables = array(); $result = $db->query('SHOW TABLES'); $tables= $result->fetchAll(PDO::FETCH_COLUMN, 0); } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } $return=""; //cycle through foreach($tables as $table) { $return.= 'DROP TABLE IF EXISTS '.$table.';'; $result=$db->query('SHOW CREATE TABLE '.$table); $row2 = $result->fetch(PDO::FETCH_NUM); $return.= "\n\n".$row2[1].";\n\n"; $result = $db->query('SELECT * FROM '.$table); foreach ($result->fetchAll(PDO::FETCH_ASSOC) as $key=>$value) { // build query... $return .= "INSERT INTO $table (`".implode("`, `", array_keys($value))."`) VALUES ('".implode("', '", $value)."');\n\n"; } $return.="\n\n\n"; } //save file $handle = fopen('db-backup-'.date('Ymd--Hi-s').'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); }