packages/ztd-query-postgres/tests/Unit/Connection/Parameter/PgSqlPdoPlaceholderEscaperTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Connection\Parameter;
6
7use Generator;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\DataProvider;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Platform\Postgres\Connection\Parameter\PgSqlPdoPlaceholderEscaper;
12
13#[CoversClass(PgSqlPdoPlaceholderEscaper::class)]
14#[CoversClass(\ZtdQuery\Platform\Postgres\Connection\Parameter\EscapeCursor::class)]
15#[CoversClass(\ZtdQuery\Platform\Postgres\Connection\Parameter\OperandInput::class)]
16#[CoversClass(\ZtdQuery\Platform\Postgres\Connection\Parameter\QuotedInput::class)]
17#[CoversClass(\ZtdQuery\Platform\Postgres\Connection\Parameter\TokenBoundary::class)]
18#[\PHPUnit\Framework\Attributes\UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\CommentSpan::class)]
19#[\PHPUnit\Framework\Attributes\UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\QuotedSpan::class)]
20final class PgSqlPdoPlaceholderEscaperTest extends TestCase
21{
22 public function testEscapesPostgreSqlJsonExistenceOperators(): void
23 {
24 $sql = "SELECT meta ? 'reviewed', meta ?| array['author', 'reviewed'], meta ?& array['author', 'reviewed'] FROM docs";
25
26 self::assertSame(
27 "SELECT meta ?? 'reviewed', meta ??| array['author', 'reviewed'], meta ??& array['author', 'reviewed'] FROM docs",
28 (new PgSqlPdoPlaceholderEscaper())->escape($sql),
29 );
30 }
31
32 public function testPreservesPositionalPlaceholdersInOperandPositions(): void
33 {
34 $sql = 'SELECT ? FROM docs WHERE id = ? AND score BETWEEN ? AND ? ORDER BY ? LIMIT ? OFFSET ?';
35
36 self::assertSame($sql, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
37 }
38
39 public function testDistinguishesJsonOperatorFromItsPlaceholderOperand(): void
40 {
41 $sql = 'SELECT * FROM docs WHERE meta ? ? AND details ? :key AND id = ?';
42
43 self::assertSame(
44 'SELECT * FROM docs WHERE meta ?? ? AND details ?? :key AND id = ?',
45 (new PgSqlPdoPlaceholderEscaper())->escape($sql),
46 );
47 }
48
49 public function testLeavesQuotedAndCommentedQuestionMarksUntouched(): void
50 {
51 $sql = "SELECT '?' AS literal, \"?\" FROM docs -- ?\nWHERE body = \$tag\$?\$tag\$ AND meta ? 'key' /* ? */";
52
53 self::assertSame(
54 "SELECT '?' AS literal, \"?\" FROM docs -- ?\nWHERE body = \$tag\$?\$tag\$ AND meta ?? 'key' /* ? */",
55 (new PgSqlPdoPlaceholderEscaper())->escape($sql),
56 );
57 }
58
59 public function testPreservesAlreadyEscapedOperatorAndNativeParameters(): void
60 {
61 $sql = "SELECT * FROM docs WHERE meta ?? ? AND id = \$1 AND note = E'question\\'? mark'";
62
63 self::assertSame($sql, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
64 }
65
66 #[DataProvider('providerQuestionMarkBoundaries')]
67 public function testHandlesQuestionMarkBoundaries(string $sql, string $expected): void
68 {
69 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
70 }
71
72 #[DataProvider('providerCommentForms')]
73 public function testPreservesQuestionMarksInCommentForms(string $sql, string $expected): void
74 {
75 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
76 }
77
78 #[DataProvider('providerQuotedForms')]
79 public function testPreservesQuestionMarksInQuotedForms(string $sql, string $expected): void
80 {
81 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
82 }
83
84 #[DataProvider('providerDollarQuotedStringsAndNativeParameters')]
85 public function testPreservesDollarQuotedStringsAndNativeParameters(string $sql, string $expected): void
86 {
87 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
88 }
89
90 #[DataProvider('providerOperands')]
91 public function testTracksNamedParametersIdentifiersAndNumbersAsOperands(string $sql, string $expected): void
92 {
93 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
94 }
95
96 #[DataProvider('providerKeywords')]
97 public function testKeywordsRequireAFollowingOperand(string $sql): void
98 {
99 self::assertSame($sql, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
100 }
101
102 #[DataProvider('providerPunctuation')]
103 public function testPunctuationDeterminesWhetherAnOperandFollows(string $sql, string $expected): void
104 {
105 self::assertSame($expected, (new PgSqlPdoPlaceholderEscaper())->escape($sql));
106 }
107
108 /**
109 * @return Generator<string, array{string, string}>
110 */
111 public static function providerQuestionMarkBoundaries(): Generator
112 {
113 yield 'empty query' => ['', ''];
114 yield 'placeholder' => ['?', '?'];
115 yield 'escaped question mark' => ['??', '??'];
116 yield 'any operator' => ['?|', '??|'];
117 yield 'all operator' => ['?&', '??&'];
118 yield 'operator after operand' => ['meta ?', 'meta ??'];
119 yield 'operator with placeholder operand' => ['meta ? ?', 'meta ?? ?'];
120 yield 'alternating operators and placeholders' => ['meta ? ? ?', 'meta ?? ? ??'];
121 yield 'escaped operator with placeholder' => ['meta ?? ?', 'meta ?? ?'];
122 yield 'any operator with placeholder' => ["meta ?| ? 'key'", "meta ??| ? 'key'"];
123 yield 'all operator with placeholder' => ["meta ?& ? 'key'", "meta ??& ? 'key'"];
124 yield 'opening parenthesis' => ['(?', '(?'];
125 yield 'opening bracket' => ['[?', '[?'];
126 yield 'colon' => ['meta : ?', 'meta : ??'];
127 yield 'cast with placeholder' => ['meta::? ?', 'meta::? ??'];
128 }
129
130 /**
131 * @return Generator<string, array{string, string}>
132 */
133 public static function providerCommentForms(): Generator
134 {
135 yield 'line comment' => ['-- ?', '-- ?'];
136 yield 'line comment with carriage return' => ["-- ?\rSELECT ?", "-- ?\rSELECT ?"];
137 yield 'line comment preserving state before newline' => ["meta--meta ?\n? 'key'", "meta--meta ?\n?? 'key'"];
138 yield 'line comment preserving state before carriage return' => ["meta--meta ?\r? 'key'", "meta--meta ?\r?? 'key'"];
139 yield 'line comment at end of input' => ['meta--meta ?', 'meta--meta ?'];
140 yield 'separated minus signs' => ['meta- -meta ?', 'meta- -meta ??'];
141 yield 'block comment' => ['/* ? */ SELECT ?', '/* ? */ SELECT ?'];
142 yield 'nested block comment' => ['/* outer ? /* nested ? */ tail ? */ SELECT ?', '/* outer ? /* nested ? */ tail ? */ SELECT ?'];
143 yield 'block comment preserving state' => ["meta/*meta ?*/? 'key'", "meta/*meta ?*/?? 'key'"];
144 yield 'empty block comment' => ["meta/**/? 'key'", "meta/**/?? 'key'"];
145 yield 'nested block comment preserving state' => ["meta/*outer /* inner meta ? */ outer meta ?*/? 'key'", "meta/*outer /* inner meta ? */ outer meta ?*/?? 'key'"];
146 yield 'minimal block comment' => ['/**/', '/**/'];
147 yield 'unterminated minimal block comment' => ['/*/', '/*/'];
148 yield 'unterminated block comment ending in asterisk' => ['/**', '/**'];
149 yield 'unterminated nested block comment' => ['/*/*/', '/*/*/'];
150 yield 'closed nested block comment' => ['/*/**/*/', '/*/**/*/'];
151 yield 'unterminated block comment' => ['SELECT 1 /* unterminated ?', 'SELECT 1 /* unterminated ?'];
152 yield 'unterminated block comment preserving state' => ['meta/*unterminated meta ?', 'meta/*unterminated meta ?'];
153 yield 'separated slash and asterisk' => ["meta/ *meta ?*/? 'key'", "meta/ *meta ??*/? 'key'"];
154 yield 'comment between operator operands' => ["meta /* ? */ ? 'key'", "meta /* ? */ ?? 'key'"];
155 }
156
157 /**
158 * @return Generator<string, array{string, string}>
159 */
160 public static function providerQuotedForms(): Generator
161 {
162 yield 'single quoted question mark' => ["'?' ? 'key'", "'?' ?? 'key'"];
163 yield 'single quoted expression' => ["'meta ?' ? 'key'", "'meta ?' ?? 'key'"];
164 yield 'doubled single quote' => ["'it''s ?' ? 'key'", "'it''s ?' ?? 'key'"];
165 yield 'expression after doubled single quote' => ["'meta ''nested ?' ? 'key'", "'meta ''nested ?' ?? 'key'"];
166 yield 'double quoted question mark' => ["\"?\" ? 'key'", "\"?\" ?? 'key'"];
167 yield 'double quoted expression' => ["\"meta ?\" ? 'key'", "\"meta ?\" ?? 'key'"];
168 yield 'doubled double quote' => ["\"a\"\"?\" ? 'key'", "\"a\"\"?\" ?? 'key'"];
169 yield 'expression after doubled double quote' => ["\"meta \"\"nested ?\" ? 'key'", "\"meta \"\"nested ?\" ?? 'key'"];
170 yield 'uppercase escape string' => ["E'meta \\' quoted ?' ? 'key'", "E'meta \\' quoted ?' ?? 'key'"];
171 yield 'lowercase escape string' => ["e'meta \\' quoted ?' ? 'key'", "e'meta \\' quoted ?' ?? 'key'"];
172 yield 'escape string after whitespace' => [" E'meta \\' quoted ?' ? 'key'", " E'meta \\' quoted ?' ?? 'key'"];
173 yield 'escape string after operator' => ["!E'meta \\' quoted ?' ? 'key'", "!E'meta \\' quoted ?' ?? 'key'"];
174 yield 'standard string backslash is not an escape' => ["'\\' meta ?' ? 'key'", "'\\' meta ??' ? 'key'"];
175 yield 'quoted identifier backslash is not an escape' => ['"meta \\" rest ?" ? \'key\'', '"meta \\" rest ??" ? \'key\''];
176 yield 'escape string ending in backslash' => ["E'meta \\", "E'meta \\"];
177 yield 'escaped quote before trailing question mark' => ["E'abc \\'?", "E'abc \\'?"];
178 yield 'escaped character before closing quote' => ["E'\\a'?", "E'\\a'??"];
179 yield 'escape semantics survive doubled quote' => ["E'a''\\' meta ?' ? 'key'", "E'a''\\' meta ?' ?? 'key'"];
180 yield 'identifier E suffix is not an escape prefix' => ["AE'meta \\' quoted ?' ? 'key'", "AE'meta \\' quoted ??' ? 'key'"];
181 yield 'prefixed identifier E suffix is not an escape prefix' => ["!AE'meta \\' quoted ?' ? 'key'", "!AE'meta \\' quoted ??' ? 'key'"];
182 yield 'operator delimits an escape prefix' => ["A!E'meta \\' quoted ?' ? 'key'", "A!E'meta \\' quoted ?' ?? 'key'"];
183 yield 'number E suffix is not an escape prefix' => ["0E'meta \\' quoted ?' ? 'key'", "0E'meta \\' quoted ??' ? 'key'"];
184 yield 'dollar E suffix is not an escape prefix' => ["\$E'meta \\' quoted ?' ? 'key'", "\$E'meta \\' quoted ??' ? 'key'"];
185 yield 'unterminated doubled single quote' => ["'a''", "'a''"];
186 yield 'unterminated doubled double quote' => ['"a""', '"a""'];
187 yield 'unterminated single quote' => ["'unterminated ?", "'unterminated ?"];
188 yield 'unterminated single quoted expression' => ["'unterminated meta ?", "'unterminated meta ?"];
189 yield 'unterminated double quote' => ['"unterminated ?', '"unterminated ?'];
190 yield 'unterminated double quoted expression' => ['"unterminated meta ?', '"unterminated meta ?'];
191 }
192
193 /**
194 * @return Generator<string, array{string, string}>
195 */
196 public static function providerDollarQuotedStringsAndNativeParameters(): Generator
197 {
198 yield 'untagged dollar quote' => ["\$\$meta ?\$\$ ? 'key'", "\$\$meta ?\$\$ ?? 'key'"];
199 yield 'unterminated dollar quote' => ['$tag$ ?', '$tag$ ?'];
200 yield 'prefixed unterminated dollar quote' => ['prefix $tag$meta ?', 'prefix $tag$meta ?'];
201 yield 'tagged dollar quote' => ["\$tag\$meta ?\$tag\$ ? 'key'", "\$tag\$meta ?\$tag\$ ?? 'key'"];
202 yield 'prefixed tagged dollar quote' => ["prefix \$A\$meta ?\$A\$ ? 'key'", "prefix \$A\$meta ?\$A\$ ?? 'key'"];
203 yield 'standalone dollar' => ['$', '$'];
204 yield 'unterminated dollar tag' => ['$A', '$A'];
205 yield 'empty dollar quoted string' => ['$A$', '$A$'];
206 yield 'empty dollar quote followed by dollar identifier' => ['$A$$A$B$?', '$A$$A$B$??'];
207 yield 'one digit native parameter' => ['$1 ? ?', '$1 ?? ?'];
208 yield 'two digit native parameter' => ['$19 ? ?', '$19 ?? ?'];
209 yield 'zero native parameter boundary' => ["\$0? 'key'", "\$0?? 'key'"];
210 yield 'nine native parameter boundary' => ["\$9? 'key'", "\$9?? 'key'"];
211 yield 'ten native parameter boundary' => ["\$10? 'key'", "\$10?? 'key'"];
212 yield 'ninety native parameter boundary' => ["\$90? 'key'", "\$90?? 'key'"];
213 yield 'invalid numeric dollar tag' => ["\$9\$ ? 'key'", "\$9\$ ?? 'key'"];
214
215 foreach (['tag', '_tag', 'A0', 'Z9', 'a0', 'z9'] as $tag) {
216 $sql = sprintf('$%1$s$meta ?$%1$s$ ? \'key\'', $tag);
217 $expected = sprintf('$%1$s$meta ?$%1$s$ ?? \'key\'', $tag);
218 yield $tag => [$sql, $expected];
219 }
220 }
221
222 /**
223 * @return Generator<string, array{string, string}>
224 */
225 public static function providerOperands(): Generator
226 {
227 foreach ([':name', ':a_b9$', 'A', 'Z', 'a', 'z', '_value', 'value0', 'value9', 'value$', '0', '9', '123.45'] as $operand) {
228 yield $operand . ' separated' => [$operand . " ? 'key'", $operand . " ?? 'key'"];
229 }
230
231 foreach ([':A0', ':Z9', ':a0', ':z9', ':_value', ':value$', 'A', 'Z', 'a', 'z', '_value', 'value0', 'value9', 'value$', '0', '9', '10', '90', '1.', '1.0'] as $operand) {
232 yield $operand . ' adjacent' => [$operand . "? 'key'", $operand . "?? 'key'"];
233 }
234
235 yield 'cast' => ["value::jsonb ? 'key'", "value::jsonb ?? 'key'"];
236 yield 'keyword named parameter' => [":SELECT ? 'key'", ":SELECT ?? 'key'"];
237 yield 'adjacent keyword named parameter' => [":FROM? 'key'", ":FROM?? 'key'"];
238 yield 'standalone colon' => [':', ':'];
239 }
240
241 /**
242 * @return Generator<string, array{string}>
243 */
244 public static function providerKeywords(): Generator
245 {
246 $keywords = [
247 'ALL',
248 'AND',
249 'ANY',
250 'AS',
251 'BETWEEN',
252 'BY',
253 'CASE',
254 'CONFLICT',
255 'DELETE',
256 'DISTINCT',
257 'DO',
258 'ELSE',
259 'FIRST',
260 'FROM',
261 'HAVING',
262 'ILIKE',
263 'IN',
264 'INSERT',
265 'INTO',
266 'IS',
267 'JOIN',
268 'LAST',
269 'LIKE',
270 'LIMIT',
271 'NOT',
272 'OFFSET',
273 'ON',
274 'OR',
275 'RETURNING',
276 'SELECT',
277 'SET',
278 'SIMILAR',
279 'SOME',
280 'THEN',
281 'UPDATE',
282 'USING',
283 'VALUE',
284 'VALUES',
285 'WHEN',
286 'WHERE',
287 'ZONE',
288 ];
289
290 foreach ($keywords as $keyword) {
291 yield $keyword . ' uppercase' => [$keyword . ' ?'];
292 yield $keyword . ' lowercase' => [strtolower($keyword) . ' ?'];
293 }
294 }
295
296 /**
297 * @return Generator<string, array{string, string}>
298 */
299 public static function providerPunctuation(): Generator
300 {
301 foreach (['(', '[', ',', ';', '.', '=', '<', '>', '!', '~', '+', '-', '*', '/', '%', '^', '|', '&', '#', '@'] as $prefix) {
302 yield $prefix => ['meta' . $prefix . '?', 'meta' . $prefix . '?'];
303 }
304
305 yield 'closing parenthesis' => ["() ? 'key'", "() ?? 'key'"];
306 yield 'closing bracket' => ["[] ? 'key'", "[] ?? 'key'"];
307 }
308}
309