|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Doctrine\Tests\DBAL; |
|
4
|
|
|
|
|
5
|
|
|
use Doctrine\DBAL\Connection; |
|
6
|
|
|
use Doctrine\DBAL\ParameterType; |
|
7
|
|
|
use Doctrine\DBAL\SQLParserUtils; |
|
8
|
|
|
use Doctrine\DBAL\SQLParserUtilsException; |
|
9
|
|
|
use Doctrine\Tests\DbalTestCase; |
|
10
|
|
|
|
|
11
|
|
|
/** |
|
12
|
|
|
* @group DBAL-78 |
|
13
|
|
|
* @group DDC-1372 |
|
14
|
|
|
*/ |
|
15
|
|
|
class SQLParserUtilsTest extends DbalTestCase |
|
16
|
|
|
{ |
|
17
|
|
|
/** |
|
18
|
|
|
* @return mixed[][] |
|
19
|
|
|
*/ |
|
20
|
|
|
public static function dataGetPlaceholderPositions() : iterable |
|
21
|
|
|
{ |
|
22
|
|
|
return [ |
|
23
|
|
|
// none |
|
24
|
|
|
['SELECT * FROM Foo', true, []], |
|
25
|
|
|
['SELECT * FROM Foo', false, []], |
|
26
|
|
|
|
|
27
|
|
|
// Positionals |
|
28
|
|
|
['SELECT ?', true, [7]], |
|
29
|
|
|
['SELECT * FROM Foo WHERE bar IN (?, ?, ?)', true, [32, 35, 38]], |
|
30
|
|
|
['SELECT ? FROM ?', true, [7, 14]], |
|
31
|
|
|
['SELECT "?" FROM foo', true, []], |
|
32
|
|
|
["SELECT '?' FROM foo", true, []], |
|
33
|
|
|
['SELECT `?` FROM foo', true, []], // Ticket DBAL-552 |
|
34
|
|
|
['SELECT [?] FROM foo', true, []], |
|
35
|
|
|
["SELECT 'Doctrine\DBAL?' FROM foo", true, []], // Ticket DBAL-558 |
|
36
|
|
|
['SELECT "Doctrine\DBAL?" FROM foo', true, []], // Ticket DBAL-558 |
|
37
|
|
|
['SELECT `Doctrine\DBAL?` FROM foo', true, []], // Ticket DBAL-558 |
|
38
|
|
|
['SELECT [Doctrine\DBAL?] FROM foo', true, []], // Ticket DBAL-558 |
|
39
|
|
|
['SELECT "?" FROM foo WHERE bar = ?', true, [32]], |
|
40
|
|
|
["SELECT '?' FROM foo WHERE bar = ?", true, [32]], |
|
41
|
|
|
['SELECT `?` FROM foo WHERE bar = ?', true, [32]], // Ticket DBAL-552 |
|
42
|
|
|
['SELECT [?] FROM foo WHERE bar = ?', true, [32]], |
|
43
|
|
|
['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', true, [56]], // Ticket GH-2295 |
|
44
|
|
|
["SELECT 'Doctrine\DBAL?' FROM foo WHERE bar = ?", true, [45]], // Ticket DBAL-558 |
|
45
|
|
|
['SELECT "Doctrine\DBAL?" FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558 |
|
46
|
|
|
['SELECT `Doctrine\DBAL?` FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558 |
|
47
|
|
|
['SELECT [Doctrine\DBAL?] FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558 |
|
48
|
|
|
["SELECT * FROM FOO WHERE bar = 'it\\'s a trap? \\\\' OR bar = ?\nAND baz = \"\\\"quote\\\" me on it? \\\\\" OR baz = ?", true, [58, 104]], |
|
49
|
|
|
['SELECT * FROM foo WHERE foo = ? AND bar = ?', true, [1 => 42, 0 => 30]], // explicit keys |
|
50
|
|
|
|
|
51
|
|
|
// named |
|
52
|
|
|
['SELECT :foo FROM :bar', false, [7 => 'foo', 17 => 'bar']], |
|
53
|
|
|
['SELECT * FROM Foo WHERE bar IN (:name1, :name2)', false, [32 => 'name1', 40 => 'name2']], |
|
54
|
|
|
['SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', false, [37 => 'name1', 45 => 'name2']], |
|
55
|
|
|
["SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", false, [37 => 'name1', 45 => 'name2']], |
|
56
|
|
|
['SELECT :foo_id', false, [7 => 'foo_id']], // Ticket DBAL-231 |
|
57
|
|
|
['SELECT @rank := 1', false, []], // Ticket DBAL-398 |
|
58
|
|
|
['SELECT @rank := 1 AS rank, :foo AS foo FROM :bar', false, [27 => 'foo', 44 => 'bar']], // Ticket DBAL-398 |
|
59
|
|
|
['SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [30 => 'start_date', 52 => 'start_date']], // Ticket GH-113 |
|
60
|
|
|
['SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [46 => 'start_date', 68 => 'start_date']], // Ticket GH-259 |
|
61
|
|
|
['SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1', false, [57 => 'param1']], // Ticket DBAL-552 |
|
62
|
|
|
['SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1', false, [57 => 'param1']], // Ticket DBAL-552 |
|
63
|
|
|
['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])', false, [56 => 'foo']], // Ticket GH-2295 |
|
64
|
|
|
['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[:foo])', false, [56 => 'foo']], |
|
65
|
|
|
['SELECT table.field1, ARRAY[\'3\'] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']', false, [73 => 'foo']], |
|
66
|
|
|
['SELECT table.field1, ARRAY[\'3\']::integer[] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']::integer[]', false, [84 => 'foo']], |
|
67
|
|
|
['SELECT table.field1, ARRAY[:foo] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']', false, [27 => 'foo', 74 => 'bar']], |
|
68
|
|
|
['SELECT table.field1, ARRAY[:foo]::integer[] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']::integer[]', false, [27 => 'foo', 85 => 'bar']], |
|
69
|
|
|
[ |
|
70
|
|
|
<<<'SQLDATA' |
|
71
|
|
|
SELECT * FROM foo WHERE |
|
72
|
|
|
bar = ':not_a_param1 ''":not_a_param2"''' |
|
73
|
|
|
OR bar=:a_param1 |
|
74
|
|
|
OR bar=:a_param2||':not_a_param3' |
|
75
|
|
|
OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6' |
|
76
|
|
|
OR bar='' |
|
77
|
|
|
OR bar=:a_param3 |
|
78
|
|
|
SQLDATA |
|
79
|
|
|
, |
|
80
|
|
|
false, |
|
81
|
|
|
[ |
|
82
|
|
|
74 => 'a_param1', |
|
83
|
|
|
91 => 'a_param2', |
|
84
|
|
|
190 => 'a_param3', |
|
85
|
|
|
], |
|
86
|
|
|
], |
|
87
|
|
|
["SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE '\\\\') AND (data.description LIKE :condition_1 ESCAPE '\\\\') ORDER BY id ASC", false, [121 => 'condition_0', 174 => 'condition_1']], |
|
88
|
|
|
['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE "\\\\") ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']], |
|
89
|
|
|
['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE \'\\\\\') ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']], |
|
90
|
|
|
['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE `\\\\`) AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']], |
|
91
|
|
|
['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE \'\\\\\') AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']], |
|
92
|
|
|
["SELECT * FROM Foo WHERE (foo.bar LIKE :condition_0 ESCAPE '\') AND (foo.baz = :condition_1) AND (foo.bak LIKE :condition_2 ESCAPE '\')", false, [38 => 'condition_0', 78 => 'condition_1', 110 => 'condition_2']], |
|
93
|
|
|
]; |
|
94
|
|
|
} |
|
95
|
|
|
|
|
96
|
|
|
/** |
|
97
|
|
|
* @param int[] $expectedParamPos |
|
98
|
|
|
* |
|
99
|
|
|
* @dataProvider dataGetPlaceholderPositions |
|
100
|
|
|
*/ |
|
101
|
|
|
public function testGetPlaceholderPositions(string $query, bool $isPositional, array $expectedParamPos) : void |
|
102
|
|
|
{ |
|
103
|
|
|
$actualParamPos = SQLParserUtils::getPlaceholderPositions($query, $isPositional); |
|
|
|
|
|
|
104
|
|
|
self::assertEquals($expectedParamPos, $actualParamPos); |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
/** |
|
108
|
|
|
* @return mixed[][] |
|
109
|
|
|
*/ |
|
110
|
|
|
public static function dataExpandListParameters() : iterable |
|
111
|
|
|
{ |
|
112
|
|
|
return [ |
|
113
|
|
|
// Positional: Very simple with one needle |
|
114
|
|
|
[ |
|
115
|
|
|
'SELECT * FROM Foo WHERE foo IN (?)', |
|
116
|
|
|
[[1, 2, 3]], |
|
117
|
|
|
[Connection::PARAM_INT_ARRAY], |
|
118
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?)', |
|
119
|
|
|
[1, 2, 3], |
|
120
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
121
|
|
|
], |
|
122
|
|
|
// Positional: One non-list before d one after list-needle |
|
123
|
|
|
[ |
|
124
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?)', |
|
125
|
|
|
['string', [1, 2, 3]], |
|
126
|
|
|
[ParameterType::STRING, Connection::PARAM_INT_ARRAY], |
|
127
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)', |
|
128
|
|
|
['string', 1, 2, 3], |
|
129
|
|
|
[ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
130
|
|
|
], |
|
131
|
|
|
// Positional: One non-list after list-needle |
|
132
|
|
|
[ |
|
133
|
|
|
'SELECT * FROM Foo WHERE bar IN (?) AND baz = ?', |
|
134
|
|
|
[[1, 2, 3], 'foo'], |
|
135
|
|
|
[Connection::PARAM_INT_ARRAY, ParameterType::STRING], |
|
136
|
|
|
'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?', |
|
137
|
|
|
[1, 2, 3, 'foo'], |
|
138
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING], |
|
139
|
|
|
], |
|
140
|
|
|
// Positional: One non-list before and one after list-needle |
|
141
|
|
|
[ |
|
142
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?', |
|
143
|
|
|
[1, [1, 2, 3], 4], |
|
144
|
|
|
[ParameterType::INTEGER, Connection::PARAM_INT_ARRAY, ParameterType::INTEGER], |
|
145
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?', |
|
146
|
|
|
[1, 1, 2, 3, 4], |
|
147
|
|
|
[ |
|
148
|
|
|
ParameterType::INTEGER, |
|
149
|
|
|
ParameterType::INTEGER, |
|
150
|
|
|
ParameterType::INTEGER, |
|
151
|
|
|
ParameterType::INTEGER, |
|
152
|
|
|
ParameterType::INTEGER, |
|
153
|
|
|
], |
|
154
|
|
|
], |
|
155
|
|
|
// Positional: Two lists |
|
156
|
|
|
[ |
|
157
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?)', |
|
158
|
|
|
[[1, 2, 3], [4, 5]], |
|
159
|
|
|
[Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY], |
|
160
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)', |
|
161
|
|
|
[1, 2, 3, 4, 5], |
|
162
|
|
|
[ |
|
163
|
|
|
ParameterType::INTEGER, |
|
164
|
|
|
ParameterType::INTEGER, |
|
165
|
|
|
ParameterType::INTEGER, |
|
166
|
|
|
ParameterType::INTEGER, |
|
167
|
|
|
ParameterType::INTEGER, |
|
168
|
|
|
], |
|
169
|
|
|
], |
|
170
|
|
|
// Positional: Empty "integer" array DDC-1978 |
|
171
|
|
|
[ |
|
172
|
|
|
'SELECT * FROM Foo WHERE foo IN (?)', |
|
173
|
|
|
[[]], |
|
174
|
|
|
[Connection::PARAM_INT_ARRAY], |
|
175
|
|
|
'SELECT * FROM Foo WHERE foo IN (NULL)', |
|
176
|
|
|
[], |
|
177
|
|
|
[], |
|
178
|
|
|
], |
|
179
|
|
|
// Positional: Empty "str" array DDC-1978 |
|
180
|
|
|
[ |
|
181
|
|
|
'SELECT * FROM Foo WHERE foo IN (?)', |
|
182
|
|
|
[[]], |
|
183
|
|
|
[Connection::PARAM_STR_ARRAY], |
|
184
|
|
|
'SELECT * FROM Foo WHERE foo IN (NULL)', |
|
185
|
|
|
[], |
|
186
|
|
|
[], |
|
187
|
|
|
], |
|
188
|
|
|
// Positional: explicit keys for params and types |
|
189
|
|
|
[ |
|
190
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?', |
|
191
|
|
|
[1 => 'bar', 2 => 'baz', 0 => 1], |
|
192
|
|
|
[2 => ParameterType::STRING, 1 => ParameterType::STRING], |
|
193
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?', |
|
194
|
|
|
[1 => 'bar', 0 => 1, 2 => 'baz'], |
|
195
|
|
|
[1 => ParameterType::STRING, 2 => ParameterType::STRING], |
|
196
|
|
|
], |
|
197
|
|
|
// Positional: explicit keys for array params and array types |
|
198
|
|
|
[ |
|
199
|
|
|
'SELECT * FROM Foo WHERE foo IN (?) AND bar IN (?) AND baz = ?', |
|
200
|
|
|
[1 => ['bar1', 'bar2'], 2 => true, 0 => [1, 2, 3]], |
|
201
|
|
|
[2 => ParameterType::BOOLEAN, 1 => Connection::PARAM_STR_ARRAY, 0 => Connection::PARAM_INT_ARRAY], |
|
202
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND bar IN (?, ?) AND baz = ?', |
|
203
|
|
|
[1, 2, 3, 'bar1', 'bar2', true], |
|
204
|
|
|
[ |
|
205
|
|
|
ParameterType::INTEGER, |
|
206
|
|
|
ParameterType::INTEGER, |
|
207
|
|
|
ParameterType::INTEGER, |
|
208
|
|
|
ParameterType::STRING, |
|
209
|
|
|
ParameterType::STRING, |
|
210
|
|
|
ParameterType::BOOLEAN, |
|
211
|
|
|
], |
|
212
|
|
|
], |
|
213
|
|
|
// Positional starts from 1: One non-list before and one after list-needle |
|
214
|
|
|
[ |
|
215
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ? AND foo IN (?)', |
|
216
|
|
|
[1 => 1, 2 => [1, 2, 3], 3 => 4, 4 => [5, 6]], |
|
217
|
|
|
[ |
|
218
|
|
|
1 => ParameterType::INTEGER, |
|
219
|
|
|
2 => Connection::PARAM_INT_ARRAY, |
|
220
|
|
|
3 => ParameterType::INTEGER, |
|
221
|
|
|
4 => Connection::PARAM_INT_ARRAY, |
|
222
|
|
|
], |
|
223
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ? AND foo IN (?, ?)', |
|
224
|
|
|
[1, 1, 2, 3, 4, 5, 6], |
|
225
|
|
|
[ |
|
226
|
|
|
ParameterType::INTEGER, |
|
227
|
|
|
ParameterType::INTEGER, |
|
228
|
|
|
ParameterType::INTEGER, |
|
229
|
|
|
ParameterType::INTEGER, |
|
230
|
|
|
ParameterType::INTEGER, |
|
231
|
|
|
ParameterType::INTEGER, |
|
232
|
|
|
ParameterType::INTEGER, |
|
233
|
|
|
], |
|
234
|
|
|
], |
|
235
|
|
|
// Named parameters : Very simple with param int |
|
236
|
|
|
[ |
|
237
|
|
|
'SELECT * FROM Foo WHERE foo = :foo', |
|
238
|
|
|
['foo' => 1], |
|
239
|
|
|
['foo' => ParameterType::INTEGER], |
|
240
|
|
|
'SELECT * FROM Foo WHERE foo = ?', |
|
241
|
|
|
[1], |
|
242
|
|
|
[ParameterType::INTEGER], |
|
243
|
|
|
], |
|
244
|
|
|
|
|
245
|
|
|
// Named parameters : Very simple with param int and string |
|
246
|
|
|
[ |
|
247
|
|
|
'SELECT * FROM Foo WHERE foo = :foo AND bar = :bar', |
|
248
|
|
|
['bar' => 'Some String','foo' => 1], |
|
249
|
|
|
['foo' => ParameterType::INTEGER, 'bar' => ParameterType::STRING], |
|
250
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar = ?', |
|
251
|
|
|
[1,'Some String'], |
|
252
|
|
|
[ParameterType::INTEGER, ParameterType::STRING], |
|
253
|
|
|
], |
|
254
|
|
|
// Named parameters : Very simple with one needle |
|
255
|
|
|
[ |
|
256
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo)', |
|
257
|
|
|
['foo' => [1, 2, 3]], |
|
258
|
|
|
['foo' => Connection::PARAM_INT_ARRAY], |
|
259
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?)', |
|
260
|
|
|
[1, 2, 3], |
|
261
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
262
|
|
|
], |
|
263
|
|
|
// Named parameters: One non-list before d one after list-needle |
|
264
|
|
|
[ |
|
265
|
|
|
'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)', |
|
266
|
|
|
['foo' => 'string', 'bar' => [1, 2, 3]], |
|
267
|
|
|
['foo' => ParameterType::STRING, 'bar' => Connection::PARAM_INT_ARRAY], |
|
268
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)', |
|
269
|
|
|
['string', 1, 2, 3], |
|
270
|
|
|
[ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
271
|
|
|
], |
|
272
|
|
|
// Named parameters: One non-list after list-needle |
|
273
|
|
|
[ |
|
274
|
|
|
'SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz', |
|
275
|
|
|
['bar' => [1, 2, 3], 'baz' => 'foo'], |
|
276
|
|
|
['bar' => Connection::PARAM_INT_ARRAY, 'baz' => ParameterType::STRING], |
|
277
|
|
|
'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?', |
|
278
|
|
|
[1, 2, 3, 'foo'], |
|
279
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING], |
|
280
|
|
|
], |
|
281
|
|
|
// Named parameters: One non-list before and one after list-needle |
|
282
|
|
|
[ |
|
283
|
|
|
'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz', |
|
284
|
|
|
['bar' => [1, 2, 3],'foo' => 1, 'baz' => 4], |
|
285
|
|
|
['bar' => Connection::PARAM_INT_ARRAY, 'foo' => ParameterType::INTEGER, 'baz' => ParameterType::INTEGER], |
|
286
|
|
|
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?', |
|
287
|
|
|
[1, 1, 2, 3, 4], |
|
288
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
289
|
|
|
], |
|
290
|
|
|
// Named parameters: Two lists |
|
291
|
|
|
[ |
|
292
|
|
|
'SELECT * FROM Foo WHERE foo IN (:a, :b)', |
|
293
|
|
|
['b' => [4, 5],'a' => [1, 2, 3]], |
|
294
|
|
|
['a' => Connection::PARAM_INT_ARRAY, 'b' => Connection::PARAM_INT_ARRAY], |
|
295
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)', |
|
296
|
|
|
[1, 2, 3, 4, 5], |
|
297
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
298
|
|
|
], |
|
299
|
|
|
// Named parameters : With the same name arg type string |
|
300
|
|
|
[ |
|
301
|
|
|
'SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg', |
|
302
|
|
|
['arg' => 'Some String'], |
|
303
|
|
|
['arg' => ParameterType::STRING], |
|
304
|
|
|
'SELECT * FROM Foo WHERE foo <> ? AND bar = ?', |
|
305
|
|
|
['Some String','Some String'], |
|
306
|
|
|
[ParameterType::STRING,ParameterType::STRING], |
|
307
|
|
|
], |
|
308
|
|
|
// Named parameters : With the same name arg |
|
309
|
|
|
[ |
|
310
|
|
|
'SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)', |
|
311
|
|
|
['arg' => [1, 2, 3]], |
|
312
|
|
|
['arg' => Connection::PARAM_INT_ARRAY], |
|
313
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND NOT bar IN (?, ?, ?)', |
|
314
|
|
|
[1, 2, 3, 1, 2, 3], |
|
315
|
|
|
[ParameterType::INTEGER,ParameterType::INTEGER, ParameterType::INTEGER,ParameterType::INTEGER,ParameterType::INTEGER, ParameterType::INTEGER], |
|
316
|
|
|
], |
|
317
|
|
|
|
|
318
|
|
|
// Named parameters : Same name, other name in between DBAL-299 |
|
319
|
|
|
[ |
|
320
|
|
|
'SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)', |
|
321
|
|
|
['foo' => 2,'bar' => 3], |
|
322
|
|
|
['foo' => ParameterType::INTEGER,'bar' => ParameterType::INTEGER], |
|
323
|
|
|
'SELECT * FROM Foo WHERE (? = 2) AND (? = 3) AND (? = 2)', |
|
324
|
|
|
[2, 3, 2], |
|
325
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER], |
|
326
|
|
|
], |
|
327
|
|
|
// Named parameters : Empty "integer" array DDC-1978 |
|
328
|
|
|
[ |
|
329
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo)', |
|
330
|
|
|
['foo' => []], |
|
331
|
|
|
['foo' => Connection::PARAM_INT_ARRAY], |
|
332
|
|
|
'SELECT * FROM Foo WHERE foo IN (NULL)', |
|
333
|
|
|
[], |
|
334
|
|
|
[], |
|
335
|
|
|
], |
|
336
|
|
|
// Named parameters : Two empty "str" array DDC-1978 |
|
337
|
|
|
[ |
|
338
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)', |
|
339
|
|
|
['foo' => [], 'bar' => []], |
|
340
|
|
|
['foo' => Connection::PARAM_STR_ARRAY, 'bar' => Connection::PARAM_STR_ARRAY], |
|
341
|
|
|
'SELECT * FROM Foo WHERE foo IN (NULL) OR bar IN (NULL)', |
|
342
|
|
|
[], |
|
343
|
|
|
[], |
|
344
|
|
|
], |
|
345
|
|
|
[ |
|
346
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar OR baz = :baz', |
|
347
|
|
|
['foo' => [1, 2], 'bar' => 'bar', 'baz' => 'baz'], |
|
348
|
|
|
['foo' => Connection::PARAM_INT_ARRAY, 'baz' => 'string'], |
|
349
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ? OR baz = ?', |
|
350
|
|
|
[1, 2, 'bar', 'baz'], |
|
351
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING, 'string'], |
|
352
|
|
|
], |
|
353
|
|
|
[ |
|
354
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar', |
|
355
|
|
|
['foo' => [1, 2], 'bar' => 'bar'], |
|
356
|
|
|
['foo' => Connection::PARAM_INT_ARRAY], |
|
357
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?', |
|
358
|
|
|
[1, 2, 'bar'], |
|
359
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING], |
|
360
|
|
|
], |
|
361
|
|
|
// Params/types with colons |
|
362
|
|
|
[ |
|
363
|
|
|
'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar', |
|
364
|
|
|
[':foo' => 'foo', ':bar' => 'bar'], |
|
365
|
|
|
[':foo' => ParameterType::INTEGER], |
|
366
|
|
|
'SELECT * FROM Foo WHERE foo = ? OR bar = ?', |
|
367
|
|
|
['foo', 'bar'], |
|
368
|
|
|
[ParameterType::INTEGER, ParameterType::STRING], |
|
369
|
|
|
], |
|
370
|
|
|
[ |
|
371
|
|
|
'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar', |
|
372
|
|
|
[':foo' => 'foo', ':bar' => 'bar'], |
|
373
|
|
|
[':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER], |
|
374
|
|
|
'SELECT * FROM Foo WHERE foo = ? OR bar = ?', |
|
375
|
|
|
['foo', 'bar'], |
|
376
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER], |
|
377
|
|
|
], |
|
378
|
|
|
[ |
|
379
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar', |
|
380
|
|
|
[':foo' => [1, 2], ':bar' => 'bar'], |
|
381
|
|
|
['foo' => Connection::PARAM_INT_ARRAY], |
|
382
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?', |
|
383
|
|
|
[1, 2, 'bar'], |
|
384
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING], |
|
385
|
|
|
], |
|
386
|
|
|
[ |
|
387
|
|
|
'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar', |
|
388
|
|
|
['foo' => [1, 2], 'bar' => 'bar'], |
|
389
|
|
|
[':foo' => Connection::PARAM_INT_ARRAY], |
|
390
|
|
|
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?', |
|
391
|
|
|
[1, 2, 'bar'], |
|
392
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING], |
|
393
|
|
|
], |
|
394
|
|
|
// DBAL-522 - null valued parameters are not considered |
|
395
|
|
|
[ |
|
396
|
|
|
'INSERT INTO Foo (foo, bar) values (:foo, :bar)', |
|
397
|
|
|
['foo' => 1, 'bar' => null], |
|
398
|
|
|
[':foo' => ParameterType::INTEGER, ':bar' => ParameterType::NULL], |
|
399
|
|
|
'INSERT INTO Foo (foo, bar) values (?, ?)', |
|
400
|
|
|
[1, null], |
|
401
|
|
|
[ParameterType::INTEGER, ParameterType::NULL], |
|
402
|
|
|
], |
|
403
|
|
|
[ |
|
404
|
|
|
'INSERT INTO Foo (foo, bar) values (?, ?)', |
|
405
|
|
|
[1, null], |
|
406
|
|
|
[ParameterType::INTEGER, ParameterType::NULL], |
|
407
|
|
|
'INSERT INTO Foo (foo, bar) values (?, ?)', |
|
408
|
|
|
[1, null], |
|
409
|
|
|
[ParameterType::INTEGER, ParameterType::NULL], |
|
410
|
|
|
], |
|
411
|
|
|
// DBAL-1205 - Escaped single quotes SQL- and C-Style |
|
412
|
|
|
[ |
|
413
|
|
|
"SELECT * FROM Foo WHERE foo = :foo||''':not_a_param''\\'' OR bar = ''':not_a_param''\\'':bar", |
|
414
|
|
|
[':foo' => 1, ':bar' => 2], |
|
415
|
|
|
[':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER], |
|
416
|
|
|
'SELECT * FROM Foo WHERE foo = ?||\'\'\':not_a_param\'\'\\\'\' OR bar = \'\'\':not_a_param\'\'\\\'\'?', |
|
417
|
|
|
[1, 2], |
|
418
|
|
|
[ParameterType::INTEGER, ParameterType::INTEGER], |
|
419
|
|
|
], |
|
420
|
|
|
]; |
|
421
|
|
|
} |
|
422
|
|
|
|
|
423
|
|
|
/** |
|
424
|
|
|
* @param mixed[] $params |
|
425
|
|
|
* @param mixed[] $types |
|
426
|
|
|
* @param mixed[] $expectedParams |
|
427
|
|
|
* @param mixed[] $expectedTypes |
|
428
|
|
|
* |
|
429
|
|
|
* @dataProvider dataExpandListParameters |
|
430
|
|
|
*/ |
|
431
|
|
|
public function testExpandListParameters( |
|
432
|
|
|
string $query, |
|
433
|
|
|
array $params, |
|
434
|
|
|
array $types, |
|
435
|
|
|
string $expectedQuery, |
|
436
|
|
|
array $expectedParams, |
|
437
|
|
|
array $expectedTypes |
|
438
|
|
|
) : void { |
|
439
|
|
|
[$query, $params, $types] = SQLParserUtils::expandListParameters($query, $params, $types); |
|
440
|
|
|
|
|
441
|
|
|
self::assertEquals($expectedQuery, $query, 'Query was not rewritten correctly.'); |
|
442
|
|
|
self::assertEquals($expectedParams, $params, 'Params dont match'); |
|
443
|
|
|
self::assertEquals($expectedTypes, $types, 'Types dont match'); |
|
444
|
|
|
} |
|
445
|
|
|
|
|
446
|
|
|
/** |
|
447
|
|
|
* @return mixed[][] |
|
448
|
|
|
*/ |
|
449
|
|
|
public static function dataQueryWithMissingParameters() : iterable |
|
450
|
|
|
{ |
|
451
|
|
|
return [ |
|
452
|
|
|
[ |
|
453
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
454
|
|
|
['other' => 'val'], |
|
455
|
|
|
[], |
|
456
|
|
|
], |
|
457
|
|
|
[ |
|
458
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
459
|
|
|
[], |
|
460
|
|
|
[], |
|
461
|
|
|
], |
|
462
|
|
|
[ |
|
463
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
464
|
|
|
[], |
|
465
|
|
|
['param' => Connection::PARAM_INT_ARRAY], |
|
466
|
|
|
], |
|
467
|
|
|
[ |
|
468
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
469
|
|
|
[], |
|
470
|
|
|
[':param' => Connection::PARAM_INT_ARRAY], |
|
471
|
|
|
], |
|
472
|
|
|
[ |
|
473
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
474
|
|
|
[], |
|
475
|
|
|
['bar' => Connection::PARAM_INT_ARRAY], |
|
476
|
|
|
], |
|
477
|
|
|
[ |
|
478
|
|
|
'SELECT * FROM foo WHERE bar = :param', |
|
479
|
|
|
['bar' => 'value'], |
|
480
|
|
|
['bar' => Connection::PARAM_INT_ARRAY], |
|
481
|
|
|
], |
|
482
|
|
|
]; |
|
483
|
|
|
} |
|
484
|
|
|
|
|
485
|
|
|
/** |
|
486
|
|
|
* @param mixed[] $params |
|
487
|
|
|
* @param mixed[] $types |
|
488
|
|
|
* |
|
489
|
|
|
* @dataProvider dataQueryWithMissingParameters |
|
490
|
|
|
*/ |
|
491
|
|
|
public function testExceptionIsThrownForMissingParam(string $query, array $params, array $types = []) : void |
|
492
|
|
|
{ |
|
493
|
|
|
$this->expectException(SQLParserUtilsException::class); |
|
494
|
|
|
$this->expectExceptionMessage('Value for :param not found in params array. Params array key should be "param"'); |
|
495
|
|
|
|
|
496
|
|
|
SQLParserUtils::expandListParameters($query, $params, $types); |
|
497
|
|
|
} |
|
498
|
|
|
} |
|
499
|
|
|
|
doctrine /
dbal
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.