Ryan Malloy
e92b7f8700
Because cats have 9 lives, but servers don't - so they need backup-restore! Complete backup solution with S3/MinIO support. - Full WordPress backup (files + database) - S3 / MinIO / S3-compatible storage backends - Scheduled automatic backups - Disaster recovery / one-click restore - Backup integrity validation - Cat-themed admin interface Includes build.sh and .distignore for WordPress-installable release ZIPs.
672 lines
20 KiB
PHP
672 lines
20 KiB
PHP
<?php
|
|
/**
|
|
* Database Backup
|
|
*
|
|
* Secure database backup functionality with prepared statements
|
|
* Addresses SQL injection vulnerabilities found in XCloner
|
|
*
|
|
* @package TigerStyleLife9
|
|
* @since 1.0.0
|
|
*/
|
|
|
|
// Exit if accessed directly
|
|
if (!defined('ABSPATH')) {
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* Database backup class
|
|
*
|
|
* @since 1.0.0
|
|
*/
|
|
class TigerStyle_Life9_Database_Backup {
|
|
|
|
/**
|
|
* Security instance
|
|
*
|
|
* @var TigerStyle_Life9_Security
|
|
*/
|
|
private $security;
|
|
|
|
/**
|
|
* Database connection
|
|
*
|
|
* @var wpdb
|
|
*/
|
|
private $wpdb;
|
|
|
|
/**
|
|
* Backup progress callback
|
|
*
|
|
* @var callable
|
|
*/
|
|
private $progress_callback;
|
|
|
|
/**
|
|
* Constructor
|
|
*/
|
|
public function __construct() {
|
|
global $wpdb;
|
|
$this->wpdb = $wpdb;
|
|
$this->security = tigerstyle_life9()->get_security();
|
|
}
|
|
|
|
/**
|
|
* Export database to SQL file
|
|
*
|
|
* @param string $output_file Output file path
|
|
* @param array $config Export configuration
|
|
* @return bool Success status
|
|
*/
|
|
public function export_database($output_file, $config = []) {
|
|
$defaults = [
|
|
'include_tables' => [],
|
|
'exclude_tables' => [],
|
|
'add_drop_table' => true,
|
|
'add_if_not_exists' => false,
|
|
'disable_keys' => true,
|
|
'single_transaction' => true,
|
|
'lock_tables' => false,
|
|
'where_conditions' => [],
|
|
'max_query_size' => 1048576, // 1MB
|
|
'compress_output' => false
|
|
];
|
|
|
|
$config = array_merge($defaults, $config);
|
|
|
|
// Validate output file path
|
|
if (!$this->security->validate_path(dirname($output_file))) {
|
|
throw new Exception('Invalid output file path');
|
|
}
|
|
|
|
try {
|
|
$tables = $this->get_tables_to_backup($config);
|
|
|
|
if (empty($tables)) {
|
|
throw new Exception('No tables found to backup');
|
|
}
|
|
|
|
$this->log_info('Starting database backup', [
|
|
'tables_count' => count($tables),
|
|
'output_file' => $output_file
|
|
]);
|
|
|
|
// Open output file
|
|
$handle = fopen($output_file, 'w');
|
|
if (!$handle) {
|
|
throw new Exception('Cannot open output file for writing');
|
|
}
|
|
|
|
// Write SQL header
|
|
$this->write_sql_header($handle, $config);
|
|
|
|
// Begin transaction if configured
|
|
if ($config['single_transaction']) {
|
|
fwrite($handle, "START TRANSACTION;\n");
|
|
fwrite($handle, "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n");
|
|
fwrite($handle, "SET AUTOCOMMIT = 0;\n\n");
|
|
}
|
|
|
|
// Disable foreign key checks temporarily
|
|
fwrite($handle, "SET FOREIGN_KEY_CHECKS = 0;\n\n");
|
|
|
|
$total_tables = count($tables);
|
|
$processed_tables = 0;
|
|
|
|
// Export each table
|
|
foreach ($tables as $table) {
|
|
$this->export_table($handle, $table, $config);
|
|
|
|
$processed_tables++;
|
|
if ($this->progress_callback) {
|
|
call_user_func($this->progress_callback, 'database',
|
|
round(($processed_tables / $total_tables) * 100), $table);
|
|
}
|
|
}
|
|
|
|
// Re-enable foreign key checks
|
|
fwrite($handle, "\nSET FOREIGN_KEY_CHECKS = 1;\n");
|
|
|
|
// Commit transaction if configured
|
|
if ($config['single_transaction']) {
|
|
fwrite($handle, "COMMIT;\n");
|
|
}
|
|
|
|
// Write SQL footer
|
|
$this->write_sql_footer($handle);
|
|
|
|
fclose($handle);
|
|
|
|
// Compress if requested
|
|
if ($config['compress_output']) {
|
|
$this->compress_sql_file($output_file);
|
|
}
|
|
|
|
$this->log_info('Database backup completed successfully', [
|
|
'file_size' => filesize($output_file),
|
|
'tables_exported' => count($tables)
|
|
]);
|
|
|
|
return true;
|
|
|
|
} catch (Exception $e) {
|
|
if (isset($handle) && $handle) {
|
|
fclose($handle);
|
|
}
|
|
|
|
$this->log_error('Database backup failed', ['error' => $e->getMessage()]);
|
|
|
|
// Clean up partial file
|
|
if (file_exists($output_file)) {
|
|
unlink($output_file);
|
|
}
|
|
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get list of tables to backup
|
|
*
|
|
* @param array $config Backup configuration
|
|
* @return array Array of table names
|
|
*/
|
|
private function get_tables_to_backup($config) {
|
|
$all_tables = $this->get_all_tables();
|
|
|
|
// If specific tables are included, use only those
|
|
if (!empty($config['include_tables'])) {
|
|
$tables = array_intersect($all_tables, $config['include_tables']);
|
|
} else {
|
|
$tables = $all_tables;
|
|
}
|
|
|
|
// Remove excluded tables
|
|
if (!empty($config['exclude_tables'])) {
|
|
$tables = array_diff($tables, $config['exclude_tables']);
|
|
}
|
|
|
|
// Validate table names for security
|
|
$sanitizer = new TigerStyle_Life9_Sanitizer();
|
|
$valid_tables = [];
|
|
|
|
foreach ($tables as $table) {
|
|
$clean_table = $sanitizer->sanitize_table_name($table);
|
|
if ($clean_table && $this->table_exists($clean_table)) {
|
|
$valid_tables[] = $clean_table;
|
|
}
|
|
}
|
|
|
|
return $valid_tables;
|
|
}
|
|
|
|
/**
|
|
* Get all tables in database
|
|
*
|
|
* @return array Array of table names
|
|
*/
|
|
private function get_all_tables() {
|
|
$tables = [];
|
|
|
|
$results = $this->wpdb->get_results("SHOW TABLES", ARRAY_N);
|
|
|
|
foreach ($results as $row) {
|
|
if (isset($row[0])) {
|
|
$tables[] = $row[0];
|
|
}
|
|
}
|
|
|
|
return $tables;
|
|
}
|
|
|
|
/**
|
|
* Check if table exists
|
|
*
|
|
* @param string $table_name Table name
|
|
* @return bool True if table exists
|
|
*/
|
|
private function table_exists($table_name) {
|
|
$table = $this->wpdb->get_var($this->wpdb->prepare(
|
|
"SHOW TABLES LIKE %s",
|
|
$table_name
|
|
));
|
|
|
|
return $table === $table_name;
|
|
}
|
|
|
|
/**
|
|
* Export single table
|
|
*
|
|
* @param resource $handle File handle
|
|
* @param string $table Table name
|
|
* @param array $config Export configuration
|
|
*/
|
|
private function export_table($handle, $table, $config) {
|
|
$this->log_info('Exporting table: ' . $table);
|
|
|
|
// Get table structure
|
|
$create_table = $this->get_table_structure($table, $config);
|
|
|
|
if ($config['add_drop_table']) {
|
|
fwrite($handle, "DROP TABLE IF EXISTS `{$table}`;\n");
|
|
}
|
|
|
|
fwrite($handle, $create_table . "\n\n");
|
|
|
|
// Get table data
|
|
$this->export_table_data($handle, $table, $config);
|
|
|
|
fwrite($handle, "\n");
|
|
}
|
|
|
|
/**
|
|
* Get table structure (CREATE TABLE statement)
|
|
*
|
|
* @param string $table Table name
|
|
* @param array $config Export configuration
|
|
* @return string CREATE TABLE statement
|
|
*/
|
|
private function get_table_structure($table, $config) {
|
|
$result = $this->wpdb->get_row($this->wpdb->prepare(
|
|
"SHOW CREATE TABLE `%s`",
|
|
$table
|
|
), ARRAY_A);
|
|
|
|
if (!$result || !isset($result['Create Table'])) {
|
|
throw new Exception("Failed to get structure for table: {$table}");
|
|
}
|
|
|
|
$create_table = $result['Create Table'];
|
|
|
|
// Modify CREATE statement if needed
|
|
if ($config['add_if_not_exists']) {
|
|
$create_table = str_replace(
|
|
'CREATE TABLE `' . $table . '`',
|
|
'CREATE TABLE IF NOT EXISTS `' . $table . '`',
|
|
$create_table
|
|
);
|
|
}
|
|
|
|
return $create_table . ';';
|
|
}
|
|
|
|
/**
|
|
* Export table data
|
|
*
|
|
* @param resource $handle File handle
|
|
* @param string $table Table name
|
|
* @param array $config Export configuration
|
|
*/
|
|
private function export_table_data($handle, $table, $config) {
|
|
// Get column information
|
|
$columns = $this->get_table_columns($table);
|
|
|
|
if (empty($columns)) {
|
|
return; // No columns, skip data export
|
|
}
|
|
|
|
// Build column list
|
|
$column_list = '`' . implode('`, `', array_keys($columns)) . '`';
|
|
|
|
// Add DISABLE KEYS for MyISAM tables if configured
|
|
if ($config['disable_keys']) {
|
|
fwrite($handle, "ALTER TABLE `{$table}` DISABLE KEYS;\n");
|
|
}
|
|
|
|
// Prepare base query
|
|
$base_query = "SELECT {$column_list} FROM `{$table}`";
|
|
|
|
// Add WHERE conditions if specified
|
|
$where_clause = '';
|
|
if (isset($config['where_conditions'][$table])) {
|
|
$where_condition = $config['where_conditions'][$table];
|
|
// Validate WHERE condition for security
|
|
if ($this->validate_where_condition($where_condition)) {
|
|
$where_clause = " WHERE {$where_condition}";
|
|
}
|
|
}
|
|
|
|
$query = $base_query . $where_clause;
|
|
|
|
// Get total row count for progress tracking
|
|
$count_query = "SELECT COUNT(*) FROM `{$table}`" . $where_clause;
|
|
$total_rows = $this->wpdb->get_var($count_query);
|
|
|
|
if ($total_rows == 0) {
|
|
return; // No data to export
|
|
}
|
|
|
|
// Export data in chunks to manage memory
|
|
$chunk_size = 1000;
|
|
$offset = 0;
|
|
$current_insert = '';
|
|
$current_size = 0;
|
|
|
|
while ($offset < $total_rows) {
|
|
$chunk_query = $query . " LIMIT {$chunk_size} OFFSET {$offset}";
|
|
$rows = $this->wpdb->get_results($chunk_query, ARRAY_A);
|
|
|
|
if (empty($rows)) {
|
|
break;
|
|
}
|
|
|
|
foreach ($rows as $row) {
|
|
$values = $this->prepare_row_values($row, $columns);
|
|
$insert_line = "({$values})";
|
|
|
|
// Start new INSERT statement if needed
|
|
if (empty($current_insert)) {
|
|
$current_insert = "INSERT INTO `{$table}` ({$column_list}) VALUES\n";
|
|
$current_size = strlen($current_insert);
|
|
}
|
|
|
|
// Check if adding this row would exceed max query size
|
|
if ($current_size + strlen($insert_line) > $config['max_query_size']) {
|
|
// Write current INSERT and start new one
|
|
fwrite($handle, rtrim($current_insert, ",\n") . ";\n");
|
|
$current_insert = "INSERT INTO `{$table}` ({$column_list}) VALUES\n";
|
|
$current_size = strlen($current_insert);
|
|
}
|
|
|
|
$current_insert .= $insert_line . ",\n";
|
|
$current_size += strlen($insert_line) + 2;
|
|
}
|
|
|
|
$offset += $chunk_size;
|
|
}
|
|
|
|
// Write final INSERT statement
|
|
if (!empty($current_insert)) {
|
|
fwrite($handle, rtrim($current_insert, ",\n") . ";\n");
|
|
}
|
|
|
|
// Re-enable keys if configured
|
|
if ($config['disable_keys']) {
|
|
fwrite($handle, "ALTER TABLE `{$table}` ENABLE KEYS;\n");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get table columns information
|
|
*
|
|
* @param string $table Table name
|
|
* @return array Column information
|
|
*/
|
|
private function get_table_columns($table) {
|
|
$columns = [];
|
|
|
|
$results = $this->wpdb->get_results($this->wpdb->prepare(
|
|
"SHOW COLUMNS FROM `%s`",
|
|
$table
|
|
), ARRAY_A);
|
|
|
|
foreach ($results as $column) {
|
|
$columns[$column['Field']] = [
|
|
'type' => $column['Type'],
|
|
'null' => $column['Null'] === 'YES',
|
|
'key' => $column['Key'],
|
|
'default' => $column['Default'],
|
|
'extra' => $column['Extra']
|
|
];
|
|
}
|
|
|
|
return $columns;
|
|
}
|
|
|
|
/**
|
|
* Prepare row values for INSERT statement
|
|
*
|
|
* @param array $row Row data
|
|
* @param array $columns Column information
|
|
* @return string Formatted values string
|
|
*/
|
|
private function prepare_row_values($row, $columns) {
|
|
$values = [];
|
|
|
|
foreach ($row as $column => $value) {
|
|
if ($value === null) {
|
|
$values[] = 'NULL';
|
|
} else {
|
|
// Escape value based on column type
|
|
$column_info = $columns[$column] ?? [];
|
|
$escaped_value = $this->escape_value($value, $column_info);
|
|
$values[] = $escaped_value;
|
|
}
|
|
}
|
|
|
|
return implode(', ', $values);
|
|
}
|
|
|
|
/**
|
|
* Escape value for SQL
|
|
*
|
|
* @param mixed $value Value to escape
|
|
* @param array $column_info Column information
|
|
* @return string Escaped value
|
|
*/
|
|
private function escape_value($value, $column_info) {
|
|
// Use WordPress's built-in escaping
|
|
if (is_numeric($value) && !empty($column_info['type'])) {
|
|
$type = strtolower($column_info['type']);
|
|
|
|
// For numeric types, don't quote if it's actually numeric
|
|
if (strpos($type, 'int') !== false ||
|
|
strpos($type, 'decimal') !== false ||
|
|
strpos($type, 'float') !== false ||
|
|
strpos($type, 'double') !== false) {
|
|
return $value;
|
|
}
|
|
}
|
|
|
|
// For all other types, escape and quote
|
|
return "'" . $this->wpdb->_escape($value) . "'";
|
|
}
|
|
|
|
/**
|
|
* Validate WHERE condition for security
|
|
*
|
|
* @param string $condition WHERE condition
|
|
* @return bool True if condition is safe
|
|
*/
|
|
private function validate_where_condition($condition) {
|
|
// Basic validation to prevent SQL injection
|
|
$dangerous_keywords = [
|
|
'DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER', 'CREATE',
|
|
'EXEC', 'EXECUTE', 'UNION', 'SCRIPT', '--', '/*', '*/'
|
|
];
|
|
|
|
$upper_condition = strtoupper($condition);
|
|
|
|
foreach ($dangerous_keywords as $keyword) {
|
|
if (strpos($upper_condition, $keyword) !== false) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Write SQL file header
|
|
*
|
|
* @param resource $handle File handle
|
|
* @param array $config Export configuration
|
|
*/
|
|
private function write_sql_header($handle, $config) {
|
|
$header = "-- TigerStyle Life9 Database Backup\n";
|
|
$header .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
|
|
$header .= "-- WordPress Version: " . get_bloginfo('version') . "\n";
|
|
$header .= "-- Database: " . DB_NAME . "\n";
|
|
$header .= "-- Host: " . DB_HOST . "\n";
|
|
$header .= "-- PHP Version: " . PHP_VERSION . "\n";
|
|
$header .= "-- Plugin Version: " . TIGERSTYLE_LIFE9_VERSION . "\n";
|
|
$header .= "--\n";
|
|
$header .= "-- WARNING: This file contains sensitive data.\n";
|
|
$header .= "-- Do not share or store in public locations.\n";
|
|
$header .= "--\n\n";
|
|
|
|
$header .= "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n";
|
|
$header .= "SET time_zone = '+00:00';\n\n";
|
|
|
|
fwrite($handle, $header);
|
|
}
|
|
|
|
/**
|
|
* Write SQL file footer
|
|
*
|
|
* @param resource $handle File handle
|
|
*/
|
|
private function write_sql_footer($handle) {
|
|
$footer = "\n-- Backup completed successfully\n";
|
|
$footer .= "-- End of TigerStyle Life9 Database Backup\n";
|
|
|
|
fwrite($handle, $footer);
|
|
}
|
|
|
|
/**
|
|
* Compress SQL file using gzip
|
|
*
|
|
* @param string $file_path SQL file path
|
|
* @return bool Success status
|
|
*/
|
|
private function compress_sql_file($file_path) {
|
|
if (!function_exists('gzencode')) {
|
|
return false;
|
|
}
|
|
|
|
$data = file_get_contents($file_path);
|
|
if ($data === false) {
|
|
return false;
|
|
}
|
|
|
|
$compressed = gzencode($data, 9);
|
|
if ($compressed === false) {
|
|
return false;
|
|
}
|
|
|
|
$compressed_file = $file_path . '.gz';
|
|
$result = file_put_contents($compressed_file, $compressed) !== false;
|
|
|
|
if ($result) {
|
|
// Remove original file and rename compressed file
|
|
unlink($file_path);
|
|
rename($compressed_file, $file_path);
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Set progress callback
|
|
*
|
|
* @param callable $callback Progress callback function
|
|
*/
|
|
public function set_progress_callback($callback) {
|
|
$this->progress_callback = $callback;
|
|
}
|
|
|
|
/**
|
|
* Get database size
|
|
*
|
|
* @return array Database size information
|
|
*/
|
|
public function get_database_size() {
|
|
$query = "SELECT
|
|
table_schema as 'database_name',
|
|
SUM(data_length + index_length) as 'size_bytes',
|
|
COUNT(*) as 'table_count'
|
|
FROM information_schema.tables
|
|
WHERE table_schema = %s
|
|
GROUP BY table_schema";
|
|
|
|
$result = $this->wpdb->get_row($this->wpdb->prepare($query, DB_NAME), ARRAY_A);
|
|
|
|
if (!$result) {
|
|
return [
|
|
'database_name' => DB_NAME,
|
|
'size_bytes' => 0,
|
|
'table_count' => 0,
|
|
'formatted_size' => '0 B'
|
|
];
|
|
}
|
|
|
|
$result['formatted_size'] = $this->format_bytes($result['size_bytes']);
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get table sizes
|
|
*
|
|
* @return array Array of table size information
|
|
*/
|
|
public function get_table_sizes() {
|
|
$query = "SELECT
|
|
table_name,
|
|
table_rows,
|
|
data_length,
|
|
index_length,
|
|
(data_length + index_length) as total_size
|
|
FROM information_schema.tables
|
|
WHERE table_schema = %s
|
|
ORDER BY total_size DESC";
|
|
|
|
$results = $this->wpdb->get_results($this->wpdb->prepare($query, DB_NAME), ARRAY_A);
|
|
|
|
foreach ($results as &$table) {
|
|
$table['formatted_size'] = $this->format_bytes($table['total_size']);
|
|
}
|
|
|
|
return $results;
|
|
}
|
|
|
|
/**
|
|
* Test database connection
|
|
*
|
|
* @return bool True if connection is working
|
|
*/
|
|
public function test_connection() {
|
|
try {
|
|
$result = $this->wpdb->get_var("SELECT 1");
|
|
return $result === '1';
|
|
} catch (Exception $e) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Format bytes to human readable format
|
|
*
|
|
* @param int $bytes Number of bytes
|
|
* @return string Formatted size
|
|
*/
|
|
private function format_bytes($bytes) {
|
|
$units = ['B', 'KB', 'MB', 'GB', 'TB'];
|
|
|
|
for ($i = 0; $bytes > 1024; $i++) {
|
|
$bytes /= 1024;
|
|
}
|
|
|
|
return round($bytes, 2) . ' ' . $units[$i];
|
|
}
|
|
|
|
/**
|
|
* Log info message
|
|
*
|
|
* @param string $message Log message
|
|
* @param array $context Additional context
|
|
*/
|
|
private function log_info($message, $context = []) {
|
|
error_log("TigerStyle Life9 DB Backup [INFO]: {$message}");
|
|
}
|
|
|
|
/**
|
|
* Log error message
|
|
*
|
|
* @param string $message Log message
|
|
* @param array $context Additional context
|
|
*/
|
|
private function log_error($message, $context = []) {
|
|
error_log("TigerStyle Life9 DB Backup [ERROR]: {$message}");
|
|
}
|
|
} |