prefix or SHOW TABLES and are backtick-quoted; values use prepare(). Caching would return stale rows. use SiteZip\Backup\JobStore; use SiteZip\Backup\ZipWriter; defined( 'ABSPATH' ) || exit; /** * Streams the site's tables into numbered SQL parts. * * mysqldump is unavailable on almost every shared host, so the dump is * generated through $wpdb with a row cursor. Table names are checked * against the live table list before ever reaching a query, and values are * escaped through the same driver that will replay them. * * The byte length of the current part is part of the cursor. A request that * dies after appending rows but before saving state would otherwise leave * those rows in the file twice, and the duplicate primary keys would make * the dump impossible to import. */ class DatabaseSource implements SourceInterface { const ROWS_PER_BATCH = 500; /** * Bounds for the adaptive batch size. Batches grow while rows are small * and shrink when they are large, keeping each one near BATCH_BYTES. */ const MIN_BATCH = 1; const MAX_BATCH = 10000; const BATCH_BYTES = 4194304; const PART_BYTES = 20971520; /** * Start a new INSERT statement once one grows past this, to stay well * under typical max_allowed_packet limits on import. */ const STATEMENT_BYTES = 1048576; /** * Scratch directory for SQL parts. * * @var string */ private $dir; /** * Backup options (skip_revisions, skip_spam, skip_transients). * * @var array */ private $options; /** * Constructor. * * @param string $dir Scratch directory. * @param array $options Backup options. */ public function __construct( $dir, array $options = array() ) { $this->dir = trailingslashit( $dir ); $this->options = $options; } /** * Label for the UI. * * @return string */ public function label() { return __( 'Database', 'sitezip' ); } /** * Dump a bounded number of rows. * * @param array $state Job state. * @param ZipWriter $writer Archive writer. * @param float $deadline Stop-by timestamp. * @return bool|\WP_Error */ public function step( array &$state, ZipWriter $writer, $deadline ) { global $wpdb; if ( ! isset( $state['db'] ) ) { \SiteZip\Backup\JobRunner::trace( 'database: listing tables' ); $tables = $this->tables(); if ( is_wp_error( $tables ) ) { return $tables; } $state['db'] = array( 'tables' => $tables, 'ti' => 0, 'offset' => 0, 'last' => null, 'batch' => self::ROWS_PER_BATCH, 'part' => 1, 'bytes' => 0, 'rows' => 0, ); } $db = &$state['db']; $handle = $this->open_part( $db['part'], $db['bytes'] ); if ( is_wp_error( $handle ) ) { return $handle; } if ( 0 === (int) $db['bytes'] ) { $db['bytes'] += $this->append( $handle, $this->file_header() ); } while ( microtime( true ) < $deadline ) { if ( $db['ti'] >= count( $db['tables'] ) ) { // Everything dumped: seal the final part into the archive. $db['bytes'] += $this->append( $handle, "\nSET FOREIGN_KEY_CHECKS=1;\n" ); fclose( $handle ); $sealed = $this->seal( $db['part'], $writer ); if ( is_wp_error( $sealed ) ) { return $sealed; } $state['db_rows'] = $db['rows']; unset( $state['db'] ); return true; } $table = $db['tables'][ $db['ti'] ]; if ( 0 === (int) $db['offset'] ) { $db['pk'] = $this->integer_key( $table ); $db['last'] = null; $db['batch'] = $this->first_batch( $table ); $schema = $this->schema( $table ); if ( is_wp_error( $schema ) ) { fclose( $handle ); return $schema; } $db['bytes'] += $this->append( $handle, $schema ); } $batch = isset( $db['batch'] ) ? (int) $db['batch'] : self::ROWS_PER_BATCH; $pk = isset( $db['pk'] ) ? $db['pk'] : ''; $conds = array_filter( array( $this->row_filter( $table ) ) ); if ( '' !== $pk ) { // Seek by primary key: unlike OFFSET, every batch costs the // same however deep into a large table it is. if ( null !== $db['last'] ) { $conds[] = $wpdb->prepare( "`{$pk}` > %d", $db['last'] ); // phpcs:ignore WordPress.DB.PreparedSQL -- $pk validated by integer_key(). } $where = $conds ? 'WHERE ' . implode( ' AND ', $conds ) . ' ' : ''; // Not passed through prepare(): row filters contain literal '%'. $sql = "SELECT * FROM `{$table}` {$where}ORDER BY `{$pk}` LIMIT " . (int) $batch; } else { $where = $conds ? 'WHERE ' . implode( ' AND ', $conds ) . ' ' : ''; $sql = "SELECT * FROM `{$table}` {$where}LIMIT " . (int) $batch . ' OFFSET ' . (int) $db['offset']; } \SiteZip\Backup\JobRunner::trace( sprintf( 'database: %s rows %d+ (batch %d, table %d of %d)', $table, (int) $db['offset'], $batch, $db['ti'] + 1, count( $db['tables'] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL -- prepared above. $rows = $wpdb->get_results( $sql, ARRAY_A ); // wpdb keeps its own copy of the result; drop it now. $wpdb->flush(); if ( $wpdb->last_error ) { fclose( $handle ); return new \WP_Error( 'sitezip_select', sprintf( /* translators: 1: table name, 2: database error. */ __( 'Could not read table %1$s: %2$s', 'sitezip' ), $table, $wpdb->last_error ) ); } if ( empty( $rows ) ) { $db['ti']++; $db['offset'] = 0; continue; } $sql_out = $this->inserts( $table, $rows ); $db['bytes'] += $this->append( $handle, $sql_out ); $db['offset'] += count( $rows ); $db['rows'] += count( $rows ); if ( '' !== $pk ) { $db['last'] = (int) $rows[ count( $rows ) - 1 ][ $pk ]; } $length = strlen( $sql_out ); if ( $length > self::BATCH_BYTES ) { $db['batch'] = max( self::MIN_BATCH, (int) ( $batch / 2 ) ); } elseif ( count( $rows ) === $batch && $length < self::BATCH_BYTES / 4 ) { $db['batch'] = min( self::MAX_BATCH, $batch * 2 ); } unset( $rows, $sql_out ); if ( $db['bytes'] >= self::PART_BYTES ) { fclose( $handle ); $sealed = $this->seal( $db['part'], $writer ); if ( is_wp_error( $sealed ) ) { return $sealed; } $db['part']++; $db['bytes'] = 0; // A sealed part is a good moment to hand control back. return false; } } fclose( $handle ); return false; } /** * Rows to leave out of a table, as a SQL condition for the rows to KEEP, * or '' to keep everything. * * @param string $table Table name. * @return string */ private function row_filter( $table ) { global $wpdb; $o = $this->options; if ( ! empty( $o['skip_revisions'] ) && $table === $wpdb->posts ) { return "`post_type` <> 'revision'"; } if ( ! empty( $o['skip_revisions'] ) && $table === $wpdb->postmeta ) { return "`post_id` NOT IN (SELECT `ID` FROM `{$wpdb->posts}` WHERE `post_type` = 'revision')"; } if ( ! empty( $o['skip_spam'] ) && $table === $wpdb->comments ) { return "`comment_approved` NOT IN ('spam','trash')"; } if ( ! empty( $o['skip_spam'] ) && $table === $wpdb->commentmeta ) { return "`comment_id` NOT IN (SELECT `comment_ID` FROM `{$wpdb->comments}` WHERE `comment_approved` IN ('spam','trash'))"; } if ( ! empty( $o['skip_transients'] ) && $table === $wpdb->options ) { return "`option_name` NOT LIKE '\\_transient\\_%' AND `option_name` NOT LIKE '\\_site\\_transient\\_%'"; } return ''; } /** * Rows to read in the first batch of a table, from its average row size. * * @param string $table Table name. * @return int */ private function first_batch( $table ) { global $wpdb; // Each table starts from its own average row size: a batch size // learned on small rows would load thousands of large ones (stored // documents, logs) at once and run out of memory. $stats = $wpdb->get_row( $wpdb->prepare( 'SELECT AVG_ROW_LENGTH AS avg_len, DATA_LENGTH AS data_len, TABLE_ROWS AS row_count FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ), ARRAY_A ); $avg = 0; if ( $stats ) { $avg = max( (int) $stats['avg_len'], (int) $stats['row_count'] > 0 ? (int) ( (int) $stats['data_len'] / (int) $stats['row_count'] ) : 0 ); } if ( $avg <= 0 ) { return 100; } return max( self::MIN_BATCH, min( self::MAX_BATCH, (int) floor( ( self::BATCH_BYTES / 2 ) / $avg ) ) ); } /** * The table's primary key column when it is a single integer column, * else an empty string (the dump then falls back to OFFSET paging). * * @param string $table Table name. * @return string */ private function integer_key( $table ) { global $wpdb; // phpcs:ignore WordPress.DB.PreparedSQL -- $table validated by tables(). $keys = $wpdb->get_results( "SHOW KEYS FROM `{$table}` WHERE Key_name = 'PRIMARY'", ARRAY_A ); if ( ! is_array( $keys ) || 1 !== count( $keys ) ) { return ''; } $column = $keys[0]['Column_name']; if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $column ) ) { return ''; } // phpcs:ignore WordPress.DB.PreparedSQL -- $table validated by tables(). $info = $wpdb->get_row( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $wpdb->esc_like( $column ) ), ARRAY_A ); if ( ! $info || ! preg_match( '/^(tiny|small|medium|big)?int\b/i', $info['Type'] ) ) { return ''; } return $column; } /** * The base tables belonging to this installation. * * Views are skipped (their rows belong to other tables), and so is the * plugin's own job table, which must survive a restore untouched. * * @return array|\WP_Error */ private function tables() { global $wpdb; $rows = $wpdb->get_results( 'SHOW FULL TABLES', ARRAY_N ); if ( ! $rows ) { return new \WP_Error( 'sitezip_no_tables', __( 'Could not read the list of database tables.', 'sitezip' ) ); } $prefix = $wpdb->base_prefix; $skip = JobStore::table(); $tables = array(); foreach ( $rows as $row ) { $table = $row[0]; $type = isset( $row[1] ) ? $row[1] : 'BASE TABLE'; if ( 'BASE TABLE' !== $type || $table === $skip || 0 !== strpos( $table, $prefix ) ) { continue; } // Anything that is not a plain identifier is refused outright // rather than quoted, so no crafted table name can reach SQL. if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) { continue; } $tables[] = $table; } return $tables; } /** * CREATE TABLE statement for a table. * * @param string $table Validated table name. * @return string|\WP_Error */ private function schema( $table ) { global $wpdb; // phpcs:ignore WordPress.DB.PreparedSQL -- $table validated by tables(). $row = $wpdb->get_row( "SHOW CREATE TABLE `{$table}`", ARRAY_N ); if ( empty( $row[1] ) ) { return new \WP_Error( 'sitezip_schema', sprintf( /* translators: %s: database table name. */ __( 'Could not read the structure of table %s.', 'sitezip' ), $table ) ); } return "\n-- Table: {$table}\nDROP TABLE IF EXISTS `{$table}`;\n" . $row[1] . ";\n"; } /** * Build INSERT statements for a batch of rows. * * @param string $table Table name. * @param array $rows Rows as associative arrays. * @return string */ private function inserts( $table, array $rows ) { $columns = '`' . implode( '`,`', array_map( array( $this, 'escape_identifier' ), array_keys( $rows[0] ) ) ) . '`'; $prefix = "INSERT INTO `{$table}` ({$columns}) VALUES\n"; $sql = ''; $tuples = array(); $bytes = 0; foreach ( $rows as $row ) { $values = array(); foreach ( $row as $value ) { if ( null === $value ) { $values[] = 'NULL'; } elseif ( ! mb_check_encoding( (string) $value, 'UTF-8' ) ) { // Binary column: hex literals survive a text SQL file intact. $values[] = '' === (string) $value ? "''" : '0x' . bin2hex( (string) $value ); } else { $values[] = "'" . $this->escape( (string) $value ) . "'"; } } $tuple = '(' . implode( ',', $values ) . ')'; $tuples[] = $tuple; $bytes += strlen( $tuple ); if ( $bytes >= self::STATEMENT_BYTES ) { $sql .= $prefix . implode( ",\n", $tuples ) . ";\n"; $tuples = array(); $bytes = 0; } } if ( $tuples ) { $sql .= $prefix . implode( ",\n", $tuples ) . ";\n"; } return $sql; } /** * Escape a value for a quoted SQL literal. * * wpdb::_real_escape() replaces every "%" with a random placeholder that * is only valid for the current request. Written to a file and replayed * later, those placeholders would corrupt every "%" in the database, so * they are removed again here. * * @param string $value Raw value. * @return string */ private function escape( $value ) { global $wpdb; $escaped = $wpdb->_real_escape( $value ); return method_exists( $wpdb, 'remove_placeholder_escape' ) ? $wpdb->remove_placeholder_escape( $escaped ) : $escaped; } /** * Strip anything that is not a plain identifier character. * * @param string $name Column name. * @return string */ private function escape_identifier( $name ) { return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $name ); } /** * Header written at the top of every SQL part. * * @return string */ private function file_header() { global $wpdb; return "-- SiteZip SQL dump\n" . '-- Site: ' . home_url() . "\n" . '-- Generated: ' . gmdate( 'c' ) . "\n" . "SET FOREIGN_KEY_CHECKS=0;\n" . "SET NAMES '" . preg_replace( '/[^A-Za-z0-9_]/', '', $wpdb->charset ) . "';\n" . "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n"; } /** * Path of a SQL part. * * @param int $part Part number. * @return string */ private function part_path( $part ) { return $this->dir . sprintf( 'db-part-%03d.sql', $part ); } /** * Open a part for appending, discarding anything past the saved length. * * @param int $part Part number. * @param int $bytes Saved length. * @return resource|\WP_Error */ private function open_part( $part, $bytes ) { $handle = @fopen( $this->part_path( $part ), 'c+b' ); if ( ! $handle ) { return new \WP_Error( 'sitezip_sql_part', __( 'Could not write the database dump file.', 'sitezip' ) ); } ftruncate( $handle, (int) $bytes ); fseek( $handle, (int) $bytes ); return $handle; } /** * Append to an open part. * * @param resource $handle Part handle. * @param string $sql SQL text. * @return int Bytes written. */ private function append( $handle, $sql ) { return (int) fwrite( $handle, $sql ); } /** * Add a finished part to the archive. * * The local copy is left in place until the whole backup finishes, so a * request that dies after this point can still re-add it on resume. * * @param int $part Part number. * @param ZipWriter $writer Archive writer. * @return true|\WP_Error */ private function seal( $part, ZipWriter $writer ) { $added = $writer->add_file( $this->part_path( $part ), sprintf( 'database/db-part-%03d.sql', $part ) ); if ( is_wp_error( $added ) ) { return $added; } if ( true !== $added ) { return new \WP_Error( 'sitezip_sql_part', __( 'Could not add the database dump to the backup.', 'sitezip' ) ); } return true; } }