api-platform / api-platform/core

PartialSearchFilter: ESCAPE '\' causes ORA-01425 on Oracle

Đang mở
#8,434 0 bình luận 0 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
PHP
Star
2.6k
Fork
980
Merge trung bình
2 ngày 4 giờ
Pull request đã merge (30 ngày)
49

Mô tả

**API Platform version(s) affected**: 4.3.10
(`api-platform/doctrine-orm` / `api-platform/symfony`)

**Description**
`ApiPlatform\Doctrine\Orm\Filter\PartialSearchFilter` generates SQL `LIKE` expressions with `ESCAPE '\'`.

On Oracle this fails with:

```text
ORA-01425: escape character must be character string of length 1
```

In the executed SQL the escape clause appears as `ESCAPE '\\'` / `ESCAPE '\\\\'` (depending on logging), which Oracle rejects because the escape character is not a single-character string.

This affects both case-sensitive and case-insensitive modes, since both append the same `ESCAPE '\'` clause.

**How to reproduce**
1. Use API Platform 4.3.x with Doctrine ORM on an **Oracle** database.
2. Configure a collection operation with `PartialSearchFilter`:

```php
use ApiPlatform\Doctrine\Orm\Filter\PartialSearchFilter;
use ApiPlatform\Metadata\GetCollection;
use ApiPlatform\Metadata\QueryParameter;

new GetCollection(
uriTemplate: '/items',
parameters: [
'name' => new QueryParameter(
filter: new PartialSearchFilter(),
property: 'name',
),
],
)
```

3. Call:

```http
GET /items?name=test
```

4. Observe the generated SQL containing something like:

```sql
LOWER(o.name) LIKE LOWER(:name_p1) ESCAPE '\\'
```

and Oracle raising `ORA-01425`.

Relevant code in `PartialSearchFilter`:

```php
$field.' LIKE :'.$parameterName.' ESCAPE \'\\\''
// and
'LOWER('.$field.') LIKE LOWER(:'.$parameterName.') ESCAPE \'\\\''
```

with:

```php
private function formatLikeValue(string $value): string
{
return '%'.addcslashes($value, '\\%_').'%';
}
```

**Possible Solution**
Make the escape character configurable (constructor option), and/or use a DB-agnostic escape character that Oracle accepts (e.g. `!`), updating both:

- the `ESCAPE '...'` SQL clause
- `formatLikeValue()` escaping of `%`, `_`, and the escape character itself

Example approach:

```php
public function __construct(
private readonly bool $caseSensitive = false,
private readonly string $escapeCharacter = '\\',
) {}
```

Then for Oracle consumers:

```php
new PartialSearchFilter(escapeCharacter: '!')
```

Alternatively, detect the database platform and choose a safe default escape character for Oracle.

**Additional Context**
- Database: Oracle
- Error: `Doctrine\DBAL\Exception\DriverException` wrapping `ORA-01425`
- Workaround used locally: custom filter based on `PartialSearchFilter`, replacing `\` with `!` as escape character in both the SQL `ESCAPE` clause and value escaping.
- Same issue likely impacts any environment where `ESCAPE '\'` is not treated as a single-character literal.

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Hướng nghiên cứu

Start by locating ApiPlatform\Doctrine\Orm\Filter\PartialSearchFilter and reading the two LIKE expressions plus formatLikeValue(). Check how the filter is constructed in api-platform/doctrine-orm and api-platform/symfony before choosing between a configurable escape character or Oracle-specific handling. Done means both case-sensitive modes generate an Oracle-accepted ESCAPE clause and escape %, _, and the escape character consistently.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
php, sql, symfony
Lĩnh vực
api, backend, database
Loại issue
Lỗi
Độ khó
3/5
Thời gian dự kiến
1-2 ngày
Mức độ hoạt động
Ít trao đổi
Độ rõ ràng
Khá rõ ràng
Mức phù hợp với người mới
62/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.