making a SQL dump of a MySQL database with only PHP

Sat, 07 May 2011 00:37:46 -0700 

The context here was that, for whatever reason, the Argentimes did not
have shell access to its web server any longer, and was migrating its
WordPress data to a new web server.  So I wrote this script to make a
SQL dump of the database.  A couple of the web fetches did give me some
kind of server error message instead of SQL data, so I had to retry
those.

<?php

// Script to back up a MySQL database you have only PHP access to.

// You should not install this in a publicly-accessible location for
// two reasons:
// 1. You probably have some secret information in your database
//    (e.g. users' passwords).
// 2. No effort has been made to guard against SQL injection;
//    consequently anyone who has access to this script has complete
//    control of your database.

// Additionally, no effort has been made to guard against cross-site
// scripting (XSS) attacks, so anyone who knows the URL where this is
// installed will be able to steal and abuse whatever credentials your
// browser may have for the site. So don't install it in a
// publicly-known place and don't leave it installed.

// Once you have this on your web server, you can use any reliable web
// crawler to make a MySQL-syntax SQL backup of the database;
// e.g. wget -r -w 1 http://example.com/mysecretdir/dumpdb.php.

// Because the backup script extracts data in an unspecified order
// over a period of time, if you do this on a database that's being
// changed, you may miss records.


// Your parameters.
$per_page = 150;
$mysql_server_hostname = "localhost";
$database_username = "johndoe";
$database_password = "letmein";
$database = "johndoe_db";
$scriptname = "dumpdb.php";     // filename under which this is installed


// You shouldn't need to change anything in the rest of this script,
// unless I screwed it up. Considering it's PHP, that's not that unlikely.

mysql_connect($mysql_server_hostname, $database_username, $database_password) 
or die(mysql_error());
mysql_select_db($database) or die(mysql_error());


function sql($query) {
  $result = mysql_query($query) or die(mysql_error());
  return $result;
}

function tables() {
  $tables_list = sql("show tables");
  $tables = array();
  while ($row = mysql_fetch_array($tables_list)) {
    $tables[] = $row[0];
  }
  return $tables;
}

function page_link($table, $start, $count, $per_page) {
  $first = $start + 1;          // 0 -> 1, etc.
  $last = $start + $per_page;
  if ($last > $count) {
    $last = $count;
  }

  global $scriptname;
  return "<li><a href=\"$scriptname?table=$table&start=$start\">$table, rows 
$first-$last</a></li>\n";
}

function index_page() {
  header('Content:type: text/html; charset=utf-8');

  global $mysql_server_hostname, $database_username, $database;
  $date = date("Y-m-d");
  $title = "Database dump directory of $database for user $database_username on 
$mysql_server_hostname on $date";
  print "<html><head><title>$title</title></head>\n<body><h1>$title</h1>\n";

  global $scriptname;
  print "<p><a href=\"$scriptname?schema=please\">Database schema.</a></p>\n";

  foreach (tables() as $table) {
    $result = sql("select count(*) from `$table`");
    $row = mysql_fetch_array($result);
    $count = $row[0];

    global $per_page;
    for ($i = 0; $i < $count; $i += $per_page) {
      print page_link($table, $i, $count, $per_page);
    }
  }

  print "</ul></body></html>\n";
}

function text_output() {
  header('Content-type: text/plain; charset=utf-8');
}

function schema_page() {
  text_output();
  foreach (tables() as $table) {
    $result = sql("show create table `$table`");
    while ($row = mysql_fetch_array($result)) {
      print $row[1];            // $row[0] is the table name
      print ";\n\n";
    }
  }
}

// Turns an associative array into an INSERT statement in the MySQL SQL dialect.
function insert_query($table, $record) {
  $fields = array();
  $values = array();
  foreach ($record as $field => $value) {
    $fields[] = "`".$field."`";
    $values[] = "'".addslashes($value)."'";
  }

  $field_str = join(", ", $fields);
  $values_str = join(", ", $values);

  return "insert into `$table` ($field_str) values ($values_str);\n";
}

function dump_table($table, $start) {
  text_output();
  global $per_page;

  $result = sql("select * from `$table` limit $per_page offset $start");
  while ($row = mysql_fetch_assoc($result)) {
    print insert_query($table, $row);
  }
}

if (isset($_GET['schema'])) {
  schema_page();
} else if (isset($_GET['table'])) {
  dump_table($_GET['table'], $_GET['start']);
} else {
  index_page();
}
-- 
To unsubscribe: http://lists.canonical.org/mailman/listinfo/kragen-hacks

Reply via email to