'table' === $t ) ); } /** * Views to dump. * * @param bool|null $all Null = the setting. * @return string[] */ public static function views( $all = null ) { return array_keys( array_filter( self::objects( $all ), static fn( $t ) => 'view' === $t ) ); } /** * All tables and views: name => 'table' | 'view'. * * ROLLBAKR's own working tables (nktmp_*, nkold_*, nkbad_*: what a restore loads, keeps aside * or sets apart, possibly left behind by one that was interrupted) are never dumped, with * "all tables" too: the restore refuses a dump that holds such a name, so a backup with one * could never be restored. * * @param bool|null $all Null = the setting. * @return array */ public static function objects( $all = null ) { global $wpdb; $all = null === $all ? (bool) Rollbakr_Settings::get( 'all_tables' ) : (bool) $all; $rows = $wpdb->get_results( 'SHOW FULL TABLES', ARRAY_N ); // phpcs:ignore WordPress.DB $out = array(); foreach ( (array) $rows as $row ) { $name = (string) $row[0]; if ( ! $all && ! str_starts_with( $name, $wpdb->prefix ) ) { continue; } if ( self::is_own( $name ) ) { continue; } $out[ $name ] = 'VIEW' === strtoupper( (string) $row[1] ) ? 'view' : 'table'; } ksort( $out ); return $out; } /** * Whether a name is one of ROLLBAKR's own working tables (see objects()). * * @param string $name Table or view name. * @return bool */ public static function is_own( $name ) { foreach ( array( Rollbakr_Db_Import::TMP, Rollbakr_Db_Import::OLD, Rollbakr_Db_Import::BAD ) as $own ) { if ( str_starts_with( $name, $own ) ) { return true; } } return false; } /** * Put the connection in UTC, so that TIMESTAMP columns are read as UTC and the dump's own * "SET time_zone" line puts them back unchanged wherever it is loaded (mysqldump's --tz-utc). * The connection is per request, so this is done at the start of every database step. * * @return string|false The time zone the connection had (give it to restore_time_zone() when the * step is over), or false when the server refused (then the dump carries no * time_zone line). */ public static function use_utc() { global $wpdb; $before = (string) $wpdb->get_var( 'SELECT @@session.time_zone' ); // phpcs:ignore WordPress.DB if ( '' === $before || false === $wpdb->query( "SET time_zone = '+00:00'" ) ) { // phpcs:ignore WordPress.DB return false; } return $before; } /** * Put the connection's time zone back, so that whatever runs later in the same request * (other plugins, a cron that continues) sees NOW() as before. * * @param string|false $tz What use_utc() returned. * @return void */ public static function restore_time_zone( $tz ) { global $wpdb; if ( false !== $tz && '' !== $tz ) { $wpdb->query( $wpdb->prepare( 'SET time_zone = %s', $tz ) ); // phpcs:ignore WordPress.DB } } /** * Comment lines at the top of the dump. * * @param string $id Backup id. * @param bool $utc Whether the rows are read in UTC (use_utc() succeeded). * @return string */ public static function header( $id, $utc = true ) { global $wpdb; // The same charset the rows are read with (DB_CHARSET), so nothing is converted twice. $charset = preg_match( '/^[a-z0-9_]+$/i', (string) $wpdb->charset ) ? $wpdb->charset : 'utf8mb4'; return "-- ROLLBAKR backup {$id}\n" . '-- Site: ' . home_url() . "\n" . "-- Prefix: {$wpdb->prefix}\n" . '-- Site id: ' . Rollbakr_Storage::site_id() . "\n" . '-- ROLLBAKR version: ' . ROLLBAKR_VERSION . "\n" . '-- WordPress ' . get_bloginfo( 'version' ) . ' / PHP ' . PHP_VERSION . ' / MySQL ' . $wpdb->db_version() . "\n" . '-- Date: ' . wp_date( 'c' ) . "\n\n" . "SET NAMES {$charset};\n" . "SET FOREIGN_KEY_CHECKS = 0;\n" . "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n" . ( $utc ? "SET time_zone = '+00:00';\n" : '' ) . "\n"; } /** * Lines at the end. * * @return string */ public static function footer() { return "\nSET FOREIGN_KEY_CHECKS = 1;\n-- ROLLBAKR end\n"; } /** * DROP + CREATE for a table or a view. * * @param string $name Table. * @param string $kind 'table' | 'view'. * @return string */ public static function create( $name, $kind = 'table' ) { global $wpdb; // Table names cannot be placeholders in prepare(); here and below they are quoted with backticks // (a ` inside doubled) and come from SHOW TABLES, never from input. Hence the phpcs:ignore lines. $row = $wpdb->get_row( 'SHOW CREATE TABLE `' . str_replace( '`', '``', $name ) . '`', ARRAY_N ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter if ( ! $row || ! isset( $row[1] ) ) { throw new RuntimeException( esc_html( sprintf( 'SHOW CREATE TABLE failed for %s', $name ) ) ); } $sql = (string) $row[1]; if ( 'view' === $kind ) { // Without DEFINER=user@host the view belongs to whoever loads the dump; with it, loading // stops when that user no longer exists (mysqldump --skip-definer). $sql = (string) preg_replace( '/\s+DEFINER\s*=\s*(`[^`]*`|\'[^\']*\'|\S+?)@(`[^`]*`|\'[^\']*\'|\S+)(?=\s)/i', '', $sql ); } $what = 'view' === $kind ? 'VIEW' : 'TABLE'; return "\n-- {$kind} {$name}\nDROP {$what} IF EXISTS `" . str_replace( '`', '``', $name ) . "`;\n" . $sql . ";\n"; } /** * Columns of a table: name => true when binary (written as hex). Generated columns are left * out (a value cannot be inserted into them). * * @param string $name Table. * @return array */ public static function columns( $name ) { global $wpdb; $rows = $wpdb->get_results( 'SHOW FULL COLUMNS FROM `' . str_replace( '`', '``', $name ) . '`', ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter $cols = array(); foreach ( (array) $rows as $row ) { if ( str_contains( strtoupper( (string) ( $row['Extra'] ?? '' ) ), 'GENERATED' ) ) { continue; } $type = strtolower( (string) $row['Type'] ); $cols[ $row['Field'] ] = (bool) preg_match( '/^(tiny|medium|long)?blob|^(var)?binary|^bit\b|^geometry|^(multi)?(point|linestring|polygon)/', $type ); } return $cols; } /** * The single integer primary key of a table, or '' when it has none (then rows are read by offset). * * @param string $name Table. * @return string */ public static function int_key( $name ) { global $wpdb; $rows = $wpdb->get_results( 'SHOW INDEX FROM `' . str_replace( '`', '``', $name ) . "` WHERE Key_name = 'PRIMARY'", ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter if ( 1 !== count( (array) $rows ) ) { return ''; } $col = (string) $rows[0]['Column_name']; $type = $wpdb->get_row( 'SHOW COLUMNS FROM `' . str_replace( '`', '``', $name ) . '` WHERE Field = ' . $wpdb->prepare( '%s', $col ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter return $type && preg_match( '/int\b|^int\(/', strtolower( (string) $type['Type'] ) ) ? $col : ''; } /** * How many rows to read at a time from a table: ROWS, or fewer when its rows are big, so that * one SELECT stays around SELECT_BYTES (post_content of a page builder can be 200 KB a row). * * @param string $name Table. * @return int */ public static function limit_for( $name ) { global $wpdb; $avg = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT avg_row_length FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = %s', $name ) ); // phpcs:ignore WordPress.DB if ( $avg <= 0 ) { return self::ROWS; } return max( 10, min( self::ROWS, intdiv( self::SELECT_BYTES, $avg ) ) ); } /** * Read the next rows of a table. * * @param string $name Table. * @param string $key Integer key column, or ''. * @param mixed $after Last key value read (int; PHP_INT_MIN before the first row), or the offset * when $key is '' (0 before the first row: a negative OFFSET is a syntax error). * @param int $limit Rows. * @return array> * @throws RuntimeException When the SELECT failed (an empty result must never pass for "no more rows"). */ public static function rows( $name, $key, $after, $limit = self::ROWS ) { global $wpdb; $table = '`' . str_replace( '`', '``', $name ) . '`'; if ( '' !== $key ) { $k = '`' . str_replace( '`', '``', $key ) . '`'; $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE {$k} > %d ORDER BY {$k} LIMIT %d", (int) $after, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter } else { $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} LIMIT %d OFFSET %d", $limit, (int) $after ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter } // query() cleared last_error before running; what is in it now is this SELECT's own error. It is // read before flush() below, which clears it again (a failed SELECT once passed for an empty table). $error = (string) $wpdb->last_error; // $wpdb keeps a copy of the last result; drop it so that big rows are held once, not twice. $wpdb->flush(); if ( '' !== $error ) { throw new RuntimeException( esc_html( sprintf( 'Database error: %s', $error ) ) ); } return $rows; } /** * INSERT statements for rows: one statement, cut into several when it grows past INSERT_BYTES. * * @param string $name Table. * @param array $cols Result of columns(). * @param array> $rows Rows. * @return string */ public static function insert( $name, array $cols, array $rows ) { if ( ! $rows ) { return ''; } $names = array_keys( $cols ); $quoted = array_map( static fn( $c ) => '`' . str_replace( '`', '``', $c ) . '`', $names ); $head = 'INSERT INTO `' . str_replace( '`', '``', $name ) . '` (' . implode( ',', $quoted ) . ") VALUES\n"; $out = ''; $stmt = ''; foreach ( $rows as $row ) { $v = array(); foreach ( $names as $col ) { $val = $row[ $col ] ?? null; if ( null === $val ) { $v[] = 'NULL'; } elseif ( $cols[ $col ] ) { $v[] = '' === $val ? "''" : '0x' . bin2hex( (string) $val ); } else { $v[] = "'" . self::escape( (string) $val ) . "'"; } } $tuple = '(' . implode( ',', $v ) . ')'; if ( '' !== $stmt && strlen( $stmt ) + strlen( $tuple ) > self::INSERT_BYTES ) { $out .= $head . $stmt . ";\n"; $stmt = ''; } $stmt .= ( '' === $stmt ? '' : ",\n" ) . $tuple; } return $out . $head . $stmt . ";\n"; } /** * Escape a string for the dump. Not $wpdb->_real_escape() alone: that one also turns every * '%' into a placeholder mark meant for prepare(), which would stay in the dump for good. * * @param string $val Value. * @return string */ public static function escape( $val ) { global $wpdb; return $wpdb->remove_placeholder_escape( $wpdb->_real_escape( $val ) ); } }