packages/sql-fixture/fuzz/Target/InsertSelectTarget.php

1<?php
2
3declare(strict_types=1);
4
5namespace Fuzz\Target;
6
7use Error;
8use Faker\Factory;
9use Faker\Generator;
10use JsonException;
11use PDO;
12use SqlFixture\FixtureProvider;
13
14/**
15 * Fuzz target for INSERT/SELECT consistency validation.
16 *
17 * This target generates fixtures, inserts them into MySQL,
18 * and validates the data can be retrieved correctly.
19 */
20final class InsertSelectTarget
21{
22    private const ALL_TYPES_TABLE = <<<'SQL'
23        CREATE TABLE all_types (
24            id INT PRIMARY KEY AUTO_INCREMENT,
25            col_tinyint TINYINT,
26            col_tinyint_unsigned TINYINT UNSIGNED,
27            col_smallint SMALLINT,
28            col_mediumint MEDIUMINT,
29            col_int INT,
30            col_bigint BIGINT,
31            col_float FLOAT,
32            col_double DOUBLE,
33            col_decimal DECIMAL(10,2),
34            col_bit BIT(8),
35            col_char CHAR(10),
36            col_varchar VARCHAR(255),
37            col_tinytext TINYTEXT,
38            col_text TEXT,
39            col_enum ENUM('a','b','c'),
40            col_set SET('x','y','z'),
41            col_date DATE,
42            col_time TIME,
43            col_datetime DATETIME,
44            col_timestamp TIMESTAMP NULL,
45            col_year YEAR,
46            col_json JSON
47        )
48        SQL;
49
50    private Generator $faker;
51    private FixtureProvider $fixtureProvider;
52
53    /**
54     * Initializes the collaborators and declared state for this object.
55     */
56    public function __construct(
57        private readonly PDO $pdo,
58    ) {
59        $this->faker = Factory::create();
60        $this->fixtureProvider = new FixtureProvider($this->faker);
61
62        $this->pdo->exec(str_replace('CREATE TABLE', 'CREATE TEMPORARY TABLE', self::ALL_TYPES_TABLE));
63    }
64
65    /**
66     * Fuzz target callable.
67     *
68     * @param string $input Raw fuzzer input (mutated bytes)
69     * @throws Error On INSERT/SELECT mismatch
70     * @throws JsonException When a round-tripped JSON value is malformed
71     */
72    public function __invoke(string $input): void
73    {
74        $seed = crc32(str_pad($input, 4, "\0"));
75        $this->faker->seed($seed);
76
77        $fixture = $this->fixtureProvider->fixture(self::ALL_TYPES_TABLE);
78
79        $columns = array_keys($fixture);
80        $placeholders = array_fill(0, count($columns), '?');
81
82        $sql = sprintf(
83            'INSERT INTO all_types (%s) VALUES (%s)',
84            implode(', ', $columns),
85            implode(', ', $placeholders)
86        );
87
88        $this->pdo->beginTransaction();
89        try {
90            $stmt = $this->pdo->prepare($sql);
91            foreach (array_values($fixture) as $index => $value) {
92                $type = match (true) {
93                    $value === null => PDO::PARAM_NULL,
94                    is_int($value) => PDO::PARAM_INT,
95                    is_bool($value) => PDO::PARAM_BOOL,
96                    default => PDO::PARAM_STR,
97                };
98                $stmt->bindValue($index + 1, $value, $type);
99            }
100            $stmt->execute();
101
102            $id = (int) $this->pdo->lastInsertId();
103
104            $stmt = $this->pdo->prepare('SELECT * FROM all_types WHERE id = ?');
105            $stmt->execute([$id]);
106            $result = $stmt->fetch(PDO::FETCH_ASSOC);
107
108            if (!is_array($result)) {
109                throw new Error(
110                    "Failed to retrieve inserted row\n" .
111                    "Seed: $seed\n" .
112                    "ID: $id"
113                );
114            }
115
116            foreach ($fixture as $column => $expected) {
117                $actual = $result[$column] ?? null;
118
119                if ($column === 'col_bit' && is_string($actual) && strlen($actual) === 1) {
120                    $actual = ord($actual);
121                }
122                if ($column === 'col_json' && is_string($expected) && is_string($actual)) {
123                    $expected = json_decode($expected, true, 512, JSON_THROW_ON_ERROR);
124                    $actual = json_decode($actual, true, 512, JSON_THROW_ON_ERROR);
125                }
126                if ($column === 'col_set' && is_string($expected) && is_string($actual)) {
127                    $expected = explode(',', $expected);
128                    $actual = explode(',', $actual);
129                    sort($expected);
130                    sort($actual);
131                }
132                $matches = match (true) {
133                    $expected === null || $actual === null => $expected === $actual,
134                    is_float($expected) && is_numeric($actual) => $expected === 0.0
135                        ? abs((float) $actual) < 0.0001
136                        : abs($expected - (float) $actual) / abs($expected) < 0.001,
137                    is_int($expected) && is_numeric($actual) => $expected === (int) $actual,
138                    is_bool($expected) && is_numeric($actual) => $expected === (bool) $actual,
139                    default => $expected === $actual,
140                };
141
142                if (!$matches) {
143                    throw new Error(
144                        "Value mismatch\n" .
145                        "Seed: $seed\n" .
146                        "Column: $column\n" .
147                        'Expected: ' . var_export($expected, true) . "\n" .
148                        'Actual: ' . var_export($actual, true)
149                    );
150                }
151            }
152
153        } finally {
154            $this->pdo->rollBack();
155        }
156    }
157}
158