PrestoDriver.prepareQueryWithParams uses MySQL-style escaping, breaks Trino/Presto with single quotes
- 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
Assessment
This issue has not been assessed yet.