cube-js / cube-js/cube

PrestoDriver.prepareQueryWithParams uses MySQL-style escaping, breaks Trino/Presto with single quotes

Open
#10,545 0 comments 1 reaction 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## Bug Report

### Description

`PrestoDriver.prepareQueryWithParams` uses `sqlstring.escape()` which produces MySQL-style backslash escaping (`\'`) for single quotes. Trino and Presto use standard SQL escaping (`''`), causing queries with single quotes in filter values to fail.

### Steps to Reproduce

1. Use `@cubejs-backend/trino-driver` with a Trino data source
2. Send a query with a `contains` filter that includes a single quote:

```json
{
"dimensions": ["my_cube.ad_name"],
"measures": ["my_cube.total_ad_spend"],
"filters": [
{
"member": "my_cube.campaign_name",
"operator": "contains",
"values": ["maya'k"]
}
]
}
```

3. Query fails with:

```
Error: line 24:389: mismatched input 'k'. Expecting: '%', ')', '*', '+', ',', '-', '.', '/', 'AND', 'AT', 'OR', 'ORDER', '[', '||',
```

### Root Cause

In [`PrestoDriver.ts` (prepareQueryWithParams)](https://github.com/cube-js/cube/blob/master/packages/cubejs-prestodb-driver/src/PrestoDriver.ts):

```typescript
prepareQueryWithParams(query: string, values: unknown[]) {
return SqlString.format(query, (values || []).map(value => (typeof value === 'string' ? {
toSqlString: () => SqlString.escape(value).replace(/\\\\([_%])/g, '\\$1'),
} : value)));
}
```

`SqlString.escape()` (from the `sqlstring` npm package) produces MySQL-style escaping:
- Input: `maya'k`
- Output: `'maya\'k'`

Trino/Presto expects standard SQL escaping:
- Expected: `'maya''k'`

Since `TrinoDriver` extends `PrestoDriver` without overriding this method, both drivers are affected.

### Expected Behavior

Filter values containing single quotes should be escaped using standard SQL `''` escaping, producing valid Trino/Presto SQL.

### Proposed Solution

Override `prepareQueryWithParams` in a custom driver:

```javascript
public prepareQueryWithParams(query: string, values: unknown[]) {
return SqlString.format(query, (values || []).map(value => (typeof value === 'string' ? {
toSqlString: () => {
const escaped = value.replace(/'/g, "''");
return `'${escaped}'`;
},
} : value)));
}
```

### Version

- `@cubejs-backend/trino-driver`: 1.6.7 (also verified still present in 1.6.27)
- `@cubejs-backend/prestodb-driver`: same version
- Trino: 4xx+

### Additional Context

- PR #5529 fixed double-escaping of `_` and `%` in `contains`/`notContains` but did not address the `\'` vs `''` escaping for single quotes.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.