packages/sql-formatter/fuzz/Target/MySqlEquivalence.php
1<?php
2
3declare(strict_types=1);
4
5namespace Fuzz\Target;
6
7use Error;
8use PDO;
9use PDOException;
10use SqlFormatter\Core\FormattingException;
11use SqlFormatter\Core\Style;
12use SqlFormatter\Facade\Formatter;
13use SqlParser\Lexer\SourceException;
14
15/**
16 * Runs a generated statement and its formatted text on MySQL and reports a different answer.
17 *
18 * MySQL cannot undo most statements, and the server may be shared with other test runs,
19 * so statements run as a user of their own with rights on a database of their own only:
20 * the server denies everything that would reach beyond it, such as SHUTDOWN, KILL of other
21 * sessions or SET GLOBAL, and the database is dropped and created again before every
22 * statement, so both texts meet the same empty schema. A statement that ends the
23 * connection, or changes the fuzz user's own password, is followed by a fresh user and
24 * connection. A mismatch is confirmed by running the original once more, so a statement
25 * that reads the clock is not reported.
26 */
27final class MySqlEquivalence
28{
29 /**
30 * The user and database the statements run in, recreated whenever the connection is replaced.
31 */
32 public const USER = 'sql_formatter_fuzz';
33
34 /**
35 * Client and server errors after which the connection is replaced rather than compared:
36 * the server went away, the connection was killed, or the fuzz user cannot log in any more.
37 *
38 * @var list<int>
39 */
40 public const CONNECTION_ERRORS = [1040, 1045, 1820, 2002, 2006, 2013, 3118];
41
42 private PDO $pdo;
43
44 /**
45 * @param string $dsn PDO data source name of the MySQL server under test, without a database
46 * @param string $rootPassword Password of the root account that creates the fuzz user
47 * @param Formatter $formatter The formatter under test
48 * @param Style $style The layout preset in use, for findings
49 * @param string $grammarVersion Grammar version that produced the statement, e.g. "mysql-8.4.7"
50 */
51 public function __construct(
52 private readonly string $dsn,
53 private readonly string $rootPassword,
54 private readonly Formatter $formatter,
55 private readonly Style $style,
56 private readonly string $grammarVersion,
57 ) {
58 $this->pdo = $this->connect();
59 }
60
61 /**
62 * Verifies that MySQL answers the formatted statement as it answers the original.
63 *
64 * @param string $sql Statement produced by the grammar
65 * @param string $input Fuzzer input that produced the statement, so a finding can be replayed
66 *
67 * @throws Error When the statement is empty, formatting fails verification, or the answers differ
68 */
69 public function verify(string $sql, string $input): void
70 {
71 if ($sql === '') {
72 throw new Error("Statement generation returned an empty string\nInput (hex): " . bin2hex($input));
73 }
74 $context = "Grammar: {$this->grammarVersion}\nStyle: {$this->style->value}\nInput (hex): " . bin2hex($input) . "\nSQL: {$sql}";
75 try {
76 $formatted = $this->formatter->format($sql);
77 } catch (SourceException) {
78 return;
79 } catch (FormattingException $failure) {
80 throw new Error("Formatting failed verification\n{$context}\nError: {$failure->getMessage()}", 0, $failure);
81 }
82 $before = $this->execute($sql);
83 $after = $this->execute($formatted);
84 if ($before === $after || $this->execute($sql) !== $before) {
85 return;
86 }
87 throw new Error(
88 "Formatted SQL behaves differently on MySQL\n{$context}\nFormatted: {$formatted}\n" .
89 'Before: ' . json_encode($before, JSON_INVALID_UTF8_SUBSTITUTE) . "\n" .
90 'After: ' . json_encode($after, JSON_INVALID_UTF8_SUBSTITUTE),
91 );
92 }
93
94 /**
95 * Executes one statement on a recreated database and answers its rows, its affected row count, or its error.
96 *
97 * A parse error quotes the remaining text and names its line; both are the statement's
98 * own layout, so they are dropped before the message is compared.
99 *
100 * @return array{string, mixed}|array{string, int, string} ["ok", rows or count], ["error", code, message] or ["lost", code]
101 */
102 public function execute(string $sql): array
103 {
104 try {
105 $this->pdo->exec('DROP DATABASE IF EXISTS ' . self::USER);
106 $this->pdo->exec('CREATE DATABASE ' . self::USER);
107 $this->pdo->exec('USE ' . self::USER);
108 $statement = $this->pdo->query($sql);
109 if ($statement === false) {
110 fwrite(STDERR, "PDO::query returned false without throwing.\n");
111 exit(2);
112 }
113 $answer = $statement->columnCount() > 0 ? $statement->fetchAll(PDO::FETCH_NUM) : $statement->rowCount();
114 $statement->closeCursor();
115 return ['ok', $answer];
116 } catch (PDOException $rejection) {
117 $code = $rejection->errorInfo[1] ?? 0;
118 $code = is_int($code) ? $code : 0;
119 if (in_array($code, self::CONNECTION_ERRORS, true)) {
120 $this->pdo = $this->connect();
121 return ['lost', $code];
122 }
123 $message = $rejection->errorInfo[2] ?? null;
124 $message = is_string($message) ? $message : $rejection->getMessage();
125 $message = preg_replace("/ near '.*' at line \\d+$/s", '', $message) ?? $message;
126 return ['error', $code, $message];
127 }
128 }
129
130 /**
131 * Recreates the fuzz user with rights on its own database and opens a connection as that user.
132 *
133 * The run ends when the root account cannot do so, because the server is then gone.
134 */
135 public function connect(): PDO
136 {
137 $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false];
138 $user = self::USER;
139 try {
140 $root = new PDO($this->dsn, 'root', $this->rootPassword, $options);
141 $root->exec("DROP USER IF EXISTS '{$user}'@'%'");
142 $root->exec("CREATE USER '{$user}'@'%' IDENTIFIED BY '{$user}'");
143 $root->exec("GRANT ALL ON `{$user}`.* TO '{$user}'@'%'");
144 $pdo = new PDO($this->dsn, $user, $user, $options);
145 $pdo->exec('SET SESSION max_execution_time = 2000');
146 return $pdo;
147 } catch (PDOException $failure) {
148 fwrite(STDERR, "MySQL connection failed: {$failure->getMessage()}\n");
149 exit(2);
150 }
151 }
152}
153