PHP数据库转储脚本-有问题吗? [英] PHP Database Dump Script - are there any issues?
问题描述
我找到了一个PHP函数来转储别人编写的mySQL数据库，然后对其进行清理和格式化.我想知道是否可以对此进行批评.我已经运行它，并在Wordpress博客上对其进行了测试，并且数据库已完全还原，但希望对代码有其他了解.
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:
- 任何可能破坏数据的内容-无法正确转义等
- 违反任何最佳做法/原则
- 安全问题
- 您可能会认为还有其他问题
注意:我不正在使用mysqldump，我想从代码中完全生成此sql备份.我也意识到文件名可以随机生成，但是SQL文件也将被上传到保管箱，我想用相同的名称对其进行版本控制.
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.
谢谢！
代码:
// 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);
推荐答案
该脚本存在严重的交易问题.除最琐碎的数据库外，它不适用于任何其他数据库.
That script has serious, deal-breaking problems. It will not work for any but the most trivial database.
- 不支持NULL.
- 不考虑字符集.
- 表名没有定界.
- 仅支持表-不支持视图，存储过程，触发器，函数等.
-
addslashes()字符集不安全. -
mysql_query()从表中预取 all 行，因此，如果查询具有数百万行的表，则将超出PHP内存限制.使用mysql_unbuffered_query()代替.再次考虑，我看到您收集了$ return中的所有输出，因此没有意义. - 使用
@运算符抑制错误是不正确的做法.检查错误并通过提示信息正常失败.
- 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. Usemysql_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.
您不使用mysqldump的要求是荒谬的.
为什么要自己做很多工作来重新发明轮子，而仍然犯错呢?只需通过 shellexec() 运行mysqldump.
Why make so much work for yourself reinventing the wheel, and still get it so wrong? Just run mysqldump via shellexec().
另请参阅:
这篇关于PHP数据库转储脚本-有问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
