packages/sql-formatter/fuzz/Target/PgEquivalence.php
1<?php
2
3declare(strict_types=1);
4
5namespace Fuzz\Target;
6
7use Error;
8use PgSql\Connection;
9use SqlFormatter\Core\FormattingException;
10use SqlFormatter\Core\Style;
11use SqlFormatter\Facade\Formatter;
12use SqlParser\Lexer\SourceException;
13
14/**
15 * Runs a generated statement and its formatted text on PostgreSQL and reports a different answer.
16 *
17 * Every statement runs inside a transaction that is rolled back, so DDL and DML leave nothing
18 * behind; a statement PostgreSQL refuses inside a transaction block fails the same way both
19 * times. Errors are compared by SQLSTATE and primary message, which carry no position. A
20 * mismatch is confirmed by running the original once more, so a statement that reads the
21 * clock is not reported.
22 */
23final class PgEquivalence
24{
25 /**
26 * Session settings applied to every connection so that a runaway statement ends in time.
27 *
28 * @var list<string>
29 */
30 public const SETTINGS = ['SET statement_timeout = 2000', 'SET lock_timeout = 2000', "SET client_min_messages = 'error'"];
31
32 private Connection $connection;
33
34 /**
35 * @param string $dsn Connection string for pg_connect
36 * @param Formatter $formatter The formatter under test
37 * @param Style $style The layout preset in use, for findings
38 */
39 public function __construct(
40 private readonly string $dsn,
41 private readonly Formatter $formatter,
42 private readonly Style $style,
43 ) {
44 $this->connection = $this->connect();
45 }
46
47 /**
48 * Verifies that PostgreSQL answers the formatted statement as it answers the original.
49 *
50 * @param string $sql Statement produced by the grammar
51 * @param string $input Fuzzer input that produced the statement, so a finding can be replayed
52 *
53 * @throws Error When the statement is empty, formatting fails verification, or the answers differ
54 */
55 public function verify(string $sql, string $input): void
56 {
57 if ($sql === '') {
58 throw new Error("Statement generation returned an empty string\nInput (hex): " . bin2hex($input));
59 }
60 $context = "Grammar: pg-17.2\nStyle: {$this->style->value}\nInput (hex): " . bin2hex($input) . "\nSQL: {$sql}";
61 try {
62 $formatted = $this->formatter->format($sql);
63 } catch (SourceException) {
64 return;
65 } catch (FormattingException $failure) {
66 throw new Error("Formatting failed verification\n{$context}\nError: {$failure->getMessage()}", 0, $failure);
67 }
68 $before = $this->run($sql);
69 $after = $this->run($formatted);
70 if ($before === $after || $this->run($sql) !== $before) {
71 return;
72 }
73 throw new Error(
74 "Formatted SQL behaves differently on PostgreSQL\n{$context}\nFormatted: {$formatted}\n" .
75 'Before: ' . json_encode($before, JSON_INVALID_UTF8_SUBSTITUTE) . "\n" .
76 'After: ' . json_encode($after, JSON_INVALID_UTF8_SUBSTITUTE),
77 );
78 }
79
80 /**
81 * Runs one statement inside a transaction that is rolled back afterwards.
82 *
83 * A statement that leaves the session busy, such as a COPY, or outside an idle
84 * transaction state is followed by a fresh connection.
85 *
86 * @return array{string, mixed}|array{string, string, string} ["ok", rows or command tag] or ["error", SQLSTATE, message]
87 */
88 public function run(string $sql): array
89 {
90 $this->execute('BEGIN');
91 $answer = $this->execute($sql);
92 if (pg_transaction_status($this->connection) === PGSQL_TRANSACTION_ACTIVE) {
93 $this->connection = $this->connect();
94 return $answer;
95 }
96 $this->execute('ROLLBACK');
97 if (pg_transaction_status($this->connection) !== PGSQL_TRANSACTION_IDLE) {
98 $this->connection = $this->connect();
99 }
100 return $answer;
101 }
102
103 /**
104 * Sends one statement and reads every result it produced, keeping the last.
105 *
106 * The statement is sent asynchronously so that a server error never becomes a PHP warning.
107 *
108 * @return array{string, mixed}|array{string, string, string} ["ok", rows or command tag] or ["error", SQLSTATE, message]
109 */
110 public function execute(string $sql): array
111 {
112 if (pg_connection_status($this->connection) !== PGSQL_CONNECTION_OK || pg_send_query($this->connection, $sql) === false) {
113 fwrite(STDERR, 'PostgreSQL connection failed: ' . pg_last_error($this->connection) . "\n");
114 exit(2);
115 }
116 $answer = ['ok', 'EMPTY'];
117 while (($result = pg_get_result($this->connection)) !== false) {
118 $status = pg_result_status($result);
119 if ($status === PGSQL_TUPLES_OK) {
120 $answer = ['ok', pg_fetch_all($result, PGSQL_NUM)];
121 } elseif ($status === PGSQL_COMMAND_OK) {
122 $answer = ['ok', pg_result_status($result, PGSQL_STATUS_STRING)];
123 } elseif ($status === PGSQL_COPY_IN || $status === PGSQL_COPY_OUT) {
124 pg_free_result($result);
125 return ['ok', 'COPY'];
126 } elseif ($status !== PGSQL_EMPTY_QUERY) {
127 $state = pg_result_error_field($result, PGSQL_DIAG_SQLSTATE);
128 $state = is_string($state) ? $state : '';
129 $message = pg_result_error_field($result, PGSQL_DIAG_MESSAGE_PRIMARY);
130 $message = is_string($message) ? $message : 'No server error text.';
131 if (str_starts_with($state, '08') || str_starts_with($state, '57P0')) {
132 fwrite(STDERR, "PostgreSQL connection failed: {$message}\n");
133 exit(2);
134 }
135 $answer = ['error', $state, $message];
136 }
137 pg_free_result($result);
138 }
139 return $answer;
140 }
141
142 /**
143 * Opens a connection with the session settings, ending the run when the server is unreachable.
144 */
145 public function connect(): Connection
146 {
147 $connection = @pg_connect($this->dsn, PGSQL_CONNECT_FORCE_NEW);
148 if ($connection === false) {
149 fwrite(STDERR, "Cannot connect to PostgreSQL with \"{$this->dsn}\".\n");
150 exit(2);
151 }
152 $this->connection = $connection;
153 foreach (self::SETTINGS as $setting) {
154 $this->execute($setting);
155 }
156 return $connection;
157 }
158}
159