api-platform / api-platform/core

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

Open
#8,434 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
PHP
Stars
2.6k
Forks
980
Avg merge
2d 4h
Merged PRs (30d)
49

Description

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

Contributor guide

Open the contributing guide

Research direction

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.

Written by the indexing model from the issue text.

Assessment

Tech stack
php, sql, symfony
Domain
api, backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
62/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.