packages/ztd-query-mysql/fuzz/Target/BehaviorTarget.php
1<?php
2
3declare(strict_types=1);
4
5namespace Fuzz\Target;
6
7use Error;
8use PDO;
9use PDOException;
10use PDOStatement;
11use Throwable;
12use ZtdQuery\Config\UnknownSchemaBehavior;
13use ZtdQuery\Config\UnsupportedSqlBehavior;
14use ZtdQuery\Config\ZtdConfig;
15use ZtdQuery\Connection\ConnectionInterface;
16use ZtdQuery\Connection\Exception\DatabaseException;
17use ZtdQuery\Connection\ResultColumn;
18use ZtdQuery\Connection\StatementInterface;
19use ZtdQuery\Exception\DuplicateKeyException;
20use ZtdQuery\Exception\NotNullViolationException;
21use ZtdQuery\Exception\UnknownSchemaException;
22use ZtdQuery\Platform\MySql\MySqlPlatform;
23use ZtdQuery\Platform\ResultColumnTypeResolver;
24use ZtdQuery\QueryExecutor;
25use ZtdQuery\Rewrite\QueryKind;
26
27/**
28 * Compares native SQL with core QueryExecutor plus Platform, independently of adapters.
29 * Checks rows (including multiplicity and NULL), affected counts, rejection reasons,
30 * subsequent table reads, and physical isolation, including on rejected statements.
31 * Unordered row bags are compared; result ordering is outside this target's contract.
32 */
33final class BehaviorTarget implements ConnectionInterface
34{
35 private PDO $physical;
36
37 /**
38 * Connect only to the disposable server owned by the entrypoint.
39 */
40 public function __construct(private readonly string $dsn)
41 {
42 }
43
44 /**
45 * Reset a private database and use a user with privileges on that database only.
46 */
47 public function database(string $side): PDO
48 {
49 $name = 'ztd_fuzz_' . getmypid();
50 $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false];
51 try {
52 $root = new PDO($this->dsn, 'root', 'root', $options);
53 $root->exec("DROP DATABASE IF EXISTS `$name`");
54 $root->exec("CREATE DATABASE `$name`");
55 $root->exec("DROP USER IF EXISTS '$name'@'%'");
56 $root->exec("CREATE USER '$name'@'%' IDENTIFIED BY '$name'");
57 $root->exec("GRANT ALL ON `$name`.* TO '$name'@'%'");
58 $pdo = new PDO($this->dsn . ';dbname=' . $name, $name, $name, $options);
59 $pdo->exec('SET SESSION max_execution_time = 2000');
60 $pdo->exec('SET SESSION innodb_lock_wait_timeout = 2');
61 $pdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY NOT NULL, value INTEGER, label VARCHAR(80))');
62 if ($side === 'physical') {
63 $pdo->exec("INSERT INTO items VALUES (9999, 9999, 'physical-only')");
64 }
65 return $pdo;
66 } catch (PDOException $failure) {
67 fwrite(STDERR, "MySQL setup failed: {$failure->getMessage()}\n");
68 exit(2);
69 }
70 }
71
72 /**
73 * Run a grammar statement against equal fixtures and compare observable effects.
74 * Native execution is repeated on a fresh database to identify volatile SQL.
75 * All three runs reuse the same catalog and user names, avoiding environment mismatches.
76 * No SQL exception is accepted merely because both executions threw something.
77 *
78 * @throws Error When ZTD differs from the native server
79 * @throws PDOException When reading a database fails
80 * @throws DatabaseException When fixture setup through ZTD fails
81 */
82 public function verify(string $sql, string $input): void
83 {
84
85 unset($this->physical);
86 $fixtures = "INSERT INTO items VALUES (1, -2, 'first'), (2, 0, NULL), (3, 7, 'O''Brien')";
87 $native = $this->database('reference');
88 $native->exec($fixtures);
89 $native->exec('UPDATE items SET value = value WHERE 0 = 1');
90 $expected = $this->native($native, $sql);
91 $state = $this->tables($native);
92 $native = null;
93 $native = $this->database('reference');
94 $native->exec($fixtures);
95 $native->exec('UPDATE items SET value = value WHERE 0 = 1');
96 $stable = $this->native($native, $sql) === $expected && $this->tables($native) === $state;
97 $native = null;
98 $this->physical = $this->database('physical');
99 $before = $this->snapshot($this->physical);
100 $context = 'Input (hex): ' . bin2hex($input) . "\nSQL: {$sql}";
101 try {
102 $executor = new QueryExecutor($this, new MySqlPlatform(), new ZtdConfig(UnsupportedSqlBehavior::Exception, UnknownSchemaBehavior::Exception));
103 $this->execute($executor, $fixtures);
104 $this->physical->exec('UPDATE items SET value = value WHERE 0 = 1');
105 $actual = $this->virtual($executor, $sql);
106 if (!$stable) {
107 return;
108 }
109 if ($expected !== $actual) {
110 throw new Error($context . "\nNative and ZTD outcomes differ.\nExpected: " . var_export($expected, true) . "\nActual: " . var_export($actual, true));
111 }
112 foreach (array_unique(['items', ...array_keys($state)]) as $table) {
113 $definition = $executor->session()->tableDefinition($table);
114 $observed = $definition === null ? null : [
115 'columns' => $definition->columns,
116 'rows' => $this->virtual($executor, 'SELECT * FROM ' . $this->quote($table)),
117 ];
118 if (($state[$table] ?? null) !== $observed) {
119 throw new Error($context . "\nVirtual table differs after the statement: " . $table . "\nExpected: " . var_export($state[$table] ?? null, true) . "\nActual: " . var_export($observed, true));
120 }
121 }
122 } finally {
123
124 if ($before !== $this->snapshot($this->physical)) {
125 throw new Error("The physical database changed.\n" . $context);
126 }
127 }
128 }
129
130 /**
131 * Execute through core, including transaction statements and returning writes.
132 * @return array<mixed>
133 * @throws PDOException When native result-select SQL fails
134 * @throws DatabaseException When ZTD rejects a statement
135 * @throws Error When ZTD silently skips a statement
136 */
137 public function execute(QueryExecutor $executor, string $sql): array
138 {
139 $transaction = $executor->transactionStatement($sql);
140 if ($transaction !== null) {
141 $executor->session()->applyTransactionStatement($transaction);
142 return ['ok', 0];
143 }
144 $plan = $executor->rewrite($sql);
145 if ($plan->kind() === QueryKind::SKIPPED) {
146 throw new Error('ZTD silently skipped: ' . $sql);
147 }
148 $result = $executor->processExecutedStatement($plan, $this->query($plan->sql()));
149 return ['ok', $result->hasResultSet() ? $this->rows($result->fetchAll()) : $result->rowCount()];
150 }
151
152 /**
153 * Capture declared database rejections, leaving programming failures to PHP-Fuzzer.
154 * @return array<mixed>
155 * @throws Error When the session silently skips SQL
156 */
157 public function virtual(QueryExecutor $executor, string $sql): array
158 {
159 try {
160 return $this->execute($executor, $sql);
161 } catch (PDOException|DatabaseException $failure) {
162 return ['error', $this->rejection($failure)];
163 }
164 }
165
166 /**
167 * Use the native server as the outcome oracle, including for invalid statements.
168 * @return array<mixed>
169 */
170 public function native(PDO $pdo, string $sql): array
171 {
172 try {
173 $statement = $pdo->query($sql);
174 if ($statement === false) {
175 fwrite(STDERR, "PDO::query failed without an exception.\n");
176 exit(2);
177 }
178 $answer = ['ok', $statement->columnCount() > 0 ? $this->rows($statement->fetchAll(PDO::FETCH_ASSOC)) : $statement->rowCount()];
179 $statement->closeCursor();
180 return $answer;
181 } catch (PDOException $failure) {
182 return ['error', $this->rejection($failure)];
183 }
184 }
185
186 /**
187 * Compare narrowly identified domain failures; every other native error keeps its identity.
188 * Unknown ZTD rejections retain their exception class and message and cannot match native errors.
189 */
190 public function rejection(Throwable $failure): string
191 {
192 for ($cause = $failure; $cause !== null; $cause = $cause->getPrevious()) {
193 if ($cause instanceof DuplicateKeyException) {
194 return 'unique';
195 }
196 if ($cause instanceof NotNullViolationException) {
197 return 'not-null';
198 }
199 if ($cause instanceof UnknownSchemaException) {
200 return 'missing-table';
201 }
202 if ($cause instanceof PDOException) {
203 $state = $cause->errorInfo[0] ?? '';
204 $code = $cause->errorInfo[1] ?? 0;
205 $message = $cause->errorInfo[2] ?? $cause->getMessage();
206 $state = is_string($state) ? $state : '';
207 $message = is_string($message) ? $message : $cause->getMessage();
208 if (str_starts_with($state, '08') || str_starts_with($state, '57P0') || in_array($code, [2002, 2006, 2013], true)) {
209 fwrite(STDERR, 'Database connection failed: ' . $message . "\n");
210 exit(2);
211 }
212 if ($state === '23505' || $code === 1062 || str_contains($message, 'UNIQUE constraint failed:')) {
213 return 'unique';
214 }
215 if ($state === '23502' || $code === 1048 || str_contains($message, 'NOT NULL constraint failed:')) {
216 return 'not-null';
217 }
218 if ($state === '42P01' || $code === 1146 || str_starts_with($message, 'no such table:')) {
219 return 'missing-table';
220 }
221 return $state . ':' . (is_scalar($code) ? (string) $code : '') . ':' . $message;
222 }
223 }
224 return $failure::class . ':' . $failure->getMessage();
225 }
226
227 /**
228 * Preserve driver values, column names, NULLs and duplicates, ignoring only row order.
229 * @param array<mixed> $rows
230 * @return list<string>
231 */
232 public function rows(array $rows): array
233 {
234 $rows = array_map(serialize(...), $rows);
235 sort($rows, SORT_STRING);
236 return $rows;
237 }
238
239 /**
240 * Inspect every current user table, including tables created by the generated statement.
241 * @return array<string, array{columns: list<string>, rows: array<mixed>}>
242 * @throws PDOException When catalog inspection fails
243 */
244 public function tables(PDO $pdo): array
245 {
246 $tables = [];
247 $catalog = $pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' ORDER BY table_name");
248 if ($catalog === false) {
249 fwrite(STDERR, "Cannot inspect the database catalog.\n");
250 exit(2);
251 }
252 foreach ($catalog->fetchAll(PDO::FETCH_COLUMN) as $name) {
253 if (!is_string($name)) {
254 continue;
255 }
256 $statement = $pdo->query('SELECT * FROM ' . $this->quote($name));
257 if ($statement === false) {
258 fwrite(STDERR, "Cannot inspect table rows.\n");
259 exit(2);
260 }
261 $columns = [];
262 for ($index = 0; $index < $statement->columnCount(); ++$index) {
263 $metadata = $statement->getColumnMeta($index);
264 if ($metadata === false) {
265 fwrite(STDERR, "Cannot inspect result metadata.\n");
266 exit(2);
267 }
268 $columns[] = $metadata['name'];
269 }
270 $tables[$name] = ['columns' => $columns, 'rows' => ['ok', $this->rows($statement->fetchAll(PDO::FETCH_ASSOC))]];
271 }
272 ksort($tables);
273 return $tables;
274 }
275
276 /**
277 * Include catalog definitions so that a physical DDL change is also a finding.
278 * @return array<mixed>
279 * @throws PDOException When snapshot inspection fails
280 */
281 public function snapshot(PDO $pdo): array
282 {
283 $tables = $this->tables($pdo);
284 $definitions = [];
285 foreach (array_keys($tables) as $table) {
286 $definitions[$table] = $this->native($pdo, 'SHOW CREATE TABLE ' . $this->quote($table));
287 }
288 return [$tables, $definitions,
289 $this->native($pdo, 'SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = DATABASE()'),
290 $this->native($pdo, 'SELECT trigger_name, action_statement FROM information_schema.triggers WHERE trigger_schema = DATABASE()'),
291 $this->native($pdo, 'SELECT routine_name, routine_definition FROM information_schema.routines WHERE routine_schema = DATABASE()'),
292 ];
293 }
294
295 /**
296 * Quote catalog identifiers without interpreting generated SQL.
297 */
298 public function quote(string $name): string
299 {
300 return '`' . str_replace('`', '`' . '`', $name) . '`';
301 }
302
303 /**
304 * Minimal native bridge for the platform contract; no production adapter is involved.
305 * @throws PDOException When result-select SQL fails on the server
306 * @throws Error When the driver silently fails
307 */
308 public function query(string $sql): StatementInterface
309 {
310 $statement = $this->physical->query($sql);
311 if ($statement === false) {
312 throw new Error('Native result-select execution silently failed.');
313 }
314 return new class ($statement) implements StatementInterface {
315 /**
316 * Wrap the native result.
317 */
318 public function __construct(private readonly PDOStatement $statement)
319 {
320 }
321
322 /**
323 * Execute the native statement.
324 */
325 public function execute(?array $params = null): bool
326 {
327 return $this->statement->execute($params);
328 }
329
330 /**
331 * Fetch native rows without coercing driver values.
332 * @throws Error When a native row has an unexpected shape
333 */
334 public function fetchAll(): array
335 {
336 $rows = [];
337 foreach ($this->statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
338 if (!is_array($row)) {
339 throw new Error('The driver did not return an associative row.');
340 }
341 $values = [];
342 foreach ($row as $column => $value) {
343 $values[(string) $column] = $value;
344 }
345 $rows[] = $values;
346 }
347 return $rows;
348 }
349
350 /**
351 * Resolve native column metadata using the platform contract.
352 * @throws Error When metadata is unavailable
353 */
354 public function resultColumns(ResultColumnTypeResolver $typeResolver): array
355 {
356 $columns = [];
357 for ($index = 0; $index < $this->statement->columnCount(); ++$index) {
358 $metadata = $this->statement->getColumnMeta($index);
359 if ($metadata === false) {
360 throw new Error('The driver omitted result metadata.');
361 }
362 $columns[] = new ResultColumn($metadata['name'], $typeResolver->resolve($metadata));
363 }
364 return $columns;
365 }
366
367 /**
368 * Return the native affected count.
369 */
370 public function rowCount(): int
371 {
372 return $this->statement->rowCount();
373 }
374 };
375 }
376}
377