api-platform / api-platform/core

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

未关闭
#8,434 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看
主要语言
PHP
星标
2.6k
派生
980
平均合并
2 天 4 小时
30 天内合并 PR
49

描述

**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.

贡献指南

打开贡献指南

调研方向

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.

由索引模型根据 Issue 内容生成。

评估

技术栈
php, sql, symfony
领域
api, backend, database
Issue 类型
缺陷
难度
3/5
预计耗时
1-2 天
活跃度
冷清
描述清晰度
基本清楚
新手友好度
62/100

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。