` (…" without options that reach outside the table's own * storage (DATA/INDEX DIRECTORY, CONNECTION, UNION, FEDERATED/CONNECT/MERGE engines) or define it * from a query; * - views: "CREATE [ALGORITHM=…] [SQL SECURITY …] VIEW `` AS select|with …"; * - triggers: "CREATE TRIGGER BEFORE|AFTER INSERT|UPDATE|DELETE ON FOR EACH ROW …". * * Each statement is also run on its own (mysqli runs one statement per query). * * @package AccessNow\Replica */ namespace AccessNow\Replica\Db; defined( 'ABSPATH' ) || exit; /** * DDL checks. */ final class Ddl_Guard { /** * Table definitions containing any of these (in the text MySQL would run: see without_literals()) * are refused. "=" is optional in MySQL table options, so none of these needs it. ENGINE has to * be followed by one of the listed engines, written as a plain word. */ const FORBIDDEN = '/\b(?:DATA|INDEX)\s+DIRECTORY\b|\bCONNECTION\b|\bUNION\b|\bENGINE\b(?!\s*=?\s*(?:InnoDB|MyISAM|Aria|MEMORY|HEAP|ARCHIVE|CSV|BLACKHOLE|ROCKSDB|TokuDB)\b)|\bSELECT\b|\bVALUES\b(?!\s+(?:LESS\s+THAN|IN)\b)|\bTABLE\b.*\bTABLE\b|;/is'; /** * The SQL MySQL would run, with string contents and quoted identifiers emptied: keywords stay. * * One pass from left to right, so a quote inside a comment can't hide what follows it, and a * comment marker inside a string is only text. Comments become a space. Executable comments * (/*!50100 ... *\/ and MariaDB's /*M! ... *\/) are run by the server, so their content is kept. * * @param string $sql SQL. */ public static function without_literals( $sql ) { $sql = (string) $sql; $n = strlen( $sql ); $out = ''; $exec = 0; $i = 0; while ( $i < $n ) { $c = $sql[ $i ]; $next = $i + 1 < $n ? $sql[ $i + 1 ] : ''; if ( "'" === $c || '"' === $c || '`' === $c ) { $j = $i + 1; while ( $j < $n ) { if ( '`' !== $c && '\\' === $sql[ $j ] ) { $j += 2; continue; } if ( $sql[ $j ] === $c ) { if ( $j + 1 < $n && $sql[ $j + 1 ] === $c ) { $j += 2; continue; } break; } ++$j; } $out .= $c . $c; $i = $j + 1; continue; } if ( '/' === $c && '*' === $next ) { $marker = substr( $sql, $i + 2, 2 ); if ( '!' === ( $marker[0] ?? '' ) || 'M!' === $marker ) { $i += 'M!' === $marker ? 4 : 3; while ( $i < $n && ctype_digit( $sql[ $i ] ) ) { ++$i; } ++$exec; $out .= ' '; continue; } $end = strpos( $sql, '*/', $i + 2 ); $i = false === $end ? $n : $end + 2; $out .= ' '; continue; } if ( $exec && '*' === $c && '/' === $next ) { --$exec; $i += 2; $out .= ' '; continue; } if ( '#' === $c || ( '-' === $c && '-' === $next && ( $i + 2 >= $n || ctype_space( $sql[ $i + 2 ] ) ) ) ) { $end = strpos( $sql, "\n", $i ); $i = false === $end ? $n : $end; $out .= ' '; continue; } $out .= $c; ++$i; } return $out; } /** * Whether a table name is acceptable (up to 64 bytes; no control characters, backticks or slashes). * * @param mixed $name Name. */ public static function valid_name( $name ) { return is_string( $name ) && '' !== $name && strlen( $name ) <= 64 && ! preg_match( '/[\x00-\x1f`\/\\\\]/', $name ); } /** * What is wrong with one table or view of a manifest, or null. * * @param mixed $t Manifest table entry. * @return string|null */ public static function table_problem( $t ) { if ( ! is_array( $t ) || ! self::valid_name( $t['name'] ?? null ) ) { return 'a table has a name that is not allowed'; } $name = (string) $t['name']; $create = (string) ( $t['create'] ?? '' ); $type = (string) ( $t['type'] ?? '' ); if ( 'BASE TABLE' === $type ) { if ( 0 !== strpos( $create, 'CREATE TABLE ' . Connection::ident( $name ) . ' (' ) ) { return sprintf( 'the definition of table %s is not a plain CREATE TABLE', $name ); } if ( preg_match( self::FORBIDDEN, self::without_literals( $create ) ) ) { return sprintf( 'table %s uses options that reach outside the database table (files, servers or other tables)', $name ); } return null; } if ( 'VIEW' === $type ) { if ( ! preg_match( '/^CREATE (?:ALGORITHM=\w+ )?(?:SQL SECURITY (?:DEFINER|INVOKER) )?VIEW ' . preg_quote( Connection::ident( $name ), '/' ) . ' AS (?:select|with)\b/i', $create ) ) { return sprintf( 'the definition of view %s is not a plain CREATE VIEW', $name ); } if ( false !== strpos( self::without_literals( $create ), ';' ) ) { return sprintf( 'the definition of view %s contains more than one statement', $name ); } return null; } return sprintf( 'table %s has an unknown type', $name ); } /** * Parts of a trigger definition, or null when it isn't one. * * @param string $sql CREATE TRIGGER statement (as exported, without DEFINER). * @return array{name: string, timing: string, event: string, table: string, body: string}|null */ public static function parse_trigger( $sql ) { $ident = '(?:`((?:[^`]|``)+)`|([A-Za-z0-9_$]+))'; if ( ! preg_match( '/^\s*CREATE\s+TRIGGER\s+' . $ident . '\s+(BEFORE|AFTER)\s+(INSERT|UPDATE|DELETE)\s+ON\s+' . $ident . '\s+FOR\s+EACH\s+ROW\s+(.+)$/is', (string) $sql, $m ) ) { return null; } $name = '' !== $m[1] ? str_replace( '``', '`', $m[1] ) : $m[2]; $table = '' !== $m[5] ? str_replace( '``', '`', $m[5] ) : $m[6]; if ( ! self::valid_name( $name ) || ! self::valid_name( $table ) ) { return null; } return array( 'name' => $name, 'timing' => strtoupper( $m[3] ), 'event' => strtoupper( $m[4] ), 'table' => $table, 'body' => $m[7], ); } /** * Everything wrong with a manifest's database definitions (empty when it can be restored). * * @param array $manifest Manifest. * @return string[] */ public static function manifest_problems( array $manifest ) { $tables = $manifest['database']['tables'] ?? null; if ( ! is_array( $tables ) ) { return array( 'the backup has no list of database tables' ); } $problems = array(); $names = array(); foreach ( $tables as $t ) { $problem = self::table_problem( $t ); if ( null !== $problem ) { $problems[] = $problem; } else { $names[ $t['name'] ] = true; } } foreach ( (array) ( $manifest['database']['triggers'] ?? array() ) as $trigger ) { $parsed = is_array( $trigger ) ? self::parse_trigger( (string) ( $trigger['sql'] ?? '' ) ) : null; if ( null === $parsed || ! isset( $names[ $parsed['table'] ] ) ) { $problems[] = sprintf( 'trigger %s is not a plain trigger on a table in the backup', is_array( $trigger ) ? (string) ( $trigger['name'] ?? '?' ) : '?' ); } } return array_slice( array_unique( $problems ), 0, 10 ); } }