!.|&%^~:;'; /** Utility class. */ private function __construct() {} /** * Splits a payload into statements at the semicolons that sit outside quotes. * * A string holding ";\n" stays inside its statement, which is what separates * this from cutting on the two characters. * * @return list Trimmed, non-empty statements without their terminator. * @throws PeerSqlRefused On an unterminated string or comment, or anything the scanner refuses. */ public static function split( string $sql ): array { $statements = []; $start = 0; foreach ( self::scan( $sql ) as [ $kind, $text, $end ] ) { if ( 'op' !== $kind || ';' !== $text ) { continue; } $statement = trim( substr( $sql, $start, $end - 1 - $start ) ); if ( '' !== $statement ) { $statements[] = $statement; } $start = $end; } $tail = trim( substr( $sql, $start ) ); if ( '' !== $tail ) { $statements[] = $tail; } return $statements; } /** * Accepts one row INSERT or one CREATE TABLE on the placeholder, or refuses. * * @param string $target The table name the head must carry. * @param list $reference_prefixes Prefixes a foreign key may reference; empty means any plain name. * * @throws PeerSqlRefused When the statement is anything else. */ public static function assert_statement( string $statement, string $target = self::TARGET, array $reference_prefixes = [] ): void { $first = strtoupper( strtok( ltrim( $statement ), " \t\r\n(" ) ?: '' ); match ( $first ) { 'INSERT' => self::check_insert( $statement, $target ), 'CREATE' => self::check_create( $statement, $target, $reference_prefixes ), default => throw new PeerSqlRefused( 'not an INSERT or CREATE TABLE' ), }; } /** * Accepts a schema that is exactly one CREATE TABLE on the target. * * @param list $reference_prefixes Prefixes a foreign key may reference; empty means any plain name. * * @throws PeerSqlRefused When it is anything else, or holds a second statement. */ public static function assert_schema( string $schema, string $target = self::TARGET, array $reference_prefixes = [] ): void { $statements = self::split( $schema ); if ( 1 !== count( $statements ) ) { throw new PeerSqlRefused( 'schema is not a single statement' ); } self::check_create( $statements[0], $target, $reference_prefixes ); } /** * Accepts a CONSTRAINT ... FOREIGN KEY or CHECK clause as ALTER TABLE ... ADD would carry it. * * @param list $reference_prefixes Prefixes a foreign key may reference; empty means any plain name. * * @throws PeerSqlRefused On anything else, or a reference outside the transfer. */ public static function assert_constraint( string $clause, array $reference_prefixes = [] ): void { $tokens = []; $prev = null; foreach ( self::scan( $clause, 0, true ) as $token ) { self::screen( $token, $prev ); $tokens[] = $token; $prev = $token; } self::check_definition_tokens( $tokens, $reference_prefixes, true ); } /** * Refuses column names that cannot sit safely between backticks. * * @param array $columns Names the peer listed for a row set. * * @throws PeerSqlRefused On an empty, over-long or unsafe name. */ public static function assert_columns( array $columns ): void { foreach ( $columns as $column ) { if ( ! is_string( $column ) || '' === $column || strlen( $column ) > 64 || 1 === preg_match( '/[`\x00-\x1f\x7f]/', $column ) ) { throw new PeerSqlRefused( 'unsafe column name' ); } } } /** * Whether a statement reaches the database server's files: INTO OUTFILE/DUMPFILE, * LOAD_FILE() or LOAD DATA/XML. String literals and backticked names are ignored, so * a post that talks about them does not trip it. * * For trusted input (an uploaded dump, our own backup), where the peer allow-list * would be too strict but file access has no business. */ public static function reaches_server_files( string $sql ): bool { if ( 1 !== preg_match( '/OUTFILE|DUMPFILE|LOAD_FILE|\bLOAD\b/i', $sql ) ) { return false; } return 1 === preg_match( '/\bINTO\s+(?:OUTFILE|DUMPFILE)\b|\bLOAD_FILE\s*\(|\bLOAD\s+(?:DATA|XML)\b/i', self::without_literals( $sql ) ); } /** The statement with quoted strings and backticked names cut out. */ private static function without_literals( string $sql ): string { $code = ''; $len = strlen( $sql ); $i = 0; while ( $i < $len ) { $run = strcspn( $sql, '\'"`', $i ); $code .= substr( $sql, $i, $run ); $i += $run; if ( $i >= $len ) { break; } $quote = $sql[ $i ]; $j = $i + 1; while ( $j < $len && $sql[ $j ] !== $quote ) { $j += ( '\\' === $sql[ $j ] && '`' !== $quote ) ? 2 : 1; } $code .= ' '; $i = $j + 1; } return $code; } /** * Reads SQL into [kind, text, end offset] tokens, refusing what the allow-list never needs. * * Kinds: str (text empty, content is never inspected), ident (unescaped), word, * num, hex (0x, X'', 0b, b'') and op. Comments and `@` are refused, except an * executable `/*!` comment, whose body is scanned as if it were written inline. * * @param bool $escaped_quotes Accept `\'text\'` after an introducer, which is how MySQL 8 prints * string literals inside CHECK expressions. * * @return Generator * @throws PeerSqlRefused */ private static function scan( string $sql, int $depth = 0, bool $escaped_quotes = false ): Generator { $len = strlen( $sql ); $i = 0; $introduce = false; while ( $i < $len ) { $skip = strspn( $sql, " \t\r\n\x0b\x0c", $i ); if ( $skip > 0 ) { $i += $skip; continue; } $c = $sql[ $i ]; $next = $sql[ $i + 1 ] ?? ''; $introduce_here = $introduce; $introduce = false; if ( "'" === $c || '"' === $c ) { $i = self::string_end( $sql, $i, $c ); yield [ 'str', '', $i ]; continue; } if ( '`' === $c ) { $end = $i + 1; while ( true ) { $end += strcspn( $sql, '`', $end ); if ( $end >= $len ) { throw new PeerSqlRefused( 'unterminated identifier' ); } if ( '`' === ( $sql[ $end + 1 ] ?? '' ) ) { $end += 2; continue; } break; } $name = str_replace( '``', '`', substr( $sql, $i + 1, $end - $i - 1 ) ); if ( '' === $name ) { throw new PeerSqlRefused( 'empty identifier' ); } $i = $end + 1; yield [ 'ident', $name, $i ]; continue; } if ( '#' === $c || ( '-' === $c && '-' === $next ) ) { throw new PeerSqlRefused( 'comment' ); } if ( '/' === $c && '*' === $next ) { $end = strpos( $sql, '*/', $i + 2 ); if ( false === $end || 0 !== $depth || 1 !== preg_match( '/\G\/\*(?:M)?!\d*/', $sql, $m, 0, $i ) ) { throw new PeerSqlRefused( 'comment' ); } $body = substr( $sql, $i + strlen( $m[0] ), $end - $i - strlen( $m[0] ) ); yield from self::scan( $body, 1, $escaped_quotes ); $i = $end + 2; continue; } if ( '\\' === $c && "'" === $next && $escaped_quotes && $introduce_here ) { $close = strpos( $sql, "\\'", $i + 2 ); if ( false === $close || strcspn( $sql, '\'"\\', $i + 2 ) < $close - $i - 2 ) { throw new PeerSqlRefused( 'bad escaped string' ); } $i = $close + 2; yield [ 'str', '', $i ]; continue; } if ( ctype_digit( $c ) || ( '.' === $c && ctype_digit( $next ) ) ) { preg_match( '/\G(?:0[xX][0-9a-fA-F]+|0[bB][01]+|(?:\d+\.?\d*|\.\d+)(?:[eE][+-]?\d+)?)/', $sql, $m, 0, $i ); $i += strlen( $m[0] ); // 1abc is an identifier to the server and a malformed value to us. if ( 1 === preg_match( '/\G[A-Za-z0-9_$\x80-\xff]/', $sql, $unused, 0, $i ) ) { throw new PeerSqlRefused( 'malformed number' ); } yield [ 1 === preg_match( '/^0[xXbB][0-9a-fA-F]/', $m[0] ) ? 'hex' : 'num', $m[0], $i ]; continue; } if ( 1 === preg_match( "/\\G(?:[xX]'[0-9a-fA-F]*'|[bB]'[01]*')/", $sql, $m, 0, $i ) ) { $i += strlen( $m[0] ); yield [ 'hex', $m[0], $i ]; continue; } if ( 1 === preg_match( '/\G[A-Za-z_$\x80-\xff][A-Za-z0-9_$\x80-\xff]*/', $sql, $m, 0, $i ) ) { $i += strlen( $m[0] ); $introduce = '_' === $m[0][0]; yield [ 'word', $m[0], $i ]; continue; } if ( false !== strpos( self::OPERATORS, $c ) ) { if ( ';' === $c && 0 !== $depth ) { throw new PeerSqlRefused( 'statement break inside a comment' ); } ++$i; yield [ 'op', $c, $i ]; continue; } throw new PeerSqlRefused( 'unexpected character' ); } } /** Offset just past the string that opens at $start. Honours \x escapes and a doubled quote. */ private static function string_end( string $sql, int $start, string $quote ): int { $len = strlen( $sql ); $end = $start + 1; while ( true ) { $end += strcspn( $sql, $quote . '\\', $end ); if ( $end >= $len ) { throw new PeerSqlRefused( 'unterminated string' ); } if ( '\\' === $sql[ $end ] ) { $end += 2; continue; } if ( $quote === ( $sql[ $end + 1 ] ?? '' ) ) { $end += 2; continue; } return $end + 1; } } /** INSERT INTO `target` (`col`, ...) VALUES (literal, ...)[, (...)]... and nothing after it. */ private static function check_insert( string $statement, string $target ): void { $tokens = self::scan( $statement ); self::expect_word( $tokens, 'INSERT' ); self::expect_word( $tokens, 'INTO' ); $name = self::take( $tokens ); if ( 'ident' !== $name[0] || $target !== $name[1] ) { throw new PeerSqlRefused( 'insert into a table other than the placeholder' ); } self::expect_op( $tokens, '(' ); do { if ( 'ident' !== self::take( $tokens )[0] ) { throw new PeerSqlRefused( 'column list' ); } $separator = self::take( $tokens ); } while ( self::is_op( $separator, ',' ) ); if ( ! self::is_op( $separator, ')' ) ) { throw new PeerSqlRefused( 'column list' ); } self::expect_word( $tokens, 'VALUES' ); while ( true ) { self::expect_op( $tokens, '(' ); do { self::check_value( $tokens ); $separator = self::take( $tokens ); } while ( self::is_op( $separator, ',' ) ); if ( ! self::is_op( $separator, ')' ) ) { throw new PeerSqlRefused( 'row values' ); } if ( ! $tokens->valid() ) { return; } if ( ! self::is_op( self::take( $tokens ), ',' ) ) { throw new PeerSqlRefused( 'text after the rows' ); } } } /** One value: a string, number, NULL, hex or bit literal, or an introducer and its literal. */ private static function check_value( Generator $tokens ): void { $value = self::take( $tokens ); switch ( $value[0] ) { case 'str': case 'num': case 'hex': return; case 'op': if ( ( '-' === $value[1] || '+' === $value[1] ) && 'num' === self::take( $tokens )[0] ) { return; } break; case 'word': $upper = strtoupper( $value[1] ); if ( 'NULL' === $upper ) { return; } if ( in_array( $upper, self::INTRODUCERS, true ) && in_array( self::take( $tokens )[0], [ 'str', 'hex' ], true ) ) { return; } break; } throw new PeerSqlRefused( 'a value that is not a literal' ); } /** * One CREATE TABLE on the target: a balanced column list, table options without a * SELECT, LIKE, file or remote clause, and no second statement. * * @param list $reference_prefixes */ private static function check_create( string $statement, string $target, array $reference_prefixes ): void { $tokens = self::scan( $statement, 0, true ); self::expect_word( $tokens, 'CREATE' ); self::expect_word( $tokens, 'TABLE' ); $name = self::take( $tokens ); if ( 'ident' !== $name[0] || $target !== $name[1] ) { throw new PeerSqlRefused( 'create of a table other than the placeholder' ); } self::expect_op( $tokens, '(' ); $depth = 1; $definition = []; $prev = null; while ( true ) { $token = self::take( $tokens ); self::screen( $token, $prev ); $prev = $token; if ( self::is_op( $token, '(' ) ) { ++$depth; } elseif ( self::is_op( $token, ')' ) ) { if ( 0 === --$depth ) { self::check_definition_tokens( $definition, $reference_prefixes ); break; } } elseif ( self::is_op( $token, ',' ) && 1 === $depth ) { self::check_definition_tokens( $definition, $reference_prefixes ); $definition = []; continue; } $definition[] = $token; } $depth = 0; $engine = false; while ( $tokens->valid() ) { $token = self::take( $tokens ); self::screen( $token, $prev ); $prev = $token; if ( 'word' === $token[0] && in_array( strtoupper( $token[1] ), self::DENIED_IN_OPTIONS, true ) ) { throw new PeerSqlRefused( 'a clause that copies or reaches outside the table' ); } if ( $engine ) { if ( self::is_op( $token, '=' ) ) { continue; } if ( 'word' !== $token[0] || ! in_array( strtoupper( $token[1] ), self::ENGINES, true ) ) { throw new PeerSqlRefused( 'storage engine' ); } $engine = false; continue; } if ( 'word' === $token[0] && 'ENGINE' === strtoupper( $token[1] ) ) { $engine = true; } elseif ( self::is_op( $token, '(' ) ) { ++$depth; } elseif ( self::is_op( $token, ')' ) && --$depth < 0 ) { throw new PeerSqlRefused( 'unbalanced parentheses' ); } } if ( 0 !== $depth || $engine ) { throw new PeerSqlRefused( 'unbalanced parentheses' ); } } /** * Words that read files, run a subquery or stall the server, and a stray statement break. * * @param array{0: string, 1: string, 2: int}|null $prev The token before this one. */ private static function screen( array $token, ?array $prev ): void { if ( self::is_op( $token, ';' ) ) { throw new PeerSqlRefused( 'second statement' ); } if ( 'word' !== $token[0] ) { return; } $upper = strtoupper( $token[1] ); if ( in_array( $upper, self::DENIED, true ) || ( 'VALUES' === $upper && null !== $prev && self::is_op( $prev, '(' ) ) ) { throw new PeerSqlRefused( 'a word that can read files or run a subquery' ); } } /** * A top-level entry of the column list. Columns and indexes pass; a constraint is checked. * * @param list $definition * @param list $reference_prefixes */ private static function check_definition_tokens( array $definition, array $reference_prefixes, bool $must_be_constraint = false ): void { if ( [] === $definition ) { throw new PeerSqlRefused( 'empty definition' ); } $first = 'word' === $definition[0][0] ? strtoupper( $definition[0][1] ) : ''; if ( in_array( $first, [ 'CONSTRAINT', 'FOREIGN', 'CHECK' ], true ) ) { self::check_constraint_tokens( $definition, $reference_prefixes ); return; } if ( $must_be_constraint ) { throw new PeerSqlRefused( 'not a foreign key or check' ); } // SHOW CREATE TABLE quotes every column name, so a bare word here is an index // keyword or something like `(LIKE other_table)`, which copies a table. if ( 'ident' !== $definition[0][0] && ! in_array( $first, self::INDEX_KEYWORDS, true ) ) { throw new PeerSqlRefused( 'unexpected table element' ); } } /** * CONSTRAINT [`name`] FOREIGN KEY (`cols`) REFERENCES `table` [(`cols`)] [MATCH x] [ON DELETE|UPDATE action]... * or [CONSTRAINT [`name`]] CHECK (expression) [[NOT] ENFORCED]. * * @param list $t * @param list $reference_prefixes */ private static function check_constraint_tokens( array $t, array $reference_prefixes ): void { $i = 0; if ( self::word_is( $t[0], 'CONSTRAINT' ) ) { $i = 1; if ( isset( $t[ $i ] ) && ! self::word_is( $t[ $i ], 'FOREIGN' ) && ! self::word_is( $t[ $i ], 'CHECK' ) ) { self::plain_name( $t[ $i ] ); ++$i; } } if ( isset( $t[ $i ] ) && self::word_is( $t[ $i ], 'CHECK' ) ) { self::check_check_tokens( $t, $i + 1 ); return; } if ( ! isset( $t[ $i ], $t[ $i + 1 ] ) || ! self::word_is( $t[ $i ], 'FOREIGN' ) || ! self::word_is( $t[ $i + 1 ], 'KEY' ) ) { throw new PeerSqlRefused( 'not a foreign key or check' ); } $i += 2; if ( isset( $t[ $i ] ) && 'op' !== $t[ $i ][0] ) { self::plain_name( $t[ $i++ ] ); } $i = self::plain_list( $t, $i ); if ( ! isset( $t[ $i ] ) || ! self::word_is( $t[ $i ], 'REFERENCES' ) ) { throw new PeerSqlRefused( 'foreign key without REFERENCES' ); } ++$i; if ( ! isset( $t[ $i ] ) ) { throw new PeerSqlRefused( 'foreign key without a table' ); } $table = self::plain_name( $t[ $i++ ] ); if ( [] !== $reference_prefixes && [] === array_filter( $reference_prefixes, static fn( string $p ): bool => str_starts_with( $table, $p ) ) ) { throw new PeerSqlRefused( 'foreign key to a table outside this transfer' ); } if ( isset( $t[ $i ] ) && self::is_op( $t[ $i ], '(' ) ) { $i = self::plain_list( $t, $i ); } $count = count( $t ); while ( $i < $count ) { if ( self::word_is( $t[ $i ], 'MATCH' ) && isset( $t[ $i + 1 ] ) && self::word_in( $t[ $i + 1 ], [ 'FULL', 'PARTIAL', 'SIMPLE' ] ) ) { $i += 2; continue; } if ( self::word_is( $t[ $i ], 'ON' ) && isset( $t[ $i + 1 ], $t[ $i + 2 ] ) && self::word_in( $t[ $i + 1 ], [ 'DELETE', 'UPDATE' ] ) ) { $action = $t[ $i + 2 ]; if ( self::word_in( $action, self::ACTIONS ) ) { $i += 3; continue; } if ( self::word_is( $action, 'NO' ) && isset( $t[ $i + 3 ] ) && self::word_is( $t[ $i + 3 ], 'ACTION' ) ) { $i += 4; continue; } if ( self::word_is( $action, 'SET' ) && isset( $t[ $i + 3 ] ) && self::word_in( $t[ $i + 3 ], [ 'NULL', 'DEFAULT' ] ) ) { $i += 4; continue; } } throw new PeerSqlRefused( 'foreign key clause' ); } } /** * CHECK ( expression ) with balanced parentheses, then at most [NOT] ENFORCED. * * @param list $t */ private static function check_check_tokens( array $t, int $i ): void { if ( ! isset( $t[ $i ] ) || ! self::is_op( $t[ $i ], '(' ) ) { throw new PeerSqlRefused( 'check without an expression' ); } $depth = 0; $count = count( $t ); for ( ; $i < $count; ++$i ) { if ( self::is_op( $t[ $i ], '(' ) ) { ++$depth; } elseif ( self::is_op( $t[ $i ], ')' ) && 0 === --$depth ) { ++$i; break; } } if ( 0 !== $depth ) { throw new PeerSqlRefused( 'unbalanced parentheses' ); } $rest = array_map( static fn( array $token ): string => 'word' === $token[0] ? strtoupper( $token[1] ) : '?', array_slice( $t, $i ) ); if ( ! in_array( $rest, [ [], [ 'ENFORCED' ], [ 'NOT', 'ENFORCED' ] ], true ) ) { throw new PeerSqlRefused( 'text after a check' ); } } /** * ( `a`, `b` ) of plain names, starting at $i; returns the index after the closing parenthesis. * * @param list $t */ private static function plain_list( array $t, int $i ): int { if ( ! isset( $t[ $i ] ) || ! self::is_op( $t[ $i ], '(' ) ) { throw new PeerSqlRefused( 'column list' ); } ++$i; while ( true ) { if ( ! isset( $t[ $i ] ) ) { throw new PeerSqlRefused( 'column list' ); } self::plain_name( $t[ $i++ ] ); if ( ! isset( $t[ $i ] ) ) { throw new PeerSqlRefused( 'column list' ); } if ( self::is_op( $t[ $i ], ')' ) ) { return $i + 1; } if ( ! self::is_op( $t[ $i ], ',' ) ) { throw new PeerSqlRefused( 'column list' ); } ++$i; } } /** * A name made of letters, digits, underscore and dollar, quoted or not. * * @param array{0: string, 1: string, 2: int} $token */ private static function plain_name( array $token ): string { if ( ! in_array( $token[0], [ 'ident', 'word' ], true ) || 1 !== preg_match( '/\A[A-Za-z0-9_$]{1,64}\z/', $token[1] ) ) { throw new PeerSqlRefused( 'name with unusual characters' ); } return $token[1]; } /** * Next token, or a refusal when the statement ends early. * * @return array{0: string, 1: string, 2: int} */ private static function take( Generator $tokens ): array { if ( ! $tokens->valid() ) { throw new PeerSqlRefused( 'statement ends early' ); } $token = $tokens->current(); $tokens->next(); return $token; } private static function expect_word( Generator $tokens, string $word ): void { if ( ! self::word_is( self::take( $tokens ), $word ) ) { throw new PeerSqlRefused( "expected {$word}" ); } } private static function expect_op( Generator $tokens, string $op ): void { if ( ! self::is_op( self::take( $tokens ), $op ) ) { throw new PeerSqlRefused( "expected {$op}" ); } } /** @param array{0: string, 1: string, 2: int} $token */ private static function is_op( array $token, string $op ): bool { return 'op' === $token[0] && $op === $token[1]; } /** @param array{0: string, 1: string, 2: int} $token */ private static function word_is( array $token, string $word ): bool { return 'word' === $token[0] && 0 === strcasecmp( $token[1], $word ); } /** * @param array{0: string, 1: string, 2: int} $token * @param list $words Upper case. */ private static function word_in( array $token, array $words ): bool { return 'word' === $token[0] && in_array( strtoupper( $token[1] ), $words, true ); } }